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

Yesterday -- The Backup Song

A couple of days ago I stumbled across this funny song:

Yesterday -- The Backup Song 


Yesterday,
All those backups seemed a waste of pay.
Now my database has gone away.
Oh I believe in yesterday.

Suddenly,
There's not half the files there used to be,
And there's a milestone hanging over me
The system crashed so suddenly.

I pushed something wrong
What it was I could not say.
Now all my data's gone
and I long for yesterday-ay-ay-ay.

Yesterday,
The need for back-ups seemed so far away.
I knew my data was all here to stay,
Now I believe in yesterday.

ORACLE: Purge a cursor from the shared pool

Have you ever wanted to purge a specific SQL statement from the shared pool.
This comes in handy when you want to force a hard parse for a specific statement.


1. find the sql_id from the specific statement in the shared pool

select sql_id from v$sql where sql_text='sql text';

2. find the address + hash value of this particular statement:

SELECT address||','||hash_value, version_count  FROM v$sqlarea WHERE sql_id = 'sql id';

3. purge the statement from the shared pool using the above address+hash:

EXECUTE dbms_shared_pool.purge('address + hash','C',1);

A short example of the above steps, assumed you are looking for the sql statement:
select * from t1 where object_id=3000;

SQL> select sql_id from v$sql where sql_text='select * from t1 where object_id=3000';

SQL_ID        
------------- 
79xnbb98drf3m 

Elapsed: 00:00:00.026

SQL> SELECT address||','||hash_value FROM v$sqlarea WHERE sql_id = '79xnbb98drf3m';

ADDRESS||','||HASH_VALUE                          
------------------------------------------------- 
27197058,1356576883                               

Elapsed: 00:00:00.003

SQL> EXECUTE dbms_shared_pool.purge('27197058,1356576883','C',1);

PL/SQL procedure successfully completed.

SQL> select sql_id from v$sql where sql_text='select * from t1 where object_id=3000';

no rows selected