Execution plan of query in Oracle

Today i study about the area of execution plans and this is the post , that what i collect after reading several
websites and blog and youtube and presentation from scribd. My main motive to get actual execution plan and below is the notes.

We can get the query execution plan by below ways:-
1. explain plan
2. autotrace
3. tkprof
4. dbms_xplan.display_cursor

First three ways are used for estimated execution plan of query in oracle and fourth give us acual execution plan.
1. explain plan
->explain plan statement that produces execution plan without execute the statement
    - where to use
        - in long running queries.
        - if we need a fair idea of flow of execution plans.

    how to use:
    -
    explain plan for select * from emp;
    -
    select * from table(dbms_xplan.display);

2. autotrace  
->autotrace on same as explain plan (generation of plan is similler as "explain plan")
    - autotrace statement produces execution plan after execution of the statement.
    - need to wait till query is not executed
  how to use:


 
sql> set autotrace on               [block 1,2,3 will display]
sql> set autotrace trace           [block 2,3 will display]
sql> set autotrace on explain   [block 1,2 will display]
sql> set autotrace on statistics [block 1,3 will display]

--enable autotrace from sql*plus window
see here
Difference between explain plan and autotrace

3. tkprof  
-> tkprof
    - get reports after only when we enable trace
    - have execution plan , more detail info all disk inputs and outputs
    - get info from trace file

    Detail usability

4.dbms_xplan.display_cursor
->dbms_xplan.display_cursor show actual execution plan from dynamic performance views

    More details See here :
    How to get actual execution plan in oracle

 Similler series of posts
1. Difference between explain plan and autotrace
2. Usability of tkprof
3. How to get actual explain plan of query in oracle

For any modification , updatation in post please comment