FAQ
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

Search Discussions

  • Srinivas Chintamani at Jun 30, 2009 at 11:27 am
    Hi Neeraj,
    I think "Troubleshooting Oracle Performance" by Christian
    Antognin<http://www.amazon.com/Christian-Antognini/e/B001MOWTIE/ref=ntt_athr_dp_pel_1>
    found here<http://www.amazon.com/exec/obidos/ASIN/1590599179/buythisbooks-20>is
    an excellent book on this topic.

    Regards,
    Srinivas Chintamani.
    On Tue, Jun 30, 2009 at 3:34 PM, Neeraj Bhatia 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
    --
    Regards,
    Srinivas Chintamani

    --
    http://www.freelists.org/webpage/oracle-l
  • LS Cheng at Jun 30, 2009 at 11:43 am
    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 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
  • LS Cheng at Jun 30, 2009 at 11:49 am
    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 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 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJun 30, '09 at 10:04a
activeJun 30, '09 at 11:49a
posts4
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase