The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer.A statement’s execution plan is the sequence of operations Oracle performs to run the statement.execution plans are read inside-out.if there are two statements at the same level, the first statement is executed first.
You can use “Explain plan statement for this.” which should be explained first.
The basic syntax to get explain plan is explain plan set statement_id= for
For Ex:
ind> explain plan set statement_id=’ORAFAQ’ for
2 select
3 a.empno,
4 a.ename,
5 b.dname
6 from
7 emp_sriram a,
8 dept b
9 where
10 a.deptno=b.deptno
11 /
Explained.
Now the sequential steps of this sql statement is stored in a Table called PLAN_TABLE.
Lets have a look at the table.
ind> select LPad(‘ ‘, 2*(Level-1)) || Level || ‘.’ || Nvl(Position,0)|| ‘ ‘ ||
2 Operation || ‘ ‘ || Options || ‘ ‘ || Object_Name || ‘ ‘ || Object_Type
3 || ‘ ‘ || Decode(id, 0, Statement_Id ||’ Cost = ‘ || Position) || cost
4 || ‘ ‘ || Object_Node “Query Plan”
5 from plan_table
6 start with id = 0 And statement_id=’ORAFAQ’
7 connect by prior id = parent_id
8 and statement_id=’ORAFAQ’
9 /
Query Plan
———————————————————————————————–
1.4 SELECT STATEMENT ORAFAQ Cost = 44
2.1 NESTED LOOPS 4
3.1 TABLE ACCESS FULL EMP_SRIRAM TABLE 3
3.2 TABLE ACCESS BY INDEX ROWID DEPT TABLE 1
4.1 INDEX UNIQUE SCAN PK_DEPT INDEX (UNIQUE) 0
5 rows selected.
You can Also Use DBMS_XPLAN.DISPLAY.
ind> SELECT *
2 FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’,’ORAFAQ’,’BASIC’));
PLAN_TABLE_OUTPUT
———————————————————————————-
Plan hash value: 2868360194
—————————————————
| Id | Operation | Name |
—————————————————
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | TABLE ACCESS FULL | EMP_SRIRAM |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT |
| 4 | INDEX UNIQUE SCAN | PK_DEPT |
—————————————————
11 rows selected.
The basic structure of this package is
dbms_xplan.display(
table_name IN VARCHAR2 DEFAULT ‘PLAN_TABLE’,
statement_id IN VARCHAR2 DEFAULT NULL,
format IN VARCHAR2 DEFAULT ‘TYPICAL’);
Format choices are
BASIC ….. displays minimum information
TYPICAL … displays most relevant information
SERIAL …. like TYPICAL but without parallel information
ALL ……. displays all information
SQL plus autotrace also produce the explain plan …..
set autotrace off
set autotrace on
set autotrace traceonly
set autotrace on explain
set autotrace on statistics
set autotrace on explain statistics
set autotrace traceonly explain
set autotrace traceonly statistics
set autotrace traceonly explain statistics
set autotrace off explain
set autotrace off statistics
set autotrace off explain statistics
Setting autotrace allows to display some statistics and/or an query execution plan for DML statements.
set autotrace on: Shows the execution plan as well as statistics of the statement.
set autotrace on explain: Displays the execution plan only.
set autotrace on statistics: Displays the statistics only.
set autotrace traceonly: Displays the execution plan and the statistics (as set autotrace on does), but doesn't print a query's result.
set autotrace off: Disables all autotrace
Now lets have a look at execution plan order ….
ind> select LPAD(‘ ‘,2*(LEVEL-1))||operation “OPERATION”, options “OPTIONS”,
2 DECODE(TO_CHAR(id),’0’,’COST = ‘ || NVL(TO_CHAR(position),’n/a’),
3 object_name) “OBJECTNAME”, id ||’-‘|| NVL(parent_id, 0)||’-‘||
4 NVL(position, 0) “ORDER”, SUBSTR(optimizer,1,6) “OPT”
5 from plan_table
6 start with id = 0
7 and statement_id=’ORAFAQ’
8 connect by prior id = parent_id
9 and statement_id=’ORAFAQ’;
OPERATION OPTIONS OBJECTNAME ORDER OPT
—————————— —————————— —————————— ——-
SELECT STATEMENT COST = 4 0-0-4 ALL_RO
NESTED LOOPS 1-0-1
TABLE ACCESS FULL EMP_SRIRAM 2-1-1 ANALYZ
TABLE ACCESS BY INDEX ROWID DEPT 3-1-2 ANALYZ
INDEX UNIQUE SCAN PK_DEPT 4-3-1 ANALYZ
5 rows selected.
ind> select LPAD(‘ ‘,2*(LEVEL-1))||operation “OPERATION”, options “OPTIONS”,
2 DECODE(TO_CHAR(id),’0’,’COST = ‘ || NVL(TO_CHAR(position),’n/a’),
3 object_name) “OBJECTNAME”, id ||’-‘|| NVL(parent_id, 0)||’-‘||
4 NVL(position, 0) “ORDER”
5 from plan_table
6 start with id = 0
7 and statement_id=’ORAFAQ’
8 connect by prior id = parent_id
9 and statement_id=’ORAFAQ’;
OPERATION OPTIONS OBJECTNAME ORDER
—————————— —————————— —————————— ——-
SELECT STATEMENT COST = 4 0-0-4
NESTED LOOPS 1-0-1
TABLE ACCESS FULL EMP_SRIRAM 2-1-1
TABLE ACCESS BY INDEX ROWID DEPT 3-1-2
INDEX UNIQUE SCAN PK_DEPT 4-3-1
5 rows selected.
On 0-0-4 it is in an order like ID and parent id nad its position.
Like wise if we can order them
00
10
21
31
43
here Level 1 has 2 childs(2,3) and 3 has 1 child(4).
So the execution steps sequential order will be 2,4,3,1
i.e
TABLE ACCESS FULL EMP_SRIRAM
INDEX UNIQUE SCAN PK_DEPT
TABLE ACCESS BY INDEX ROWID DEPT
Produce the result
SELECT STATEMENT Indicates that its a "SELECT STATEMENT"
Here First it will read data from emp_sriram then a unique Index scan on dept (step 4)
which gives the rowids and based the rowid input it goes to the step 1
(we can say for understanding) and produce the result.
For More Information Please have a look at Oracle documents.
17.047762
80.098187
Not Just Databases 