Execution Plan

Execution Plan Operations and Options

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 …

Execution Plan Operations and Options Read More »

DBMS_XPLAN.DISPLAY_CURSOR results in error cannot fetch plan for SQL_ID: 9babjv8yq8ru3

You may have received the below error while you tried to lookup the execution plan using DBMS_XPLAN.DISPLAY_CURSOR procedure. The error is due to the SQL*Plus environment variable SERVEROUTPUT is turned on. One of the Prerequisites for running the package is to turn off SERVEROUTPUT. You should be able to see the execution plan after turning …

DBMS_XPLAN.DISPLAY_CURSOR results in error cannot fetch plan for SQL_ID: 9babjv8yq8ru3 Read More »

DBMS_XPLAN.DISPLAY_CURSOR does not display execution plan of all children associated with the SQL_ID

To display the execution plan of all children associated with the SQL ID; you would have to call the table function in below format. Following steps will load two children cursors in the cursor cache By querying V$SQL, We can see that SQL_ID gfvh124qr5nzy has two childrens, 0 and 1; Let us execute DBMS_XPLAN.DISPLAY_CURSOR with …

DBMS_XPLAN.DISPLAY_CURSOR does not display execution plan of all children associated with the SQL_ID Read More »

How to read an Oracle SQL Execution Plan?

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 The basic …

How to read an Oracle SQL Execution Plan? Read More »