Re: Multiple SQL version count with cusror_sharing=similar

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
>

Other related posts: