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

/

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

/