What Are Your System Statistics?
I’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.
10gR2, 11gR1, Execution Plans, Optimizer, Oracle, Performance, SQL Tuning, Statistics
Trackbacks
Comments
Well, I have some noworkload stats:
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
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
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.
@Richa
I Richa’s IOSEEKTIM parameter is very interesting -is it possible to know what type of storage is being used ?