Sunday, July 1, 2012

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 - topaas.zip.  


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

7 comments:

Joaquin Gonzalez said...

Hi Marcin,

Very nice tool.
Just say it fails if nls_numeric_characters is ',.'.
The problem is in read_data cursor.

Anyway, thank you for providing such a useful tool.

Joaquín González

Marcin Przepiorowski said...

Good catch - never use it before on different locals. It should be easy to fix in next version.

Marcin

Jim Boles said...

Thx for the post. This went right into my toolbox.

Nyckelpiga said...

Hello Marcin,

many thanks!
topaas is a very useful tool, I love it, and I will use it, IE because I have a lot of Standard licensed databases and there are only very few good tools to monitor performance without enterprise&diagnostic licenses.

Regards, Gerrit

Nyckelpiga said...

Hello again Marcin,

any plans to release the program under some opensource license?

Regards, Gerrit

Marcin Przepiorowski said...

Actually never thought what kind of license I could use for it. What do you need it for ?

Marcin

Kyle Hailey said...

This is very cool. At some point I want to dig into it. I did dived into moats and added I/O histograms to it, but am not that satisfied with the result. Would be cool to modify this and add the top events and sql below it as wel as I/O times.
- Kyle Hailey
dboptimizer.com