6 Replies Latest reply: Dec 10, 2007 5:51 AM by 601585 RSS

    Waits on event "log file sync"

    576856
      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
          '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
            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"
              ben23
              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
                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
                  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
                    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
                    ========================================