Show Historical Redo Usage

Show the Object Name that Created the Most Redo for the Time Period Specified

--
-- Show the Object Name that Created the Most Redo for the Time Period Specified
--
 
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
SELECT dhso.object_name, SUM (db_block_changes_delta)
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 ('2022-03-05 14','YYYY-MM-DD HH24')
AND 
TO_DATE ('2022-03-05 22','YYYY-MM-DD HH24')
GROUP BY dhso.object_name
ORDER BY SUM (db_block_changes_delta) DESC
/


Clipboard IE Only

Show the Queries Running for the Given Time Period Specified Using the Object Name from Above Query

--
-- Show the Queries Running for the Given Time Period Specified Using the Object Name from Above
--
 
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
SELECT  dhss.sql_id,dhss.module,dhss.action,begin_interval_time,dbms_lob.substr(sql_text,4000,1)
FROM dba_hist_sqlstat dhss,
dba_hist_snapshot dhs,
dba_hist_sqltext dhst
WHERE upper(dhst.sql_text) LIKE '%LIVE_SUMMARY%'
AND dhss.snap_id=dhs.snap_id
AND dhss.instance_Number=dhs.instance_number
AND begin_interval_time BETWEEN to_date('2022_03_05 04','YYYY_MM_DD HH24') AND to_date('2022_03_05 05','YYYY_MM_DD HH24')
AND dhss.sql_id = dhst.sql_id and rownum<2
/


Clipboard IE Only


Published 1st August 2021

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License