How To Identify the Execution Plan of a SQL Statement

For most tuning exercises, the tuning happens at the SQL Statement level. When a user complains about a report being slow, you as a DBA, have to first identify the SQL Statement and then the execution plan. Today’s post is about identifying the execution plan for a specific SQL Statement. Before we dive in, let me ask you a question, what is an execution plan?

The Execution Plan of a SQL statement is a sequence of steps or instructions performed by the database to get or retrieve the data for the SQL statement. Each step within the execution plan is responsible for retrieving data from the database, or preparing the data for the next step. The execution plan includes an access path (how the table is accessed, think full table scan, index scan) along with the join order and join method (think nested loops, hash joins). The SQL Statement has a SQL ID associated with it, that uniquely identifies it and the Execution Plan has a Plan Hash Value associated with it, which is represented in a number format. DBAs usually refer to it as PHV.

Now let’s get to it! How can you identify the execution plan? There are several methods, each offering its own insights. For instance, the EXPLAIN PLAN command provides a hypothetical execution plan, while techniques like tracing or utilizing DBMS_XPLAN.DISPLAY_CURSOR offer actual execution plans. In my experience, developers often lean towards using EXPLAIN PLAN, whereas DBAs prefer methods that yield the real execution plan. Why the discrepancy? Because EXPLAIN PLAN predicts the plan without actually executing the statement. This can lead to misleading information, as the optimizer might opt for a different plan at runtime.

So, what’s the better alternative?

Personally, I favor using DMBS_XPLAN.DISPLAY_CURSOR. This function displays the actual execution plan that was used to run a query. The function uses the information stored in various V$ views, like V$SQL_PLAN_STATISTICS_ALL, V$SQL, and V$SQL_PLAN. The function takes three optional parameters: sql_id, child_number and format. If the SQL_ID is omitted, the execution plan of the last statement executed by the session is displayed. If you know the SQL_ID, you can provide that as the first parameter. That is what I use the most. By default the function will return all the cursors for the specified SQL_ID, unless you specify an actual child_number. For the format entry you have different option available, depending on the value of STATISTICS_LEVEL parameter setting.

Let’s look at some examples on how to use DMBS_XPLAN.DISPLAY_CURSOR.

select employee_id, last_name, department_name 
from hr.employees e, hr.departments d 
where e.department_id=d.department_id order by 1;

EMPLOYEE_ID LAST_NAME		      DEPARTMENT_NAME
----------- ------------------------- ------------------------------
  100       King                      Executive
  101       Kochhar                   Executive
  102       De Haan                   Executive
  103       Hunold                    IT
  104       Ernst                     IT
  105       Austin                    IT
  106       Pataballa                 IT
  107       Lorentz                   IT
  108       Greenberg                 Finance
  109       Faviet                    Finance
  110       Chen                      Finance
...

--display the execution plan of the above statement

SELECT * FROM  TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

If you want to get more information about the execution plan, you can use different values for the format parameter: BASIC, TYPICAL, SERIAL, ALL. These values can be combines with other options: ROWS, BYTES, PARALLEL, PROJECTION etc, depending what is relevant and applicable. For details on these options, check out Oracle’s documentation here.

--display the execution plan using format set to ALL

SELECT * FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALL'));

If you want to know the execution plan of a statement that ran in the past, and it is still in the cursor cache, then you would have to pass the SQL_ID to the DBMS_XPLAN.DISPLAY_CURSOR function:

SELECT * FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR('349tuswvzxu1r'));

Remember, as a DBA you should rarely use the EXPLAIN PLAN, as it can be deceiving, instead use DBMS_XPLAN.DISPLAY_CURSOR.

In the next post I will discuss ways to figure out the SQL_ID of a statement. Stay tuned, until next time!

Free Video Tutorial