Martins Blog

Trying to explain complex things in simple terms

A brief history of time^H^H Oracle session statistics

Posted by Martin Bach on August 6, 2014

I didn’t intend to write another blog post yesterday evening at all, but found something that was worth sharing and got me excited… And when I started writing I intended it to be a short post, too.

If you have been digging around Oracle session performance counters a little you undoubtedly noticed how their number has increased with every release, and even with every patch set. Unfortunately I don’t have a 11.1 system (or earlier) at my disposal to test, but here is a comparison of how Oracle has instrumented the database. I have already ditched my system as well, so no comparison there either :( This is Oracle on Linux.

The script

In the following examples I am going to use a simple query to list the session statistics by their class. The decode statement is based on the official documentation set. There you find the definition of v$statname plus an explanation of the meaning of the class-column. Here is the script:

with stats as (
        select name, decode(class,
                1, 'USER',
                2, 'REDO',
                4, 'ENQUEUE',
                8, 'CACHE',
                16, 'OS',
                32, 'RAC',
                64, 'SQL',
                128, 'DEBUG',
        ) as decoded_class from v$statname
select count(decoded_class), decoded_class
 from stats
 group by rollup(decoded_class)
 order by 1

Oracle is probably the most common 11g Release 2 version currently out there in the field. Or at least that’s my observation. According to MOS Doc ID 742060.1 was released on 23 September 2011 (is that really that long ago?) and already out of error correction support by the way.

Executing the above-mentioned script gives me the following result:

-------------------- -------
                   9 ENQUEUE
                  16 OS
                  25 RAC
                  32 REDO
                  47 NA
                  93 SQL
                 107 USER
                 121 CACHE
                 188 DEBUG

So there are 638 of these counters. Let’s move on to


Oracle is interesting as it has been released after It is the terminal release for Oracle 11.2, and you should consider migrating to it as it is in error correction support. The patch set came out on 28 August 2013. What about the session statistics?

-------------------- -------
                   9 ENQUEUE
                  16 OS
                  25 RAC
                  34 REDO
                  48 NA
                  96 SQL
                 117 USER
                 127 CACHE
                 207 DEBUG

A few more, all within what can be expected.


Oracle is fresh off the press, released just a few weeks ago. Unsurprisingly the number of session statistics has been increased again. What did surprise me was the number of statistics now available for every session! Have a look at this:

-------------------- -------
                   9 ENQUEUE
                  16 OS
                  35 RAC
                  68 REDO
                  74 NA
                 130 SQL
                 130 USER
                 151 CACHE
                 565 DEBUG

That’s nearly double what you found for Incredible, and hence this post. Comparing with you will notice the:

  • same number of enqueue stats
  • same number of OS stats
  • 10 additional RAC stats
  • twice the number of REDO related statistics
  • quite a few more not classified (26)
  • 34 more sql related
  • 13 more in the user-class
  • 24 additional stats in the cache-class
  • and a whopping 298 (!) in the debug class

The debug class (128) shows lots of statistics (including spare ones) for the in-memory option (IM):

SQL> select count(1), class from v$statname where name like 'IM%' group by class;

  COUNT(1)      CLASS
---------- ----------
       211        128

Happy troubleshooting! Reminds me to look into the IM-option in more detail.

3 Responses to “A brief history of time^H^H Oracle session statistics”

  1. Jason said

    Some older systems for you to compare against. Hopefully the formatting is somewhat retained, but may not be.
    -------------------- -------------
                       6 ENQUEUE
                      14 REDO
                      17 OS
                      20 NA
                      29 RAC
                      34 SQL
                      53 USER
                      73 DEBUG
                      87 CACHE
    -------------------- -------------
                       6 ENQUEUE
                      15 REDO
                      16 OS
                      22 NA
                      24 RAC
                      40 SQL
                      74 USER
                      88 DEBUG
                      95 CACHE
    -------------------- -------------
                       6 ENQUEUE
                      16 OS
                      23 REDO
                      25 RAC
                      38 NA
                      58 SQL
                      85 USER
                     103 CACHE
                     131 DEBUG
  2. […] Bach on a new buffering option for 12c I was prompted to take a look at another of his posts on the instance activity stats, which reminded me that the CLASS column on v$statname is a bit flag, which we can dissect using […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Get every new post delivered to your Inbox.

Join 2,947 other followers

%d bloggers like this: