Re: log_buffer size in Oracle 10g Rel.2

  • From: Riyaj Shamsudeen <rshamsud@xxxxxxxxxxxx>
  • To: Paul Drake <bdbafh@xxxxxxxxx>
  • Date: Wed, 09 Aug 2006 10:18:50 -0500

Paul

Assumptions??? Hope not. Could you please clarify what my assumptions are ?

Yes, I am aware of private /shared redo threads in 10g. Redo threads specifically pertains to space allocation for redo generation. You could see # of redo allocation latches nearly equal to # of private threads active currently. For example, here is a line from alert log.

Private_strands 303 at log switch

Query to the database for # of latch children for redo allocation latches, which is acquired to allocate space for redo ( 10gR1, Sun platform).

select name, count(*) from v$latch_children
where name='redo allocation'
group by name
/

NAME                                                 COUNT(*)
-------------------------------------------------- ----------
redo allocation                                           305

I am _assuming_ here that 2 additional redo allocation latches are for shared threads and docid 3072557.1 seems to suggest that too, but not so explicitly.

Coming back to my original posting, these private redo threads are for allocating and copying in to log buffer. Still, foreground process must post LGWR and wait during commits. LGWR wakes up [trussed LGWR], acquire relevant latches [ Don't know all the latches acquired and their sequence ] and write the log buffer contents to log file. I am yet to see a contrary test case, that suggests that LGWR isn't woken up for commit or LGWR doesn't wakeup after 1M full. I don't have 10gR2 handy, but might worth testing this again. If you have a test case or Oracle document, I would like to learn that.

**Basic premise of my posting is that there is no need tweaking these parameters unless there is an excessive amount of redo generation and commits. We don't know how much time is spent waiting for these events by these foreground processes. We can not possibly recommend to set an undocumented parameter. 

Thanks

Riyaj Shamsudeen

Paul Drake wrote:
On 8/9/06, *Riyaj Shamsudeen* <rshamsud@xxxxxxxxxxxx <mailto:rshamsud@xxxxxxxxxxxx>> wrote:

    Syed

    >>We are oftenly getting 'log file sync' and 'log file parallel wait'
    wait event.
    How much time is spent on these waits by the foreground processes
    ? Can
    you post top 5 wait event section from statspack report or AWR
    report ?
    Are you trying to tune a specific process or instance wide tuning ?

    >>I feel, 16M is very big value for log_buffer.
    That is not an universally true statement to make. You can have
    100M log
    buffer and still have stellar performance. Log buffer size must be
    determined in conjunction with redo generation rate and commit rate.

    >>redo_buffer can't be resized, as it depends on no. of datafiles.
    Would you please post the document ID so that we can review this,
    please
    ? This doesn't sound correct. I don't know why log_buffer size
    would be
    dependent upon # of datafiles.

>>The _log_io_size is 0. Can we play with this hidden parameter to
bring
>> back the redo_buffer size to 3M?
LGWR flushes the log buffer when there is more than 1M to write or
1/3rd
full. So, any value for _log_io_size above 1M is meaningless. 


Riyaj,

Your assumptions may have been invalidated by 10g R2, as that is the version that the poster stated in the header.

http://www.orafaq.com/usenet/comp.databases.oracle.server/2005/10/27/1581.htm

I have nothing further to say on this, but likely have to include additional material to not get flagged by the lameness filter.

Paul


    But, if
    you set it too low, then LGWR might be overactive introducing latch
    contention issues. Unless commit rate is excessive in your application
    or an I/O problem with LGWR, there is no need to tweak these
    parameters.

    In essence, does statspack report/AWR report /sqltrace shows that
    these
    waits are excessive and must be tuned ?

    Thanks
    Riyaj

    Syed Jaffar Hussain wrote:
    > Hello List,
    >
    > We have recently upgrade our 9i Rel.2 database to 10g Rel.2, on
    AIX 5L
    > OS.
    > We are oftenly getting 'log file sync' and 'log file parallel wait'
    > wait event.
    > In 9i, we have solved this by enabling CIO to the filesystem.
    > Surprisingly, the log_buffer size is set to 16M, by default.
    > We tried to set multiple value, but, by default it takes 16M.
    > I feel, 16M is very big value for log_buffer.
    > When I go and search in the metalink, it says, its a bug and
    > redo_buffer can't be resized, as it depends on no. of datafiles.
    > Our server has 16cpus and 160 datafiles.
    > The _log_io_size is 0. Can we play with this hidden parameter to
    bring
    > back the redo_buffer size to 3M?
    >




The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material.  If the reader of this message is not the intended recipient,
you are hereby notified that your access is unauthorized, and any review,
dissemination, distribution or copying of this message including any
attachments is strictly prohibited.   If you are not the intended
recipient, please contact the sender and delete the material from any
computer.

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 08-2006