OS VERSION :

Linux 2.6.32-200.13.1.el5uek #1 SMP Wed Jul 27 21:02:33 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux

 

ORACLE VERSION:
SQL> select  * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

PL/SQL Developer version : 7.0.1.1066

现象：用PL/SQL Developer 连接到ORACLE 11G 的时候开始会特别的慢。

数据库中的等待事件：asynch descriptor resize

与该等待时间对于的SQL是：

select s.synonym_name object_name, o.object_type
from sys.all_synonyms s,
     sys.all_objects o
where s.owner in ('PUBLIC', user)
and o.owner = s.table_owner
and o.object_name = s.table_name
and o.object_type in ('TABLE', 'VIEW', 'PACKAGE','TYPE', 'PROCEDURE', 'FUNCTION', 
'SEQUENCE')

 

run了一下这个SQL要171秒。这个SQL的执行计划很庞大，就不贴出来了。感觉是统计信息有问题，重新收集了一下系统表的统计信息。问题依旧。

MOS上有这个等待事件的描述，说是和异步IO有关。
【EVENT: 'asynch descriptor resize' [ID 1081977.1] 】

Applies to:
Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.3 - Release: 11.1 to 11.2

Purpose
To understand this undocumented wait event 'asynch descriptor resize'
EVENT: 'asynch descriptor resize'
Wait event 'asynch descriptor resize' is set when the number of asynchronous descriptors reserved inside the OS kernel has to be readjusted.

It is signaled when the number of asynchronous I/O's submitted by a process has to be increased. Many Unix Kernels (for example: Linux kernel) do not allow the limit to be increased when there are outstanding I/O's; all outstanding I/O's must be resolved before the limit is increased. This event is shown when the kernel wants to increase the limit and is waiting for all the outstanding I/O's to be resolved so that the increase can be implemented.

If you see this wait event often, it might be a good idea to install the fix for Bug: 9829397 ASYNC DESCRIPTOR RESIZE.

【High Numbers of 'asynch descriptor resize' Wait Events Seen [ID 1273748.1]】
What this event means is that sessions are doing more async IO than the OS settings expect and so have to 'wait' for this to be adjusted.
However because the wait time is insignificant this does not seem to be an issue. 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.

Check the AWR for the period in question or 10046 trace

Top 5 Timed Foreground Events

If the average time for these waits is 0 or close to zero then its impact on the database is minimal and it may be possible to ignore this.

 

It there is an 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

 

【http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams072.htm】
DISK_ASYNCH_IO controls whether I/O to datafiles, control files, and logfiles is asynchronous (that is, whether parallel server processes can overlap I/O requests with CPU processing during table scans). If your platform supports asynchronous I/O to disk, Oracle recommends that you leave this parameter set to its default value. However, if the asynchronous I/O implementation is not stable, you can set this parameter to false to disable asynchronous I/O. If your platform does not support asynchronous I/O to disk, this parameter has no effect.

If you set DISK_ASYNCH_IO to false, then you should also set DBWR_IO_SLAVES to a value other than its default of zero in order to simulate asynchronous I/O.

SQL> show parameter  DISK_ASYNCH_IO

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
disk_asynch_io                       boolean     TRUE

[oracle@db01 fs]$ more  /etc/sysctl.conf | grep aio-max-nr
fs.aio-max-nr = 1048576

[oracle@db01 fs]$ more /proc/sys/fs/aio-max-nr
1048576

 

目前还没有找到好的解决方法，先记录一下。