This discussion is archived
6 Replies Latest reply: Dec 10, 2007 3:51 AM by 601585 RSS

Waits on event "log file sync"

576856 Journeyer
Currently Being Moderated
Hi,

This is the output of ADDM Report......

------------------------------------------
FINDING 2: 31% impact (140 seconds)
-----------------------------------
Waits on event "log file sync" while performing COMMIT and ROLLBACK operations
were consuming significant database time.

RECOMMENDATION 1: Application Analysis, 31% benefit (140 seconds)
ACTION: Investigate application logic for possible reduction in the
number of COMMIT operations by increasing the size of transactions.
RATIONALE: The application was performing 88 transactions per minute
with an average redo size of 3265 bytes per transaction.

RECOMMENDATION 2: Host Configuration, 31% benefit (140 seconds)
ACTION: Investigate the possibility of improving the performance of I/O
to the online redo log files.
RATIONALE: The average size of writes to the online redo log files was 3
K and the average time per write was 17 milliseconds.

SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "Commit" was consuming significant database time.
(31% impact [140 seconds])

FINDING 3: 27% impact (124 seconds)
-----------------------------------
SQL statements with the same text were not shared because of cursor
environment mismatch. This resulted in additional hard parses which were
consuming significant database time.

RECOMMENDATION 1: Application Analysis, 27% benefit (124 seconds)
ACTION: Look for top reason for cursor environment mismatch in
V$SQL_SHARED_CURSOR.

ADDITIONAL INFORMATION:
Common causes of environment mismatch are session NLS settings, SQL
trace settings and optimizer parameters.

SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Hard parsing of SQL statements was consuming significant
database time. (64% impact [290 seconds])
-----------------------------------------------------------------------------------

how to tune according to above...mine is 10.2.0.1 on HP-UX Itanium....Please sched some light on this
  • 1. Re: Waits on event "log file sync"
    153119 Pro
    Currently Being Moderated
    'How to tune' is outlined in the paragraphs labeled 'Recommendation'

    Assuming you read them: what needs clarification?
    If you don't read them, please read them and work from there.

    --
    Sybrand Bakker
    Senior Oracle DBA
  • 2. Re: Waits on event "log file sync"
    AlokKumar Explorer
    Currently Being Moderated
    Where did you place your redolog files ? may be, you need to plcae them on different discks to get max throughput.

    hare krishna
    Alok
  • 3. Re: Waits on event "log file sync"
    602378 Newbie
    Currently Being Moderated
    Just a comment re: "log file sync" - you should aim to put the online redo logs on the fastest I/O devices you have available. This is quick to do, compared to the other recommendations.
  • 4. Re: Waits on event "log file sync"
    561093 Oracle ACE
    Currently Being Moderated
    Hi,

    1) As the recommendation says, decrease frequency of COMMIT by committing a larger portion of a transaction instead of each individual statement. You may also get benefited by setting COMMIT_WRITE parameter. Read more about this parameter in docs as this will have adverse affects when recovery is required.

    2) What is the value of CURSOR_SHARING initialization parameter?


    Regards
  • 5. Re: Waits on event "log file sync"
    576856 Journeyer
    Currently Being Moderated
    Hi,

    Infact we have 2 Disks which are mirrored, All the Datafiles, Controlfile and Redo log files are on that mirrrored Disk. Server Can accomoadate maximum 3 Disks only.....so we have kept all the files in that mirrored disk...


    -------------------------------------------------------------------
    SQL> show parameter commit_write

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    commit_write string
    SQL> show parameter cursor_sharing

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    cursor_sharing string EXACT
    SQL>
    ----------------------------------------------------------------------------
  • 6. Re: Waits on event "log file sync"
    601585 Oracle ACE
    Currently Being Moderated
    1. Your storage configuration implies that you might suffer from physical I/O contention.
    Investigate top events and active sessions which waits for I/O operation.

    Reducing commit count is general recommendation for resolving log file I/O contention.
    But it's not easy to reduce commit count in general applications.
    In this case, enhancing I/O performance will be a good alternative.

    2. As recommendations says, why don't you investigate v$sql_shared_cursor view?
    Your SQLs seem to have high version count because of some kinds of mismatches.
    For instance, bind mismatch has a bad reputation for unshareable child LCOs especially for insert statement with a long bind variable list.
    v$sql_shared_cursor view will show you the exact picture.


    ========================================
    EXEM. The Performance Artist Group
    Dion Cho
    Email: ukja@ex-em.com, ukja.dion@gmail.com
    Blog: http://ukjax.blogspot.com
    ========================================