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.
Well, I have some noworkload stats:
[...] http://structureddata.org/2008/01/02/what-are-your-system-statistics/ [...]
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 ?
Windows 2008 on VMware ESX 3.5 on a two node cluster with 6 DBs. Each node has two dual core 2.66 GHz processors. The storage is an HP EVA with 35 disks shared among about two dozen systems.
hmmm, upgraded to
why are MREADTIM and SREADTIM so high ?
Have they changed something ?
(couldn’t find something about any change, is it a bug ?)
Greg, can you explain the MREATIM and SREADTIM values?
@Sokrates
I’m not aware of any changes.
Sokrates – I’m glad I found your values.
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
This seems to be bug 9842771 – “Wrong SREADTIM and MREADTIM statistics in AUX_STATS$”
and described as
“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.
“
Datawarehouse
15g sga, 55g ram, 12 cpu with 6 cores