Search This Blog

Total Pageviews

Thursday 3 February 2011

Oracle datafile More then 20% disk I/O

SELECT
TO_CHAR(sn.end_interval_time,'yyyy-mm-dd HH24:MI:SS') end_interval_time,
new.filename file_name,
new.phywrts-old.phywrts writes
FROM dba_hist_filestatxs old, dba_hist_filestatxs new,
dba_hist_snapshot sn
WHERE
sn.snap_id = (select max(snap_id) from dba_hist_snapshot)
AND new.snap_id = sn.snap_id
AND old.snap_id = sn.snap_id-1
AND new.filename = old.filename
AND (new.phywrts-old.phywrts)*20 > (SELECT(newsnap.value-oldsnap.value) writes
FROM
dba_hist_sysstat oldsnap, dba_hist_sysstat newsnap, dba_hist_snapshot sn1
WHERE
sn.snap_id = sn1.snap_id
AND newsnap.snap_id = sn.snap_id
AND oldsnap.snap_id = sn.snap_id-1
AND oldsnap.stat_name = 'physical writes'
AND newsnap.stat_name = 'physical writes'
AND (newsnap.value-oldsnap.value) > 0);

No comments:

Oracle DBA

anuj blog Archive