Oracle Cursor Sharing with 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.
<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.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.
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.
Thus CURSOR_SHARING=SIMILAR reduces the no. parent cursors. which means reduce the usage of shared pool.
- 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.
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.
CURSOR_SHARING=SIMILAR WITHOUT HISTOGRAM
A parent and child will be created <strong>Parent | Child </strong> -- 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.
- 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.
- 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.
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.
