[转帖]利用SQL Monitor Report对SQL进行诊断与调优_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 4011 | 回复: 0   主题: [转帖]利用SQL Monitor Report对SQL进行诊断与调优        下一篇 
kim
注册用户
等级:中校
经验:1729
发帖:222
精华:0
注册:2011-7-21
状态:离线
发送短消息息给kim 加好友    发送短消息息给kim 发消息
发表于: IP:您无权察看 2011-9-15 10:51:39 | [全部帖] [楼主帖] 楼主

本文要分析的是下面这个SQL,执行了半个多钟头还没返回结果。

INSERT INTO T_D SELECT * FROM T_A a ,T_B b ,T_C c WHERE a.id = b.id AND b.number = c.number ;



Wait events 是了解Oracle运行状态的一个重要途径。对于某个具体的SQL,SQL Monitor Report提供了drill down的方式得到这个具体SQL在运行中的wait events的分布情况,下面是SQL Monitor Report的相应图形。

北京联动北方科技有限公司

这里最突出的等待事件是enq: TS – contention,这是关于临时segment的等待事件,这可能是一般的表空间的争用(例如并行直接路径加载数据),也可能是临时表空间的争用(例如为了hash join或者sort)。那么这时临时表空间的增长状态是怎么样子的呢?SQL Monitor Report提供了这方面的信息。

北京联动北方科技有限公司

可以看出,temp space从开始的3G慢慢增长到20G,整个过程2800 seconds,如果算下速度的话17GB/2800=6MB/s。这刚好与上面的等待事件 enq: TS – contention相吻合。

这是一个直接加载数据的例子,在这个例子中,最后表的大小是30GB左右。可以怀疑,这些临时表空间的分配是为了存放最后放入目标表的数据。

这于这个等待事件enq: TS – contention发生在这个SQL执行过程中的哪一步呢?SQL Monitor Report也给出了答案,在执行计划那一页:

北京联动北方科技有限公司

注意上图鼠标位置,标志着位于HASH JOIN BUFFERED这个步骤的等待事件enq: TS – contention占了这个SQL所有等待活动的97%!

问题到这里已经基本明了了:

1. 优化器采用Hash Join Buffered的方式返回三个表Join的结果,当为这些结果集分配临时表的空间时,碰到了严重的竞争。

2. 由于分配速度极其缓慢,导致了整个SQL超过97%的时间花在了这个等待事件上,通过去掉这个等待事件,这个SQL应该能提升上百倍的速度。

解决这个问题的根本在于加速临时表空间的回收速度,不过,也有workaround的办法,那就是预先分配足够的临时表空间,避免回收临时表空间时出现TS – contention竞争。

另外一种思路,则在于减少对临时表空间的利用,在这里,为什么要用到这么多临时表空间在于Oracle采用Hash Join Buffered而不是采用Hash Join,这意味着Oracle会先对Join的结果集进行buffer,等到所有结果ready之后再写到目标表,另一种思路当然是让结果直接写到目标表中,也就是实现并行的DML插入操作。方法很简单:在运行这个SQL之前加上

ALTER session enable parallel dml;


即可,在这种情形下,Oracle不需要再为结果集分配临时表空间,同时,由于采用并于DML操作,整个SQL的运行时间会得到很大的提速。下面是相应的执行计划。

北京联动北方科技有限公司

可以看出,Hash Join Buffered变成了Hash Join,同时,等待事件enq: TS contention已然消失,换成了”喜闻乐见”的ON CPU。同时,整个SQL在小于30s的时间内完成了。

不可否认,Hash Join Buffered是这里极其隐蔽,可能不小心就被忽略了,以为上面两个执行计划就是LOAD AS SELECT的位置从第2行搬到了第4行,其实背后发生的事情远没有这么简单。以后有机会再对这个话题进行展开吧。




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论