Into my first blog entry on this topic I used 3 scripts to get real-time statistics at the database and session/sql_id level and wait events at the database level:

This new post entry add a new one : (click on the link and then on the view source button to copy/paste the source code) that is usefull to diagnose more in depth real-time relationship between sql_id, sessions and wait events.

It basically takes a snapshot each second (default interval) of the v$session_event cumulative view (values are gathered since the session started up) and computes the differences with the previous snapshot.

For example, let’s check sql_id in relation with smart table scan:

./ event='%cell%'
02:00:02 INST_NAME SID  SQL_ID          EVENT                   NB_WAITS    TIME_WAITED ms/Wait
02:00:02 BDT1      ALL  100dajbkzu295   cell smart table scan   9           408816          45.424
--------------------------------------> NEW
02:00:03 INST_NAME SID  SQL_ID          EVENT                   NB_WAITS    TIME_WAITED ms/Wait
02:00:03 BDT1      ALL  100dajbkzu295   cell smart table scan   4           273434          68.359

As you can see during the last second 4 “cell smart table scan” wait events occurred to sql_id “100dajbkzu295”.

By default all the SID have been aggregated but you could also filter on a particular sid and/or on a particular sql_id (see the help).


  • Those 4 scripts are not exclusively related to Exadata as you can use them on all the wait events or statistics, I simply used them with filters to focus on ‘%cell%’ .
  • You can choose the number of snapshots to display and the time to wait between snapshots.
  • The scripts are oracle RAC aware : you can work on all the instances, a subset or the local one.
  • You have to set oraenv on one instance of the database you want to diagnose first.


We now have 4 scripts at our disposal to diagnose real-time wait events and statistics at the database level and at the session/sql_id level.