Question:
How can I calculate my Oracle I/O per
second? I know that stats$sysstat
can compute total I/O, but I need to calculate my I/O per second
using the AWR tables.
Answer: You can use the
dba_hist_sysstat table to compute I/O per second (IOPS).
If you don't want to write your own I/O per
second script, see the
Oracle script
collection.
This script will calculate Oracle I/O throughput in
I/O per second (IOPS):
select
to_char(sn.end_interval_time,'yyyymmddhh24') the_date,
sum(decode(sn2.startup_time,sn3.startup_time,(newreads.value-
oldreads.value),newreads.value)) reads,
sum(decode(sn2.startup_time,sn3.startup_time,(newwrites.value-
oldwrites.value),newwrites.value)) writes,
(sum(decode(sn2.startup_time,sn3.startup_time,(newreads.value-
oldreads.value),newreads.value)))+
(sum(decode(sn2.startup_time,sn3.startup_time,(newwrites.value-
oldwrites.value),newwrites.value)))
total
from
dba_hist_sysstat oldreads,
dba_hist_sysstat newreads,
dba_hist_sysstat oldwrites,
dba_hist_sysstat
newwrites,
dba_hist_snapshot sn,
dba_hist_snapshot sn2,
dba_hist_snapshot sn3
where
see Code Depot for Full Script
group by
to_char(sn.end_interval_time,'yyyymmddhh24')
order by
to_char(sn.end_interval_time,'yyyymmddhh24')
;
 |
|
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.
|
