RE: Oh Where Oh Where Is My Redo Coming From

John that doesn't make sense to me. Deleting 10,000 rows from a table with 200 columns is certainly going to generate more redo than a table with 1 column (which I am sure you know, so there must be some confusion in my understanding). The avg_row_len is going to be much bigger in the table with 200 columns. I think grabbing redo info out of sesstat is a great way to do this but the drawback is that I have to sit here and poll v$sesstat every "N" minutes trying to capture the session and SQL generating the redo. Not very practical in all circumstances.

What I wanted is a quick way to sit down at any database and get a rough guess as to the objects which likely are involved in creating the most redo. This can help me get pointing in the direction of tuning HR jobs, Finance jobs, Inventory jobs etc...pretty easy to go query V$SQLAREA for table and "INSERT", "UPDATE" "DELETE" at that point.

The query I posted shows that the bulk of redo is coming from single table in the JDE Oweworld schema. It is a lot more than I am use to seeing in other systems I manage from the same table so I am pretty sure they have some poor code someplace. I can get the # of jobs and how long they run from the job queue tables, F986110. This will help me identify the top 5 jobs that hit the system. From there I will focus on the one or two jobs that hit the F0911 table.

This approach to tuning is more of a top down approach. I don't want to try focusing on a single SQL statement/session as a starting point, the batch jobs will be my starting point. This information combined with the top "N" jobs will put me right where I want to be to begin making the biggest impact with the least amount of effort.

• Ethan

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

Ethan,

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

Note that the amount of redo does not depend on the average row size. It depends on the amount of _change_ (+ some overhead). This argument might skew the situation towards a table that has a large row size but that does not have that many updates...

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

What you _do_ need to do is to use this SQL to detect the SIDs performing redo:

select sid, name, value
from v$statname n, v$sesstat v
where v.statistic# = n.statistic#
and name like 'redo size'
and value > 100000
order by value desc

You can then look at V$OPEN_CURSORS for those SIDs...

John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

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