Home » RDBMS Server » Performance Tuning » redo buffer allocation retries not near 0, how do I set _log_parallelism_max (11.2.0.3 SE, SLES 11 SP3)
redo buffer allocation retries not near 0, how do I set _log_parallelism_max [message #631039] Thu, 08 January 2015 04:41 Go to next message
juniordbanewbie
Messages: 250
Registered: April 2014
Senior Member

SYS@RAC>SELECT name, value FROM v$sysstat WHERE name ='redo buffer allocation re
tries';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo buffer allocation retries                                        26603


from Tuning the Redolog Buffer Cache and Resolving Redo Latch Contention (Doc ID 147471.1)

Quote:


The value of redo buffer
allocation retries should be near zero over an interval. If this value increments
consistently, then processes have had to wait for space in the redo log buffer.
The wait can be caused by the log buffer being too small or by checkpointing.
Increase the size of the redo log buffer, if necessary, by changing the value of
the initialization parameter LOG_BUFFER. The value of this parameter is expressed
in bytes.

In 11.2, there is also Bug 13520743 - HIGH REDO LATCH CONTENTION ON 'REDO ALLOCATION', closed as not a bug:
The following parameter is used to reduce Redo Allocation contention: "_log_parallelism_max".
The bug instructs to set _log_parallelism_max values of 4,5,6 and measure the improvement based on AWR reports.


when I read bug 13520743 , there's no instruction as to how to set _log_parallelism_max

I have done some query as mentioned in MOS 147471.1


1  SELECT  substr(ln.name, 1, 20), gets, misses, immediate_gets, immediate_mis
ses
  2  FROM v$latch l, v$latchname ln
  3  WHERE   ln.name in ('redo allocation', 'redo copy')
  4*                 and ln.latch# = l.latch#
SYS@RAC>/

SUBSTR(LN.NAME,1,20)
--------------------------------------------------------------------------------

      GETS     MISSES IMMEDIATE_GETS IMMEDIATE_MISSES
---------- ---------- -------------- ----------------
redo copy
     27298          0       71398089            30925

redo allocation
  82578913      12330              0                0


It's quite obvious the ratio of misses to gets is less than 1 percent. so if that's the case why did system statistics redo buffer allocation retries tell a different story? If it is a bug and I need to set _log_parallelism_max, how do I set it

Do I need to tune the redo log based on the fact that redo buffer allocation retries is not near to zero?


thanks a lot!
Re: redo buffer allocation retries not near 0, how do I set _log_parallelism_max [message #631040 is a reply to message #631039] Thu, 08 January 2015 04:49 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You need to start by asking "is there actually a problem?" I do not think that your users are telephoning you to complain that "there are too many buffer allocation retries". What are they complaining about?

That having been said, the value of of that counter in v$sysstat is meaningless, because it is accumulated since the instance started. You need to use a tool such as statspack (or AWR reports if you have the licence) that will show you the figures over a defined time frame. THen you can extract some useful information.
Re: redo buffer allocation retries not near 0, how do I set _log_parallelism_max [message #631049 is a reply to message #631039] Thu, 08 January 2015 06:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In addition, do not set or try to set any underscore parameter.
Do it only if Oracle support tells you to do so.

Re: redo buffer allocation retries not near 0, how do I set _log_parallelism_max [message #631083 is a reply to message #631049] Thu, 08 January 2015 22:59 Go to previous messageGo to next message
juniordbanewbie
Messages: 250
Registered: April 2014
Senior Member
there's no problem. I come across this discovery because I was writing a documentation on performance tuning.


This is Standard Edition, when I run the AWR report, the report showed no instance activity stats. I've countered check http://docs.oracle.com/cd/E11882_01/license.112/e47877/editions.htm#DBLIC116

Quote:


Oracle Diagnostic Pack N N Y
=>only EE have this feature


since u said counter in v$sysstat is useless, and dba_hist_sysstat is not populated in SE, I guess it is required for us to collect statistics and populate into tables that are same in structure as those in dba_hist_ views every hour right?

from http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_4061.htm#REFRN23453

Quote:


DBA_HIST_SYSSTAT displays historical system statistics information. This view contains snapshots of V$SYSSTAT.



Others if we don't create those tables, how are we going to do meaningful performance troubleshooting?


thanks a lot!
Re: redo buffer allocation retries not near 0, how do I set _log_parallelism_max [message #631095 is a reply to message #631083] Fri, 09 January 2015 00:34 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Use StatsPack.

Previous Topic: max and min function taking lot of time
Next Topic: advice to rewrite a query with a bad desing
Goto Forum:
  


Current Time: Thu Mar 28 09:33:00 CDT 2024