Re: Multiple SQL version count with cusror_sharing=similar
- From: LS Cheng <exriscer@xxxxxxxxx>
- To: neeraj.dba@xxxxxxxxx
- Date: Tue, 30 Jun 2009 13:49:35 +0200
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
>>
>
>
Other related posts:
