The adaptive cursor sharing in 11G

Oracle introduced a new feature, adaptive cursor sharing, in 11g. This ensures that the best execution plan will be used depending on acutal data distribution.

See the test:

SQL> create table testacs (id number,name varchar2(128));

表已创建。
SQL> insert into testacs select 1,object_name from dba_objects;

已创建72057行。

SQL> insert into testacs select 1,object_name from dba_objects;

已创建72058行。

SQL> insert into testacs select 1,object_name from dba_objects;

已创建72058行。

SQL> insert into testacs select 2,object_name from dba_objects where rownum<345;

已创建344行。

SQL> commit;

提交完成。

SQL> select id,count(*) from testacs group by id;

ID COUNT(*)
---------- ----------
1 72057
2 344

SQL> create index testidx on testacs(id);

索引已创建。

SQL> exec dbms_stats.gather_table_stats(user,'TESTACS',method_opt=>'For all columns size skewonly');

PL/SQL 过程已成功完成。

SQL> select column_name,histogram from user_tab_columns where table_name='TESTACS';

COLUMN_NAME HISTOGRAM
------------------------------ ---------------
ID FREQUENCY
NAME HEIGHT BALANCED
SQL> select id,count(*) from testacs group by id;

ID COUNT(*)
---------- ----------
1 216173
2 344

SQL> set autotrace traceonly exp;
SQL> select * from testacs where id=1;

执行计划
----------------------------------------------------------
Plan hash value: 1053663967

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 216K| 5702K| 267 (2)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TESTACS | 216K| 5702K| 267 (2)| 00:00:04 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ID"=1)

SQL> select * from testacs where id=2;

执行计划
----------------------------------------------------------
Plan hash value: 1378877601

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 215 | 5805 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TESTACS | 215 | 5805 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TESTIDX | 215 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=2)

SQL> select * from testacs where id=3;

执行计划
----------------------------------------------------------
Plan hash value: 1378877601

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TESTACS | 1 | 27 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TESTIDX | 1 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=3)

SQL>

The Oracle choose the different Plan for the same query.