Re: Slower performance after enabling async io on Oracle Linux

It doesn't make any difference. Sysadmin checked everything between the guest/host and the iSCSI box, they didn't see anything wrong. I now analyzed a long-running query (> 5 minutes) with a level 8 10046 trace. This is with async io enabled (disk_asynch_io=TRUE,filesystemio_options=SETALL):

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        3        0.00          0.00
  db file sequential read                    143264        0.78        242.64
  latch: cache buffers lru chain                  2        0.00          0.00
  latch: object queue header operation            2        0.00          0.00
  log file switch completion                      3        0.06          0.15
  db file scattered read                       2333        0.07          4.93
  asynch descriptor resize                        1        0.00          0.00
  log buffer space                                3        0.13          0.25
  direct path read temp                          97        0.00          0.32
  reliable message                               67        0.00          0.03
  latch: cache buffers chains                     1        0.00          0.00

   11 user SQL statements in session.
    6 internal SQL statements in session.    17 SQL statements in session.

       1  session in tracefile.
      11  user  SQL statements in trace file.
       6  internal SQL statements in trace file.
      17  SQL statements in trace file.
      13  unique SQL statements in trace file.
 5560145  lines in trace file.
     716  elapsed seconds in trace file.

This is with async io disabled (disk_asynch_io=FALSE,filesystemio_options=NONE):

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        4        0.00          0.00
  db file sequential read                    166147        2.21         84.01
  db file scattered read                       2406        1.74          7.05
  undo segment extension                          2        0.01          0.01
  log file switch completion                      2        0.02          0.05
  log buffer space                                4        2.86          2.92
  latch free                                      1        0.00          0.00
  db file parallel read                           2        0.15          0.19
  direct path write temp                        577        0.53          2.46
  latch: shared pool                             11        0.00          0.00
  direct path read temp                         577        0.01          0.54
  latch: object queue header operation            1        0.00          0.00

   11 user SQL statements in session.
    6 internal SQL statements in session.    17 SQL statements in session.

       1  session in tracefile.
      11  user  SQL statements in trace file.
       6  internal SQL statements in trace file.
      17  SQL statements in trace file.
      13  unique SQL statements in trace file.
 5583431  lines in trace file.
     409  elapsed seconds in trace file.

So, you see, turning off async io drastically improves performance in my situation (409 versus 716 seconds). I'm going to leave it off for now, and maybe create a service request on MOS to see if they can help. I couldn't find a lot on OTN.

Matthias Hoys Received on Wed May 16 2012 - 03:32:51 CDT