--
-- Redo generation top by object from AWR history
-- Usage: SQL> @redogen_obj_hist "03-Sep-13 16:00" "03-Sep-13 17:00" 3500000
--
set echo off feedback off heading on timi off pages 1000 lines 500 VERIFY OFF
col WHEN for a20
col object_name for a30
select * from (
SELECT to_char(begin_interval_time,'YY-MM-DD HH24:MI') as WHEN,
dhso.object_name,
sum(db_block_changes_delta) as db_block_changes,
to_char(round((RATIO_TO_REPORT(sum(db_block_changes_delta)) OVER ())*100,2),'99.00') as PERCENT
FROM dba_hist_seg_stat dhss,
dba_hist_seg_stat_obj dhso,
dba_hist_snapshot dhs
WHERE dhs.snap_id = dhss.snap_id
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhso.obj#
AND dhss.dataobj# = dhso.dataobj#
AND begin_interval_time BETWEEN to_date('&1', 'DD-Mon-YY HH24:MI')
AND to_date('&2', 'DD-Mon-YY HH24:MI')
GROUP BY to_char(begin_interval_time,'YY-MM-DD HH24:MI'),
dhso.object_name
ORDER BY to_char(begin_interval_time,'YY-MM-DD HH24:MI'),
db_block_changes desc
) where rownum <= &&3
/
set feedback on echo off VERIFY ON
Anúncios