High Workload System Statistics – 11gR2

Bug 9842771 exists in relation to gathering WORKLOAD system statistics on 11gR2.

See my SREADTIM and MREADTIM below.
I sure hope they’re not accurate average times in milliseconds :)

SQL> select * from v$version;

BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> select * from sys.aux_stats$;

SNAME           PNAME                PVAL1 PVAL2
--------------- --------------- ---------- ------------------------------
SYSSTATS_INFO   STATUS                     COMPLETED
SYSSTATS_INFO   DSTART                     03-22-2011 17:37
SYSSTATS_INFO   DSTOP                      03-22-2011 17:37
SYSSTATS_INFO   FLAGS                    1
SYSSTATS_MAIN   CPUSPEEDNW             717
SYSSTATS_MAIN   IOSEEKTIM               17
SYSSTATS_MAIN   IOTFRSPEED            8707
SYSSTATS_MAIN   SREADTIM          76000.74
SYSSTATS_MAIN   MREADTIM         90191.077
SYSSTATS_MAIN   CPUSPEED               745
SYSSTATS_MAIN   MBRC                    16
SYSSTATS_MAIN   MAXTHR           188917760
SYSSTATS_MAIN   SLAVETHR            509952

13 rows selected.

Bug description says 11.2.0.1 and 11.2.0.2 affected – no patch listed as currently addressing issue.

Unrealistic high values may be seen for SREADTIM and MREADTIM after
gathering system statistics.

Workaround
Set the correct statistics manually using DBMS_STATS.SET_SYSTEM_STATS.

Listed as fixed in 12.1 – great…

On the plus side, system statistics can be a bit of a dilemma – do you use NOWORKLOAD? do you gather WORKLOAD running a “representative” workload? Do you gather some timings outside of the database and set manually? Or do you set manually according to the specifications? With this bug, one of those options is a non starter at least.

About these ads

11 Responses to High Workload System Statistics – 11gR2

  1. Doug Burns says:

    Bug description says 11.2.0.1 and 11.2.0.2 affected

    Listed as fixed in 12.1 – great…

    Seems to be the story of our lives at the moment.

    • Dom Brooks says:

      What do we think about the level of bugs in 11.2?

      I suppose it’s only a X.X.0.2 and in my head maybe I’m unfairly comparing it to, say, 11.1.0.6.

      • Doug Burns says:

        Our thoughts on 11.2.0.1 are best left unprinted.

        11.2.0.2 seems better but we appear to be good at flushing out bugs in that one too.

        I’m not sure if it’s just a perception thing or whether there have been a lot of recent changes in key areas and lots of (cough) innovation where maybe more care would have been taken in the past?

        Probably just my perception.

      • Dom Brooks says:

        Was it an agile implementation do you think? ;)

  2. I thought this was on AIX only – but it looks like you’re having this on linux too ?

  3. Dom Brooks says:

    Kurt – yes, bang on, Linux.

    Metalink lists bug as generic / all platform.

  4. Centinul says:

    I’ve seen it on my Windows platforms as well.

    I e-mailed Christian Antognini back in November about this. He then promptly made a blog entry about it.

    Workload System Statistics Bug in 11.2

  5. Pingback: Fixed in 12.1 « OraStory

  6. Just thought I’d drop this in, even though its an old thread. It appears Oracle patch 9842771 addresses this issue.

    https://blogs.oracle.com/UPGRADE/entry/wrong_statistics_in_aux_stats

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 72 other followers

%d bloggers like this: