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

数据库环境;

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@]数据库环境;

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等待事件没有在出现。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26179867/viewspace-1055842/,如需转载,请注明出处,否则将追究法律责任。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值