一则sql优化的例子

今天 开发的 同事跟我说 一个奇怪的现象，一个统计的sql语句如果在存储过程里面查询就很慢，半个多小时都查不出来，但是把sql语句拷到pl develop里面执行就很快，100s左右就执行完了， 让我看看什么情况，我用pl develop 登录上去后，首先找到这个会话的sid(从pl develop的session窗口可以找到其sql语句,对比一下就知道了)，根据我的习惯，首先看等待事件，通过v$sessiion视图查看等待事件，状态，执行时长等信息。很奇怪的是：这个会话的状态为active，等待事件是SQL*Net message from client， 执行时长2000多秒。有点发蒙，通常我们看到SQL*Net message from client 等待事件的时候都是inactive状态，然后网上查了下，有一篇文章说 继续观察 v$sql里面的buffer_gets,disk_reads,cpu_time,elapsed_time字段值，如果这些值还在增长，说明sql语句有问题，需要调优。

继而查询了v$sql 中的这些字段值，sql语句如下：
select buffer_gets,disk_reads,cpu_time,elapsed_time
from v$sql where sql_id='0tbcdsp23xta9'

发现这些值果然在增长，看来是sql语句的问题。
看来需要对sql语句进行优化，根据我的思路，首先查看执行计划：
select * from table(dbms_xplan.display_cursor('3ru5wuxz0qkz0',null,'all'));
发现执行计划走了个不该走的索引，现在问题算是定位到了。

找到问题就比较好解决了，本来以为清空共享池会走正确的执行计划(报表数据库，清空共享池不会造成大的影响)，因为清空共享池会重新生成执行计划，试了一下，还是会走之前的执行计划。不凑效，只好在sql语句中加hint让它不走该索引，在select后面加上 /*+ no_index(A IND_WLAN_USER_1)*/就可以了，继续测试，发现很快就执行完了。

同事问我为啥在pl develop里面执行就比较快，在存储过程中执行就变慢了，我解释：
因为sql语句的执行计划不一样，在存储里面用的绑定变量，使用已生成的执行计划；在pl develop里面使用的是字面值，得重新解析生成新的执行计划。事实上，oracle会认为这是两个不同的sql语句，执行计划就可能不一样了。

至于为啥出现存储过程在执行过程中(未执行完)，状态是active，sql_id不为空，而等待事件是 SQL*Net message from client，我还是没搞清楚，希望能得到高人解惑！

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

转载于:http://blog.itpub.net/7773403/viewspace-1135653/