Real-Time Wait Events and Statistics related to Exadata : Part II

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.

Advertisements
This entry was posted in Exadata, Perl Scripts, ToolKit and tagged , , . Bookmark the permalink.

One Response to Real-Time Wait Events and Statistics related to Exadata : Part II

  1. Heya i’m for the first time here. I found this board and I find It really useful & it helped me out much. I hope to give something back and help others like you aided me.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s