11G Adaptive Cursor Sharing(ACS)自適應遊標測試

被10G變數窺視搞得很頭痛，看看11G的ACS新特性。

SQL*Plus: Release 11.2.0.1.0 Production on 星期四 7月 8 14:18:36 2010

Copyright (c) 1982, 2010, Oracle. All rights reserved.

SQL> conn test/test
已連線。
SQL> create table t(id integer,text varchar2(4000));

表已建立。

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

已建立63861行。

SQL> insert into t values(2,'aaa');

已建立 1 行。

SQL> insert into t select 3,object_name from dba_objects where rownum<10000;

已建立9999行。

SQL> commit;

提交完成。

SQL> create index t_idx on t(id);

索引已建立。

SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt=>'for all indexed columns',cascade=>true);

PL/SQL 過程已成功完成。

[@more@]

SQL> var f_id number;
SQL> exec :f_id:=1;

PL/SQL 過程已成功完成。

SQL> set autot trace exp stat
SQL> select * from t where id=:f_id;

已選擇63861行。

執行計劃
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24620 | 625K| 95 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 24620 | 625K| 95 (3)| 00:00:02 |
--------------------------------------------------------------------------

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

1 - filter("ID"=TO_NUMBER(:F_ID))

統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
4596 consistent gets
0 physical reads
0 redo size
1822826 bytes sent via SQL*Net to client
47242 bytes received via SQL*Net from client
4259 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
63861 rows processed

SQL> exec :f_id:=2;

PL/SQL 過程已成功完成。

SQL> select * from t where id=:f_id;

執行計劃
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24620 | 625K| 95 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 24620 | 625K| 95 (3)| 00:00:02 |
--------------------------------------------------------------------------

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

1 - filter("ID"=TO_NUMBER(:F_ID))

統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
361 consistent gets
0 physical reads
0 redo size
479 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> exec :f_id:=3;

PL/SQL 過程已成功完成。

SQL> select * from t where id=:f_id;

已選擇9999行。

執行計劃
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24620 | 625K| 95 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 24620 | 625K| 95 (3)| 00:00:02 |
--------------------------------------------------------------------------

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

1 - filter("ID"=TO_NUMBER(:F_ID))

統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
1025 consistent gets
0 physical reads
0 redo size
286139 bytes sent via SQL*Net to client
7741 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9999 rows processed

SQL> set autot off
SQL> select sql_text,sql_id from v$sql where sql_text like '%select * from t where %';

SQL_TEXT
--------------------------------------------------------------------------------

SQL_ID
-------------
select * from t where id=:f_id
409grq5z4dn0x

SQL> select sql_id,child_number,executions,is_bind_sensitive,is_bind_aware from
v$sql where sql_id='409grq5z4dn0x';

SQL_ID CHILD_NUMBER EXECUTIONS I I
------------- ------------ ---------- - -
409grq5z4dn0x 0 2 Y N
409grq5z4dn0x 1 1 Y Y

SQL> select * from table(dbms_xplan.display_cursor('409grq5z4dn0x',0));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

SQL_ID 409grq5z4dn0x, child number 0
-------------------------------------
select * from t where id=:f_id

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 95 (100)| |
|* 1 | TABLE ACCESS FULL| T | 63679 | 1616K| 95 (3)| 00:00:02 |
--------------------------------------------------------------------------

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

1 - filter("ID"=:F_ID)

已選擇18行。

SQL> select * from table(dbms_xplan.display_cursor('409grq5z4dn0x',1));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

SQL_ID 409grq5z4dn0x, child number 2
-------------------------------------
select * from t where id=:f_id

Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 64 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 10170 | 258K| 64 (0)| 00:00:01|
|* 2 | INDEX RANGE SCAN | T_IDX | 10170 | | 20 (0)| 00:00:01|
-------------------------------------------------------------------------------------

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

2 - access("ID"=:F_ID)

已選擇19行。