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 12.1.0.1 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',
'NA'
) as decoded_class from v$statname
)
select count(decoded_class), decoded_class
from stats
group by rollup(decoded_class)
order by 1
/
Oracle 11.2.0.3
11.2.0.3 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 11.2.0.3 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 11.2.0.4
Oracle 11.2.0.4
Oracle 11.2.0.4 is interesting as it has been released after 12.1.0.1. 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 12.1.0.2
Oracle 12.1.0.2 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 11.2.0.3. Incredible, and hence this post. Comparing 11.2.0.4 with 12.1.0.2 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.
Martins Blog 