在版本11.2.0.1.0，ACS（adaptiver cursor sharing）測試的結果是：
1. 在綁定窺探有效的情況下，使用綁定變量，在直方圖准確的情況下，相同的SQL,CBO會對比之前的執行計劃,以前執行過此SQL,如果第一次發現效率比以前低，第二次會重新生成執行計劃。
2. 在綁定窺探失效的情況下，使用綁定變量，CBO完全忽略直方圖。
SQL> select * from v$version;
BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> show parameter _optimizer_adaptive_cursor_sharing;
NAME TYPE VALUE
------------------------------------ ----------- ----------------------
_optimizer_adaptive_cursor_sharing boolean TRUE
SQL> show parameter _optimizer_extended_cursor_sharing;
NAME TYPE VALUE
------------------------------------ ----------- ---------------------
_optimizer_extended_cursor_sharing string UDO
_optimizer_extended_cursor_sharing_rel string SIMPLE
--制造數據
drop table test purge;
create table test(id number,name varchar2(10),descri varchar2(1000));
begin
for i in 1 .. 100000 loop
insert into test values(i,'100',lpad('a',900,'b'));
end loop;
commit;
end;
/
update test set name='200' where rownum=1;
commit;
create index ind_name on TEST(name) nologging
;
--由於在11.2.0.1.0版本下用dbms_stats收集直方圖不准確，所以用analyze
--在Oracle 11.2.0.1.0下dbms_stats.gather_table_stats收集直方圖不准
SQL> analyze table test compute statistics for table for columns name size 2; --exec dbms_stats.gather_table_stats(user,'test',cascade => true,method_opt =>'for all columns size skewonly'); --數據分布 SQL> select name,count(1) from test group by name; NAME COUNT(1) ---------- ---------- 100 99999 200 1 SQL> set autotrace traceonly SQL> select * from test where name='100'; 已選擇99999行。 執行計劃 ---------------------------------------------------------- Plan hash value: 1357081020 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 99999 | 86M| 2664 (1)| 00:00:38 | |* 1 | TABLE ACCESS FULL| TEST | 99999 | 86M| 2664 (1)| 00:00:38 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------
---------------------------------------------------------- 1 recursive calls 0 db block gets 13515 consistent gets 133 physical reads 0 redo size 1564422 bytes sent via SQL*Net to client 73663 bytes received via SQL*Net from client 6668 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 99999 rows processed SQL> select * from test where name='200'; 執行計劃 ---------------------------------------------------------- Plan hash value: 688048857 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 909 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 909 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IND_NAME | 1 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------
---------------------------------------------------------- 1 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 1342 bytes sent via SQL*Net to client 337 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> set autotrace off
--使用綁定窺探，先使用查大部分數據的條件，可以看到兩個都是全表掃描
--先看到綁定窺探是有效的
SQL> show parameter _optim_peek_user_binds; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ _optim_peek_user_binds boolean TRUE SQL> alter system flush shared_pool; SQL> alter system flush buffer_cache; SQL> var ccc varchar2(10); SQL> exec :ccc:='100'; SQL> set autotrace trace stat SQL> select * from test where name=:ccc;
已選擇99999行。
統計信息
---------------------------------------------------------- 354 recursive calls 0 db block gets 13565 consistent gets 6853 physical reads 0 redo size 1564422 bytes sent via SQL*Net to client 73664 bytes received via SQL*Net from client 6668 SQL*Net roundtrips to/from client 7 sorts (memory) 0 sorts (disk) 99999 rows processed SQL> set autotrace off SQL> select hash_value, child_number from v$sql where sql_text = 'select * from test where name=:ccc'; HASH_VALUE CHILD_NUMBER ---------- ------------ 1301684711 0 SQL> select * from table(dbms_xplan.display_cursor(1301684711,0)); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- HASH_VALUE 1301684711, child number 0 -------------------------------------- select * from test where name=:ccc Plan hash value: 1357081020 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2664 (100)| | |* 1 | TABLE ACCESS FULL| TEST | 99999 | 86M| 2664 (1)| 00:00:38 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------
1 - filter("NAME"=:CCC)
第一次執行:ccc:='200'
SQL> exec :ccc:='200'; SQL> set autotrace trace stat SQL> select * from test where name=:ccc; 統計信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 6849 consistent gets 0 physical reads 0 redo size 1342 bytes sent via SQL*Net to client 338 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> set autotrace off SQL> select hash_value, child_number from v$sql where sql_text = 'select * from test where name=:ccc'; HASH_VALUE CHILD_NUMBER ---------- ------------ 1301684711 0
第二次執行:ccc:='200'
SQL> exec :ccc:='200';
SQL> set autotrace trace stat
SQL> select * from test where name=:ccc;
統計信息
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
8 physical reads
0 redo size
1342 bytes sent via SQL*Net to client
338 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace off
SQL> select hash_value, child_number from v$sql
where sql_text = 'select * from test where name=:ccc';
HASH_VALUE CHILD_NUMBER
---------- ------------
1301684711 0
1301684711 1
SQL> select * from table(dbms_xplan.display_cursor(1301684711,1));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
HASH_VALUE 1301684711, child number 1
--------------------------------------
select * from test where name=:ccc
Plan hash value: 688048857
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 909 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_NAME | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NAME"=:CCC)
--使用綁定窺探，先使用查小部分數據的條件
SQL> alter system flush shared_pool;
SQL> alter system flush buffer_cache;
SQL> select hash_value, child_number from v$sql
where sql_text = 'select * from test where name=:ccc';
未選定行
SQL> exec :ccc:='200';
SQL> set autotrace trace stat
SQL> select * from test where name=:ccc;
統計信息
----------------------------------------------------------
354 recursive calls
0 db block gets
54 consistent gets
27 physical reads
0 redo size
1342 bytes sent via SQL*Net to client
338 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace off
SQL> select hash_value, child_number from v$sql
where sql_text = 'select * from test where name=:ccc';
HASH_VALUE CHILD_NUMBER
---------- ------------
1301684711 0
SQL> select * from table(dbms_xplan.display_cursor(1301684711,0));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
HASH_VALUE 1301684711, child number 0
--------------------------------------
select * from test where name=:ccc
Plan hash value: 688048857
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 909 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_NAME | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NAME"=:CCC)
第一次執行ccc:='100'
SQL> exec :ccc:='100';
SQL> set autotrace trace stat
SQL> select * from test where name=:ccc;
已選擇99999行。
統計信息
----------------------------------------------------------
0 recursive calls
0 db block gets
20106 consistent gets
6884 physical reads
0 redo size
1564422 bytes sent via SQL*Net to client
73664 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
99999 rows processed
SQL> set autotrace off
SQL> select hash_value, child_number from v$sql
where sql_text = 'select * from test where name=:ccc';
HASH_VALUE CHILD_NUMBER
---------- ------------
1301684711 0
第二次執行ccc:='100'
SQL> exec :ccc:='100';
SQL> set autotrace trace stat
SQL> select * from test where name=:ccc;
已選擇99999行。
統計信息
----------------------------------------------------------
1 recursive calls
0 db block gets
13515 consistent gets
133 physical reads
0 redo size
1564422 bytes sent via SQL*Net to client
73664 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
99999 rows processed
SQL> set autotrace off
SQL> select hash_value, child_number from v$sql
where sql_text = 'select * from test where name=:ccc';
HASH_VALUE CHILD_NUMBER
---------- ------------
1301684711 0
1301684711 1
SQL> select * from table(dbms_xplan.display_cursor(1301684711,1));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
HASH_VALUE 1301684711, child number 1
--------------------------------------
select * from test where name=:ccc
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2664 (100)| |
|* 1 | TABLE ACCESS FULL| TEST | 99999 | 86M| 2664 (1)| 00:00:38 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NAME"=:CCC)
--不使用綁定窺探，先使用查小部分數據的條件
SQL> alter session set "_optim_peek_user_binds"=false;
SQL> alter system flush shared_pool;
SQL> alter system flush buffer_cache;
SQL> select hash_value, child_number from v$sql
where sql_text = 'select * from test where name=:ccc';
SQL> exec :ccc:='200';
SQL> set autotrace trace stat
SQL> select * from test where name=:ccc;
統計信息
----------------------------------------------------------
354 recursive calls
0 db block gets
6899 consistent gets
6853 physical reads
0 redo size
1342 bytes sent via SQL*Net to client
338 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace off
SQL> select hash_value, child_number from v$sql
where sql_text = 'select * from test where name=:ccc';
HASH_VALUE CHILD_NUMBER
---------- ------------
1301684711 0
--看到是全表掃描，正確的是索引掃描
SQL> select * from table(dbms_xplan.display_cursor(1301684711,0));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
HASH_VALUE 1301684711, child number 0
--------------------------------------
select * from test where name=:ccc
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2663 (100)| |
|* 1 | TABLE ACCESS FULL| TEST | 50000 | 43M| 2663 (1)| 00:00:38 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NAME"=:CCC)
SQL> exec :ccc:='100';
SQL> set autotrace trace stat
SQL> select * from test where name=:ccc;
已選擇99999行。
統計信息
----------------------------------------------------------
0 recursive calls
0 db block gets
13515 consistent gets
0 physical reads
0 redo size
1564422 bytes sent via SQL*Net to client
73664 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
99999 rows processed
SQL> set autotrace off
SQL> select hash_value, child_number from v$sql
where sql_text = 'select * from test where name=:ccc';
HASH_VALUE CHILD_NUMBER
---------- ------------
1301684711 0
SQL> exec :ccc:='100';
SQL> set autotrace trace stat
SQL> select * from test where name=:ccc;
已選擇99999行。
統計信息
----------------------------------------------------------
0 recursive calls
0 db block gets
13515 consistent gets
0 physical reads
0 redo size
1564422 bytes sent via SQL*Net to client
73664 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
99999 rows processed
SQL> set autotrace off
SQL> select hash_value, child_number from v$sql
where sql_text = 'select * from test where name=:ccc';
HASH_VALUE CHILD_NUMBER
---------- ------------
1301684711 0
