Re: Multiple SQL version count with cusror_sharing=similar

have you tried turn off dynamic sampling :-?

--
LSC


On Tue, Jun 30, 2009 at 1:43 PM, LS Cheng <exriscer_at_gmail.com> 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_at_gmail.com>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_at_ORADB11G> alter session set optimizer_features_enable='10.2.0.2';

>> Session altered.

>> scott_at_ORADB11G> show parameter cursor_sharing

>> NAME                                 TYPE

>> VALUE

>> ------------------------------------ --------------------------------

>> ------------------------------

>> cursor_sharing                       string

>> SIMILAR

>> scott_at_ORADB11G> exec dbms_stats.delete_table_stats(user,'EMP');

>> PL/SQL procedure successfully completed.

>> scott_at_ORADB11G> alter system flush shared_pool;

>> System altered.

>> scott_at_ORADB11G> select /* TEST */ * from emp where deptno= 10;

>> scott_at_ORADB11G> select /* TEST */ * from emp where deptno= 20;

>> scott_at_ORADB11G> select /* TEST */ * from emp where deptno= 30;

>> scott_at_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_at_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_at_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_at_ORADB11G> alter session set "_optim_peek_user_binds"=false;

>> Session altered.

>>

>> scott_at_ORADB11G> select /* TEST */ * from emp where deptno= 10;

>> scott_at_ORADB11G> select /* TEST */ * from emp where deptno= 20;

>> scott_at_ORADB11G> select /* TEST */ * from emp where deptno= 30;

>> scott_at_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_at_ORADB11G> alter system set cursor_sharing='FORCE';

>> System altered.

>> scott_at_ORADB11G> alter system flush shared_pool;

>> System altered.

>> scott_at_ORADB11G> select /* TEST */ * from emp where deptno= 10;

>> scott_at_ORADB11G> select /* TEST */ * from emp where deptno= 20;

>> scott_at_ORADB11G> select /* TEST */ * from emp where deptno= 30;

>> scott_at_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_at_ORADB11G> alter system flush shared_pool;

>> System altered.

>> scott_at_ORADB11G> alter system set cursor_sharing='EXACT';

>> System altered.

>> scott_at_ORADB11G> select /* TEST */ * from emp where deptno= 10;

>> scott_at_ORADB11G> select /* TEST */ * from emp where deptno= 20;

>> scott_at_ORADB11G> select /* TEST */ * from emp where deptno= 30;

>> scott_at_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

>>

>

>


--
http://www.freelists.org/webpage/oracle-l