ops missread, you dont have histograms have you tried turn off dynamic sampling :-? -- LSC On Tue, Jun 30, 2009 at 1:43 PM, LS Cheng <exriscer@xxxxxxxxx> wrote: > 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 >> > >