When trying to get the information of redo generated per SQL statement, I came to the conclusion that there is no easy link between amount of redo generated and SQL statement as, apparently,Oracle doesn’t keep the information at statement level but only at session level (what to do if the session is persistent and never disconnect ?).

Best we can do is is using V$SESSTAT and continuously selecting into it to get those SQL statements, but again it will not be per SQL statement but for the session. It gives anyway interesting information, so sharing the SQL code I’ve written for it:

SELECT
s.sid,
s.username,
s.program,
ROUND(t.VALUE/(1024*1024)) AS "Redo Size MB",
sa.sql_text
FROM V$SESSION s, V$SESSTAT t, V$STATNAME sn, V$SQLAREA sa
WHERE s.sid = t.sid
AND t.statistic# = sn.statistic#
AND sn.name = 'redo size'
AND sa.sql_id = s.sql_id
AND ROUND(t.VALUE/(1024*1024)) != 0
ORDER BY t.VALUE DESC;

You can also find an interest on the ones that are the highs redo generation rate. For this create first a snapshot of V$SESSTAT in a temporary table:

DROP TABLE v$sesstat_snap;
 
CREATE global TEMPORARY TABLE v$sesstat_snap
ON COMMIT preserve ROWS
AS SELECT t.*
FROM v$SESSTAT t, V$STATNAME sn
WHERE t.statistic# = sn.statistic#
AND sn.name = 'redo size';

Remark:
Do not forget the on commit preserve rows unless the table is created empty. If you get the ORA-14452: attempt to create, alter or drop an index on temporary table already in use when trying to drop the table please logoff/logon, table is persistent at session level…

Then a moment later issue continuously the following SQL statement (limit is above 1MB which avoid to see your own session in the list):

SET lines 130
SET pages 45
col SQL_TEXT FOR a55 wrapped
col program FOR a30
col username FOR a10
 
SELECT
s.sid,
s.username,
s.program,
ROUND((t.value-snap.VALUE)/(1024*1024)) AS "Redo Size MB",
sa.sql_text
FROM V$SESSION s, V$SESSTAT t, V$STATNAME sn, V$SQLAREA sa, V$SESSTAT_SNAP snap
WHERE s.sid = t.sid
AND snap.sid = s.sid
AND (t.value-snap.VALUE) > (1024*1024)
AND t.statistic# = sn.statistic#
AND sn.name = 'redo size'
AND sa.sql_id = s.sql_id
ORDER BY (t.value-snap.VALUE) DESC;

Remark:
When you keep using the above SQL please note that session that generated redo information and that have disconnected are no more listed…

About Post Author

Yannick Jaquier

administrator

Find more about me on social media.

See author's posts