Skip to Main Content
  • Questions
  • how can I find the session(s) which generated so much undo and redo data?


Question and Answer

Connor McDonald

Thanks for the question, Andrea.

Asked: February 14, 2012 - 1:55 am UTC

Last updated: January 28, 2022 - 2:41 am UTC

Version: 11.2.0

Viewed 1000+ times

You Asked

Hi Tom,

I am trying to find out how to monitor UNDO generation and REDO generation.

On some databases I noticed some dangeros peak in undo generation and redo generation and I need to find out the culprit.
Unfortunatly AWR and STATSPACK do not help me: the "top sessions" are read-only sessions and they do not provide information about undo generation.

Is there some way to find out which transactions generated "a lot of" undo and which sessions they belong to ?

Since undo generation and redo generation are quite correlated, is there some way to find out which sqls generated "a lot of" redo and which sessions they belong to ?



and Tom said...

you could use

to alert yourself when someone starts generate a lot of redo.

you could look for sql processing large numbers of rows (or small numbers of rows A LOT)

you could mine the redo

You could code a logoff trigger to capture the sid/serial# and whatever v$mystat metrics you wanted to capture


  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment


finding the user session (and the schema objects) generating huge redo

Morgan, January 26, 2022 - 8:10 am UTC

Greetings !

Could you please show how to get the metrics from AWR or internal views on the details of service name along with user session/SQL statements generating huge amount of redo.

Database is on


Connor McDonald
January 28, 2022 - 2:41 am UTC

Some places to look

v$segment_statistics will show "db block changes" for every object
v$sqlstats - "rows processed" for insert/update/delete
v$sessstat - "redo size" for every session
dba_audit_trail (if you have auditing) show logical reads/writes

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library