The Broken Statistics: "parse count (total)" and "session cursor cache hits"

The values provided by the “parse count (total)” and “session cursor cache hits” statistics are subject to several bugs. And, what’s worse, for years Oracle didn’t care to fix it. This is my impression, at least.

Then, when few weeks ago I read in the Oracle Support note 13837105.8 (Bug 13837105 – statistics “parse count (total)” and “session cursor cache hits” miscounted) that the bug introduced in 11.2.0.3 was fixed, I hoped that others bugs in this area were fixed as well.

Unfortunately, it’s not the case. What a disappointment!

Even though several posts were already wrote about this topic (e.g. here and here; check the comments as well…), I thought it could be useful to summarize what the current status is.

The bugs you are more likely to be observed are the following:

• As of 11.1.0.6 the “session cursor cache hits” statistic is incremented also for cursors taking advantage of the PL/SQL client-side statement caching. As a result, the “session cursor cache hits” statistic can be much higher than the “parse count (total)” statistic. Since using client-side statement caching is the default for a PL/SQL program, the “session cursor cache hits” can be considered useless when PL/SQL is in use.
• As of 11.2.0.1 the “session cursor cache hits” statistic at the session level is stored in an unsigned integer taking 16 bits. Therefore, sessions with more than 65,535 hits experience an overflow and the value restarts from 0. Even though the statistic at the system level does not has such a limitation, when an overflow is experienced at the session level the statistic at the system level decreases of 65,535! As a result, the “session cursor cache hits” statistic is almost useless both at the session and at the system level.
• In 11.2.0.3 the “parse count (total)” statistic is not incremented for cursors taking advantage of server-side statement caching. As a result, the “session cursor cache hits” statistic can be much higher than the “parse count (total)” statistic. Since using server-side statement caching is the default, the “parse count (total)” statistic can be considered useless in 11.2.0.3. This is the only bug that was fixed in 12.1.0.1. I expect that it is fixed in 11.2.0.4 as well.

Final note: to prepare this list I carried out some tests with both a standalone Java application and PL/SQL code on all database releases from 10.2.0.1 up to 12.1.0.1.

Update 2014-03-01: as I expected the bug related to the “parse count (total)” statistic was fixed in 11.2.0.4.