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.