Adaptive Cursor Sharing - Part II

This will be continuation of previous post Adaptive cursor sharing in 11gR2. What a difference, If we consider LIKE predicates on highly skewed columns 11gR2 optimizer is not picking up the best execution plan, instead reusing the same execution plans. I set up a test to benchmark.

rajesh@11GR2> select * from v$version;

BANNER
--------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Elapsed: 00:00:00.01
rajesh@11GR2> drop table t purge;

Table dropped.

Elapsed: 00:00:00.06
rajesh@11GR2>
rajesh@11GR2> create table t
  2  as
  3  select 'aaa' as id,
  4       a.*
  5  from all_objects a;

Table created.

Elapsed: 00:00:05.76
rajesh@11GR2>
rajesh@11GR2> insert into t
  2  select 'bbb' as id,
  3        a.*
  4  from all_objects a
  5  where rownum = 1;

1 row created.

Elapsed: 00:00:00.09
rajesh@11GR2>
rajesh@11GR2> commit;

Commit complete.

Elapsed: 00:00:00.00
rajesh@11GR2>
rajesh@11GR2> create index t_ind on t(id) nologging;

Index created.

Elapsed: 00:00:00.48
rajesh@11GR2>
rajesh@11GR2> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'T',estimate_percent=>100,method_opt=>'for all indexed columns size 254');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.85
rajesh@11GR2>

variable x varchar2(10);

exec :x := 'bbb';


SELECT /* ACS */ *
FROM
 T LOOK_FOR_ME WHERE ID like :X

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          3          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID T (cr=3 pr=0 pw=0 time=0 us cost=3 size=101 card=1)
      1   INDEX RANGE SCAN T_IND (cr=2 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 74805)
 
The plan indicates that the optimizer chose an index range scan, which is expected because of the selectivity (only 1%) of the value 'bbb'.

exec :x := 'aaa';
SELECT /* ACS */ *
FROM
 T LOOK_FOR_ME WHERE ID like :X

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      478      0.15       0.15          0       2156          0       71490
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      480      0.15       0.15          0       2156          0       71490

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 91 

Rows     Row Source Operation
-------  ---------------------------------------------------
  71490  TABLE ACCESS BY INDEX ROWID T (cr=2156 pr=0 pw=0 time=136187 us cost=3 size=101 card=1)
  71490   INDEX RANGE SCAN T_IND (cr=628 pr=0 pw=0 time=50003 us cost=2 size=0 card=1)(object id 74805)

Now I have given adaptive cursor sharing a chance to figure out "made a mistake",

exec :x := 'aaa';
SELECT /* ACS */ *
FROM
 T LOOK_FOR_ME WHERE ID like :X

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      478      0.09       0.15          0       2156          0       71490
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      480      0.09       0.15          0       2156          0       71490

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 91 

Rows     Row Source Operation
-------  ---------------------------------------------------
  71490  TABLE ACCESS BY INDEX ROWID T (cr=2156 pr=0 pw=0 time=135291 us cost=3 size=101 card=1)
  71490   INDEX RANGE SCAN T_IND (cr=628 pr=0 pw=0 time=51538 us cost=2 size=0 card=1)(object id 74805)



exec :x := 'aaa';
SELECT /* ACS */ *
FROM
 T LOOK_FOR_ME WHERE ID like :X

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      478      0.18       0.15          0       2156          0       71490
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      480      0.18       0.15          0       2156          0       71490

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 91 

Rows     Row Source Operation
-------  ---------------------------------------------------
  71490  TABLE ACCESS BY INDEX ROWID T (cr=2156 pr=0 pw=0 time=135291 us cost=3 size=101 card=1)
  71490   INDEX RANGE SCAN T_IND (cr=628 pr=0 pw=0 time=51538 us cost=2 size=0 card=1)(object id 74805)

As you see above, The optimizer is not kicking up adaptive cursor sharing even for multiple execution of same cursors.