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........
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>
