Oracle Child Cursor – Part 2

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_NUMBERPLAN_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 that 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.

Oracle Child Cursors – Part1

What does Child_Number=0 mean. Does it mean that there are no child cursors for the given SQL ID?

There has been an often misconception towards the value “0” associated with Child_Number in V$SQL for a cursor.  Before we deep dive into child cursors, let’s get into cursors.

In a cursor / Query‘s life cycle the major components are

1. Query – FULL Text – Case sensitive
2. Literals / Bind Variables
3. Statistics
4. Execution Plan

There is a One-To-Many relationship between Query Text and other components within the SQL Life Cycle i.,e For a given Query (SQL) there can be

1. Range of bind values
2. Fluctuating statistics
3. Changing Environments
4. N number of execution plans

Based on the changing and non changing components of a cursor they are splitted into parent and child cursors, which looks like below

          Thus for every cursor, Oracle internally treats them in a parent child format. It is just the value given as “0” for the initial child cursor.  The parent cursor is a representation of the Hash Value and the child cursor represents the metadata for the SQL. When the metadata associated with the SQL starts changing it leads to a creation of a  new child cursor. Every cursor has one parent and one or more child cursors. Therefore under these circumstances the 1:1 relation b/w parent and child cursor becomes 1:n i,.e as below

Find bind variable values in oracle

How to get bind variable values in oracle ?

To retrieve the value of the bind variables V$SQL_BIND_CAPTURE view has to be queried, But does oracle really provides us the bind variable's value? lets see in detail

SQL> SELECT * FROM V$VERSION;

BANNER
——————————————————————————

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
PL/SQL Release 11.2.0.1.0 – Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 – Production
NLSRTL Version 11.2.0.1.0 – Production

SQL> SELECT NAME,VALUE FROM V$PARAMETER WHERE NAME like ‘%cursor_sharing%’;

NAME VALUE
————– ———-
cursor_sharing EXACT

SQL> VARIABLE X NUMBER;
SQL> EXEC :X:=10;

PL/SQL procedure successfully completed.
SQL> SELECT ID,ID_VAL FROM BV_CAPTURE WHERE ID = :X;

ID ID_VAL
———- ———
10 10ORACLE

SQL> SELECT SQL_ID,SQL_TEXT,EXECUTIONS AS EXEC,CHILD_NUMBER CHD_NUM FROM V$SQL WHERE SQL_ID = ’4pfw91tshj4yp’;

SQL_ID SQL_TEXT EXEC CHD_NUM
——————– —————————————- —- ——-
4pfw91tshj4yp SELECT ID,ID_VAL FROM BV_CAPTURE WHERE I 1 0
D = :X

Lets query V$SQL_BIND_CAPTURE to retrieve the bind variable data

SQL> SELECT SQL_ID,DATATYPE_STRING,NAME,VALUE_STRING,VALUE_ANYDATA FROM V$SQL_BIND_CAPTURE WHERE SQL_ID = ’4pfw91tshj4yp’;

SQL_ID DATATYPE_STRING NAME VALUE_STRING VALUE_ANYDATA()
————– —————- —– ————- —————-
4pfw91tshj4yp NUMBER :X 10 ANYDATA()

Lets Repeat the same and check what we get,

SQL> EXEC :X:=100;

PL/SQL procedure successfully completed.

SQL> SELECT ID,ID_VAL FROM BV_CAPTURE WHERE ID = :X;

ID ID_VAL
———- ———-
100 100ORACLE

SQL> SELECT SQL_ID,SQL_TEXT,EXECUTIONS AS EXEC,CHILD_NUMBER CHD_NUM FROM V$SQL WHERE SQL_ID = ’4pfw91tshj4yp’;

SQL_ID SQL_TEXT EXEC CHD_NUM
——————– —————————————- —- ——-
4pfw91tshj4yp SELECT ID,ID_VAL FROM BV_CAPTURE WHERE I 2 0
D = :X

and now what does V$SQL_BIND_CAPTURE provide us

SQL> SELECT SQL_ID,DATATYPE_STRING,NAME,VALUE_STRING,VALUE_ANYDATA FROM V$SQL_BIND_CAPTURE WHERE SQL_ID = ’4pfw91tshj4yp’;

SQL_ID DATATYPE_STRING NAME VALUE_STRING VALUE_ANYDATA()
————– —————- —– ————- —————-
4pfw91tshj4yp NUMBER :X 10 ANYDATA()

oh! still sticking to value 10, it has to be 100. The support link Note-444551.1 provides us an work around to get the timestamp values using “anydata.accesstimestamp(value_anydata)” but does anydata.accessnumber help in our case, lets check that too

SQL> SELECT SQL_ID,DATATYPE_STRING,NAME,VALUE_STRING,ANYDATA.ACCESSNUMBER(VALUE_ANYDATA) AS VALUE_ANYDATA FROM V$SQL_BIND_CAPTURE WHERE SQL_ID = ’4pfw91tshj4yp’;

SQL_ID DATATYPE_STRING NAME VALUE_STRING VALUE_ANYDATA
————- —————– —– ———— ————-
4pfw91tshj4yp NUMBER :X 10 10

Ops! the issue is not with timestamp alone, it is also with Numbers. The run time bind variables are not updated for executions greater than one. Varchar2 also have the same issue. On checking with oracle, Oracle states as below,

"Bind values are not always captured for this view and have referenced to Not Every Bind Values Captured in v$sql_bind_capture (Doc ID 1370816.1) "