DB version : 12.1
I want to generate execution plan with SQL_ID I have, using DBMS_XPLAN.DISPLAY_CURSOR function
Following is from the DESCRIBE command output of DBMS_XPLAN package
FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN DEFAULT
CURSOR_CHILD_NO NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
I have few questions on this :
Question 1. What exactly is CURSOR_CHILD_NO ?
Question 2.
A cursor can have several different plans ? If so, how will optimizer choose which plan to use ?
Question 3. In the below example, CHILD_NUMBERs 0 AND 4 did not return an execution plan.
So, execution plan which the cursor was using for 0 and 4 got removed for some reason ?
Question4. What effect does passing NULL as a parameter for CURSOR_CHILD_NO have ?
Like this example :
select * from TABLE(dbms_xplan.display_cursor('92g4ksw82t7b3', NULL));
My attempts to generate execution plans for Child cursors 0, 1 and 4
SQL> select sql_id,child_number from gv$sql where sql_id = '92g4ksw82t7b3';
SQL_ID CHILD_NUMBER
------------- ------------
92g4ksw82t7b3 0
92g4ksw82t7b3 1
92g4ksw82t7b3 4
SQL> select * from TABLE(dbms_xplan.display_cursor('92g4ksw82t7b3', 0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID: 92g4ksw82t7b3, child number: 0 cannot be found
SQL> select * from TABLE(dbms_xplan.display_cursor('92g4ksw82t7b3', 1));
A plan is returned. But, I am not posting it here for readability
SQL> select * from TABLE(dbms_xplan.display_cursor('92g4ksw82t7b3', 4));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 92g4ksw82t7b3, child number 4
SELECT S.HRT_ID ........
..
<SELECT query is snipped>
NOTE: cannot fetch plan for SQL_ID: 92g4ksw82t7b3, CHILD_NUMBER: 4
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)
21 rows selected.
SQL>