Display System Activity without Enterprise Manager

I regularly use the system-level activity chart available in Enterprise Manager. In my opinion it is a simple and effective way to know how much a specific database is loaded at a specific time. This is for example an interesting way for observing how a specific load is processed (see this post for an example).
Unfortunately it also happens that this possibility is not available. The main reasons I faced in the past are the following:

Standard Edition is used

Enterprise Edition is used but Diagnostic Pack is not licensed

Enterprise Manager is not available

GUI is not available

The aim of this post is to present you a utility that I wrote to cope with these restrictions. It goes without saying that its main purpose is to display information similar to the one provided by the system-level activity chart when working in Standard Edition (or Enterprise without Diagnostic Pack) in a terminal. And that, with both 10g and 11g.

Several dynamic performance views externalize information that can be used for displaying the activity of a system. The two I chose are v$sys_time_model and v$system_wait_class. The challenge of using these views is that they provide only cumulative statistics that are incremented on a regular basis. It is therefore necessary to use a utility that samples the information they provide. In other words, to find out how much a specific statistic changes over a short period of time.

To install the utility execute, as SYS, the system_activity_setup.sql script. It creates several object types, a function (the core of the utility) and a public synonym. In addition it grants the privilege to execute the function to public.

To use the utility you can directly call the system_activity function or, to have a decent output, execute the system_activity.sql script in SQL*Plus. As the following example shows the script requires two parameters:

The first parameter, interval, specifies in seconds how much time to wait to compute the deltas. Since the database engine does not update the statistics in real-time, specifying less than 10-15 seconds is usually pointless.

For comparison purposes, here is the activity chart for the very same period of time. Even though the data is not exactly the same (the activity chart is based on ASH, a completely different source of information), it is good enough to know how much a database is loaded and which wait class is the major contributor.

Nice scripts. The continuous output with pipeline is cool. I first saw this with Adrian and Tanel’s moats.sql. Definitely something I want to play around with more. I modified moats.sql to show average I/O latency and histogram for the I/O events and sent it off to Adrian to see if he wanted to include it. In the mean time I have been meaning to pull this I/O part out into it’s own script so I could blog about it. Given your nice example, that may make the job easier.

For web enabled ( fails the “no gui” test, though output could be converted to ascii :P ) I put together a highcharts (jquery library) interface that is pretty much like the top activity page with area selection and drilldown and it works without the diagnostics pack: http://dboptimizer.com/2011/10/31/w-ash-web-enabled-ash/
It was mainly a proof of concept. Should probably put it on github and start iterating and improving it.

today I’ve found your post and I’m very happy to find something for a Oracle Standard One Edition :)
I run the setup.sql and try the whole thing. It works great but I’m not sure that there are correct values. The value of CPU% says the most time 100% (or above 80%) but when I look at the comman top on the machine its’s only uses under 10% . So I looked into the setup script and there are coloums with l_cpu2 and l_cpu1. Have I to adapt the script for more available cpus?