Oracle Cursor Sharing Examples

In the previous post we discussed about the Parent & Child Cursors in Oracle and how do they work or shared.

To get into deeper, Oracle controls the sharing of cursors with parameter called cursor_sharing. It has three values

• EXACT
• SIMILAR
• FORCE

  In this post we will create a table which has some records with different data skewness, and we will experiment with above three values.

Test environment:-

<pre>

-- 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;

Let's experiment with different values :-

</pre>

CURSOR_SHARING = EXACT

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

<pre>

Parent   Parent  Parent
    |          |          |
Child    Child      Child

-- Flush the shared pool
-- Set cursor_sharing=exact

SYS>alter system set CURSOR_SHARING='EXACT';
SYS>alter system flush shared_pool;
SYS>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;

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

</pre>

Observations:-

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)

Points to ponder :-

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. which means reduce the usage of shared pool.

Notes:-

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 in our example table, 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

A parent and child will be created
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');
Note: method_opt in dbms_stats if the columns size is 1 that means no histograms
-- Set cursor_sharing = similar
-- Flush the shared pool
-- Issue identical statements with different values of literals for the column 
   on which histogram is not there (id1)

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

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"

This is called bind peeking, Optimizer does not aware of data skewness and opts same plan for all executions, this was addressed in 11g with Adaptive Cursor sharing.

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)
   Observe, 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"

Observations:- 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.

Points to ponder here:-

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"

Observations:-

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"

To conclude,

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.

-Thanks

Geek DBA