Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Pradeep.

Asked: November 13, 2017 - 3:58 pm UTC

Last updated: November 14, 2017 - 2:11 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Dear Sir,

How can I found which object generate more redo in database.

Thanks
Pradeep

and Connor said...

It is a bit of a vague relationship. For example, if I update a table and it has 100 indexes - do you call that "table" redo ? or do you call "index" redo?

In any event, on the assumption that you are trying to track down heavy consumers, two places to start:

v$segment_statistics - look for high values of 'db block changes'
v$sqlstats - look for high values of 'rows_processed' where the SQL is not a SELECT statement


Rating

  (2 ratings)

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

Comments

Sunny Shah, November 14, 2017 - 6:49 am UTC

select a.* from v$segment_statistics a where a.STATISTIC_NAME = 'db block changes' and a.OWNER='SVM' order by a.VALUE desc ;

select * from v$sqlstats b where upper(b.SQL_TEXT) like 'INSERT %' or upper(b.SQL_TEXT) like 'DELETE %' or upper(b.SQL_TEXT) like 'UPDATE %'
order by b.ROWS_PROCESSED desc ;

Thanks

Pradeep prajapati, November 14, 2017 - 12:47 pm UTC

Hello sir,

Thanks for revert ..will use ur suggestion.

Thanks
Pradeep
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

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database