1 Reply Latest reply: Jan 4, 2013 8:45 PM by sb92075 RSS

    High Wait on System I/O on AWR

    user8101600
      Hi, I am currently looking into a performance issue on an OLTP database. Here are some statistics from the 60 minutes AWR report.

      From the Load Profile, transaction / sec and transaction size is not too extreme.

      I cannot figure out why does it take so long for the commit and write to the system.

      When looking the system statistics IO wait time is 0.

      Can anyone advise what could the issue here?

      Thank you very much!!

      Top 5 Timed Events Avg %Total
      ~~~~~~~~~~~~~~~~~~ wait Call
      Event Waits Time (s) (ms) Time Wait Class
      ------------------------------ ------------ ----------- ------ ------ ----------
      log file sync 145,634 5,359 37 74.0 Commit
      db file parallel write 40,891 3,324 81 45.9 System I/O
      log file parallel write 126,741 2,755 22 38.0 System I/O
      CPU time 1,887 26.1
      control file parallel write 1,613 137 85 1.9 System I/O
      -------------------------------------------------------------

      Avg
      %Time Total Wait wait Waits
      Wait Class Waits -outs Time (s) (ms) /txn
      -------------------- ---------------- ------ ---------------- ------- ---------
      System I/O 172,667 .0 6,216 36 1.7
      Commit 145,634 .0 5,359 37 1.5
      Application 86,572 .0 21 0 0.9
      Network 2,324,023 .0 8 0 23.2
      User I/O 273 .0 6 21 0.0
      Other 2,645 .0 3 1 0.0
      Concurrency 4,756 .7 2 0 0.0
      Configuration 1,454 .0 1 1 0.0
      -------------------------------------------------------------


      Load Profile
      ~~~~~~~~~~~~ Per Second Per Transaction
      --------------- ---------------
      Redo size: 46,782.35 1,688.79
      Logical reads: 5,463.19 197.21
      Block changes: 422.23 15.24
      Physical reads: 0.09 0.00
      Physical writes: 44.53 1.61
      User calls: 827.33 29.87
      Parses: 435.42 15.72
      Hard parses: 0.00 0.00
      Sorts: 24.11 0.87
      Logons: 0.01 0.00
      Executes: 661.11 23.87
      Transactions: 27.70

      Wait Events DB/Inst: USRSE/usrse Snaps: 465-469
      -> s - second
      -> cs - centisecond - 100th of a second
      -> ms - millisecond - 1000th of a second
      -> us - microsecond - 1000000th of a second
      -> ordered by wait time desc, waits desc (idle events last)

      Avg
      %Time Total Wait wait Waits
      Event Waits -outs Time (s) (ms) /txn
      ---------------------------- -------------- ------ ----------- ------- ---------
      log file sync 145,634 .0 5,359 37 1.5
      db file parallel write 40,891 .0 3,324 81 0.4
      log file parallel write 126,741 .0 2,755 22 1.3
      control file parallel write 1,613 .0 137 85 0.0
      SQL*Net break/reset to clien 86,572 .0 21 0 0.9
      SQL*Net message to client 2,323,913 .0 8 0 23.2
      db file sequential read 174 .0 6 33 0.0
      enq: TX - contention 1,575 .0 2 1 0.0
      os thread startup 8 .0 1 150 0.0
      enq: HW - contention 1,412 .0 1 1 0.0
      control file sequential read 3,422 .0 0 0 0.0
      row cache lock 755 .0 0 1 0.0
      buffer busy waits 3,627 .0 0 0 0.0
      rdbms ipc reply 58 .0 0 7 0.0
      cursor: pin S wait on X 34 91.2 0 9 0.0
      latch free 8 .0 0 28 0.0
      enq: SQ - contention 41 .0 0 1 0.0
      latch: cache buffers chains 323 .0 0 0 0.0
      LGWR wait for redo copy 802 .0 0 0 0.0
      db file scattered read 94 .0 0 0 0.0
      SQL*Net more data to client 64 .0 0 0 0.0
      direct path write 5 .0 0 1 0.0
      latch: In memory undo latch 9 .0 0 0 0.0
      cursor: pin S 202 .0 0 0 0.0
      SQL*Net more data from clien 46 .0 0 0 0.0
      latch: redo writing 1 .0 0 0 0.0
      SQL*Net message from client 2,323,916 .0 120,921 52 23.2
      Streams AQ: qmn slave idle w 129 .0 3,510 27213 0.0
      Streams AQ: qmn coordinator 258 50.0 3,510 13606 0.0
      Streams AQ: waiting for time 1 100.0 3,469 ####### 0.0
      class slave wait 9 .0 0 0 0.0
      -------------------------------------------------------------

      Operating System Statistics DB/Inst: USRSE/usrse Snaps: 465-469

      Statistic Total
      -------------------------------- --------------------
      AVG_BUSY_TIME 41,929
      AVG_IDLE_TIME 319,218
      AVG_IOWAIT_TIME 0
      AVG_SYS_TIME 12,531
      AVG_USER_TIME 29,288
      BUSY_TIME 336,295
      IDLE_TIME 2,554,540
      IOWAIT_TIME 0
      SYS_TIME 101,141
      USER_TIME 235,154
      LOAD 1
      OS_CPU_WAIT_TIME 164,400
      RSRC_MGR_CPU_WAIT_TIME 0
      VM_IN_BYTES 0
      VM_OUT_BYTES 0
      PHYSICAL_MEMORY_BYTES 33,727,037,440
      NUM_CPUS 8
      -------------------------------------------------------------
        • 1. Re: High Wait on System I/O on AWR
          sb92075
          Please edit you post & add
          tag (exactly as shown above) at the start & end of your post
          since it is hard to read in present state
          
          
          How do I ask a question on the forums? 
          SQL and PL/SQL FAQ
          
          Handle:      user8101600  
          Status Level:      Newbie
          Registered:      Jul 17, 2012
          Total Posts:      4
          Total Questions:      4 (4 unresolved)
          
          why do you waste time here when you never get any question answered?