What Are Your System Statistics?
January 2, 2008I’ve been working on a few test cases and I’m in search of some real-world data. If your production Oracle database uses system statistics, either Workload Statistics or Noworkload Statistics, and you are willing to share them, please post a comment with the output from the following two queries:
select version from v$instance; select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';
For example, my noworkload system statistics look like this:
SQL> select version from v$instance; VERSION ----------------- 11.1.0.6.0 SQL> select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN'; PNAME PVAL1 ------------------------------ ---------- CPUSPEED CPUSPEEDNW 726.951 IOSEEKTIM 4.683 IOTFRSPEED 36625.24 MAXTHR MBRC MREADTIM SLAVETHR SREADTIM
To help with fixed width formatting (pretty printing), please surround your results in the comment text box with a pre tag like such:
<pre>
blah blah blah
</pre>
Thanks for participating!
Quick link to 10.2 System Statistics Documentation for those unfamiliar with it.
29 Responses to “What Are Your System Statistics?”
By Todd on Jan 2, 2008
Well, I have some noworkload stats:
By jason arneil on Jan 3, 2008
By Luke on Jan 3, 2008
By Eric on Jan 3, 2008
By radino on Jan 3, 2008
By Ajay on Jan 3, 2008
By JP on Jan 3, 2008
By Stephan on Jan 3, 2008
By Cam on Jan 3, 2008
By Bruce.Zheng on Jan 3, 2008
By Ronnie Doggart on Jan 4, 2008
By Sokrates on Jan 4, 2008
By John Thompson on Jan 4, 2008
By Martin W on Jan 4, 2008
By Muhammad Riaz on Jan 6, 2008
By M. Coak on Jan 10, 2008
By Ed Grimm on Jan 10, 2008
By Neil on Jan 15, 2008
By Ash on Jan 16, 2008
By Colin 't Hart on Jan 16, 2008
By Richa on Jan 24, 2008
By Jeroen on Jan 27, 2008
By Michael Seiwert on Mar 3, 2008
By Jimmy Green on Mar 20, 2008
By ajayy on Mar 24, 2008
Sun v40x w/ 4x Single-core opterons. 32gb ram connected via 4 2gb PCIx FC HBAs to a small HDS SAN.
Probably will be upgrading to a 4x quad-core opteron w/ 64gb ram. Either a Sun x4400 or HP dl585
By RJarertt on May 18, 2008
Hi Greg,
I’ve been tuning some queries and it appears that I’m getting a lot more full table scans than I would expect. I’ve noticed from sys_auxstats$ that my MBRC is 21, considering the block size of my database is 16K, this equates to a whopping multiblock read of 336K, when I thought the max i/o size of most Unix platforms is 64K (give or take a bit). I was careful to gather sys stats first thing in the morning after the batch jobs that run where I work have completed (against databases I’m not using for my work), so as to make sure that I was getting stats for the disk rather than the SAN cache. However, I can’t help feel that some sort of read ahead optimization has kicked in, giving an artificialy high MRBC value. The MRBCs of 82 and 105 that some of your respondants have given are quite interesting. What is your view of this and what sort of bounds a reasonable MRBC should be within ?.
Chris
By Chris Adkin on May 25, 2008
Chris-
The largest I/O that Oracle can currently issue is 1MB. All modern operating systems support this (as far as I know) and some support even larger, though Oracle does not currently.
The size of MBRC will depend mostly on the type of workload. If it is mostly index access, then it is likely to be on the smaller side (16 * 8k). If it is a data warehouse, then it very well may be on the upper bound, 1MB (128 * 8k). On a hybrid system, probably somewhere in between.
I would be less concerned with the just MBRC size, and more concerned with the single and multi block read times (SREADTIM & MREADTIM), in relationship to a single block and the MBRC size. If these times are too close, and MBRC is on the larger side, it could possible cause the cost of a FTS to be artificially cheap.
By Greg Rahn on May 28, 2008