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:
system_event.pl
sysstat.pl
sqlidstat.pl
This new post entry add a new one :
sqlidevent.pl (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:
./sqlidevent.pl 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).
Remarks:
- 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.
Conclusion:
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.