Average Active Session in SQL*plus with refresh

Recently when I hit performance issues and figure out that OEM agent is mis-configured for that host I wish I have script to display live Average Active Session in SQL*Plus. Of course there is a plenty of other great tools like Tanel Poder’s Snapper or Tanel and Adrian Billington MOATS.
MOATS could be a answer for my needs but it required some objects to be created in database. From other side Snapper is using dynamic objects only but it is not displaying history so I can’t see at a glance if system performance has been improved or not.

I decided to answer my needs and I have created SQL*Plus script displaying AAS history using only dynamic objects like Snapper. I would like to thanks Tanel and Adrian for inspiration how to build SQL*Plus active output scripts.

Here is a sample screenshot.

Average Active Session is calculated based on v$session sampling and output is divided into three event category – CPU, DISK I/O and OTHER. Technically it is possible to add more classes but it become more tricky to read it from screen – so I think these three are a good balance between knowing what is going on and visibility. For deeper investigation you can use Snapper.

This tool is using two scripts (both have to be in one directory):

• runtopaas.sql - is a main script to parse run attributes and specify a run environment for topaas.sql script. It is calling topaas.sql 100 times

• topaas.sql - is sampling v$session every 1 s for time specified in refresh rate parameters and keep it in PL/SQL collection.  At the end AAS (divided into 3 sections: CPU, Disk I/O and other) is calculated and displayed on screen.  In addition to that AAS results are added to bind variables together with sample time. When topaas.sql is called next time it is reading data from bind variables and it allow it to have history of AAS from past and display it on screen. Default configuration allow to display 100 data point

How to use it:

• Change SQL*Plus window / screen terminal to 45 characters height and 150 characters wide
  
• Run in SQL*Plus window:
  
• SQL> @runtopaas.sql aas:refresh rate  - it will specify refresh rate (ex. 15 s) and with 100 samples it allow to keep 25 min of AAS in SQL*Plus window.If script will be started again after 100 cycles or after user break in this same session it will still be able to display historical data
  
• SQL> @runtopaas.sql aas:refresh rate:reset - like above but historical data are cleared
  
• SQL> @runtopaas.sql aas:refresh rate:max aas - like above but maximum value of AAS (y axis) is set by user
  
• SQL> @runtopaas.sql aas:refresh rate:max aas:reset - like above but historical data are cleared

Examples:

SQL> @runtopaas aas:15

Yes there was a problem - AAS around 600 is not a normal one. When issue has been fixed I want to reset historic data and run script again


SQL> @runtopaas aas:15:reset

after some time 

You can download both scripts here - Github repo.  


Let me know if this tool is useful for you or you if you found any problems with it.
regards,
Marcin