TIP 73#: How to have execution plan with run-time

As you may know, the 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.
There are 2 ways to find out run-time of SQL statement which could be used greatly in SQL tuning :

Option 1 :

Trace SQL statement when statistics set to All and find out run-time from the trace file.

Obviously this option is traditional/old way which uses resources and takes time to scan trace file and find out run-time.

Option 2 :

Use gather_plan_statistics in SQL statement which let Oracle to gather most required stats for SQL tuning.

This option is simpler and faster.
I demonstrate it with the following example :

Step 1 : Enable sqlplus trace :

In order to prevent SQL statement to output the result on screen, it is better of to enable trace in order to just run and report statistics.

set autotrace traceonly stat;


Step 2 : Run query with hint:

My sample runs a simple query against test1 table.


SQL> desc test1
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
NAME VARCHAR2(30 CHAR)

SQL> select /*+ gather_plan_statistics */ * from test1 where id=:myid;

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
648 bytes sent via SQL*Net to client
488 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed

3. Find SQL statement in shared pool


SQL> select sql_id,executions from v$sql where sql_text='select /*+ gather_plan_statistics */ * from test1 where id=:myid';

SQL_ID EXECUTIONS
------------- ----------
82u1sypmr2k1f 1


4. Find execution plan with all run-time :


SQL> select * from table(dbms_xplan.display_cursor('82u1sypmr2k1f',null,'ALL IOSTATS LAST'));





As you see, E-Time shows execution plan which definitely is helpful to find out bottleneck in SQL execution and to find out at which part SQL statement is taking most of the time.