一次有意思的错选执行计划问题定位

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 一次有意思的错选执行计划问题定位

这是去年碰见的一个问题,比较有意思,过程也比较曲折。。。

问题现象:

  1. 11g的库,话说有一个应用程序新上线,应用中使用了绑定变量的方式执行一条简单的SQL,例如select a from b where c = :x,c列是该表复合主键的前导列,表定义是varchar2类型,从spotlight监控看这条SQL的执行计划是全表扫描,一次执行要1个小时,这张表是运行很久的引用分区表,数据量是亿级,测试的时候正常,但很显然测试的数据量可能和生产非常不一致,导致没察觉。

  2. 在sqlplus中手工执行SQL,变量使用字面值,例如select a from b where c = ‘abc’,执行的非常快,查看执行计划,是用的索引范围扫描。

初步怀疑:

  1. 对如此数据量的一张表,理应使用索引,但应用程序未使用索引,用的全表扫描,手工执行SQL时却可以用到索引,那么问题就在于为什么对于应用程序,Oracle选择了全表扫描的执行计划,而不是索引?

开始时的几种猜测:

  1. 是否该表上线时有大量的数据变更,对执行计划产生影响,且未到达夜维统计信息收集的时间,造成因统计信息不准导致错误执行计划的可能?

经询问,确认上线时未有大量数据的变更,且上线后手工收集过统计信息。这种猜测不对。

  1. 是否因为使用不同的查询条件会有不同的执行计划,由于绑定变量窥探的影响,导致采用了错误的执行计划?进一步解释猜测,例如第一次执行应用程序时,使用的条件值对应的执行计划是全表扫描,由于使用绑定变量窥探的作用,又由于应用使用了绑定变量,接下来的每次执行都会采用全表扫描,除非shared_pool被清空或对该表有DDL操作,才会重新硬解析,有可能采用另外的执行计划,这是绑定变量窥探的副作用。

因为他用的是引用分区,符合条件的记录在主表如果是存储于多个分区中,是否Oracle认为全表扫描效率高,如果存储于很少的分区,Oracle认为索引扫描效率高?

证明上述猜测的依据,就是无论哪种方式执行,应用程序或sqlplus,执行计划都是全表扫描,但实际是sqlplus执行SQL时并没有采用全表扫描,用的是索引范围扫描。这种猜测不对。

  1. 是不是索引设置为了invisible?

invisible是11g的新特性,允许设置索引为invisible,效果是DML操作仍会维护索引,但优化器会忽略索引的存在,除非设置参数optimizer_use_invisible_indexes,否则即使使用该索引字段,也不会使用索引。

但和问题2相同,使用sqlplus时会使用索引扫描,不可能应用程序执行时会临时设置该参数,因为这参数是系统级的,不是session级的,需要重启数据库生效,这不是应用程序能做的,而且也没有任何理由需要由应用程序来做这个操作。这种猜测不对。

究竟为什么应用程序运行时,这个SQL使用了全表扫描,但sqlplus执行SQL时却用的索引范围扫描呢?

越来越邪乎了。。。

但哲学观点证明,因果关联,肯定有某种因素让Oracle对不同场景使用了不同的执行计划,就像dbsnake所说的,90%的Oracle问题都是SQL的书写不正确导致的,之前还有幸听了RWP中国巡讲,Tom同样提到了这点,不是Oracle做错了,而是你可能给Oracle的某些错误信息,让其出现了这种错误。

如何进一步证明?

首先跑了一个10046,第一次反馈没看出什么问题。

接着跑一个sqlplus下执行SQL的10053,发现优化器选择的就是索引范围扫描,其成本值最低,而全表扫描的成本值是15491.05。

无论如何,是不会选择全表扫描的啊?

其实开始的时候并没有第一时间反应过来,一条SQL语句,Oracle计算的成本中索引范围扫描最优,但应用程序运行时没有用,而是用的全表扫描,排除上面几种猜测后,剩下的可能不多了。其实问题已经缩小到为什么应用程序执行的SQL索引失效了?

经过进一步排查,发现应用程序中对查询条件变量使用了OracleDbType.NVarchar2的定义,但实际字段类型是VARCHAR2,即where VARCHAR2 = NVARCHAR2,那么是否因为这种类型不匹配,造成做了隐式转换,导致索引失效?可能用过NVARCHAR2的朋友就已经发现问题了,由于以前基本没用过这种类型,所以只是怀疑,需要验证。

实验:

  1. 定义了一张测试表,定义NVARCHAR2类型的变量,模拟应用程序。
一次有意思的错选执行计划问题定位
  1. 首先使用explain plan for查看执行计划
一次有意思的错选执行计划问题定位 发现是用的索引范围扫描啊,没有任何问题???
  1. explain plan for方式得到的执行计划有可能是不准的,dbsnake的书中有详细介绍,那么看看display_cursor方式得到的执行计划有不同
一次有意思的错选执行计划问题定位 一次有意思的错选执行计划问题定位 看到不同了。。。

注意filter中显示SYS_OP_C2C(“OBJECT_NAME”)=:X,说明Oracle对左值使用了一个叫SYS_OP_C2C的函数,我们都知道这个常识,如果对索引字段使用了函数,那么是不会采用这个索引作为执行计划的,肯定是全表扫描。

看来问题是找到关键了,但还没完,这个SYS_OP_C2C是什么,为什么对VARCHAR2 = NVARCHAR2这种情况会调用这个函数?

首先搜到了MOS有篇文章SYS_OP_C2C Causing Full Table/Index Scans (文档 ID 732666.1),简明扼要地说明了这个问题:

  1. You are executing a query using bind variables.
  1. The binding occurs via an application (eg. .NET, J2EE ) using a “string” variable to bind.
  1. The query is incorrectly performing a full table/index scan instead of an unique/range index scan.  
  1. When looking at advanced explain plan, sqltxplain or 10053 trace, you notice that the “Predicate Information” shows is doing a “filter(SYS_OP_C2C)”.

e.g select * from table(dbms_xplan.display_cursor(&sql_id,null,’ADVANCED’));

Predicate Information (identified by operation id):

1 - filter(SYS_OP_C2C(“COL1”)=:B1)            === filter operation occurring

CAUSE

The bind variable “string” is using a different datatype to the column that is being queried.  
This means that an implicit conversion of the data is required to execute the query.  SYS_OP_C2C is the implicit function which is used to convert the column between nchar and char.

SOLUTION

  1. Create a function based index on the column.

e.g create index index_name on table_name (SYS_OP_C2C(column));

OR

  1. Ensure that your bind “string” datatype and column datatype are the same.

A java example where this can occurs is when defaultNChar=TRUE.  This will cause strings to bind as NVARCHAR2 causing the predicate that are subset datatypes to be converted to NVARCHAR2.

e.g.    -Doracle.jdbc.defaultNChar=true

connection-property name=”defaultNChar”true/connection-property

说明的很是详细了,如果应用程序(例如.NET,Java)中使用了“string”的绑定变量,查询语句就会使用全表扫描/索引全扫描,不会使用到唯一索引扫描/索引范围扫描。使用advanced选项的explain plan或10053等方式才能发现这个问题。

原因就是“string”的绑定变量是使用了另外一种和查询列定义不同的数据类型。Oracle需要使用SYS_OP_C2C函数在NCHAR和CHAR类型之间做隐式转换。

解决方法:

  1. 创建函数索引。

  2. 确保应用程序中使用的“string”类型和列字段类型一致。

另外,杨长老对这个问题也有过说明(http://blog.itpub.net/4227/viewspace-531728/)。

后话是,开发同事之前没用过Oracle,写程序时不知怎的就用了NVARCHAR2的定义,且测试和生产环境不一致,才导致了这个在上线后才出现的问题。

总结:

  1. 一个很小的字段定义,有可能造成意想不到的后果,说明了解Oracle一些基本原理的重要性,这里不仅指提到的字段类型。

  2. 分析一个问题,要有正确的思路,要能抓到问题本质,像这个问题,就是不同方式执行同一条SQL,会有不同的执行计划,那么为什么Oracle选择了错误的执行计划?是有原因的,不是Oracle自己选择错了,更多情况是我们给他的信息错了,影响了其选择执行计划的准确性。如果能排除一些参数影响,定位到什么原因导致索引失效,进而查看列字段定义和应用程序中的字段类型定义,这个问题就可能更快的找到原因。这点还有待加强。

  3. 要有分析的方法,这里10046、10053,包括display_cursor,都是可能找到问题的重要手段,一是要知道什么场景下使用这些工具,二是知道如何使用这些工具,像explain plan for得到的执行计划有可能是不准的,尤其在有绑定变量的情况,上述就论证了这点,关键看是否真正执行了这条SQL语句;还有就像使用display_cursor,前提是执行过这条SQL,并且执行计划仍在缓存中,通过v$sqlarea可以找到对应的SQLID,这都是基础。

  4. 要细心,对于上述问题,可能从10046中就可以看到谓词条件带有隐式转换的线索,进而可以找到问题的真正原因,即使之前不知道SYS_OC_C2C,不知道NVARCHAR2和VARCHAR2之间的区别,也可以让我们有正确的认识。

  5. 要能模拟出问题,像这里使用var x nvarchar2的方式,就是模拟了应用程序使用绑定变量的逻辑。

  6. 自己需要提高的地方还很多,宽度深度都如此,加油。

如果您觉得此篇文章对您有帮助,欢迎关注微信公众号:bisal的个人杂货铺,您的支持是对我最大的鼓励!

一次有意思的错选执行计划问题定位
本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 一次有意思的错选执行计划问题定位


 上一篇
一个执行计划异常变更的案例 – 外传之ASH 一个执行计划异常变更的案例 – 外传之ASH
之前的几篇文章:《一个执行计划异常变更的案例 - 前传》《一个执行计划异常变更的案例 - 外传之绑定变量窥探》《一个执行计划异常变更的案例 - 外传之查看绑定变量值的几种方法》《一个执行计划异常变更的案例 - 外传之rolling inva
下一篇 
一个执行计划异常变更的案例 – 外传之SQL AWR 一个执行计划异常变更的案例 – 外传之SQL AWR
之前的几篇文章:《一个执行计划异常变更的案例 - 前传》《一个执行计划异常变更的案例 - 外传之绑定变量窥探》《一个执行计划异常变更的案例 - 外传之查看绑定变量值的几种方法》《一个执行计划异常变更的案例 - 外传之rolling inva