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.

The utility is based on three scripts:

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.
  • The second parameter, count, specifies the number of samples.
SQL> @system_activity 15 50

                                       DBS112.ANTOGNINI.CH / 2011-02-28

Time     AvgActSess Other% Queue%   Net%   Adm%  Conf%  Comm%  Appl%  Conc% Clust% SysIO% UsrIO% Sched%   CPU%
-------- ---------- ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------
06:30:19        1.1    0.0    0.0    0.0    0.0    0.0    0.8    0.0    0.0    0.0    1.6   89.9    0.0    7.7
06:30:34        1.5    0.0    0.0    0.0    0.0    0.0    0.9    0.0    0.3    0.0    2.2   87.5    0.0    9.2
06:30:49        1.2    0.0    0.0    0.1    0.0    0.0    0.7    0.0    0.0    0.0    1.4   91.3    0.0    6.5
06:31:04        1.2    0.7    0.0    0.0    0.0    0.0    0.8    0.0    0.0    0.0    1.7   89.6    0.0    7.3
06:31:19        1.1    0.0    0.0    0.0    0.0    0.0    0.8    0.0    0.0    0.0    1.6   86.1    0.0   11.5
06:31:34        1.4    0.0    0.0    0.0    0.0    0.0    0.6    0.0    0.2    0.0    1.3   91.8    0.0    6.0
06:31:49        1.0    0.0    0.0    0.0    0.0    0.0    0.8    0.0    0.0    0.0    1.7   89.6    0.0    7.9
06:32:04        1.1    0.0    0.0    0.0    0.0    0.0    0.7    0.0    0.0    0.0    1.6   90.2    0.0    7.5
06:32:19        1.1    0.0    0.0    0.1    0.0    0.0    0.8    0.0    0.0    0.0    1.7   89.1    0.0    8.4
06:32:34        1.1    0.1    0.0    0.0    0.0    0.0    0.7    0.0    0.2    0.0    1.7   89.9    0.0    7.4
06:32:49        1.0    0.0    0.0    0.0    0.0    0.0    0.6    0.0    0.0    0.0    1.7   90.3    0.0    7.3
06:33:04        1.1    0.0    0.0    0.0    0.0    0.0    0.7    0.0    0.0    0.0    1.7   90.5    0.0    7.0
06:33:19        1.1    0.0    0.0    0.0    0.0    0.0    1.2    0.0    0.0    0.0    1.9   89.4    0.0    7.4
06:33:34        1.1    0.0    0.0    0.1    0.0    0.1    0.7    0.0    0.2    0.0    1.8   88.8    0.0    8.4
06:33:49        1.3   17.3    0.0    0.0    0.0    0.0    0.6    0.0    0.0    0.0    1.5   74.7    0.0    5.9
06:34:04        1.4   22.6    0.0    0.0    0.0    0.0    0.6    0.0    0.0    0.0    1.2   69.3    0.0    6.3
06:34:19        1.1    0.0    0.0    0.0    0.0    0.0    0.8    0.0    0.0    0.0    1.7   89.6    0.0    8.0
06:34:34        1.0    0.0    0.0    0.0    0.0    0.0    0.7    0.0    0.2    0.0    2.2   89.3    0.0    7.7
06:34:49        1.1    0.1    0.0    0.0    0.0    0.0    0.6    0.0    0.0    0.0    1.4   91.0    0.0    6.9
06:35:04        1.2   -0.1    0.0    0.1    0.0    0.0    0.8    0.0    0.4    0.0    1.7   85.5    0.0   11.6
06:35:19        6.0    0.0    0.0    0.0    0.0    0.0    1.0    0.0    0.0    0.0    1.7   88.8    0.0    8.5
06:35:34        7.4    0.0    0.0    0.0    0.0    0.0    0.8    0.0    0.0    0.0    1.9   91.2    0.0    6.0
06:35:49        7.1    0.0    0.0    0.0    0.0    0.5    1.2    0.0    0.0    0.0    1.9   90.7    0.0    5.7
06:36:04        7.2    0.9    0.0    0.0    0.0    0.0    0.9    0.0    0.0    0.0    1.7   90.7    0.0    5.8
06:36:19        7.2    0.0    0.0    0.0    0.0    0.0    1.0    0.0    0.0    0.0    1.8   90.9    0.0    6.3
06:36:34        6.8    0.0    0.0    0.0    0.0    0.0    1.0    0.0    0.0    0.0    1.7   91.3    0.0    6.0
06:36:49        7.1    0.0    0.0    0.0    0.0    0.5    1.1    0.0    0.0    0.0    1.9   90.9    0.0    5.6
06:37:04        6.9    0.0    0.0    0.0    0.0    0.0    1.0    0.0    0.0    0.0    1.8   91.3    0.0    5.9
06:37:19        6.9    0.0    0.0    0.0    0.0    0.0    0.9    0.0    0.0    0.0    1.7   91.7    0.0    5.7
06:37:34        7.1    0.0    0.0    0.0    0.0    0.0    0.9    0.0    0.0    0.0    1.8   91.6    0.0    5.8
06:37:49        6.9    0.0    0.0    0.0    0.0    0.0    1.1    0.0    0.0    0.0    1.8   91.3    0.0    5.8
06:38:04        7.0    0.0    0.0    0.0    0.0    1.1    1.0    0.0    0.2    0.0    1.9   90.2    0.0    5.7
06:38:19        6.8    0.0    0.0    0.0    0.0    0.0    0.9    0.0    0.0    0.0    1.6   91.6    0.0    5.9
06:38:34        7.2    0.3    0.0    0.0    0.0    0.0    0.9    0.0    0.0    0.0    1.7   91.0    0.0    6.0
06:38:49        6.7    0.0    0.0    0.0    0.0    0.0    1.1    0.0    0.0    0.0    1.7   91.4    0.0    5.8
06:39:04        7.4    0.0    0.0    0.0    0.0    0.4    1.2    0.0    0.0    0.0    2.0   90.9    0.0    5.5
06:39:19        2.0   -0.3    0.0    0.0    0.0    0.0    1.6    0.0    0.1    0.0    2.0   86.5    0.0   10.1
06:39:34        1.1    0.0    0.0    0.1    0.0    0.0    0.7    0.0    0.2    0.0    1.7   89.2    0.0    8.1
06:39:49        1.1    0.0    0.0    0.0    0.0    0.0    0.9    0.0    0.0    0.0    1.8   89.6    0.0    7.6
06:40:04        1.1    0.0    0.0    0.0    0.0    0.0    0.8    0.0    0.0    0.0    2.1   89.6    0.0    7.6
06:40:19        1.5   14.4    0.0    0.0    0.0    0.0    0.7    0.0    1.1    0.0    1.3   74.3    0.0    8.3
06:40:34        1.6   -0.2    0.0    0.0    0.0    0.0    0.6    0.0    0.3    0.0    1.4   87.7    0.0   10.2
06:40:49        1.3    0.0    0.0    0.1    0.0    0.0    0.6    0.0    0.0    0.0    1.6   91.2    0.0    6.5
06:41:04        1.2    0.9    0.0    0.0    0.0    0.0    0.7    0.0    0.0    0.0    1.7   89.5    0.0    7.3
06:41:19        1.2    0.0    0.0    0.0    0.0    0.0    0.7    0.0    0.0    0.0    1.8   87.3    0.0   10.3
06:41:34        1.3    0.0    0.0    0.0    0.0    0.0    0.5    0.0    0.1    0.0    1.4   91.5    0.0    6.3
06:41:49        1.1    0.1    0.0    0.0    0.0    0.0    0.8    0.0    0.0    0.0    1.6   90.3    0.0    7.2
06:42:04        1.1    0.0    0.0    0.1    0.0    0.0    0.8    0.0    0.0    0.0    2.0   89.9    0.0    7.2
06:42:19        1.1    0.0    0.0    0.0    0.0    0.0    0.7    0.0    0.0    0.0    1.5   90.8    0.0    7.1
06:42:34        1.1    0.0    0.0    0.0    0.0    0.0    0.8    0.0    0.2    0.0    2.0   89.4    0.0    7.6

The output provides:

  • the timestamp of the end of the sampling period,
  • the number of average active sessions, and
  • the percentage of time spent for each wait class and CPU.

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.

System Activity Chart

Any feedback or suggestion to improve the scripts is highly welcome.

11 Comments

  1. June 6, 2012    

    Don’t forget Ash Viewer (http://sourceforge.net/projects/ashv/).

    You can run it in standard mode and it doesn’t use the ASH views, so you can use it without D&T pack, or on older database versions.

    Cheers

    Tim…

    • Christian Antognini's Gravatar Christian Antognini
      June 6, 2012    

      Hi Tim

      I do not forget it… but, AFAIK, it doesn’t fulfill the “No GUI” requirement.

      Cheers,
      Chris

  2. June 6, 2012    

    Very nice!
    This could also be included in some DB monitoring or even RRD tool in front of this.

  3. June 6, 2012    

    Hi Christian,

    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.

    The ashviewer is cool as well.

    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.

    - Kyle Hailey

  4. Andrea's Gravatar Andrea
    June 7, 2012    

    Hello:
    I do agree completely with the “No GUI” requirement, but sometimes a picture is worth a thousand words.
    Depending on who you are discussing your data with, or to better understand correlations among values, I found GnuPlot being an invaluable too.
    So I propose to integrate the script with an optional step to plot the data.
    Supposing that the performance data goes spooled on a file named “gpash.txt”, the parameter file could be like this (save with name, say, “gpash.gp”):

    # Gnuplot script for ASH style graphs
    set terminal jpeg font "./LiberationMono-Regular.ttf,10" noenhanced size 768,384
    set output "gpash.jpg"
    set autoscale
    set decimalsign locale "en_US.UTF8" # on Windows use "american"
    set title "Top Activity"
    set ylabel "Active Sessions"
    set grid xtics ytics
    set xdata time
    set format x "%H:%M:%S"
    set timefmt "%H:%M:%S"
    set style line 3 linecolor rgbcolor "#F06EAA" # pink Other
    set style line 11 linecolor rgbcolor "#C9C2AF" # lightest-brown Cluster
    set style line 4 linecolor rgbcolor "#C2B79B" # lighter-brown Queuing
    set style line 5 linecolor rgbcolor "#9F9371" # light-brown Network
    set style line 6 linecolor rgbcolor "#717354" # med-brown Administrative
    set style line 7 linecolor rgbcolor "#5C440B" # dark-brown Configuration
    set style line 8 linecolor rgbcolor "#E46800" # orange Commit
    set style line 9 linecolor rgbcolor "#C02800" # red Application
    set style line 10 linecolor rgbcolor "#8B1A00" # brick Concurrency
    set style line 12 linecolor rgbcolor "#0094E7" # light-blue System I/O
    set style line 13 linecolor rgbcolor "#004AE7" # dark-blue User I/O
    set style line 14 linecolor rgbcolor "#CCFFCC" # light-green Scheduler
    set style line 15 linecolor rgbcolor "#00CC00" # green CPU
    set style data lines
    plot
    "gpash.txt" every ::2 using 1:($2*($3+$4+$5+$6+$7+$8+$9+$10+$11+$12+$13+$14+$15)/100) linestyle 3 title "Other" with filledcurve x1,
    "" every ::2 using 1:($2*($4+$5+$6+$7+$8+$9+$10+$11+$12+$13+$14+$15)/100) linestyle 11 title "Cluster" with filledcurve x1,
    "" every ::2 using 1:($2*($4+$5+$6+$7+$8+$9+$10+$12+$13+$14+$15)/100) linestyle 4 title "Queueing" with filledcurve x1,
    "" every ::2 using 1:($2*($5+$6+$7+$8+$9+$10+$12+$13+$14+$15)/100) linestyle 5 title "Network" with filledcurve x1,
    "" every ::2 using 1:($2*($6+$7+$8+$9+$10+$12+$13+$14+$15)/100) linestyle 6 title "Administrative" with filledcurve x1,
    "" every ::2 using 1:($2*($7+$8+$9+$10+$12+$13+$14+$15)/100) linestyle 7 title "Configuration" with filledcurve x1,
    "" every ::2 using 1:($2*($8+$9+$10+$12+$13+$14+$15)/100) linestyle 8 title "Commit" with filledcurve x1,
    "" every ::2 using 1:($2*($9+$10+$12+$13+$14+$15)/100) linestyle 9 title "Application" with filledcurve x1,
    "" every ::2 using 1:($2*($10+$12+$13+$14+$15)/100) linestyle 10 title "Concurrency" with filledcurve x1,
    "" every ::2 using 1:($2*($12+$13+$14+$15)/100) linestyle 12 title "System I/O" with filledcurve x1,
    "" every ::2 using 1:($2*($13+$14+$15)/100) linestyle 13 title "User I/O" with filledcurve x1,
    "" every ::2 using 1:($2*($14+$15)/100) linestyle 14 title "Scheduler" with filledcurve x1,
    "" every ::2 using 1:($2*($15)/100) linestyle 15 title "CPU" with filledcurve x1

    Then you run with the command “gnuplot gpash.gp”.
    You can see the resulting image here.

    Hope you like it,
    Andrea

    • Christian Antognini's Gravatar Christian Antognini
      June 12, 2012    

      Hi Andrea

      Thank you very much for the nice example. I personally like Excel for this kind of stuff, though.

      Cheers,
      Chris

  5. Tom Dale's Gravatar Tom Dale
    June 13, 2012    

    Hi Christian,

    Really like the script and the blog.

    Here is another sqlplus monitoring script…

    http://thomasdale.wordpress.com/2012/06/13/whats-going-on-in-my-database/

    Tom

  6. Rene Jeruschkat's Gravatar Rene Jeruschkat
    June 15, 2012    

    I took the liberty to rewrite your script to skip the need for upfront object creation. Apart from that the only other difference is that the output is not printed live but at the end of the run.

  7. Jessica H.'s Gravatar Jessica H.
    June 20, 2012    

    Hi Christian,

    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?

    Cheers,
    Jessica

    • Christian Antognini's Gravatar Christian Antognini
      June 20, 2012    

      Hi Jessica

      Be careful…

      AvgActSess tells you how busy is the system (average number of active sessions).

      All “%” values tells you how much DB Time is spent in each wait class. In other words, the sum of all “%” is always 100%.

      An example:
      - AvgActSess = 10
      - CPU% = 80%
      –> 10 * 0.8 = 8 CPUs are fully used

      Best,
      Chris

      • Jessica H.'s Gravatar Jessica H.
        June 20, 2012    

        Hi Chris,

        first of all: big thanks because of your fast answer an your good explanation!
        Now it is much more easier for me to understand the whole thing :)

        Cheers,
        Jessica

Leave a Reply

Your email address will not be published. Required fields are marked *