Skip to content
Home » Oracle » SQL*Plus Explain Plan

SQL*Plus Explain Plan

Sometime, there is no tool like SQL Developer or Enterprise Manager around to check the explain plan of a specific statement. We need a bare way to do the job in SQL*Plus.

Here are examples to check the explain plan.

Explain Plan for SQL

SQL> show user
USER is "HR"
SQL> explain plan for select count(*) from employees where salary > 10000;

Explained.

Display Explain Plan

SQL> set linesize 80;
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1756381138

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |     4 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |           |     1 |     4 |            |          |
|*  2 |   TABLE ACCESS FULL| EMPLOYEES |    68 |   272 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   2 - filter("SALARY">10000)

14 rows selected.

The DBMS_XPLAN will retrieve the most recent execution plan for you.

BASIC Format

If you would like to display only basic column with less information other than a typical one, you can query like this:

SQL> select * from table(dbms_xplan.display(format => 'BASIC'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1756381138

----------------------------------------
| Id  | Operation          | Name      |
----------------------------------------
|   0 | SELECT STATEMENT   |           |
|   1 |  SORT AGGREGATE    |           |
|   2 |   TABLE ACCESS FULL| EMPLOYEES |
----------------------------------------

9 rows selected.

Name the Plan

A finer way to get the execution plans is to set the STATEMENT_ID for every statement, and then retrieve specific plains by statement_id.

SQL> explain plan set statement_id = 'Happy Employees Count' for select count(*) from employees where salary > 10000;

Explained.

We can query the execution plan like this:

SQL> column ID format 99;
SQL> column OPERATION format a25;
SQL> column OPTIONS format a15;
SQL> column OBJECT_NAME format a15;
SQL> column OBJECT_ALIAS format a15;
SQL> column QBLOCK_NAME format a10;
SQL> column POSITION format 99;
SQL> set linesize 120;
SQL> SELECT id,
  2    LPAD(' ',2*(LEVEL-1))
  3    ||operation operation,
  4    OPTIONS,
  5    object_name,
  6    object_alias,
  7    qblock_name,
  8    position
  9  FROM plan_table
 10    START WITH id       = 0
 11  AND statement_id      = 'Happy Employees Count'
 12    CONNECT BY PRIOR id = parent_id
 13  AND statement_id      = 'Happy Employees Count'
 14  order by id;

The result is as below:

 ID OPERATION                 OPTIONS         OBJECT_NAME     OBJECT_ALIAS    QBLOCK_NAM POSITION
--- ------------------------- --------------- --------------- --------------- ---------- --------
  0 SELECT STATEMENT                                                                            3
  1   SORT                    AGGREGATE                                       SEL$1             1
  2     TABLE ACCESS          FULL            EMPLOYEES       "EMPLOYEES"@"SE SEL$1             1
                                                              L$1"

They're the same.

For most developers, using GUI tools like SQL developer to explain plan of a SQL statement is a more efficient way to display and analyze a tree-structured path.

Leave a Reply

Your email address will not be published. Required fields are marked *