ORACLE SQL调优之'PLAN_TABLE' is old version

  在为国投做SQL调优时，他们开发说不要动现在的SQL，调整一下执行计划即可，即查询某个表时执行特定的执行计划。乍一听，我是吓了一跳！
 由于他们开发不让动SQL结构（该SQL经过PLSQL优化后有500多行，其是2层嵌套递归查询，外边一个SQL如图1-2，外层SQL的每一个列是一个子查询如下图1-1，递归子查询有32个），所以只能从SQL涉及的表、索引下手，查找问题的具体原因及解决办法。我的做法是，先查看了SQL涉及的表的统计信息，问题SQL涉及了8张表（最大的表有300M左右，小表只有几M大小），表的统计信息距离现在有3个月。通过与他们维护人员沟通，对问题SQL涉及的8张表进行了统计信息更新；然后在他们的测试环境中测试问题SQL，发现SQL执行的速度比之前的20多分钟缩减到15分钟，速度有提升但是不明显。然后，在不改变SQL结构的前提下，我做了如下调整（由于SQL涉及薪水查询，比较敏感，这里只能大概给出处理涉及的部分）：
 
  图 1-1 递归查询部分中添加的hint
 
 图 1-2 外层查询中添加的hint
 我的处理依据是：执行该sql生成的执行计划中有比较多的全表扫描（全表扫描的表是同一个表，大小有12M，全表扫的次数有18次之多），根据提示的全表扫描，又查询了谓词涉及的列中是否有索引，查询到谓词涉及的列中有索引但是执行计划没有走而是全表扫描。先不计较添加hint是否有利于执行速度的提高，我就试了一下。添加hint后，SQL的执行速度果然有提高，在他们的测试库中执行时间从15分钟减少到3分多钟，有明显的提高了。但是，在他们的生产库中进行优化实施后，发现SQL的执行速度不但没有改善反而严重下降了，从原来的20多分钟到3个多小时，简直让人发疯。
 此时，我意识到，他们运行SQL是通过ORACLE DISCOVER运行的，执行后生成报表，就是薪资表，但是他们开发坚持该SQL执行客户端没问题。我只能在他们的生产库中生成执行计划查找原因，经过与测试环境中调整后的SQL执行计划对比发现个很严重的问题，如下图所示：

 执行计划提示：当前SQL执行使用的执行计划是老版本，也就是说我们做的统计信息更新及hint的设置根本没任何作用，这就是导致SQL问题慢的具体原因了（也是他们开发说的让调整SQL执行计划，但是又不能改变SQL的结构）。
 接下来，我做了如下步骤的操作：
1、业务用户登录数据库服务器
2、SQL>set autot on
   SQL>set timing on
3、执行速度慢的SQL
4、观察执行计划尾部是否有如下提示：
   Note -- plan table is old version
5、在4有提示的情况下（建议在服务器端sqlplus中执行）
  SQL>drop table plan_table;  --业务用户执行
  SQL>@?/rdbms/admin/utlxplan; --具有DBA权限的用户（SYS）执行
6、再次重复执行速度慢的SQL3-5次，观察SQL速度是否有所改善
 经过调整后，再次运行SQL，发现添加hint的SQL执行时间是3:15s，不添加hint的SQL执行时间是6::35s；终于，生产库的执行时间也能从20多分钟减少到3分多钟，达到了他们开发的基本要求了。总结：问题SQL执行慢的原因有3个，一是表的统计信息没有及时更新，二是表的索引没有被用上，三是plan_table缓存了执行计划。