EXPERT ORACLE DBA: Cursor sharing and bind variable

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

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 )

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

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)

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

--------------------------------------

CURSOR_SHARING

1. = EXACT (default)

1.1. if SQL statement uses literals: the optimizer will generate a new execution plan for every combination of literals - optimizer will not replace literals with binds. A new parent cursor is generated for every literal combination.

1.2. if SQL statement uses bind variables: first time the statment is run, the optimizer will peek at the value of the bind variables and use those specific values to generate an execution plan - all future statements with those bind variables will use that same plan (even if the plan is suboptimal for other values of the bind variable).

2. = FORCE

2.1. optimizer will replace all literals with binds - and will basically use the same algorithm as scenario 1.2

3. = SIMILAR

3.1. no histogram: optimizer replaces all literals with binds -> same final effect as with 1.2 and 2.1

3.2. with histogram: optmizer replaces all literals with binds, but peeks at the bind variable EVERY time the statement is run (as opposed to just on the first run through) to see if there is a more optimal execution plan for that specific value of the bind variable (based on histogram statistics). Therefore, a new child cursor is effectively created for every distinct value of the bind variable that the optimizer encounters.

EXPERT ORACLE DBA

Thursday, September 15, 2016

Cursor sharing and bind variable

No comments:

Post a Comment

Blog Archive