CURSOR SHARING DEMYSTIFIED

As I discussed in my earlier post on parent and child cursors, multiple child cursors may be created for the same parent cursor if bind variables have different values.
In this post I will discuss about the parameter CURSOR_SHARING which controls the sharing of child cursors if bind variables have different values.

The parameter CURSOR_SHARING can take 3 values :

  •  – EXACT
  •  – SIMILAR
  •  – FORCE

Let’s see the impact of different values :

CURSOR_SHARING = EXACT

– In this case when the same statement is issued with different literals, multiple parent cursors will be created.

— create a test table with

1 record with id1 = id2 = 1

1000 records with id1 = id2 = 2

2000 records with id1 = id2= 3

— create an index on the table

HR> drop table test purge;
create table test (id1 number, id2 number, txt char(1000));
insert into test values (1,1, ‘one’);

begin
for i in 1..1000 loop
insert into test values (2,2, ‘two’);
insert into test values (3,3, ‘three’);
end loop;
end;
/

    insert into test select * from test where id1=3;
commit;

    create index test_idx1 on test(id1);
create index test_idx2 on test(id2);

    select id1,id2, count(*)
from test
group by id1,id2;


CURSOR_SHARING=EXACT 

 

Parent   Parent  Parent
       |               |             |
 Child     Child  Child
 

— Flush the shared pool

Set cursor_sharing=exact

SYS>alter system set CURSOR_SHARING=’EXACT';
alter system flush shared_pool;
sho parameter CURSOR_SHARING

NAME                                 TYPE        VALUE
—–                                        —-        —–

cursor_sharing                       string      EXACT
— Issue identical statements with different values of literals

HR>conn hr/hr
select count(*) from test where id1=1;
select count(*) from test where id1=2;
select count(*) from test where id1=3;

— Check that the 3 parent cursors have been created

— Note that   there is one record for each statement in v$sqlarea as   one parent cursor is created for each sql statement since  each of these statements differ in their text.

  •     Each statement has different SQL_ID/HASH_VALUE
  •    There is one child per parent cursor (version_count=1)
  •     Execution plans for id = 2,3 is same (full table scan) (same PLAN_HASH_VALUE)
  •     Execution plan for id = 1 is different (indexed access)

SYS>col sql_text for a30 word_wrapped

         SELECT SQL_TEXT , SQL_ID, VERSION_COUNT, HASH_VALUE,PLAN_HASH_VALUE
FROM V$SQLAREA
WHERE LOWER(SQL_TEXT) LIKE ‘select count(*) from test%’
AND LOWER(SQL_TEXT) NOT LIKE ‘%HASH%';

SQL_TEXT                       SQL_ID        VERSION_COUNT HASH_VALUE    PLAN_HASH_VALUE
—————————— ————- ————- ———-               —————

select count(*) from test      1n09m564gh0q3          1  2297955011       4192825871
where id1=3

select count(*) from test      20nhaap8uxf7s             1   1370405112       3507950989
where id1=2

select count(*) from test      bavqx2mw26wg0         1  4163072480    3507950989
where id1=1

— Note that 3 child cursors have been created for the 3 statements

SYS>col child_number for 99
         SELECT SQL_TEXT, SQL_ID, CHILD_NUMBER CHILD#, HASH_VALUE, 
                        PLAN_HASH_VALUE
         FROM V$SQL
WHERE LOWER(SQL_TEXT) LIKE ‘select count(*) from test%’
AND LOWER(SQL_TEXT) NOT LIKE ‘%HASH%';

SQL_TEXT                       SQL_ID            CHILD# HASH_VALUE    PLAN_HASH_VALUE

—————————— ————- ———- ———-   —————

select count(*) from test      1n09m564gh0q3          0 2297955011   4192825871
where id1=3

select count(*) from test      20nhaap8uxf7s          0 1370405112       3507950989
where id1=2

select count(*) from test      bavqx2mw26wg0          0 4163072480   3507950989
where id1=1

— We can see that in all 6 cursors have been created :
– 3 parent cursors and
– 3 child cursors

Each of the cursor occupies memory. Parent cursors contain sql text whereas
child cursor contains execution plan, execution statistics and execution
environment. If we replace literal with a bind variable, all the 3 statements
will be identical and hence only parent cursor needs to be created. Multiple
child cursors can be created for different values of the bind variables.

That’s what CURSOR_SHARING=SIMILAR does. It replaces literals in the otherwise
identical SQL statements with bind variables and only one parent cursor is
created.

If histogram on a column is created with only one bucket,i.e. it does not know about the skew
in data, only one child cursor will be created.

If histogram is created on a column with >1 buckets i.e. it knows about skew in data in that
column, it  will create one child cursor for each statement even of the execution plan is same.

Thus CURSOR_SHARING=SIMILAR reduces the no. parent cursors.

If there is skew in data
If histogram on the column containing skewed data is there
multiple child cursors may be created – one for each value of the bind variable
else (histogram is not available)
only one child cursor will be created.
else (Data is not skewed)
only one child cursor will be created.

Now, since there is identical skewed data in id1 and id2 , we will create histogram  on id1
with one bucket and on id2 with 4 buckets and see the difference.

CURSOR_SHARING=SIMILAR  WITHOUT HISTOGRAM

Parent  
  |        
 Child    

— create histogram only on id1 with one bucket so that optimizer does not
know about the skew —

HR>exec dbms_stats.gather_table_stats(OWNNAME => ‘HR’,-
TABNAME => ‘TEST’,-
ESTIMATE_PERCENT =>null,-
METHOD_OPT => ‘FOR COLUMNS SIZE 1 ID1′);

— Set cursor_sharing = similar —

— Flush the shared pool

SYS>alter system set CURSOR_SHARING=’SIMILAR';
alter system flush shared_pool;
sho parameter CURSOR_SHARING

— Issue identical statements with different values of literals for the column on which histogram is not there (id1)

HR>conn hr/hr
select count(*) from test where id1=1;
select count(*) from test where id1=2;
select count(*) from test where id1=3;

— Check that the only 1 parent cursor has been created and literal has been replaced by bind variable. ( 1 record in v$SQLAREA)

.There is only one child  cursor (version_count=1) since the optimizer does not know about skew in data

SYS>col sql_text for a30 word_wrapped

         SELECT SQL_TEXT , SQL_ID, VERSION_COUNT, HASH_VALUE,PLAN_HASH_VALUE
FROM V$SQLAREA
WHERE LOWER(SQL_TEXT) LIKE ‘select count(*) from test%’
AND LOWER(SQL_TEXT) NOT LIKE ‘%HASH%';

SQL_TEXT                       SQL_ID        VERSION_COUNT HASH_VALUE    PLAN_HASH_VALUE

—————————— ————- ————- ———-  —————

select count(*) from test      07tpk6bm7j4qm             1   3866661587   3507950989
where id1=:”SYS_B_0″

— Note there is only one child cursor created i.e. same execution plan will be used for different values of the bind variable

SYS>col child_number for 99
         SELECT SQL_TEXT, SQL_ID, CHILD_NUMBER CHILD#, HASH_VALUE,
                       PLAN_HASH_VALUE
         FROM V$SQL
WHERE LOWER(SQL_TEXT) LIKE ‘select count(*) from test%’
AND LOWER(SQL_TEXT) NOT LIKE ‘%HASH%';

SQL_TEXT                       SQL_ID            CHILD# HASH_VALUE  PLAN_HASH_VALUE

—————————— ————- ———- ———-  —————

select count(*) from test      07tpk6bm7j4qm          0   3866661587    3507950989
where id1=:”SYS_B_0″

CURSOR_SHARING=SIMILAR  WITH HISTOGRAM

               Parent

                   +
   +—- —+——–+
   |                |               |
  Child    Child    Child

— create histogram  on id2 with  4 buckets so that optimizer knows about  the skew in data —

HR>exec dbms_stats.gather_table_stats(OWNNAME => ‘HR’,-
TABNAME => ‘TEST’,-
ESTIMATE_PERCENT =>null,-
CASCADE => TRUE,-
METHOD_OPT => ‘FOR COLUMNS SIZE 4 ID2′); 

— Issue identical statements with different values of literals for the column  on which histogram is there (id2)

SYS>alter system flush shared_pool;

HR>conn hr/hr
select count(*) from test where id2=1;
select count(*) from test where id2=2;
select count(*) from test where id2=3;

— Check that the only 1 parent cursor has been created and literal has been replaced by bind variable. ( 1 record in v$SQLAREA)
.There are 3 child cursors (version_count=3)

SYS>col sql_text for a30 word_wrapped

         SELECT SQL_TEXT , SQL_ID, VERSION_COUNT, HASH_VALUE,PLAN_HASH_VALUE
FROM V$SQLAREA
WHERE LOWER(SQL_TEXT) LIKE ‘select count(*) from test%’
AND LOWER(SQL_TEXT) NOT LIKE ‘%HASH%';

SQL_TEXT                       SQL_ID        VERSION_COUNT HASH_VALUE  PLAN_HASH_VALUE

—————————— ————- ————- ———-  ————–

select count(*) from test      3tcujqmqnqs8t             3   3981140249  2432738936
where id2=:”SYS_B_0″

— Note that 3 child cursors have been created as optimizer realizes that data is skewed and different execution plans will be more efficient for different values of the bind variable.
—  2 children have same execution plan (PLAN_HASH_VALUE)      (for id=2 and 3 (Full table scan )

SYS>col child_number for 99
    SELECT SQL_TEXT, SQL_ID, CHILD_NUMBER CHILD#, HASH_VALUE,  
                   PLAN_HASH_VALUE
     FROM V$SQL
WHERE LOWER(SQL_TEXT) LIKE ‘select count(*) from test%’
AND LOWER(SQL_TEXT) NOT LIKE ‘%HASH%';

SQL_TEXT                       SQL_ID            CHILD# HASH_VALUE  PLAN_HASH_VALUE

—————————— ————- ———- ———-   ————–

select count(*) from test      3tcujqmqnqs8t          0   3981140249   2432738936
where id2=:”SYS_B_0″

select count(*) from test      3tcujqmqnqs8t          1   3981140249   2432738936
where id2=:”SYS_B_0″

select count(*) from test      3tcujqmqnqs8t          2 3981140249   1489241381
where id2=:”SYS_B_0″

Hence, it can be seen that setting CURSOR_SHARING=SIMILAR
– replaces literals with bind variables in otherwise identical sql statements

  • - Only one child cursor is created if optimizer does not know about skew in   data
  • - If optimizer is aware of the skew in data, Multiple child cursors are created   for each distinct value of the bind   variable even if they have the same   executiion plan.

Ideally we would like one child cursor to be created if execution plan is same for different values of the bind variable.

Setting CURSOR_SHARING=FORCE IN 11G does precisely this but only if the optimizer is

aware about the skew in the data. Let’s see:

CURSOR_SHARING=FORCE IN 11G WITHOUT HISTOGRAM

Parent  

     |        
 Child    
– Flush the shared pool and issue query using the column without histogram on
it so that optimizer is not aware of the skew.
SYS>alter system set CURSOR_SHARING=’FORCE';

          alter system flush shared_pool;

HR>conn hr/hr
select count(*) from test where id1=1;
select count(*) from test where id1=2;
select count(*) from test where id1=3;

— Note that only one parent cursor is created

One child cursor has been created (version_count=1)

SYS>col sql_text for a30 word_wrapped
         SELECT SQL_TEXT , SQL_ID, VERSION_COUNT, HASH_VALUE,PLAN_HASH_VALUE
         FROM V$SQLAREA
WHERE LOWER(SQL_TEXT) LIKE ‘select count(*) from test%’
AND LOWER(SQL_TEXT) NOT LIKE ‘%HASH%';

SQL_TEXT                       SQL_ID        VERSION_COUNT HASH_VALUE   PLAN_HASH_VALUE

—————————— ————- ————- ———-    ————–

select count(*) from test      07tpk6bm7j4qm             1   3866661587   3507950989
where id1=:”SYS_B_0″

— Note that 1 child cursor has been created

SYS>col child_number for 99
    SELECT SQL_TEXT, SQL_ID, CHILD_NUMBER CHILD#, HASH_VALUE,
                   PLAN_HASH_VALUE
     FROM V$SQL
WHERE LOWER(SQL_TEXT) LIKE ‘select count(*) from test%’
AND LOWER(SQL_TEXT) NOT LIKE ‘%HASH%';

SQL_TEXT                       SQL_ID            CHILD# HASH_VALUE   PLAN_HASH_VALUE

—————————— ————- ———- ———-   —————

select count(*) from test      07tpk6bm7j4qm          0   3866661587    3507950989
where id1=:”SYS_B_0″

CURSOR_SHARING=FORCE IN 11G WITH HISTOGRAM
      Parent

           |

   +—+—-+
   |              |
  Child    Child

– Flush the shared pool and issue query using the column with histogram on it so that optimizer is aware of the skew.

SYS> alter system flush shared_pool;

HR>conn hr/hr
select count(*) from test where id2=1;
select count(*) from test where id2=2;
select count(*) from test where id2=3;

— Note that only one parent cursor is created

Two child cursors have been created (version_count=2)

SYS>col sql_text for a30 word_wrapped

         SELECT SQL_TEXT , SQL_ID, VERSION_COUNT, HASH_VALUE,PLAN_HASH_VALUE
FROM V$SQLAREA
WHERE LOWER(SQL_TEXT) LIKE ‘select count(*) from test%’
AND LOWER(SQL_TEXT) NOT LIKE ‘%HASH%';

SQL_TEXT                       SQL_ID        VERSION_COUNT HASH_VALUE   PLAN_HASH_VALUE
—————————— ————- ————- ———-   —————

select count(*) from test      3tcujqmqnqs8t             2   3981140249   2432738936
where id2=:”SYS_B_0″

— Note that 2 child cursors have been created and    each child has a distinct execution plan (PLAN_HASH_VALUE)

SYS>col child_number for 99
         SELECT SQL_TEXT, SQL_ID, CHILD_NUMBER CHILD#, HASH_VALUE,  
                        PLAN_HASH_VALUE
         FROM V$SQL
WHERE LOWER(SQL_TEXT) LIKE ‘select count(*) from test%’
AND LOWER(SQL_TEXT) NOT LIKE ‘%HASH%';

SQL_TEXT                       SQL_ID            CHILD# HASH_VALUE  PLAN_HASH_VALUE

—————————— ————- ———- ———-  —————

select count(*) from test      3tcujqmqnqs8t          0  3981140249   2432738936
where id2=:”SYS_B_0″

select count(*) from test      3tcujqmqnqs8t          1  3981140249  1489241381
where id2=:”SYS_B_0″

Hence, setting CURSOR_SHARING=FORCE in 11g will use the same child cursor if   execution plan is same for different values of the bind variables which means  saving in memory in the shared pool and saving in the time for scanning the  hash chains in the library cache . This new feature of 11g is called ADAPTIVE CURSOR SHARING.

Note: The behaviour of CURSOR_SHARING=FORCE in 11g is different from 9i/10g. Earlier, it would peek the value of the bind variable during the first execution and decide on the eexcution plan. On subsequent execution of the same statement with different values of the bind variable, it would reuse the same plan irrespective of the skew in the data.

CURSOR_SHARING=FORCE IN 10G WITH/WITHOUT HISTOGRAM

Parent  

  |        

 Child    
  Let’s demonstrate this by simulating 10g optimizer by setting the parameter optimizer_geatures_enable to 10.2.0.0.

SYS> alter system set optimizer_features_enable=’10.2.0.3′;

— Flush the shared pool and issue query using the column with histogram on

it so that optimizer is aware of the skew.

SYS> alter system flush shared_pool;

HR>conn hr/hr
select count(*) from test where id2=1;
select count(*) from test where id2=2;
select count(*) from test where id2=3;

— Note that only one parent cursor is created

Only child cursor has been created (version_count=1)

SYS>col sql_text for a30 word_wrapped

    SELECT SQL_TEXT , SQL_ID, VERSION_COUNT, HASH_VALUE,PLAN_HASH_VALUE
FROM V$SQLAREA
WHERE LOWER(SQL_TEXT) LIKE ‘select count(*) from test%’
AND LOWER(SQL_TEXT) NOT LIKE ‘%HASH%';

SQL_TEXT                       SQL_ID        VERSION_COUNT HASH_VALUE PLAN_HASH_VALUE

—————————— ————- ————- ———- —————

select count(*) from test      3tcujqmqnqs8t             1 3981140249      2432738936

where id2=:”SYS_B_0″

— Note that 1 child cursor has been created

SYS>col child_number for 99

    SELECT SQL_TEXT, SQL_ID, CHILD_NUMBER CHILD#, HASH_VALUE, PLAN_HASH_VALUE
FROM V$SQL
WHERE LOWER(SQL_TEXT) LIKE ‘select count(*) from test%’
AND LOWER(SQL_TEXT) NOT LIKE ‘%HASH%';

SQL_TEXT                       SQL_ID            CHILD# HASH_VALUE PLAN_HASH_VALUE

—————————— ————- ———- ———- —————

select count(*) from test      3tcujqmqnqs8t          0 3981140249      2432738936

where id2=:”SYS_B_0″

– cleanup –

SYS>alter system set optimizer_features_enable=’11.2.0.1′;

         drop table hr.test purge;


CONCLUSION:

CURSOR_SHARING = EXACT

– Causes maximum memory usage in library cache as two cursors – one parent and one child cursor are created for each distinct value of the bind variable.

– Gives best performance as optimizer creates different execution plan for each value of the bind variable.

CURSOR_SHARING = SIMILAR
- Reduces memory usage in library cache as only one parent cursor is created .
- If data is not skewed or the optimizer is not aware of the skew, optimizer peeks at the value of the bind variable on the first execution of the statement and that plan is used for all the  values of the bind variable. Thus only one child cursor is created resulting in minimum memory usage by child cursors. In this case performance will be affected if there is skew in the data.
- If data is skewed and the optimizer is aware of the skew, multiple child cursor are created – one for each distinct value of the bind variable. In this case performance will be the best as optimizer creates different execution plan for each value of the bind variable. But in this case we will have multiple child cursors created for the same execution plan.
CURSOR_SHARING = FORCE IN 10g
- Causes minimum memory usage in library cache as only one parent cursor and only one child cursor are created .
- In this case performance will be affected if there is skew in the data.
CURSOR_SHARING = FORCE IN 11g (ADAPTIVE CURSOR SHARING)
- Reduces  memory usage in library cache as only one parent cursor and only one child cursor are created .
- If data is not skewed or the optimizer is not aware of the skew, optimizer peeks at the value of the bind variable on the first execution of the statement and that plan is used for all the  values of the bind variable. Thus only one child cursor is created resulting in minimum memory usage by child cursors. In this case performance will be affected if there is skew in the data. (same scenario as cursor_sharing=similar )
- If data is skewed and the optimizer is aware of the skew, multiple child cursor are created for different values of the bind variable – one for each distinct execution plan . In this case performance will be the best as optimizer creates different execution plans for different values of the bind variable. But in this case we will have only child cursor created for the same execution plan thereby resulting in optimum memory usage by child cursors.In my next post on Tuning Shared Pool , I will demonstrate how can we reduce hard parsing by- replacing literals with bind variables- setting cursor_sharing = similar

————————————————————————————
 
Related links:

11 thoughts on “CURSOR SHARING DEMYSTIFIED

  1. Whats the difference between cursor_sharing = SIMILAR & FORCE? As both are having 1 parent – 1 child (without histogram) and 1 parent – multiple child (with histogram)

    1. Hi Ali

      WIthout histogram behaviour of cursor_sharing = SIMILAR & FORCE is essentially same. Note that optimizer can generate same execution plan for different values of bind variable. With histogram, cursor_sharing = SIMILAR will cause as many child cursors to be created as are the distinct values of bind variables whereas cursor_sharing = FORCE will cause one child cursor to be created for each distinct execution plan.
      Regards
      Anju Garg

  2. please ignore my previous question, setting cursor_sharing=FORCE will make literals to be replaced by system bind variables , causes soft parse ; on top of it adaptive cursor sharing(creating a child cursor) may or may not applicable, depending on availability of histograms.correct?

    1. Yes, you are correct. Adaptive cursor sharing comes into play when sql statement has bind variables in where clause and there is/are histogram(s) on column(s) referenced in bind variable(s).

      regards
      Anju Garg

Leave a Reply to Krunal Cancel reply