Workload System Statistics Bug in 11.2

Since the introduction of 11.2 I receive on a regular basis questions related to “strange” workload system statistics. The last email on that topic was sent to me yesterday. So, instead to send, again, a private reply, I decided to write this short post.

What’s wrong with 11.2 and workload system statistics?

Let’s have a look to the output of the following query:

SQL> SELECT pname, pval1
  2  FROM sys.aux_stats$
  3  WHERE sname = 'SYSSTATS_MAIN';

PNAME                  PVAL1
--------------- ------------
CPUSPEEDNW            1596.0
IOSEEKTIM                4.0
IOTFRSPEED            4096.0
SREADTIM             10900.3
MREADTIM              4525.8
CPUSPEED              1603.0
MBRC                     7.0
MAXTHR            17391616.0
SLAVETHR            413696.0

As you can see the SREADTIM and MREADTIM times are very high. In this case about three orders of magnitude of what you would expect from a regular system.

I’m not aware of the exact cause of this problem, but to me it seems that the statistics externalized in x$kcfio are broken. Anyway, in MOS there are several bugs related to it (9842771 and 9701256). Hence, it’s not a feature (e.g. a change in the unit of measure), it’s a bug. On my Linux test system I’m able to reproduce it on both 11.2.0.1 and 11.2.0.2. According to the bugs mentioned before, the problem is not limited to Linux.

Since with the DBMS_STATS package we are not able to gather correct statistics, the only advice I can give on that topic is that you have to manually set them to sensible values.

Update 2011-03-23

To fix the problem you can install the patch 9842771. It is available for 11.2.0.1 and 11.2.0.2. By the way, since the patch only provides a new version of the dbms_stats_internal package, the statistics externalized in x$kcfio are not broken… they just have another unit of measure.

Update 2011-10-10

The fix for this problem is included in the 11.2.0.3 patch set.

5 Comments

  1. Carol Dacko's Gravatar Carol Dacko
    December 1, 2010    

    Thanks, Chris! We are just starting our testing of 11.2.0.2 and we are investigating System Statistics. But since we did noworkload we did not encounter this bug. BUG 9842771 advice was to not gather workload statistics. I appreciate the blog post!

  2. She's Gravatar She
    January 3, 2011    

    Hi Chris,

    Why some time we get the mreadtime<sreadtim.

    Thanks

  3. January 10, 2011    

    Hi She

    Honestly, I never spent much time investigating such cases. So, my general explanation is that caching coupled with read-ahead/prefeching algorithms might easily lead to such situations. In fact, an intelligent controller/SAN/NAS should be able to spot when large sequencial reads are performed.

    HTH
    Chris

  4. Donatello Settembrino's Gravatar Donatello Settembrino
    October 7, 2011    

    Ciao Christian,
    in your case, where mreadtim < sreadtim, these two values ​​will be recalculated
    with the formulas of noworkload statistics, right?

    which is what could happen (with workload statistics gathered), for example when you change from 10g to 11g recalculating the system statistics, right?
    the cost calculation could be less accurate

    Regards,
    Donatello

  5. October 10, 2011    

    Ciao Donatello

    > these two values ​​will be recalculated with the formulas of noworkload statistics, right?

    Yes. The formulars provided at page 119 of TOP should apply.

    > which is what could happen (with workload statistics gathered), for example when you
    > change from 10g to 11g recalculating the system statistics, right?

    Yes.

    HTH
    Chris

  1. System Stats « Oracle Scratchpad on September 12, 2011 at 18:41

Leave a Reply

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