Database Reference
In-Depth Information
CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE PLAN_HASH_VALUE
------------ ----------------- ------------- ------------ ---------------
0 Y N
N
4270555908
1 Y
Y
Y 2966233522
2 Y
Y
Y 4270555908
Looking at the execution plans related to the cursor, as you might expect, you see that one of the new children has
an execution plan based on a full table scan, whereas the other is based on an index scan:
Plan hash value: 4270555908
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS BY INDEX ROWID| T |
| 3 | INDEX RANGE SCAN | T_PK |
---------------------------------------------
Plan hash value: 2966233522
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS FULL| T |
-----------------------------------
To further analyze the reason for the generation of the two child cursors, several dynamic performance views are
available:
v$sql_cs_statistics
,
v$sql_cs_selectivity
, and
v$sql_cs_histogram
. The first shows whether peeking
was used and the related execution statistics for each child cursor. In the following output, it's possible to confirm that
for one execution, the number of rows processed by child cursor 1 is higher than for child cursor 2. This is basically
the reason why, in one case, the query optimizer chose a full table scan and in the other an index scan:
SQL> SELECT child_number, peeked, executions, rows_processed, buffer_gets
2 FROM v$sql_cs_statistics
3 WHERE sql_id = 'asth1mx10aygn'
4 ORDER BY child_number;
CHILD_NUMBER PEEKED EXECUTIONS ROWS_PROCESSED BUFFER_GETS
------------ ------ ---------- -------------- -----------
0 Y 1 19 3
1 Y 1 990 18
2 Y 1 19 3
