Oracle-L: RE: Oh Where Oh Where Is My Redo Coming From

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Fri, 21 Feb 2003 08:24:39 -0800
Message-ID: <F001.005548F5.20030221082439@fatcity.com>

Ethan - 28-gig GAAAK!
Ideas:

Sample your SQL buffer to start getting some ideas.
Use LogMiner to read some of the archive logs to see the DML statements. You can also directly see how much redo is being generated by each statement. I think you have the right idea, probably some inefficient updates.

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Friday, February 21, 2003 9:44 AM
To: Multiple recipients of list ORACLE-L

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: Post, Ethan INET: Ethan.Post_at_ps.net

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

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: DENNIS WILLIAMS
INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------