Forum Stats

  • 3,758,212 Users
  • 2,251,354 Discussions
  • 7,870,111 Comments

Discussions

Generating execution plan using dbms_xplan.display_cursor function

Peasant81
Peasant81 Member Posts: 4 Green Ribbon
edited Sep 17, 2021 8:49PM in General Database Discussions

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>


Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,729 Black Diamond

    Question 1. What exactly is CURSOR_CHILD_NO ? 

    Here is very simplified explanation. Same SQL_ID simply means SQL satement text is the same. But that same SQL statement, for example can be executed by two different users so table(s) referenced by that SQL will point for user 1 to user 1 owned tables while for user 2 it will reference user 2 owned tables. User 1 & 2 tables can have different structure, different indexes, different number of rows, different statistics, etc. Or even for same user 1 something changed since last time that SQL was executed (e.g. index was created or dropped) and now we need new plan. So each time optimizer detects such type of differences it creates new child cursor and assigns it a number - cursor child number.

    Question 2. A cursor can have several different plans ? If so, how will optimizer choose which plan to use ?

    That is why we have child cursors. If optimizer realizes none of child cursors is good for the current execution of that SQL is no good it creates new child cursor with execution plan fitting current situation.

    Question4. What effect does passing NULL as a parameter for CURSOR_CHILD_NO have ? 

    NULL cursor child number (passed explicitly or if you omit it) is resolved as cursor child number 0.

    Question 3. In the below example, CHILD_NUMBERs 0 AND 4 did not return an execution plan. 

    Based on you querying GV$SQL I assume you have RAC. Unfortunately each RAC node can only see execution plans for child cursors executed on that node. So most likely child cursors 0 and 4 were execduted on other node(s). Issue:

    select sys_context('userenv','instance') my_inst_id,inst_id,sql_id,child_number from gv$sql where sql_id = '92g4ksw82t7b3';
    

    It will tell you your instance and instance where child cursor was executed.

    SY.

    Peasant81