Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Similar cursor sharing and adative cursor sharing

with 8 comments

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

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.

About these ads

Written by Dion Cho

June 1, 2009 at 2:41 am

8 Responses

Subscribe to comments with RSS.

  1. Dion,

    Nice post. I have not found SIMILAR to be useful. FORCE seems to work pretty much as advertised (although I don’t think it should be used as a long term solution). In addition to your points, I think it’s worth noting that SIMILAR has a long history of buggyness.

    Adaptive Cursor Sharing holds great promise, but I don’t believe it is quite ready for prime time yet. I just did a post (this morning actually) here detailing observations of a 10g system that was plagued with bind variable peeking problems which we recently migrated to 11.1.0.7. Unfortunately, ACS didn’t magically make all the problems go away as I had hoped it would.

    Kerry

    Kerry Osborne

    June 1, 2009 at 8:07 pm

  2. Cursor_sharing=SIMILAR has proved to be very useful for me!

    I use SIMILAR combined with bind variables at my seminars for generating troubleshooting cases like ORA-600′s, exploding library cache parent cursors, causing shared pool trouble and exposing couple bugs in Oracle instrumentation ;)

    Tanel Poder

    June 4, 2009 at 10:55 pm

  3. Ha! Maybe it is useful for something then.

    Kerry

    Kerry Osborne

    June 4, 2009 at 11:39 pm

  4. I hear someone in Oracle sobbing. :)

    Dion Cho

    June 5, 2009 at 12:35 am

  5. [...] Cho writes, “Similar cursor sharing and multiple child cursors are very common problems . . .  What makes people confused about simiar cursor [...]

  6. Our Application is almost used literal SQL statements.
    I’ll use CURSOR_SHARING=FORCE option because of huge shared pool memory and hard parsing.
    I expect to solve hard parsing and shared pool problems but concern about its side effects.
    Let me know side effects of FORCE option.
    System Info: 10gR2, set bind_peeking = true, use histogram
    Thanks~

    Kari

    December 11, 2009 at 9:34 am

  7. ORACLE said,
    “Potentially the worst case as only one plan will be used for each distinct stmt and all occurrences of that stmt will use that plan”.
    If I change all literal SQLs to binds SQL, dose it same with CURSOR_SHARING=FORCE ?

    Kari

    December 14, 2009 at 12:14 am

    • Hi, Kari.

      The side effects of forced cursor sharing would be
      - sudden execution plan change especially when you have histograms
      - bugs – you should check if there are critical bugs by cursor sharing on your version of the database

      One thing you can try would be
      - set cursor_sharing = force
      - apply CURSOR_SHARING_EXACT hint on the queries suffering from the bad execution plan

      Dion Cho

      December 14, 2009 at 1:03 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 58 other followers

%d bloggers like this: