Re: Multiple SQL version count with cusror_sharing=similar

From: Srinivas Chintamani <srinivas.chintamani_at_gmail.com>
Date: Tue, 30 Jun 2009 16:57:11 +0530
Message-ID: <98c5e2a20906300427ta13b6f7ye861dbf6739fbc55_at_mail.gmail.com>



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

-- 
Regards,
Srinivas Chintamani

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jun 30 2009 - 06:27:11 CDT

Original text of this message