一个索引惹的祸

   一个系统最近报表模块全面出现问题，应用层面设置了时间限制，如果报表查询在6s内没有返回结果，就提示错误终止。开发人员自己进行了一些测试，给出的结论是如果使用预编译（目前生产环境就使用了java语言中的预编译）性能就很差，6s内出不了结果，如果不使用预编译，在3秒内出结果。因此开发人员认为是数据库预编译相关的功能出现问题。开发人员的这个结论其实给了现场工程师错误的引导，他们说的预编译功能其实是指java的预编译写法，这是个错误的判断，本文不对Oracle的预编译进行探讨。
  下面开始分析问题，首先问题覆盖面较广，首先从全局观入手，先分析问题时段的AWR报告，如下是问题时段的AWR报告信息：

   系统负载并不大，服务器配置是非常给力了！！！

   DB CPU非常高，行锁等待也很严重，后面进过排查，行锁等待的对象与报表模块无关，这里就先忽略了。接下来观察消耗cpu较多的SQL：

  前两条sql占用了接近60%的cpu，而且第一个sql的单次执行时间非常长，经过与开发确认，fw0mvmdf7g8q9就是其中一个报表模块对应的sql，查询sql的执行计划发现存在

3个不同的cursor，如下： 
cursor 1：

cursor 2：

cursor 3：

  上面的三个执行计划中，第二个、第三个执行计划都存在笛卡尔集，检查发现通过索引 IDX_NODE_TREE过滤后的行数并不是1，所以上面存在笛卡尔集的执行计划是错误的。直接在服务器上运行sql发现走的是第一个执行计划， sql在2s左右返回结果。
   存在笛卡尔集的执行计划是由于优化器对rows估算错误导致，但检查表和索引的统计信息发现都正确，所以使用sql profile固定执行计划解决了上面这条sql的 问题。
   再检查其他报表模块对应的sql时发现情况很类似，下面是另外一条sql的两个执行计划：
    cursor 1：


    cursor 2:


   这条sql也存在多个执行计划，执行计划太长，这里选两个进行观察，这里首先粗略的进行分析，第一个执行计划存在大量NL，第一反应就感觉有问题，因为报表返回的行集一般很大，经过测试，上面的sql要返回4000多行数据，显然 NL不合适。第二个执行计划中主要以hash join为主，粗略的分析符合sql的情况。再进一步观察这两个执行计划，发现第一个执行计划之所以走NL，也是由于估算通过 IDX_NODE_TREE索引过滤后返回1行，然后选择了NL，但实际情况并不是这样，这里过滤后的结果集远大于1。检查相关表的统计信息发现并无问题。
  其余报表模块的情况与上面情况很类似，跟开发确认得知 IDX_NODE_TREE索引就是为报表模块建立的，所以这里就不再采用sql profile固定执行计划，直接删除这个索引，删除后报表模块恢复正常，索引不一定能提高sql效率，如果建立不当，可能适得其反。
  后记：再回到一开始的时候开发人员提出的问题，他们新建立的模块，没有使用java的预编译功能，性能立即得到提升，原因是由于传入数据库的sql文本有差异，引起硬解析，走了正确的执行计划，与数据库的预编译功能并没有关系，因此，在分析问题的时候要有全局思维，不要一开始就被其他人的思维引导，先入为主，把问题复杂化。

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

转载于:http://blog.itpub.net/29821678/viewspace-1848196/