Earlier I posted a list of operations and options available in Oracle database as of 11g R2, you should note that not all options are available for each operation. This post covers, over 200, commonly observed row source operations—unique combination of operations and options—each post contains a sample script to produce the operation and some may contain hints and [...]
The row source tree is the core of the execution plan. It contains the sequence of operations that the database performs to run the statement; each operation may have options associated with it. The operation in execution plan is also known as row source operator. If you look at the below example, TABLE ACCESS operation [...]
Some of you received the below error while your tried to lookup the execution plan using DBMS_XPLAN.DISPLAY_CURSOR procedure.
NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
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)
DBMS_XPLAN.DISPLAY_CURSOR does not display execution plan of all children associated with the SQL_ID
Jun 152009
Per Oracle documentation, to display the execution plan of all children associated with the SQL ID; you would have to call the table function in below format.
To execute any SQL statement Oracle has to derive an ‘execution plan’ . The execution plan of a query is a description of how Oracle will implement the retrieval of data to satisfy a given SQL statement. It is nothing but a tree which contains the order of steps and relationship between them.