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 126.96.36.199 and 188.8.131.52. 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.
To fix the problem you can install the patch 9842771. It is available for 184.108.40.206 and 220.127.116.11. 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.
The fix for this problem is included in the 18.104.22.168 patch set.