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

7 comments:

  1. When an 'explain plan' is run on a SQL, it is not necessarily the same plan that is used during its execution - rather Oracle thinks this is the plan that will be used by the optimizer at runtime. Select the plan hash value from the gv$sql view. Then select the plan from gv$sql_plan using the plan hash value. Not good to rely on 'explain plan' so I personally never use it. Your other methods should work well (10046 trace, and display cursor).

    Jack Augustin
    Principal Consultant at Enkitec

    ReplyDelete
  2. Thank you for the information! It is usefull for my job

    ReplyDelete
  3. Nice compilation.

    ReplyDelete
  4. Sundaramurthy RajuOctober 5, 2015 at 8:57 AM

    We can use DBMS_XPLAN.DISPLAY_CURSOR(), Set AUTOTRACE..Intresting fact is that the explain plan shares relatively less information from the v$ performance views. Hence, looking at the DBMS_XPLAN.DISPLAY_CURSOR() could be the better options, or the TKPROF highlighted in the blogpost.

    ReplyDelete
  5. If you are using a tool like TOAD, just select the sql for which you want to generate the explain plan and say "ctrl+E" to see the plan below. other wise, ensure that your plan table is created and use the command explain plan.

    ReplyDelete
  6. Hi Nimish.. I need a small help here you could guide me. We usually generate reports from SQL. In DB we generate basic reports like Database reports or audit reports or Data Health check Reports .. But what kind of reports are generated in the real world scenario ??? As in may be in Finance sector or Healthcare sector..

    ReplyDelete