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

---------------------------------------------------------------------------------
| 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]
现在模拟变量窥探的问题，首先查询id2大于100的数据：
[code]var id2 number
exec :id2 :=100
select count(*) from a_bind_test where id2>:id2;

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 | 9 | 27 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

1 - :ID2 (NUMBER): 100[/code]
再次查询id2>99的数据（此时查询表中绝大部分数据应该走索引快速全扫）：
[code]exec :id2 :=99
select count(*) from a_bind_test where id2>:id2;
select * from table(dbms_xplan.display_cursor('','','advanced'));

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 | 9 | 27 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

1 - :ID2 (NUMBER): 100[/code]
从上面的执行计划可以看出，即使未批量导入数据，SQL第二次执行直接使用第一次窥探id2>100解析生成的执行计划（Peeked Binds中可以看出），所以在导入大量数据之后性能的影响就会更大。指定no_invalidate=>false重新收集表的统计信息，再次执行SQL执行计划正确：
[code]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 3,536 0 0.0003 127 1111111
ID2 NUMBER N 2 0 0.0000 100 200
NAME VARCHAR2 N 2 0 0.0000 b c

exec :id2 :=199
select count(*) from a_bind_test where id2>:id2;
select * from table(dbms_xplan.display_cursor('','','advanced'));

Plan hash value: 1385593431

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 87 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX FAST FULL SCAN| IND_IBT_ID2 | 100K| 295K| 87 (5)| 00:00:02 |
-------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

1 - :ID2 (NUMBER): 199[/code]
另外第一个等值查询的SQL如果使用绑定变量，如果第一次查询变量值id2=1，那么SQL会走索引范围扫，之后该SQL都会沿用这个执行计划，而大多数大多数情况下是查id2=100或在id2=200，理论上应该走索引快速全扫，而走了效率低的索引范围扫。

其次在字段统计信息中存在low_value/high_value两个字段，这个字段主要记录了列上的最大值和最小值，如果排除变量窥探和直方图的影响（也就是执行计划不变），在最大值和最小值区间SQL的cardinality是不变的，但是在变量值小于low_value或者大于high_value时，cardinality是会变化的，且偏移越远值越少：
[code]COLUMN_NAME DATA_TYPE M NUM_VALS NUM_NULLS DNSTY LOW_V HI_V
---------------------- ---------- - ---------- ---------- ------- ------------------ ------------------
ID NUMBER N 1,723 0 0.0006 1000 100991
NAME VARCHAR2 N 2 0 0.5000 b c

SQL> select count(*) from a_bind_test where id=:id;

COUNT(*)
----------
69999

SQL> select * from table(dbms_xplan.display_cursor('','','advanced'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2hu3kz4z54kvr, child number 0
-------------------------------------
select count(*) from a_bind_test where id=:id

Plan hash value: 3741871164

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 40 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL| A_BIND_TEST | 59 | 236 | 40 (5)| 00:00:01 |
----------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

1 - :ID (NUMBER): 1000[/code]
这里将内存中的执行计划置为失效，这里方法有很多种，下一篇BLOG会介绍：
[code]SQL> exec :id :=100950

PL/SQL procedure successfully completed.

SQL> select count(*) from a_bind_test where id=:id;

COUNT(*)
----------
1

SQL> select * from table(dbms_xplan.display_cursor('','','advanced'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2hu3kz4z54kvr, child number 0
-------------------------------------
select count(*) from a_bind_test where id=:id

Plan hash value: 3741871164

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 40 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL| A_BIND_TEST | 57 | 228 | 40 (5)| 00:00:01 |
----------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

1 - :ID (NUMBER): 100950[/code]
从上面可以看出rows和bytes值都有差异，如果数据差异大，cost也会变化。也就是字段在没有直方图没有索引的情况下，为什么变量窥探出来的COST不一样。
这里需要注意的是，变量窥探一般情况下在select语句使用绑定变量都会去窥探，与字段上有无索引、直方图信息无关，虽然个人认为在没有直方图和索引的情况下意义不大，但是oracle都会去窥探变量值然后根据变量值生成执行计划，可以修改隐含参数"_optim_peek_user_binds"为FALSE禁用变量窥探（可能会引起性能问题），不过11g中引入自适应游标共享后这个问题得到了改善，在10g中直方图和变量窥探是相互矛盾的，为了性能的稳定性，需要人为去做好控制，不收集直方图信息或者不使用绑定变量，当然具体的方案都需要根据具体的情况进行分析测试。

最后需要注意的是默认情况下只收集在where条件中使用过的字段的直方图，视图sys.col_usage$中记录是否使用过
不做任何查询或者DML收集统计信息：
[code]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);
SQL> select object_id from dba_objects where object_name='A_BIND_TEST';

OBJECT_ID
----------
79789

SQL> select obj#,intcol#,equality_preds from sys.col_usage$ where obj#=79789
2 ;

no rows selected

Column Distinct Number Number Sample Date
Name Values Density Buckets Nulls Size MM-DD-YYYY
------------------------------ ------------ ----------- ------- ------------ -------------- ----------
ID1 100,000 .00001000 1 0 100,000 03-29-2016
ID2 1 .00000501 1 0 5,492 03-29-2016
NAME 1 .00000501 1 0 5,492 03-29-2016[/code]
执行带where条件的SQL，再次收集统计信息：
[code]select count(*) from A_BIND_TEST where id2=100;

select obj#,intcol#,equality_preds from sys.col_usage$ where obj#=79789;

OBJ# INTCOL# EQUALITY_PREDS
---------- ---------- --------------
79789 2 1[/code]
此时还是没有直方图，再次执行SQL，再次收集统计信息，发现字段上有了直方图信息，且name字段也没有直方图
[code] OBJ# INTCOL# EQUALITY_PREDS
---------- ---------- --------------
79789 2 2

Column Distinct Number Number Sample Date
Name Values Density Buckets Nulls Size MM-DD-YYYY
------------------------------ ------------ ----------- ------- ------------ -------------- ----------
ID1 100,000 .00001000 1 0 100,000 03-29-2016
ID2 2 .00000506 2 0 5,436 03-29-2016
NAME 2 .50000000 1 0 5,436 03-29-2016[/code]
下面执行where条件为name的SQL：
[code]select count(*) from A_BIND_TEST where name='a';
SQL> select obj#,intcol#,equality_preds from sys.col_usage$ where obj#=79789;

OBJ# INTCOL# EQUALITY_PREDS
---------- ---------- --------------
79789 2 2

SQL> 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);

PL/SQL procedure successfully completed.

SQL> select obj#,intcol#,equality_preds from sys.col_usage$ where obj#=79789;

OBJ# INTCOL# EQUALITY_PREDS
---------- ---------- --------------
79789 2 2
79789 3 1

SQL> 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);

PL/SQL procedure successfully completed.

SQL> select obj#,intcol#,equality_preds from sys.col_usage$ where obj#=79789;

OBJ# INTCOL# EQUALITY_PREDS
---------- ---------- --------------
79789 2 2
79789 3 1[/code]
再只执行一次查询，执行两次收集统计信息就会收集直方图信息
[code]Column Distinct Number Number Sample Date
Name Values Density Buckets Nulls Size MM-DD-YYYY
------------------------------ ------------ ----------- ------- ------------ -------------- ----------
ID1 100,000 .00001000 1 0 100,000 03-29-2016
ID2 2 .00000497 2 0 5,528 03-29-2016
NAME 2 .00000497 2 0 5,528 03-29-2016[/code]
也就是在执行一次查询SQL，然后收集两次统计信息后列上有了直方图信息，所以收集直方图与SQL的执行次数无关，第一次执行dbms_stats.gather_table_stats会将name的使用记录flush到SYS.COL_USAGE$中，然后再次收集就会判断这个列是否需要收集。
当然也可以手工指定method_opt参数直接对哪些列收集直方图，还可以指定for all column size repeat只对存在直方图的列收集直方图信息，关于method_opt参数的说明可以参考官博：
How does the METHOD_OPT parameter work?
https://blogs.oracle.com/optimizer/entry/how_does_the_method_opt

查询low_value/high_value脚本如下：
[code]-- col_stats
-- by Martin Widlake
col owner form a6 word wrap
col table_name form a15 word wrap
col column_name form a22 word wrap
col data_type form a12
col M form a1
col num_vals form 99999,999
col dnsty form 0.9999
col num_nulls form 99999,999
col low_v form a18
col hi_v form a18
col data_type form a10
set lines 110
select --owner
-- ,table_name
column_name
,data_type
,decode (nullable,'N','Y','N') M
,num_distinct num_vals
,num_nulls
,density dnsty
,decode(data_type
,'NUMBER' ,to_char(utl_raw.cast_to_number(low_value))
,'VARCHAR2' ,to_char(utl_raw.cast_to_varchar2(low_value))
,'NVARCHAR2' ,to_char(utl_raw.cast_to_nvarchar2(low_value))
,'BINARY_DOUBLE',to_char(utl_raw.cast_to_binary_double(low_value))
,'BINARY_FLOAT' ,to_char(utl_raw.cast_to_binary_float(low_value))
,'DATE',to_char(1780+to_number(substr(low_value,1,2),'XX')
+to_number(substr(low_value,3,2),'XX'))||'-'
||to_number(substr(low_value,5,2),'XX')||'-'
||to_number(substr(low_value,7,2),'XX')||' '
||(to_number(substr(low_value,9,2),'XX')-1)||':'
||(to_number(substr(low_value,11,2),'XX')-1)||':'
||(to_number(substr(low_value,13,2),'XX')-1)
, low_value
) low_v
,decode(data_type
,'NUMBER' ,to_char(utl_raw.cast_to_number(high_value))
,'VARCHAR2' ,to_char(utl_raw.cast_to_varchar2(high_value))
,'NVARCHAR2' ,to_char(utl_raw.cast_to_nvarchar2(high_value))
,'BINARY_DOUBLE',to_char(utl_raw.cast_to_binary_double(high_value))
,'BINARY_FLOAT' ,to_char(utl_raw.cast_to_binary_float(high_value))
,'DATE',to_char(1780+to_number(substr(high_value,1,2),'XX')
+to_number(substr(high_value,3,2),'XX'))||'-'
||to_number(substr(high_value,5,2),'XX')||'-'
||to_number(substr(high_value,7,2),'XX')||' '
||(to_number(substr(high_value,9,2),'XX')-1)||':'
||(to_number(substr(high_value,11,2),'XX')-1)||':'
||(to_number(substr(high_value,13,2),'XX')-1)
, high_value
) hi_v
from dba_tab_columns
where owner = 'AWEN'
and table_name = 'A_BIND_TEST'
ORDER BY owner,table_name,COLUMN_ID
/[/code]
最后推荐大家阅读下老熊的两篇博客：
Oracle数据库升级迁移、SPA及统计信息
http://www.laoxiong.net/oracle-database-migration-upgrade-spa-statistics.html
怎样保持Oracle数据库SQL性能的稳定性
http://www.laoxiong.net/how-keeping-sql-plan-stability.html

10g&11g中如何删除列上的直方图信息：
How do I drop an existing histogram on a column and stop the Auto Stats gathering job from creating it in the future?
https://blogs.oracle.com/optimizer/entry/how_do_i_drop_an_existing_histogram_on_a_column_and_stop_the_auto_stats_gathering_job_from_creating