调整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的执行计划一模一样了：

完。