Analyzing Redo Generation by Objects and SQL Using AWR Snapshots

 Analyzing Redo Generation by Objects and SQL Using AWR Snapshots

1. Redo Logs Generated by Objects in a Time Range

-- Shows objects generating the most redo (block changes) during a given time period

SELECT 

    TO_CHAR(begin_interval_time, 'YYYY_MM_DD HH24:MI') AS snap_time,

    dhsso.object_name,

    SUM(db_block_changes_delta) AS redo_blocks

FROM 

    dba_hist_seg_stat dhss,

    dba_hist_seg_stat_obj dhsso,

    dba_hist_snapshot dhs

WHERE 

    dhs.snap_id = dhss.snap_id

    AND dhs.instance_number = dhss.instance_number

    AND dhss.obj# = dhsso.obj#

    AND dhss.dataobj# = dhsso.dataobj#

    AND begin_interval_time BETWEEN 

        TO_DATE('2023_07_06 01', 'YYYY_MM_DD HH24') AND 

        TO_DATE('2023_07_06 07', 'YYYY_MM_DD HH24')

GROUP BY 

    TO_CHAR(begin_interval_time, 'YYYY_MM_DD HH24:MI'),

    dhsso.object_name;

2. Redo Logs Generated by a Specific Object Over Time

-- Shows how much redo a specific object (by name) generated across AWR snapshots

SELECT 

    TO_CHAR(begin_interval_time, 'YYYY_MM_DD HH24:MI') AS snap_time,

    SUM(db_block_changes_delta) AS redo_blocks

FROM 

    dba_hist_seg_stat dhss,

    dba_hist_seg_stat_obj dhsso,

    dba_hist_snapshot dhs

WHERE 

    dhs.snap_id = dhss.snap_id

    AND dhs.instance_number = dhss.instance_number

    AND dhss.obj# = dhsso.obj#

    AND dhss.dataobj# = dhsso.dataobj#

    AND dhsso.object_name = 'XDMC_MS_MRP_IBP_DATA'   -- Replace with your object name

GROUP BY 

    TO_CHAR(begin_interval_time, 'YYYY_MM_DD HH24:MI');

3. SQLs Involving Specific Object That Generated Redo

-- Identify SQLs that involved a given object and may have generated redo

SELECT 

    TO_CHAR(begin_interval_time, 'YYYY_MM_DD HH24:MI') AS snap_time,

    DBMS_LOB.SUBSTR(sql_text, 4000, 1) AS sql_text,

    dhss.instance_number,

    dhss.sql_id,

    executions_delta,

    rows_processed_delta

FROM 

    dba_hist_sqlstat dhss,

    dba_hist_snapshot dhs,

    dba_hist_sqltext dhst

WHERE 

    UPPER(dhst.sql_text) LIKE '%XDMC_MS_MRP_IBP_DATA%'  -- Replace with object name

    AND dhss.snap_id = dhs.snap_id

    AND dhss.instance_number = dhs.instance_number

    AND dhss.sql_id = dhst.sql_id;

4. Snap ID and Runtime Info for a Given SQL ID

-- Get detailed runtime history for a specific SQL_ID using AWR

SELECT 

    a.instance_number AS inst_id,

    a.snap_id,

    a.plan_hash_value,

    TO_CHAR(begin_interval_time, 'DD-MON-YY HH24:MI') AS btime,

    ABS(EXTRACT(MINUTE FROM (end_interval_time - begin_interval_time)) + 

        EXTRACT(HOUR FROM (end_interval_time - begin_interval_time)) * 60 +

        EXTRACT(DAY FROM (end_interval_time - begin_interval_time)) * 24 * 60) AS minutes,

    executions_delta AS executions,

    ROUND(ELAPSED_TIME_delta / 1000000 / GREATEST(executions_delta, 1), 4) AS "avg duration (sec)"

FROM 

    dba_hist_sqlstat a,

    dba_hist_snapshot b

WHERE 

    a.sql_id = '&sql_id'

    AND a.snap_id = b.snap_id

    AND a.instance_number = b.instance_number

ORDER BY 

    snap_id DESC,

    a.instance_number;