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.