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. Update 2016-12-20: this problem was fixed in 12.2.0.1.
- 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.2.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.
I still see the Parse count(total) much higher than “session cursor cache hits” in our 11.2.0.4 database. Is it normal?
Hi
What you describe is the expected behavior in case caching is not used or effective. So, I consider it a normal situation, not an issue related to broken statistics.
Best,
Chris
Hi Christian,
On an 11.2.0.4 base install (no PSUs) database I see session cursor cache hits much greater than parse count(total), by a factor of around 1.5. Is this some other bug that’s not been fixed?
Thanks,
Chris
Hi
What you mention fits in the description of the first bug I mentioned in the post. Or no PL/SQL is involved?
Best,
Chris
Hi Christian,
Yes there is some PL/SQL. So I guess I’m seeing the “11.1.0.6” bug you mention that’s still not fixed.
BTW On a similar note I also see cases where session cursor cache count is greater than session_cached_cursors.
Thanks,
Chris
[…] found the confirmation in this Christian’s post so this is not fixed yet in […]
I am using 12.1.0.2 and it seems it is still not completely fixed:
In my sessions I have more session cursor cache hits than parse count (total).
How can we tune session_cached_cursors with out this information?
Hi
I just verified that also 12.2 is hit by that “bug”. At least the “session cursor cache hits” statistic at the session level was fixed.
Best,
Chris