In my recently upgraded production database, I am seeing a number of SQL statements that are now experiencing high waits on the “asynch descriptor resize” event. I recently upgraded from 184.108.40.206 to 220.127.116.11 and SQL statements that never waited on this event are now getting caught.
Oracle 11.2 slightly changed the way the database and the OS kernel performed Asynchronous I/O calls. What is happening is that there a number of asynchronous I/O descriptors to be able to handle the async I/O calls. When the number of asynch I/O calls increases, the number of descriptors increases as well. When the number of asynch I/O calls decreases, the number of descriptors is decreased in a similar fashion.
Before Oracle can increase the number of descriptors, it must wait for all processes that are currently performing async I/O to complete their I/O calls. This terrible action really kills the “asynch” part of the I/O! Once all processes have completed their ansych I/O calls, Oracle can then modify the descriptors up or down depending on workload.
If your process just got done completing its asynch I/O, before it can make another asynch I/O call, it must wait for Oracle to modify the number of descriptors. As such, you are waiting for the “asynch descriptor resize” wait event.
This appears to be Bug 9829397 and you can download a patch for it from Metalink. This issue is fixed in 18.104.22.168 so it only appears in 22.214.171.124 and 126.96.36.199. One workaround is to disable Asynch I/O, but to me that workaround is highly undesirable. One can also reduce the occurences of this wait event by reducing their direct I/O and tuning their SQL statements.