Multiple SQL version count with cusror_sharing=similar

From: Neeraj Bhatia <neeraj.dba_at_gmail.com>
Date: Tue, 30 Jun 2009 15:34:21 +0530
Message-ID: <a8fd4d730906300304k5b150c07obd7dd0f0ba4f39ac_at_mail.gmail.com>

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

Received on Tue Jun 30 2009 - 05:04:21 CDT