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