Re: Oh Where Oh Where Is My Redo Coming From

Ethan,

    v$sess_io will provide a list of the sessions generating block changes and, therefore, redo. Link this back to v$session, etc. for the 'offending' sessions, sql. I've used this several times with great success. Sometimes it was a data load, others it was bad sql.

    When you change a block (insert/update/delete), you also generate changes to indexes, undo segments (1 change for each row and 1 change for each index affected) and perhaps the data dictionary for space management.

Dan Fink
Post, Ethan wrote:

>Just had a thought here, have not tried it yet. I have a database that I am
>working with that is generating 28 GB of redo each day. I would really like
>to know what objects are generating all this redo without going through the
>hassle of mining a bunch of log files. It occurred to me that if table
>monitoring is active and my stats are up to date I should be able to
>multiply the total number of updates, inserts and commits by the average row
>size and get a rough % of what objects are generating the most redo.
>
>I am sure there are a number of other factors I need to consider, any ideas
>what they are?
>
>* Should I weight inserts, updates and deletes?
>* ??
>
>The goal is to identify the objects, then identify the jobs that work on
>those objects and see if I can reduce redo. I suspect a lot of this redo is
>being generated because of some poor design issues.
>
>Thanks!
>
>- Ethan
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Daniel W. Fink
  INET: optimaldba_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).