Skip to Main Content
  • Questions

  • how can I find the session(s) which generated so much undo and redo data?

Breadcrumb

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 ?

Thanks,

Andrea

and Tom said...

you could use

http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_server_alert.htm#ARPLS67991

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)

http://docs.oracle.com/cd/E11882_01/server.112/e25513/statviews_4052.htm#I1023447


you could mine the redo

http://docs.oracle.com/cd/E11882_01/server.112/e22490/logminer.htm#SUTIL019



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


Rating

  (1 rating)

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

Comments

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 12.1.0.2.

Thanks!


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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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