Viewing an Explain Plan/Execution Plan for a SQL Statement

You can view an Explain Plan for a SQL Statement in the code window to see the predicted plan for its execution. Similarly, you can view the Execution Plan after SQL has already been executed to see the actual plan used.

Both grid based and text based plan output are available and are configurable using the settings.

To view an Explain Plan for SQL you have entered into the code window, perform the following steps:

1. Position the cursor on the line that contains the SQL and press the Explain Plan (grid) or the Explain Plan (text) icon in the toolbar. Alternatively, right click and select Explain Plan (grid) or the Explain Plan (text) from the menu.

2. To configure the results of Explain Plan, right click on the connection node and select Explain Plan and Execution Plan Settings

Note:

Explain Plan (grid) uses PLAN_TABLE while Explain Plan (text) uses the DBMS_XPLAN PL/SQL package.

To view an Execution Plan for SQL you have executed in the code window, perform the following steps:

1. In order to view an execution plan you must have SELECT privileges on V$SQL_PLAN. This can be granted using the statement:

   GRANT SELECT on V_$SQL_PLAN to <user>

2. Type a SQL statement you are interested in and execute it. The statement must be executed before you can request an Execution Plan.

3. Position the cursor on the line that contains the SQL and click the Execution Plan (grid) or the Execution Plan (text) icon in the toolbar. Alternatively, right click and select Execution Plan (grid) or the Execution Plan (text) from the menu.

4. To configure the results of Execution plan, right click on the connection node and select Explain Plan and Execution Plan Settings

Note:

Execution Plan (grid) uses V$SQL_PLAN while Execution Plan (text) uses the DBMS_XPLAN PL/SQL package.