问题现象:
1. 11g的库,话说有一个应用程序新上线,应用中使用了绑定变量的方式执行一条简单的SQL,例如select a from b where c = :x,c列是该表复合主键的前导列,表定义是varchar2类型,从spotlight监控看这条SQL的执行计划是全表扫描,一次执行要1个小时,这张表是运行很久的引用分区表,数据量是亿级,测试的时候正常,但很显然测试的数据量可能和生产非常不一致,导致没察觉。
2. 在sqlplus中手工执行SQL,变量使用字面值,例如select a from b www.0871hx.comwhere c = 'abc',执行的非常快,查看执行计划,是用的索引范围扫描。
初步怀疑:
1. 对如此数据量的一张表,理应使用索引,但应用程序未使用索引,用的全表扫描,手工执行SQL时却可以用到索引,那么问题就在于为什么对于应用程序,Oracle选择了全表扫描的执行计划,而不是索引?
开始时的几种猜测:
1. 是否该表上线时有大量的数据变更,对执行计划产生影响,且未到达夜维统计信息收集的时间,造成因统计信息不准导致错误执行计划的可能?
> 经询问,确认上线时未有大量数据的变更,且上线后手工收集过统计信息。这种猜测不对。
2. 是否因为使用不同的查询条件会有不同的执行计划,由于绑定变量窥探的影响,导致采用了错误的执行计划?进一步解释猜测,例如第一次执行应用程序时,使用的条件值对应的执行计划是全表扫描,由于使用绑定变量窥探的作用,又由于应用使用了绑定变量,接下来的每次执行都会采用全表扫描,除非shared_pool被清空或对该表有DDL操作,才会重新硬解析,有可能采用另外的执行计划,这是绑定变量窥探的副作用。
> 因为他用的是引用分区,符合条件的记录在主表如果是存储于多个分区中,是否Oracle认为全表扫描效率高,如果存储于很少的分区,Oracle认为索引扫描效率高?
证明上述猜测的依据,就是无论哪种方式执行,应用程序或sqlplus,执行计划都是全表扫描,但实际是sqlplus执行SQL时并没有采用全表扫描,用的是索引范围扫描。这种猜测不对。
3. 是不是索引设置为了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ÿ