关于sql的绑定变量，在11G之前，如果谓词列中有了直方图，不可避免的会遇到一个问题，绑定变量窥探(bind peeking)
11G出现了ACS，可以对每个不同的值生成一个相应的子cursor，保证执行计划的合理性：
今天我探究的主要目的是，ACS是否只有在有直方图的情况下才会生成子cursor，有没有直方图会不会影响ACS的应用
结论是：不是的。无论有没有直方图，值变化以后 ACS都会生效，会生成别的子cursor
不过因为没有直方图，所以任何值返回的rows都是一致的，则执行计划也是完全一致的。
实验过程
---------------------------------确认版本-------------------- SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for Linux: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production
-----------------------------------数据初始化-------------------- SQL> create table t (id1 varchar2(10),id2 varchar2(10),id3 varchar2(10)); SQL> insert into t select 'K','K','K' from dba_objects where rownum<10001; SQL> insert into t select 'A','K','K' from dba_objects where rownum<10001; SQL> insert into t select 'M','K','K' from dba_objects where rownum<21; SQL>COMMIT; --------------------------------索引--------------------------- SQL> create index idx_t_id1 on t(id1);
-----------------------------------确认数据倾泻很严重------------------------- SQL> select id1,count(*) from t group by id1;----表初始化完成 ID1 COUNT(*) ---------- ---------- K 10000 M 20 A 10000
----------------------------------刷shared_pool，初始化环境----------------------------
SQL> alter system flush shared_pool;
System altered.
---------------------------------------收集统计信息，不收集直方图---------------------------------------
SQL> exec dbms_stats.gather_table_stats('SYS','T',cascade=>true,method_opt=>'for columns size 1 id1',estimate_percent=>100);
-------------------------------------------------实验开始-----------------------------
-----------------------绑定变量a，赋值K。
SQL> variable a varchar2(20)
SQL> exec :a :='K';
PL/SQL procedure successfully completed.
SQL> select * from t where id1=:a; ---根据绑定变量来查
10000 rows selected.
查看父游标
SQL> col sql_text for a50
SQL> set linesize 150
SQL>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select * from t%';
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
--------------------------- ------------- ------------- ----------
select * from t where id1=:a dkatjhft2bfmv 1 1
第一次查询，有一个版本的cursor。这时候查看 执行计划
SQL> select * from table(dbms_xplan.display_cursor('dkatjhft2bfmv',0,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID dkatjhft2bfmv, child number 0
-------------------------------------
select * from t where id1=:a
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 10 (100)| |
|* 1 | TABLE ACCESS FULL| T | 6673 | 40038 | 10 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------