Multiple SQL version count with cusror_sharing=similar
- From: Neeraj Bhatia <neeraj.dba@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 30 Jun 2009 15:34:21 +0530
Hi,
I have some doubts regarding cursor sharing and bind value peeking. What i
observed is multiple child cursors are created in case of cursor_sharing
setting SIMILAR and FORCE.
Here is test case:
-------------- cursor_sharing = SIMILAR -----------------------------
scott@ORADB11G> alter session set optimizer_features_enable='10.2.0.2';
Session altered.
scott@ORADB11G> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ --------------------------------
------------------------------
cursor_sharing string
SIMILAR
scott@ORADB11G> exec dbms_stats.delete_table_stats(user,'EMP');
PL/SQL procedure successfully completed.
scott@ORADB11G> alter system flush shared_pool;
System altered.
scott@ORADB11G> select /* TEST */ * from emp where deptno= 10;
scott@ORADB11G> select /* TEST */ * from emp where deptno= 20;
scott@ORADB11G> select /* TEST */ * from emp where deptno= 30;
scott@ORADB11G> SELECT sql_text,version_count,address FROM V$SQLAREA WHERE
sql_text like 'select /* TEST */%';
SQL_TEXT
VERSION_COUNT ADDRESS
-----------------------------------------------------------------
------------- --------
select /* TEST */ * from emp where deptno=
:"SYS_B_0" 3 27FB2418
scott@ORADB11G> SELECT * FROM V$SQL_SHARED_CURSOR WHERE address =
'27FB2418';
SQL_ID ADDRESS CHILD_AD CHILD_NUMBER U S O O S L F E B P I S T A B D
L T R I I R L I O E M U T N F A I T D L D B P C S C P T M B M R O P M F L P
L A F L R L
------------- -------- -------- ------------ - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - -
df11x4zffkctp 27FB2418 2F5E199C 0 N N N N N N N N N N N N N N N N
N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
N N N N N N
df11x4zffkctp 27FB2418 2F5AA1AC 1 N N N N N N N N N N N N N N N N
N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
N N N N N N
df11x4zffkctp 27FB2418 27F6F188 2 N N N N N N N N N N N N N N N N
N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
N N N N N N
scott@ORADB11G> select sql_text, open_versions, parse_calls, hash_value,
address, plan_hash_value, child_address from v$sql
2 where sql_text like 'select /* TEST */%';
SQL_TEXT
OPEN_VERSIONS PARSE_CALLS HASH_VALUE ADDRESS PLAN_HASH_VALUE CHILD_AD
-----------------------------------------------------------------
------------- ----------- ---------- -------- --------------- --------
select /* TEST */ * from emp where deptno=
:"SYS_B_0" 0 1 3706270517 27FB2418
3956160932 2F5E199C
select /* TEST */ * from emp where deptno=
:"SYS_B_0" 0 1 3706270517 27FB2418
3956160932 2F5AA1AC
select /* TEST */ * from emp where deptno=
:"SYS_B_0" 0 1 3706270517 27FB2418
3956160932 27F6F188
Observations: 1) Why Oracle has created multiple versions for same SQL.
V$SQL_SHARED_CURSOR is giving no clue why child cursors are created.
2) What i have learnt is, in case of cursor_sharing='SIMILAR', Oracle check
whether execution plan change significantly (especially in case of
histograms), and if yes, create a
new child cursor.
3) Here, i have delete CBO statistics on the table and explain plans are
same for all child cursors (same plan_hash_value), still multiple versions
are created.
Let's check the behavior of bind value peeking.
scott@ORADB11G> alter session set "_optim_peek_user_binds"=false;
Session altered.
scott@ORADB11G> select /* TEST */ * from emp where deptno= 10;
scott@ORADB11G> select /* TEST */ * from emp where deptno= 20;
scott@ORADB11G> select /* TEST */ * from emp where deptno= 30;
scott@ORADB11G> SELECT sql_text,version_count,address FROM V$SQLAREA WHERE
sql_text like 'select /* TEST */%';
SQL_TEXT
VERSION_COUNT ADDRESS
-----------------------------------------------------------------
------------- --------
select /* TEST */ * from emp where deptno=
10 1 29842900
select /* TEST */ * from emp where deptno=
30 1 27F8A8BC
select /* TEST */ * from emp where deptno=
20 1 27F57DFC
Observations: 1) Why three parent cursors are created, with disabling bind
value peeking?
-------------- cursor_sharing = FORCE -----------------------------
scott@ORADB11G> alter system set cursor_sharing='FORCE';
System altered.
scott@ORADB11G> alter system flush shared_pool;
System altered.
scott@ORADB11G> select /* TEST */ * from emp where deptno= 10;
scott@ORADB11G> select /* TEST */ * from emp where deptno= 20;
scott@ORADB11G> select /* TEST */ * from emp where deptno= 30;
scott@ORADB11G> SELECT sql_text,version_count,address FROM V$SQLAREA WHERE
sql_text like 'select /* TEST */%';
SQL_TEXT
VERSION_COUNT ADDRESS
-----------------------------------------------------------------
------------- --------
select /* TEST */ * from emp where deptno=
10 1 29842900
select /* TEST */ * from emp where deptno=
:"SYS_B_0" 1 27FACC08
Observations: 1) Why two parent cursors are created while cursor_sharing is
set to FORCE. Is it expected behavior? What i was expecting is single parent
cursor with single
version (only one child cursor).
-------------- cursor_sharing = EXACT -----------------------------
scott@ORADB11G> alter system flush shared_pool;
System altered.
scott@ORADB11G> alter system set cursor_sharing='EXACT';
System altered.
scott@ORADB11G> select /* TEST */ * from emp where deptno= 10;
scott@ORADB11G> select /* TEST */ * from emp where deptno= 20;
scott@ORADB11G> select /* TEST */ * from emp where deptno= 30;
scott@ORADB11G> SELECT sql_text,version_count,address FROM V$SQLAREA WHERE
sql_text like 'select /* TEST */%';
SQL_TEXT
VERSION_COUNT ADDRESS
-----------------------------------------------------------------
------------- --------
select /* TEST */ * from emp where deptno=
10 1 29842900
select /* TEST */ * from emp where deptno=
30 1 27F8A8BC
select /* TEST */ * from emp where deptno=
20 1 27F57DFC
Observations: 1) As expected there are three parent cursors created. No
issues.
Please give reference to some good documents related to the subject.
Regards,
Neeraj Bhatia
Other related posts:
