调整oracle的一个pga参数,让一个sql语句运行飞快
最近有一个项目,项目经理反馈说某个功能模块在测试机器上3~5s就可以打开;但是在正式机器上需要10~20分钟才能打开。
项目经理怀疑是正式系统的机器硬件资源有问题。
我接到项目经理的反馈,笑而不语,反问他怎么可能会怀疑我们正式系统的机器有问题呢。要知道,正式机器的内存、cpu个数、磁盘读写速度,那都比测试机器快好多倍。
于是,我让他操作操作一下那个慢的功能模块,我好去oracle里面抓一下sql语句。
最终,我通过awr、ash报告,找到了正式机器上的那个sql语句,并找到了相应等待事件:
通过上图可以看出,在项目经理执行那个慢的功能模块的过程中,有两个等待事件比较严重:direct path read temp和direct path write temp。
相应的慢sql是如下图:
上图的sql语句太复杂,看不懂。
当然,也可以在项目经理运行那个功能模块的过程中,在oracle里面运行如下语句,看数据库当前正在跑什么语句,并对应的等待事件是什么:
select b.sid,a.sql_text,b.status,b.last_call_et,b.event from v$sql a,v$session b where a.sql_id=b.sql_id;
可以看到,上图显示的结果和awr、ash得到的结果一样。
好啦,慢sql和等待事件都找到了。那我就把这个sql语句分别在测试oracle和正式oracle里面跑一遍吧,我就不信,测试oracle比正式oracle快。
等我运行一对比,让我傻眼了,还真是,测试oracle 3~5s就能返回结果,而正式oracle 10分钟才返回结果。这不科学!
在正式oracle中运行那个sql语句过程中,我发现,正式oracle服务器的cpu和内存利用率都不高,反而磁盘io非常高。
运行iostat –x 1
通过上图可以看出,正式oracle的%util已经到了95%,磁盘利用率非常高,说明磁盘io出现问题了!!!
为了能找到问题的原因,刚才不是说测试oracle运行快,而正式oracle慢吗。那我们就看看这个sql语句在两个数据库的执行计划。
下图是测试库的执行计划:
下图是正式库的执行计划:
我去,执行计划完全不一样。
要知道,测试库和正式库的表和数据完全一致啊,可为什么会出现两种执行计划呢。
最终我发现了两个数据库的版本和操作系统版本都不一样:
测试库:oracle 11.2.0.1&?os 5.5
正式库:oracle 11.2.0.4&?os 6.5
难道是版本不一样导致的吗?
这时有点一头雾水,我于是咨询了我的弟弟(顺便打个广告,他现在和人合伙开了个培训机构,有兴趣的可以访问http://www.jiaguren.com/ 甲骨人了解)。他说你手工收集一下统计信息,于是我手工执行了一下dbms_stats.gather_schema系统包,可是执行计划还是那样。
这时我不知所措了。
对了,刚才不是说有两个等待事件direct path read temp和direct path write temp吗。
于是,死磕这两个等待事件。
百度和谷歌了很长时间,都说的意思是pga空间不够,发生了读临时表空间temp的情况,解决办法就是加大pga空间。
我的解决办法是,我将workarea_size_policy 设置为manual:
在运行那个sql语句,速度从10分钟降到3~5s了。
在看他的执行计划,和测试oracle的执行计划一模一样了:
完。
附件列表
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28916011/viewspace-2129603/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28916011/viewspace-2129603/