http://nimishgarg.blogspot.ae/2015/09/how-to-get-execution-plan-and.html
How do you check EXECUTION PLAN of a QUERY? I got this question on my facebook chat many times. "EXPLAIN PLANE" The answer was quite simple, Right? Actually it depends on what I am looking for. "AUTOT TRACE" is my personal favourite but I use one of the following depending on various situations.
1) EXPLAIN PLAN
2) AUTOT TRACE
3) DBMS_XPLAN.DISPLAY_CURSOR
4) SQL TRACE (10046)and TKPROF
Lets execute them to have an idea on how these methods are different and what information one provides and other doesn't.
1) EXPLAIN PLAN (basic and simple)
SQL> explain plan for select * from dual;
Explained.
SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 |
----------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
11 rows selected.
2) AUTOT TRACE (PLAN + STATS)
SQL> set autot trace
SQL> select * from dual;
Execution Plan
----------------------------------------------------------
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 |
----------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
25 recursive calls
13 db block gets
34 consistent gets
1 physical reads
3060 redo size
208 bytes sent via SQL*Net to client
362 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
3) DBMS_XPLAN.DISPLAY_CURSOR (PLAN + OTHER DETAILS)
SQL> SELECT * FROM DUAL;
D
-
X
SQL> SELECT SQL_ID FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT * FROM DUAL%';
SQL_ID
-------------
9g6pyx7qz035v
SQL> select * from table(dbms_xplan.display_cursor('9g6pyx7qz035v',NULL,'ADVANCED'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID 9g6pyx7qz035v, child number 0
-------------------------------------
SELECT * FROM DUAL
Plan hash value: 3543395131
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / DUAL@SEL$1
Outline Data
-------------
/*+
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "DUAL"@"SEL$1")
END_OUTLINE_DATA
*/
Column Projection Information (identified by operation id):
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
1 - "DUAL"."DUMMY"[VARCHAR2,1]
Note
-----
- SQL plan baseline SQL_PLAN_c7fbanxudy9yvef6f73b0 used for this statement
41 rows selected.
4) SQL TRACE (10046)and TKPROF
Click here to know how to generate trace files and execute TKPROF in simple steps.
http://nimishgarg.blogspot.com/2010/10/oracle-tkprof-simple-steps.html
