oracle 11.2g,oracle 11.2g 等待事件asynch descriptor resize

最新推荐文章于 2022-04-12 17:00:16 发布

Joey Jiao

最新推荐文章于 2022-04-12 17:00:16 发布

阅读量271

点赞数

文章标签： oracle 11.2g

数据库环境；

oracle 11.2g rac on hp-unix 11.31

我们的数据库已经构架完，业务在开发阶段。最近要进行业务测试，后台开始大量的采集数据往数据库里入。我的任务是优化业务sql，在查看数据库等待事件是发现top等待事件是asynch descriptor resize。

经过监控会话发现一个sql引起这个等待事件：

select s.synonym_name object_name, o.object_type from sys.all_synonyms s, sys.all_objects owhere 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')

然后，单独拿出这个sql执行使用10046跟踪发现fetch过程近乎都是等待，下面我把trace贴出来，大家帮忙分析下：

PARSE

#11529215046020269952:c=560000,e=581268,p=1,cr=104,cu=0,mis=1,r=0,dep=0,og=1,plh=203862309,tim=130866930325

EXEC

#11529215046020269952:c=0,e=428,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=203862309,tim=130866931075

WAIT #11529215046020269952: nam='SQL*Net message to client' ela= 8 driver id=1650815232 #bytes=1 p3=0 obj#=13753 tim=130866931131

WAIT #11529215046020269952: nam='asynch descriptor resize' ela= 2 outstanding #aio=0 current aio limit=0 new aio limit=65 obj#=13753 tim=130866931664

WAIT #11529215046020269952: nam='asynch descriptor resize' ela= 1 outstanding #aio=0 current aio limit=0 new aio limit=130 obj#=13753 tim=130866933978

WAIT #11529215046020269952: nam='asynch descriptor resize' ela= 5 outstanding #aio=0 current aio limit=0 new aio limit=130 obj#=13753 tim=130867110224

WAIT #11529215046020269952: nam='asynch descriptor resize' ela= 1 outstanding #aio=0 current aio limit=0 new aio limit=195 obj#=13753 tim=130867131296

WAIT #11529215046020269952: nam='asynch descriptor resize' ela= 1 outstanding #aio=0 current aio limit=0 new aio limit=260 obj#=13753 tim=130867186063

由于，整个FETCH过程太长只能贴出一部分这个等待

asynch descriptor resize描述

This event is set when the number of asynch descriptor reserved inside the OS kernel has to be readjusted.It is signaled when the number of asynch I/O's submitted by a process has to be increased. The Linux kernel does not allow the limit to be increased when there are outstanding I/O's inside the kernel. Hence, all outstanding I/O's are reaped before the limit is increased. The wait to reap all the outstanding I/O's when the limit is increased uses this event.

查找相关资料：

Applies to:Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.2 - Release: 11.1 to 11.2Information in this document applies to any platform.PurposeTo understand this undocumented wait event.asynch descriptor resizeThis event is set when the number of asynch descriptor reserved inside the OS kernel has to be readjusted.It is signaled when the number of asynch I/O's submitted by a process has to be increased. The Linux kernel does not allow the limit to be increased when there are outstanding I/O's inside the kernel. Hence, all outstanding I/O's are reaped before the limit is increased. The wait to reap all the outstanding I/O's when the limit is increased uses this event.Note 1273748.1CauseWhat 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. See:Note:1081977.1asynch descriptor resizeHowever 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.

大致意思是内核参数中关于异步io最大限制数导致的

#sam查看关于aio的系统内核参数：

通过尝试调整系统内核参数aio_max_ops后，asynch descriptor resize等待事件没有在出现。[@more@]数据库环境；