Similar cursor sharing and adative cursor sharing

Similar cursor sharing and multiple child cursors are very common problems as shown here.

• http://optimizermagic.blogspot.com/2009/05/why-do-i-have-hundreds-of-child-cursors.html
• http://forums.oracle.com/forums/thread.jspa?threadID=904558&start=0&tstart=0

What makes people confused about simiar cursor sharing is this.

What the hell does “SIMILAR” mean exactly?

My opinion is…

Even with the tiny chance of the different execution plans, Oracle would deny to share the cursor.

Okay, then, when do we have any tiny chance of different execution plans with literal predicates? Many documents mention only about histogram, but as far as I know, we have more cases.

1. Histogram predicate
2. Range predicate
3. Partition key predicate

Very reasonable, isn’t it?

But the stupidity of similar cursor sharing implementation drives some peolpe crazy. Oracle just spawns as many child cursors as the distinct count of literal value. This means that this SQL statment

alter session set cursor_sharing = similar;
(Assuming the existence of histogram on the column c1)
select * from t1 where c1 = 1;
select * from t1 where c1 = 2;
select * from t1 where c1 = 3;
...
select * from t1 where c1 = 100;

would have 100 child cursors, not just one shared child cursor. This would result in a disaster under certain circumstances.

Following is a simple test case that demonstrates the stupidity of similar cursor sharing.

UKJA@ukja102> create table t1(c1 int, c2 int);

Table created.

Elapsed: 00:00:00.01
UKJA@ukja102> 
UKJA@ukja102> insert into t1
  2  select level, 1
  3  from dual connect by level <= 10000
  4  ;

10000 rows created.

Elapsed: 00:00:00.04
UKJA@ukja102> 
UKJA@ukja102> insert into t1
  2  select level, 2
  3  from dual connect by level <= 1
  4  ;

1 row created.

Elapsed: 00:00:00.01
UKJA@ukja102> 
UKJA@ukja102> create index t1_n1 on t1(c1);

Index created.

Elapsed: 00:00:00.15
UKJA@ukja102> -- Gather histogram
UKJA@ukja102> exec dbms_stats.gather_table_stats(user, 't1', -
> 	  method_opt=>'for all columns size skewonly');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.23
UKJA@ukja102> -- SIMILAR cursor sharing
UKJA@ukja102> alter session set cursor_sharing = similar;

Session altered.

Elapsed: 00:00:00.00
UKJA@ukja102> -- Case1: Range predicate
UKJA@ukja102> select /* range_predicate */ count(c2) from t1 where c1 between 1 and 100;
UKJA@ukja102> select /* range_predicate */ count(c2) from t1 where c1 between 1 and 200;
UKJA@ukja102> select /* range_predicate */ count(c2) from t1 where c1 between 1 and 300;
UKJA@ukja102> select /* range_predicate */ count(c2) from t1 where c1 between 1 and 400;
UKJA@ukja102> select /* range_predicate */ count(c2) from t1 where c1 between 1 and 10000;

UKJA@ukja102> -- Version count is 5 which means no sharing happend
UKJA@ukja102> select sql_text, version_count from v$sqlarea
  2  where sql_text like 'select /* range_predicate */ count(c2) from t1%';

SQL_TEXT                                                                        
--------------------------------------------------------------------------------
VERSION_COUNT                                                                   
-------------                                                                   
select /* range_predicate */ count(c2) from t1 where c1 between :"SYS_B_0" and :
"SYS_B_1"                                                                       
            5    

UKJA@ukja102> -- Case2. Histogram predicate
UKJA@ukja102> select /* histogram */ count(c2) from t1 where c2 = 1;
UKJA@ukja102> select /* histogram */ count(c2) from t1 where c2 = 2;
UKJA@ukja102> select /* histogram */ count(c2) from t1 where c2 = 3;
UKJA@ukja102> select /* histogram */ count(c2) from t1 where c2 = 4;
UKJA@ukja102> select /* histogram */ count(c2) from t1 where c2 = 5;

UKJA@ukja102> -- Version count is 5 which means no sharing happend
UKJA@ukja102> select sql_text, version_count from v$sqlarea
  2  where sql_text like 'select /* histogram */ count(c2) from t1%';

SQL_TEXT                                                                        
--------------------------------------------------------------------------------
VERSION_COUNT                                                                   
-------------                                                                   
select /* histogram */ count(c2) from t1 where c2 = :"SYS_B_0"                  
            5                                                                   

UKJA@ukja102> create table t2(c1 int, c2 int)
  2  partition by list(c1) (
  3  	     partition p1 values (1),
  4  	     partition p2 values (2),
  5  	     partition p3 values (3)
  6  );

Table created.

Elapsed: 00:00:00.11
UKJA@ukja102> 
UKJA@ukja102> insert into t2
  2  select mod(level,3)+1, level
  3  from dual connect by level  
UKJA@ukja102> exec dbms_stats.gather_table_stats(user, 't2');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.15
UKJA@ukja102> 
UKJA@ukja102> alter session set cursor_sharing = similar;

Session altered.

Elapsed: 00:00:00.00
UKJA@ukja102> -- Case3: Partition key predicate
UKJA@ukja102> select /* partition */ count(c2) from t2 where c1 = 1;
UKJA@ukja102> select /* partition */ count(c2) from t2 where c1 = 2;
UKJA@ukja102> select /* partition */ count(c2) from t2 where c1 = 3;
UKJA@ukja102> select /* partition */ count(c2) from t2 where c1 = 4;
UKJA@ukja102> select /* partition */ count(c2) from t2 where c1 = 5;

UKJA@ukja102> -- Version count is 5 which means no sharing happend
UKJA@ukja102> select sql_text, version_count from v$sqlarea
  2  where sql_text like 'select /* partition */ count(c2) from t2%';

SQL_TEXT                                                                        
--------------------------------------------------------------------------------
VERSION_COUNT                                                                   
-------------                                                                   
select /* partition */ count(c2) from t2 where c1 = :"SYS_B_0"                  
            5                                                                   
            

Do you agree with me on the stupidity of SIMILAR cursor sharing implementation?

By the way, the only positive way to handle this problem is introduced as of 11g in the name of adaptive cursor sharing. The funny thing is we don’t need SIMILAR cursor sharing any more. FORCE sharing + adaptive cursor sharing would solve all these problems.

Following simple test case shows that SIMILAR cursor sharing is useless with adaptive cursor sharing. FORCE cursor sharing is now superior to SIMILAR cursor sharing!

select /* partition */ count(c2) from t2 where c1 = 1;
select /* partition */ count(c2) from t2 where c1 = 2;
select /* partition */ count(c2) from t2 where c1 = 3;
select /* partition */ count(c2) from t2 where c1 = 4;
select /* partition */ count(c2) from t2 where c1 = 5;

alter session set cursor_sharing = similar;

UKJA@ukja116> select sql_text, version_count from v$sqlarea
  2  where sql_text like 'select /* partition */ count(c2) from t2%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select /* partition */ count(c2) from t2 where c1 = :"SYS_B_0"
            5

alter session set cursors_sharing = force;

UKJA@ukja116> select sql_text, version_count from v$sqlarea
  2  where sql_text like 'select /* partition */ count(c2) from t2%';

SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT
-------------
select /* partition */ count(c2) from t2 where c1 = :"SYS_B_0"
            2

UKJA@ukja116> @shared_cursor 'select /* partition */ count(c2) from t2%'
UKJA@ukja116> set echo off
old  14:           and q.sql_text like ''&1''',
new  14:           and q.sql_text like ''select /* partition */ count(c2) from t2%''',
SQL_TEXT                       = select /* partition */ count(c2) from t2 where
c1 = :"SYS_B_0"
SQL_ID                         = 5n1vgm2quammk
ADDRESS                        = 2F63F08C
CHILD_ADDRESS                  = 2B78CBE4
CHILD_NUMBER                   = 0
LOAD_OPTIMIZER_STATS           = Y
--------------------------------------------------
SQL_TEXT                       = select /* partition */ count(c2) from t2 where
c1 = :"SYS_B_0"
SQL_ID                         = 5n1vgm2quammk
ADDRESS                        = 2F63F08C
CHILD_ADDRESS                  = 262D3680
CHILD_NUMBER                   = 1
BIND_MISMATCH                  = Y
--------------------------------------------------

Adaptive cursor sharing really looks like adaptive and more powerful than any other previous implementations on the cursor sharing. Looks like prominent new feature, but let’s see what happens in the real production system.