If you have histograms on deptno then this happens V$SQL_SHARED_CURSOR does not show you the reason if multiple version is caused by histograms Thanks -- LSC On Tue, Jun 30, 2009 at 12:04 PM, Neeraj Bhatia <neeraj.dba@xxxxxxxxx>wrote: > 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 >