High Workload System Statistics – 11gR2
March 22, 2011 10 Comments
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.
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.
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.
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.
Was it an agile implementation do you think?
I thought this was on AIX only – but it looks like you’re having this on linux too ?
Kurt – yes, bang on, Linux.
Metalink lists bug as generic / all platform.
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
Thanks
Amazing what I read and then forget
Pingback: Fixed in 12.1 « OraStory