asynch descriptor resize

High Numbers of 'asynch descriptor resize' Wait Events Seen [ID 1273748.1]

Applies to:

Oracle Server - Enterprise Edition - Version 11.2.0.1 to 11.2.0.2 [Release 11.2]
Oracle Server - Personal Edition - Version 11.2.0.1 to 11.2.0.2 [Release 11.2]
Oracle Server - Standard Edition - Version 11.2.0.1 to 11.2.0.2 [Release 11.2]
Information in this document applies to any platform.

Symptoms

• Large number of 'asynch descriptor resize' waits seen in AWR report
• Average time for  'asynch descriptor resize' waits is 0 or close to zero
• Total Wait time for  'asynch descriptor resize' is very low or insignificant

Cause

Waits for 'asynch descriptor resize' events mean that sessions are doing more async IO than the OS current settings expect and so have to 'wait' for the asynchronous I/O settings to be dynamically adjusted. See:

Note:1081977.1 EVENT: 'asynch descriptor resize'

Solution

Since the wait time for 'asynch descriptor resize' is insignificant these waits may not actually be causing any issue other than using some CPU time unnecessaily.
What is more likely to be a problem is the activity of the sessions 'waiting' for the event - it may be that their IO activity needs to be adjusted (in other words they may be requesting more data than is actually required to satisfy the query or accessing it in an in-efficient manner - SQL may need tuning).

For example: Check the AWR for the period in question (or 10046 trace) e.g.:

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                           Avg                  
                                                          wait   % DB           
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU                                            7,511          54.5           
db file sequential read             157,067          43      0     .3 User I/O  
asynch descriptor resize         10,773,269          18      0     .1 Other     
db file scattered read                2,964          11      4     .1 User I/O  
rdbms ipc reply                           6          10   1708     .1 Other   

If the average time for these waits is 0 or close to zero then the impact on the database is minimal and it may be possible to ignore this.
It there is an a performance issue, then determine the source of the waits and it is likely that the SQL in question needs to be tuned to perform less IO if possible. Candidate SQLs are likely to be those involving large number of physical IO. e.g.

    CPU                   CPU per           Elapsed                             
  Time (s)  Executions    Exec (s) %Total   Time (s)   %CPU    %IO    SQL Id    
---------- ------------ ---------- ------ ---------- ------ ------ -------------
   3,789.7            0        N/A   50.5    6,913.6   54.8     .1 3148933ha4afc
   3,672.9            0        N/A   48.9    6,711.8   54.7     .4 f81301nxp9q43

   Physical              Reads              Elapsed                             
      Reads  Executions per Exec   %Total   Time (s)   %CPU    %IO    SQL Id    
----------- ----------- ---------- ------ ---------- ------ ------ -------------
  1,291,859           0        N/A   54.3    6,711.8   54.7     .4 f81301nxp9q43 
    963,191           0        N/A   40.5    6,913.6   54.8     .1 3148933ha4afc 

The SQLs above are both reading nearly 1 million blocks from disk in the snapshot time period and have each used close to 4000 seconds of CPU time in the process. Reducing the IO may alleviate the issue.

Note: If the dynamic resizing of the asynchronous I/O settings DOES cause a performance regression, then there are patches available for many platforms to adjust the re-sizing activity. See:
   
Document 9829397.8 Bug 9829397 - Excessive CPU and many "asynch descriptor resize" waits for SQL using Async IO

The fix for (unpublished) Bug 9829397 reduces the fequency of resizing calls and included in 11.2.0.2.5, 11.2.0.3 and Oracle 12g