FAQ
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?

--
Best Regards,
Syed Jaffar Hussain
8i,9i & 10g OCP DBA
Banque Saudi Fransi,
Saudi Arabia

I blog at :http://jaffardba.blogspot.com/
http://www.oracle.com/technology/community/oracle_ace/ace1.html#hussain
----------------------------------------------------------------------------------
"Winners don't do different things. They do things differently."
--
http://www.freelists.org/webpage/oracle-l

Search Discussions

  • Riyaj Shamsudeen at Aug 9, 2006 at 1:29 pm
    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. 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.
  • Paul Drake at Aug 9, 2006 at 2:16 pm

    On 8/9/06, Riyaj Shamsudeen 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?
    --
    http://www.freelists.org/webpage/oracle-l
  • Riyaj Shamsudeen at Aug 9, 2006 at 3:18 pm
    ('binary' encoding is not supported, stored as-is)

    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*
    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.
  • Dba1 mcc at Aug 9, 2006 at 5:50 pm
    This statement:

    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.

    used to correct, but I don't think ORACLE 10G follow
    it any more.

    I saw ORACLE document say something relate to redo log
    buffer and flashback recovery. FYI:

    for large production databases, it is revommended that
    you set the LOG_BUFFER initialization parameter to at
    least 8 MB.

    Riyaj Shamsudeen 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. 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.
    Do You Yahoo!?
    Tired of spam? Yahoo! Mail has the best spam protection around
    http://mail.yahoo.com

    --
    http://www.freelists.org/webpage/oracle-l
  • Syed Jaffar Hussain at Aug 9, 2006 at 2:04 pm
    We have migrated on the same server. Therefore, it was the same server
    used for Oracle 9i and we solved it by enabling CIO to the
    filesystems. For 10g also, CIO is enabled for all filesystems on the
    server.

    Siraj,

    Please refer metalink note : 351857.1 Bug 4930608.
    On 8/9/06, Kurth, Michael J. wrote:
    These waits can also be caused by performance problems on your I/O
    Subsystem. Oracle needs to write to the online redo logs very
    frequently,
    And if it can't, it will log waits like what you are seeing.


    Mike Kurth
    Senior Oracle DBA
    Wheaton Franciscan Healthcare
    414/465-4046 Phone
    414/465-4080 Fax
    Michael.Kurth_at_wfhc.org


    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Riyaj Shamsudeen
    Sent: Wednesday, August 09, 2006 8:30 AM
    To: sjaffarhussain_at_gmail.com
    Cc: _oracle_L_list
    Subject: Re: log_buffer size in Oracle 10g Rel.2

    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. 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?
    Privileged/Confidential information may be contained in this message. The information contained in this message is intended only for the use of the recipient(s) named above and their co-workers who are working on the same matter. The recipient of this information is prohibited from disclosing the information to any other party unless this disclosure has been authorized in advance.

    If you are not intended recipient of this message or any agent responsible for delivery of the message to the intended recipient, you are hereby notified that any disclosure, copying, distribution or action taken in reliance on the contents of this message is strictly prohibited. You should immediately destroy this message and kindly notify the sender by reply E-Mail. Please advise immediately if you or your employer does not consent to Internet E-Mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of the firm shall be understood as neither given nor endorsed by it.
    --
    Best Regards,
    Syed Jaffar Hussain
    8i,9i & 10g OCP DBA
    Banque Saudi Fransi,
    Saudi Arabia

    I blog at :http://jaffardba.blogspot.com/
    http://www.oracle.com/technology/community/oracle_ace/ace1.html#hussain
    ----------------------------------------------------------------------------------
    "Winners don't do different things. They do things differently."
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedAug 9, '06 at 8:56a
activeAug 9, '06 at 5:50p
posts6
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase