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;
