什么是oracle 谓词越界,oracle谓词越界、绑定变量窥探

最近有一客户晚上新导入了一批数据到数据库中，第二天发现业务变慢，主要是其中有一条核心业务SQL执行计划走错导致。

结果排查发现客户在导入数据后并未重新收集统计信息，SQL使用绑定变量，窥探的变量刚好是越界，导致SQL第一次硬解析生成的执行计划走错，再加上10G的库导致接下来的执行计划直接沿用内存中的执行计划(默认情况下直到SQL对应的share cursor被age out出了share pool才会重新解析，那么如果存在晚上大量跑批的应用，每天都会被刷出去，于是第二天业务运行都会存在执行计划极不稳定的情况，不过手工让SQL重新解析也有多种方法)。

总结导致问题的主要两个原因：

1、统计信息陈旧，谓词越界导致执行计划走错

2、10g绑定变量窥探的bug，导致之后所有的执行计划都走错

另外，如果字段数据倾斜，字段上有直方图信息，在10g里面也会由于绑定变量窥探从而使SQL大部分变量的执行计划走错。

首先在测试环境测试一下客户的场景：

[code]SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

PL/SQL Release 10.2.0.5.0 - Production

CORE 10.2.0.5.0 Production

TNS for Linux: Version 10.2.0.5.0 - Production

NLSRTL Version 10.2.0.5.0 - Production[/code]

创建测试表并初始化数据然后默认方式收集统计信息：

[code]create table a_bind_test (id1 number,id2 number,name varchar2(5));

begin

for i in 1 .. 10

loop

insert into a_bind_test values (i,1,'a');

end loop;

end;

/

begin

for i in 11 .. 100000

loop

insert into a_bind_test values (i,100,'b');

end loop;

end;

/

exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'AWEN',TABNAME => 'A_BIND_TEST',DEGREE => 5, CASCADE => TRUE,ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,no_invalidate=>false);

COLUMN_NAME DATA_TYPE M NUM_VALS NUM_NULLS DNSTY LOW_V HI_V

---------------------- ---------- - ---------- ---------- ------- ------------------ ------------------

ID1 NUMBER N 100,000 0 0.0000 6 99978

ID2 NUMBER N 2 0 0.0000 1 100

NAME VARCHAR2 N 2 0 0.0000 a b[/code]

从上面可以看出id2的high value为100，且大部分数据都是id2=100，由于直方图中记录了数据的分布情况，在查询id2=100的SQL走索引快速全扫，另外由于199已经大于id2字段的high value，查询id2>199走的索引范围扫(结果=0)：

[code]select count(*) from a_bind_test where id2=100

Plan hash value: 1385593431

-------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 47 (100)| |

| 1 | SORT AGGREGATE | | 1 | 3 | | |

|* 2 | INDEX FAST FULL SCAN| IND_IBT_ID2 | 99973 | 292K| 47 (5)| 00:00:01 |

-------------------------------------------------------------------------------------

select count(*) from a_bind_test where id2>199

Plan hash value: 4016559932

---------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | SORT AGGREGATE | | 1 | 3 | | |

|* 2 | INDEX RANGE SCAN| IND_IBT_ID2 | 1 | 3 | 2 (0)| 00:00:01 |

---------------------------------------------------------------------------------[/code]

模拟批量导入数据：

[code] begin

for i in 11 .. 100000

loop

insert into a_bind_test values (1111111,200,'c');

end loop;

end;

/[/code]

此时表中id2>199的数据已经有了大部分，但是由于统计信息未更新，谓词越界，再次查询大于199的SQL依旧走的索引范围扫：

[code]select count(*) from a_bind_test where id2>199

Plan hash value: 4016559932

----------------