How to Get Execution Plan and Statistics of SQL Query

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



Related Posts:
- Query optimization tips for Oracle
- Oracle : TKPROF simple steps
- DBMS_PROFILER: How to analyze pl/sql performance
- Oracle SQL Developer - Autotrace - Insufficient Privileges
- Tune Complete Refresh of Materialized View by atomic_refresh=>false parameter of dbms_mview.refresh
- Why my distinct query is not using the Index?
- Avoiding unnecessary function calls to optimize SQL statements
- Append String to CLOB in Optimized way
- Why do I prefer COALESCE over NVL
- Doubt: Truncate Command and Table Statistics