Oracle Database Query execution plan with runtime statistics

Normal execution plan does not give you run-time of each pieces of any execution plan, time which is reported is not exactly real elapsed time of SQL statement.

you can find execution plan with run time (which could be used greatly in SQL tuning) by following ways

1) excute the query with " /*+gather_plan_statistics */ " hints .
then see the execution plan with
select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

2) set STATISTICS_LEVEL to all like " ALTER SESSION SET STATISTICS_LEVEL = ALL ; "
then see the execution plan with
select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

following are the demonstration with example........

 SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 5 15:02:55 2012  
 Copyright (c) 1982, 2010, Oracle. All rights reserved.  
 SQL> conn sys@orcl251 as sysdba  
 Enter password:  
 Connected.  
 SQL>  
 SQL>  
 SQL> set linesize 1000  
 SQL> set pagesize 1000  
 SQL>  
 SQL>  
 SQL>  
 SQL> select * from scott.emp where deptno=10 ;  
    EMPNO ENAME   JOB       MGR HIREDATE     SAL    COMM   DEPTNO  
 ---------- ---------- --------- ---------- --------- ---------- ---------- ----------  
    7782 CLARK   MANAGER     7839 09-JUN-81    2450          10  
    7839 KING    PRESIDENT      17-NOV-81    5000          10  
    7934 MILLER   CLERK      7782 23-JAN-82    1300          10  
 SQL>  
 SQL>  
 SQL>  
 SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));  
 PLAN_TABLE_OUTPUT  
 ----------------------------------------------------------------------------------------------  
 ----------------------------------------------------------------------------------------------  
 ----------------------------------------------------------------------------------------------  
 SQL_ID fhr3t48g0hgqu, child number 0  
 -------------------------------------  
 select * from scott.emp where deptno=10  
 Plan hash value: 3956160932  
 -------------------------------------------  
 | Id | Operation     | Name | E-Rows |  
 -------------------------------------------  
 |  0 | SELECT STATEMENT |   |    |  
 |* 1 | TABLE ACCESS FULL| EMP |   6 |  
 -------------------------------------------  
 Predicate Information (identified by operation id):  
 ---------------------------------------------------  
   1 - filter("DEPTNO"=10)  
 Note  
 -----  
   - Warning: basic plan statistics not available. These are only collected when:  
     * hint 'gather_plan_statistics' is used for the statement or  
     * parameter 'statistics_level' is set to 'ALL', at session or system level  
 24 rows selected.  
 SQL>  
 SQL>  
 SQL>  
 SQL>  
 SQL>  
 SQL>  
 SQL>  
 SQL>  
 SQL>  
 SQL>  
 SQL> select /*+gather_plan_statistics */ * from scott.emp where deptno=10  
  2 ;  
    EMPNO ENAME   JOB       MGR HIREDATE     SAL    COMM   DEPTNO  
 ---------- ---------- --------- ---------- --------- ---------- ---------- ----------  
    7782 CLARK   MANAGER     7839 09-JUN-81    2450          10  
    7839 KING    PRESIDENT      17-NOV-81    5000          10  
    7934 MILLER   CLERK      7782 23-JAN-82    1300          10  
 SQL>  
 SQL>  
 SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));  
 PLAN_TABLE_OUTPUT  
 ----------------------------------------------------------------------------------------------  
 ----------------------------------------------------------------------------------------------  
 ----------------------------------------------------------------------------------------------  
 SQL_ID 9j35xrpsjp76j, child number 0  
 -------------------------------------  
 select /*+gather_plan_statistics */ * from scott.emp where deptno=10  
 Plan hash value: 3956160932  
 ------------------------------------------------------------------------------------  
 | Id | Operation     | Name | Starts | E-Rows | A-Rows |  A-Time  | Buffers |  
 ------------------------------------------------------------------------------------  
 |  0 | SELECT STATEMENT |   |   1 |    |   3 |00:00:00.01 |    8 |  
 |* 1 | TABLE ACCESS FULL| EMP |   1 |   6 |   3 |00:00:00.01 |    8 |  
 ------------------------------------------------------------------------------------  
 Predicate Information (identified by operation id):  
 ---------------------------------------------------  
   1 - filter("DEPTNO"=10)  
 18 rows selected.  
 SQL>  
 SQL>  
 SQL>  
 SQL>  
 SQL>  
 SQL>  
 SQL>  
 SQL>  
 SQL>  
 SQL>  
 SQL>  
 SQL>  
 SQL>  
 SQL> select /*+gather_plan_statistics */ * from scott.emp where deptno=10  
  2 ...  
  3 ..  
  4  
 SQL>  
 SQL>  
 SQL> ALTER SESSION SET STATISTICS_LEVEL = ALL;  
 Session altered.  
 SQL>  
 SQL>  
 SQL> select * from scott.emp where deptno=10 ;  
    EMPNO ENAME   JOB       MGR HIREDATE     SAL    COMM   DEPTNO  
 ---------- ---------- --------- ---------- --------- ---------- ---------- ----------  
    7782 CLARK   MANAGER     7839 09-JUN-81    2450          10  
    7839 KING    PRESIDENT      17-NOV-81    5000          10  
    7934 MILLER   CLERK      7782 23-JAN-82    1300          10  
 SQL>  
 SQL>  
 SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));  
 PLAN_TABLE_OUTPUT  
 ----------------------------------------------------------------------------------------------  
 ----------------------------------------------------------------------------------------------  
 ----------------------------------------------------------------------------------------------  
 SQL_ID fhr3t48g0hgqu, child number 1  
 -------------------------------------  
 select * from scott.emp where deptno=10  
 Plan hash value: 3956160932  
 ------------------------------------------------------------------------------------  
 | Id | Operation     | Name | Starts | E-Rows | A-Rows |  A-Time  | Buffers |  
 ------------------------------------------------------------------------------------  
 |  0 | SELECT STATEMENT |   |   1 |    |   3 |00:00:00.01 |    8 |  
 |* 1 | TABLE ACCESS FULL| EMP |   1 |   6 |   3 |00:00:00.01 |    8 |  
 ------------------------------------------------------------------------------------  
 Predicate Information (identified by operation id):  
 ---------------------------------------------------  
   1 - filter("DEPTNO"=10)  
 18 rows selected.  
 SQL>