How to Get A Query’s Execution Plan (With All The Details)

In Oracle, if you want to view an execution plan for a query, you use the DBMS_XPLAN.DISPLAY_CURSOR() procedure.

NOTE: The execution plan is totally different than an explain plan; don’t confuse these two.

The easiest way to get an execution plan is to do the following.

If you’re using SQL*Plus as your database client, you’ll first need to do a SET SERVEROUTPUT OFF. This is also true if you have a database client that emulates SQL*Plus, such as Toad or SQL Developer. By setting the server output off, it makes it easy to capture the last-run-query’s execution plan without serveroutput interfering.

Next, run your query to completion. In other words, make sure that you fetch all the rows in your query.

Once the query has been run to completion, run the following query:

select *
from table(dbms_xplan.display_cursor(format => 'advanced +adaptive'));

This will give us almost all of the execution plan details for your query.

Fetching All Rows of a Large Result Set

You might have a query that has a large enough result set that makes it difficult to fetch the entire result set.

If that’s the case, then it may actually make sense to put the query into an anonymous Pl/SQL block, and actually turn SET SERVEROUTPUT ON, and fetch the execution plan inside of an anonymous block:

set serveroutput on

declare
begin
                -- Put your query that fetches a large 
                -- result set below in place of 
                -- select dummy from dual connect by 
                -- level <= 1000000
  for v_row in (select dummy
                from dual
                connect by level <= 1000000)
  loop
    null;
  end loop;

  for v_row in (select *
                from table(dbms_xplan.display_cursor(format => 'advanced +adaptive'))) loop
    dbms_output.put_line(v_row.plan_table_output);
  end loop;
end;
/

Note that serveroutput will not longer interfere here because everything is within a sing Pl/SQL block. And actually, setting serveroutput on is necessary to see the plan that we’re writing to DBMS_OUTPUT.PUT_LINE().

This will not give you information about row source statistics. If you’re interested in getting row source statistics about your query, have a look at How to Get Row Source Statistics.

Free Oracle SQL Tuning Guide

Check out my FREE guide! 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!

Get it here: tuningsql.com/secrets