介绍

我们平时在sql中经常使用捆绑变量来使sql在shared pool中可以被重用，大大减少了sql的hard parse。oracle在hard parse一个sql的时候，如果收集了histogram的信息，如果隐藏参数_optim_peek_user_binds被设置成true(default to true),

那么cbo会去偷窥一下捆绑变量的值，来选择一个更好的执行计划。当然，捆绑变量的偷窥只发生在hard parse。

一前期测试表格创建

CREATE TABLE TEST_20150518 AS SELECT * FROM DBA_OBJECTS;

select count(*) from TEST_20150518;

update TEST_20150518 set status ='VALID';

commit;

select count(*) from TEST_20150518 where status='VALID';

update TEST_20150518 set status='INVALID' where rownum<=100;

commit;

update TEST_20150518 set STATUS='TEST' WHERE ROWNUM<=3;

commit;

SQL> select count(*),status from TEST_20150518 group by status;

COUNT(*) STATUS

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

97 INVALID

3 TEST

50112 VALID

create index idx_TEST_20150518 on TEST_20150518(status);

统计信息包括柱状图收集

EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname=>'sys',tabname=>'TEST_20150518',method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO',estimate_percent=>100,cascade=>true);

从以上可以看出TEST_20150518表中status='VALID'的有50112行，status='INVALID'的有97行，status='TEST'的有三行，如果select * from TEST_20150518

where status='VALID', 那么必定是用全表扫描比较好，如果select * from TEST_20150518 where status='INVALID' 或者select * from TEST_20150518 where status='TEST'那么必定是走索引比较好。

查看柱状图的分布情况

SQL> set line 150

SQL> col owner for a10

SQL> col table_name for a15

SQL> col column_name for a15

SQL> col ENDPOINT_ACTUAL_VALUE for a30

SQL> SELECT * FROM DBA_HISTOGRAMS WHERE table_name='TEST_20150518' and column_name='STATUS';

OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE

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

SYS TEST_20150518 STATUS 97 3.8063E+35

SYS TEST_20150518 STATUS 100 4.3756E+35

SYS TEST_20150518 STATUS 50212 4.4786E+35

histogram 里三个bucket明确表明了该列数据分布情况.

二绑定变量窥视测试

var test varchar2(10);

exec :test:='VALID';

select object_id,status FROM TEST_20150518 where status=:test;

SQL> select sql_id,CHILD_NUMBER from v$sql where sql_text like '%select object_id,status FROM TEST_20150518 where status%';

SQL_ID CHILD_NUMBER

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

82hsrmdvg3vna 0

select * from table(dbms_xplan.display_cursor('82hsrmdvg3vna',0,'ADVANCED'));

PLAN_TABLE_OUTPUT

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

SQL_ID 82hsrmdvg3vna, child number 0

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

select object_id,status FROM TEST_20150518 where status=:test

Plan hash value: 2123920744

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

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

| 0 | SELECT STATEMENT | | | | 156 (100)| |

|* 1 | TABLE ACCESS FULL| TEST_20150518 | 50112 | 587K| 156 (2)| 00:00:02 |

Peeked Binds (identified by position):

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

PLAN_TABLE_OUTPUT

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

1 - :TEST (VARCHAR2(30), CSID=852): 'VALID'

可以看到，第一次硬解析，status=“valid” 时，走了正确的执行计划

exec :test:='INVALID';

select object_id,status FROM TEST_20150518 where status=:test;

SQL> select sql_id,CHILD_NUMBER from v$sql where sql_text like '%select object_id,status FROM TEST_20150518 where status%';

SQL_ID CHILD_NUMBER

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

82hsrmdvg3vna 0

select * from table(dbms_xplan.display_cursor('82hsrmdvg3vna',0,'ADVANCED'));

PLAN_TABLE_OUTPUT

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

SQL_ID 82hsrmdvg3vna, child number 0

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

select object_id,status FROM TEST_20150518 where status=:test

Plan hash value: 2123920744

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

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

| 0 | SELECT STATEMENT | | | | 156 (100)| |

|* 1 | TABLE ACCESS FULL| TEST_20150518 | 50112 | 587K| 156 (2)| 00:00:02 |

Peeked Binds (identified by position):

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

PLAN_TABLE_OUTPUT

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

1 - :TEST (VARCHAR2(30), CSID=852): 'VALID'

可以看到，虽然绑定变量的值变了，但是oracle没有硬解析，没有重新偷窥绑定变量，还是走了错误的全表扫描。

刷新共享池，使得语句重新硬解析

alter system flush shared_pool;

让 sql 重新被硬解析

exec :test:='INVALID';

select object_id,status FROM TEST_20150518 where status=:test;

select sql_id,CHILD_NUMBER,FIRST_LOAD_TIME,last_load_time,LAST_ACTIVE_TIME from v$sql where sql_text like '%select object_id,status FROM TEST_20150518 where status%';

SQL_ID CHILD_NUMBER FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTI

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

d78upf6d4hpqw 0 2015-05-18/13:21:27 2015-05-18/13:21:27 18-MAY-15

82hsrmdvg3vna 0 2015-05-18/13:21:21 2015-05-18/13:21:21 18-MAY-15

select * from table(dbms_xplan.display_cursor('82hsrmdvg3vna',0,'ADVANCED'));

PLAN_TABLE_OUTPUT

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

SQL_ID 82hsrmdvg3vna, child number 0

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

select object_id,status FROM TEST_20150518 where status=:test;

Plan hash value: 498082260

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

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

| 0 | SELECT STATEMENT | | | | 3 (100)| |

| 1 | TABLE ACCESS BY INDEX ROWID| TEST_20150518 | 97 | 1164 | 3 (0)| 00:00:01 |

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

|* 2 | INDEX RANGE SCAN | IDX_TEST_20150518 | 97 | | 1 (0)| 00:00:01 |

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

Peeked Binds (identified by position):

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

1 - :TEST (VARCHAR2(30), CSID=852): 'INVALID'

可以看到，sql被重新hard parse后，走了正确的索引路线

三解决办法

让sql在shared pool里失效，使用DBMS_SHARED_POOL.PURGE，或者alter system flush shared pool.　当然，后两者不推荐，尤其是最后一个！！

不过，oracle 11g里可以动态偷窥绑定变量，放多个执行计划在shared pool,　对这个副作用做了很大改进。具体新特性可以去看11g文档。

绑定变量窥视测试案例

介绍

二 绑定变量窥视测试

三 解决办法

二绑定变量窥视测试

三解决办法