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

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

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';

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

No comments: