jump to navigation

Oracle Child Cursor – Part 2 August 13, 2012

Posted by puthranv in Uncategorized.
trackback

 Well the blog has been moved to www.puthranv.com . All the old articles are also moved.

What is a Child Cursor ?

            Child Cursor is nothing but the cursor itself. The first one has the number Child_Number = 0 i.,e the Parent and there on the Child_Number gets incremented based on the difference in their metadata.

Lets start with ONE to ONE

Version – Oracle 11.2.0.1.0

 SQL> CREATE TABLE TBL_CC_TEST AS
2  SELECT ROWNUM AS ID,
3   decode(trunc(dbms_random.value(1,4)),
4    1,’A’,
5    2,’B’,
6    3,’C’,
7    ‘D’
8   ) AS STATUS,
9   TRUNC(dbms_random.value (1,1000000)) AS ID_VAL
10  from all_objects;

 Table created.

 SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(‘DTCC’,’TBL_CC_TEST’);

 PL/SQL procedure successfully completed.

SQL> show parameter cursor_sharing;
NAME                  TYPE          VALUE
————–       ———      ——–
cursor_sharing       string         EXACT

Data is ready and now a simple query. Remember to use binds, which is the only method to make the query reusable when cursor_sharing is EXACT.

SQL> Variable X number;
SQL> exec :X:=54321;
PL/SQL procedure successfully completed.
SQL> select * from tbl_cc_test where id = :X;
ID            S    ID_VAL
———- –      ———-
54321     C      638375

SQL> col sql_id format A20;
SQL> col sql_text format A40;
SQL> col executions format 99;
SQL> col child_number format 99;
SQL> col version_count format 99;
SQL> col plan_hash_value format 999999999999;
SQL> col hash_value format 99999999999999;
SQL>
SQL> select sql_id, sql_text, executions, child_number, plan_hash_value, hash_value
2  from v$sql where sql_text like ‘select * from tbl_cc_test%’;

SQL_ID                           SQL_TEXT                  EXECUTIONS CHILD_NUMBER PLAN_HASH_VALUE HASH_VALUE
————–        ————————-              —————- ————————– ————————— ——————
2nzy95ddbu6ss  select * from tbl_cc_test              1                                   0                          2643996215                1522342680
where id = :X

Thus for a given SQL Text we have got the parent cursor- with Child_Number = 0. Will check how this child number gets changed for the same SQL Text.

ONE to MANY

Iam going to repeat the same steps as above with a small change.

SQL> Variable X varchar2(10);
SQL> exec :X:=’54320′;
PL/SQL procedure successfully completed.

SQL> select * from tbl_cc_test where id = :X;
ID            S     ID_VAL
———- – ———-
54320    A     341189

SQL> Select Sql_Id, Sql_Text, Executions, Child_Number From V$sql Where Sql_Id =’2nzy95ddbu6ss’

SQL_ID   SQL_TEXT       EXECUTIONS CHILD_NUMBER
————-  —————————————–   ———-   ————
2nzy95ddbu6ss select * from tbl_cc_test where id = :X  1  0
2nzy95ddbu6ss select * from tbl_cc_test where id = :X  1  1

Now for the same SQL Text we have got one more child, indicated as Child_Number =  1. You may think,

  • Why a new child cursor is created?
  • Why not just reuse the same parent cursor(Child_Number = 0)

To answer these lets take a look at the v$sql_shared_cursor which would let us know the reason for creating a new child cursor.

SQL>select SQL_ID,CHILD_ADDRESS,CHILD_NUMBER, from v$sql_shared_cursor Where Sql_Id =’2nzy95ddbu6ss’;

SQL_ID              ADDRESS  CHILD_ADDRESS CHILD_NUMBER BIND_MISMATCH
————-               ——– ————- ———— ————-
2nzy95ddbu6ss      2D730F78 2D4836FC         0             N
2nzy95ddbu6ss      2D730F78 326F78F8          1             Y

The reason for oracle creating a new child cursor is Bind Mismatch, So

  • What is Bind Mismatch?
  • What are the reason which could lead to Bind Mismatch ?

When there is a change in Bind Variable definition associated with the SQL it leads to a Bind Mismatch, example like change in the data type of the bind variable(like the above test case) or change in the size of the bind variable(oracle has a range for this bind size)  leads to creation of new child cursor for the same SQL Text.

Lets confirm the Bind Mismatch as the cause of new child cursor by validating the actual data,

SQL> Select * From V$sql_Bind_Capture Where Sql_Id =’2nzy95ddbu6ss’;

SQL_ID    CHILD_ADDRESS   CHILD_NUMBER  NAME   DATATYPE      VALUE_STRING
———— ————-   ————  —–  ——-       ————
2nzy95ddbu6ss   326F78F8       1     :X     VARCHAR2(32)  54320
2nzy95ddbu6ss   2D4836FC      0     :X     NUMBER                54320

The above info clearly indicates that during the second run the bind variable’s data type is changed from Number to Varchar2 thus leading to Bind Mismatch and creation of new Child Cursor for the identical SQL Text.

Advertisements

Comments»

No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: