Question: Have you ever seen or written a query like this?
select round(io/(select sysdate-startup_time p
from v$instance),1) as "Gb/day since instance startup",name from (
select sum(value)*8192/1024/1024/1024 io,name from v$sysstat where
name in ('physical reads','physical writes') group by name) union
(select round(sysdate-startup_time,1), 'days since startup'
from v$instance) union (select
round(sum(bytes)/1024/1024/1024,1), 'Gb db size' from ( select
sum(bytes) bytes from v$datafile union select sum(bytes) from
v$log)) order by 2
It compares the size of the DB with the I/O (in Gb) per day since
instance startup.
46.7 Gb db size
60.8 days since startup
68 physical reads
2.3 physical writes
Have you ever seen or written a query like this?
Answer: Yes, I have seen queries like these, they
give me brain cramps! This mish-mosh of SQL is so complex that
Einstein would have trouble understand it, and it is it very
difficult to evaluate if it's accurate, with all of the nesting of
SQL statements!
Your query is a perfect example of hard to maintain SQL.
Whoever wrote it was trying to take a simple query language and turn
it into a full-blown program! Above I have tried to format
below, with with all of the queries within queries, it's quite
difficult to understand it without all of the nesting:
select
round(io/(
select
sysdate-startup_time p
from
v$instance),1) as "Gb/day
since instance startup",
name
from
(select
sum(value)*8192/1024/1024/1024 io,
name
from
v$sysstat
where name in ('physical
reads','physical writes') group by name)
union
(select
round(sysdate-startup_time,1),
'days since startup'
from
v$instance)
union
(select
round(sum(bytes)/1024/1024/1024,1), 'Gb db
size'
from
(select
sum(bytes) bytes
from
v$datafile
union
select sum(bytes) from v$log)) order by 2
If I were writing this, I would make it four distinct SQL
statements using an in-line view, something like this:
select
--******************************
-- Query 1
--
****************************
(select
round(sysdate-startup_time,1), 'days since
startup'
from
v$instance
as
days_since_startup)
-- *******************
-- Query 2
--
****************************
(select
round(sum(bytes)/1024/1024/1024,1), 'Gb
db size'
from
(select
sum(bytes) bytes
from
v$datafile
union
select sum(bytes) from v$log)) order by 2
as
database_size_in_gig)
--******************************
-- Query 3
--
****************************
(select
sum(value)*8192/1024/1024/1024 io,
name
from
v$sysstat
where
name in ('physical reads','physical writes')
group by
name as
total_reads_n_writes)
from from
dual;
Rather than write my own code for this, I would use pre-written
and tested Oracle scripts from my
Oracle Script Download with
over 600 pre-tested scripts!
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
