Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

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

14 August 2013 8 Comments Written by Christian Antognini

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.

10gR2, 11gR1, 11gR2, 12cR1, 12cR2, Bug
Scripts to Download Oracle Database 12c Release 1 Documentation
System Statistics Gathered in Exadata Mode – When Are They Relevant?

8 Comments

1 Ping/Trackback

  1. vishwajit vishwajit
    4 May 2015    

    I still see the Parse count(total) much higher than “session cursor cache hits” in our 11.2.0.4 database. Is it normal?

    Reply
    • Christian Antognini Christian Antognini
      8 May 2015    

      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

      Reply
  2. Chris Chris
    29 October 2015    

    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

    Reply
    • Christian Antognini Christian Antognini
      29 October 2015    

      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

      Reply
      • Chris Chris
        2 November 2015    

        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

        Reply
  3. OCI Client-Side Deployment in 12C « Oracle and beyond... OCI Client-Side Deployment in 12C « Oracle and beyond...
    24 February 2016    

    […] found the confirmation in this Christian’s post so this is not fixed yet in […]

  4. Byron Fonseca Byron Fonseca
    15 December 2016    

    I am using 12.1.0.2 and it seems it is still not completely fixed:

       SID    SERIAL# CUR_CCH_CNT CUR_CCH_HIT  PARSE_TOT   HARD_CNT
    ------ ---------- ----------- ----------- ---------- ----------
      1633      26064          24       59661      14625       2676
      3085      35924           5       11166       2737        483
      1525      34819          24       28268       6891       1282
      4650       1726          21       10384       2476        434

    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?

    Reply
    • Christian Antognini Christian Antognini
      20 December 2016    

      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

      Reply
  1. OCI Client-Side Deployment in 12C « Oracle and beyond... on 24 February 2016 at 09:25

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.