Re: Redo latch contention

Rajesh:

I'm a bit confused here. You say that you are going to tune your redo allocation latch contention, but, based on the output presented, you don't have any redo allocation latch contention. The threshold to determine contention is misses/gets >= .01 or
immediate_misses/(immediate_gets+immediate_misses) >= .01. You do seem to have a problem with redo copy latches, but not redo allocation latches. So, I wouldn't mess with the log_small_entry_max_size parameter. Besides, in 8i, to tune this parameter you would either (1) use the NOLOGGING option to reduce the amount of redo log entries for certain operations or (2) reduce the load on the latch by increasing the LOG_BUFFER parameter or decreasing _LOG_IO_SIZE. So, you really need to be worried about tuning the redo copy latch waits. To do that, Oracle recommends increasing _LOG_SIMULTANEOUS_COPIES to have more latches available. But this is on multiple CPU machines and is limited to 32. Since, under 8i, a redo copy latch is now required for all redo generation, not just that which is larger than log_small_entry_max_size, and it is held longer until the redo has been applied to the target buffers, one would expect there to be more redo copy latch contention. Based on what I have read, it seems you may also want to check for log file syncs by DBWn and tune down checkpointing if appropriate. You may also want to look at the commit rate in your application to see if that can be reasonably reduced.

Hope this helps!

-- 

Jon Walthour, OCDBA
Oracle DBA
Computer Horizons
Cincinnati, Ohio


 



> From: Rajesh Dayal <Rajesh_at_ohitelecom.com>

> Organization: Fat City Network Services, San Diego, California

> Reply-To: ORACLE-L_at_fatcity.com

> Date: Sat, 16 Jun 2001 00:25:42 -0800

> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>

> Subject: Redo latch contention

> 

> Hi All,

> I had some situation of Redo Allocation and copy latch

> contention as stated in following output.....

> 

> SQL> SELECT substr(NAME,1,18) NAME, GETS,MISSES, IMMEDIATE_GETS,

> IMMEDIATE_MISSES

> FROM V$LATCH WHERE NAME LIKE '%redo%'

> /

> 

> NAME                     GETS     MISSES IMMEDIATE_GETS IMMEDIATE_MISSES

> 

> ------------------ ---------- ---------- -------------- ----------------

> 

> redo allocation         74878         16              0                0

> 

> redo copy                 114        100          53756              232

> 

> redo writing            30219          1              0                0

> 

> 

> 3 rows selected.

> 

> Realizing small contention on redo allocation latch, I increased

> the value of "log_small_entry_max_size" from 80 to 90.

> But this would definitely overload (already suffering) redo copy

> latches, so I increased the value of log_simultaneous_copies from 2 to

> 6. 

> This sorted out redo latch contention, but somewhere in FM it's

> mentioned that value of log_simultaneous_copies shouldn't be more than

> (2 * #_of_CPUs). Again I know that the CPU is "not" heavily used so far.

> So... 

> 

> 1. Is it OK to set log_simultaneous_copies higher than 2*CPU. What are

> the golden rules. I have seen some authers not mentioning this.

> 

> 2. Why this parameter is missing from Oracle 8i?? Has Oracle changed the

> algorithm?? What is the new strategy to handle redo latch contention??

> 

> Interestingly, in Oracle 8i (Oracle 8.1.6) Tuning Manuals they

> still talk of these parameters(which are made obsolete)...

> 

> Appreciate your inputs ;-)

> 

> Cheers,

> Rajesh 

> -- 

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

> -- 

> Author: Rajesh Dayal

> INET: Rajesh_at_ohitelecom.com

> 

> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

> San Diego, California        -- Public Internet access / Mailing Lists

> --------------------------------------------------------------------

> 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.com
-- 
Author: Jon Walthour
  INET: jwalthour_at_mac.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).