绑定变量窥视测试案例

来源：这里教程网时间：2026-03-03 16:46:16 作者：

介绍

我们平时在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 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 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 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 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 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 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'