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 126.96.36.199 system as well, so no comparison there either :( This is Oracle on Linux.
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', 'NA' ) as decoded_class from v$statname ) select count(decoded_class), decoded_class from stats group by rollup(decoded_class) order by 1 /
188.8.131.52 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 184.108.40.206 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:
COUNT(DECODED_CLASS) DECODED -------------------- ------- 9 ENQUEUE 16 OS 25 RAC 32 REDO 47 NA 93 SQL 107 USER 121 CACHE 188 DEBUG 638
So there are 638 of these counters. Let’s move on to 220.127.116.11
Oracle 18.104.22.168 is interesting as it has been released after 22.214.171.124. 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?
COUNT(DECODED_CLASS) DECODED -------------------- ------- 9 ENQUEUE 16 OS 25 RAC 34 REDO 48 NA 96 SQL 117 USER 127 CACHE 207 DEBUG 679
A few more, all within what can be expected.
Oracle 126.96.36.199 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:
COUNT(DECODED_CLASS) DECODED -------------------- ------- 9 ENQUEUE 16 OS 35 RAC 68 REDO 74 NA 130 SQL 130 USER 151 CACHE 565 DEBUG 1178
That’s nearly double what you found for 188.8.131.52. Incredible, and hence this post. Comparing 184.108.40.206 with 220.127.116.11 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.