Adaptive Cursor Sharing Execution Plan

I recently came across this feature in 11g r2 and wanna test to see if the execution plan whether is really working.


And I encounter some issue when I set autotrace on using bind variable, it unable to shows what I expected.

I post a question in asktom.oracle.com and turn out that bind variable doesn't work in explain plan.


Here the final testing.
And is working as expected using dbms_xplan.display_cursor.



LUCOTUS @ TEST> create table test as select dba_objects.*, 'Y' ind from dba_objects ;

Table created.

LUCOTUS @ TEST> insert into test select dba_objects.*, 'N' from dba_objects where rownum =1;

1 row created.

LUCOTUS @ TEST> commit;

Commit complete.

LUCOTUS @ TEST> create index idx_test on test(ind);

Index created.


LUCOTUS @ TEST>
LUCOTUS @ TEST> exec dbms_stats.gather_schema_stats(user);

PL/SQL procedure successfully completed.

LUCOTUS @ TEST> exec dbms_stats.gather_table_stats(user,'TEST',method_opt=>'FOR COLUMNS SIZE 2 IND', cascade=>true);

PL/SQL procedure successfully completed.



LUCOTUS @ TEST> variable v_ind varchar2
LUCOTUS @ TEST> exec :v_ind :='Y'

PL/SQL procedure successfully completed.


LUCOTUS @ TEST> select max(timestamp) from test where ind =:v_ind ;

MAX(TIMESTAMP)
-------------------
2011-07-05:10:54:59

LUCOTUS @ TEST> set long 9999999
LUCOTUS @ TEST> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID btycqu1h6y98b, child number 0
-------------------------------------
select max(timestamp) from test where ind =:v_ind

Plan hash value: 3467505462

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 55 (100)| |
| 1 | SORT AGGREGATE | | 1 | 22 | | |

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 2 | TABLE ACCESS FULL| TEST | 14109 | 303K| 55 (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("IND"=:V_IND)


19 rows selected.

LUCOTUS @ TEST> set pagesize 40
LUCOTUS @ TEST> exec :v_ind :='N'

PL/SQL procedure successfully completed.

LUCOTUS @ TEST> select max(timestamp) from test where ind =:v_ind ;

MAX(TIMESTAMP)
-------------------
2011-06-29:09:21:29

LUCOTUS @ TEST> select * from table(dbms_xplan.display_cursor);


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3p2ja0ksm977m, child number 0
-------------------------------------
select max(timestamp) from test where ind =:v_ind

Plan hash value: 3834481504

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 22 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 22 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_TEST | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

3 - access("IND"=:V_IND)


20 rows selected.