Sunday, April 3, 2011

SASH visualization using Excel

OraSASH is a free implementation of Oracle Active Session History mechanism but it is providing only raw tables with data. To help other people use that functionality and inspired by Charles Hooper blog I have created a Excel based simple GUI interface for SASH. It is providing historical and real time view based on repository. It has been tested on Windows 7 64 bit with 32 bit Excel 2007. To make it running it is necessary to install and configure Oracle ODAC Zip file can be downloaded from SourceForge repository. Please be aware that this is alpha stage of this Excel sheet and it can contain errors. Read only access is needed to OraSASH repository. Macros have to be enabled in Excel as all functionality is based on Visual Basic.

Setup sheet
Fill out repository user and tns alias name on Setup sheet and click Start button.

Historic data should be read from repository for whole available period of time and available instances list will be fill out as well. When all data will be collected active sheet will be switched to Graph sheet.
Graph sheet

There are two charts on Graph sheet – upper one is displaying Average Active Session - for AAS concept see John Beresniewicz presentation or Kyle Hailey blog.
Chart on the right side is displaying SQL activity for selected period of time. Percent of activity is calculated against number of all samples from that time. This same activity percent can be seen on OEM performance tab. SQL details like query text and plan will be displayed in Detail SQL view when chart bar for appropriate SQL ID will be clicked.
Historic view
To change period displayed on screen change start and stop date and then click Refresh Historic View button. 
Real time view
To see current database activity switch radio button to Real Time refresh. Refresh rate can be change on this same sheet in Refresh Rate field. It take one full refresh cycle to pick up a refresh new value.
Detail SQLsheet

Separate sheet for every SQL query. Sheet name is SQL_ID and detail available information about query text and plan will be displayed there. In addition to that detail information about wait event and CPU utilization will be displayed as well.

If you have any comments or questions please feel free to contact me.