ORACLE: SQL execution statistics

Oracle provides several tools to see the internals of the SQL optimizer.

To see what's going on behind the scenes you had a couple of options in the past:

1. Using SQL Trace ( event 10046 )
The use of SQL Trace is probably the best option available because there is no way getting more detailed data about the execution of a SQL Statement. But this requires some preparation ( enable / disable the SQL Trace ) and analysis of the RAW Tracefile or using TKPROF to distill the information from the RAW File.

2. Using SQL*Plus "set autotrace"
This is a very easy to use method but it has some caveats regarding using type conversions

3. The use of V$SQL_WORKAREA
Additional information about extended execution plan statistics are available about the Row-Source operations of the SQL statement.

Now a new option is available if you are running Oracle 11g: The GATHER_PLAN_STATISTICS Hint

Like all other optimizer hints, this hint is placed as comment like /*+ GATHER_PLAN_STATISTICS */
There is another possibility for gathering the execution plan statistics using the init.ora parameter statistics_level=all which is set to 'typical' by default but not recommended to set to 'all' on a production system since there is some overhead involved.

Here is an example of how the hint is used:

SQL> select /*+ gather_plan_statistics */ * from t1 where id1 = 100 

Some Versions of oracle seem to be affected by a Bug and the resulting  numbers are incorrect. To avoid this possibility you can also set the instance/session  parameter "statistics_level" to ALL ( default is TYPICAL ).
At instance level the setting to ALL could cause some overhead to your database so  I would recommend setting it at session level, so your session only is affected.

alter session set statistics_level=all;

To lookup the information the DBMS_XPLAN Package is used

SQL> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));