What Are Your System Statistics?

January 2, 2008

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.

29 Responses to “What Are Your System Statistics?”

sql>select version from v$instance;

VERSION
-----------------
10.2.0.3.0

sql>select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';

PNAME                                 PVAL1
------------------------------ ------------
CPUSPEED                                967
CPUSPEEDNW                          948.992
IOSEEKTIM                                10
IOTFRSPEED                             4096
MAXTHR                              9858048
MBRC                                     13
MREADTIM                             10.532
SLAVETHR
SREADTIM                               3.29

By Todd on Jan 2, 2008

Well, I have some noworkload stats:

select version from v$instance;

VERSION
-----------------
10.2.0.3.0

select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';

PNAME				    PVAL1
------------------------------ ----------
CPUSPEED
CPUSPEEDNW		       1570.62401
IOSEEKTIM			       10
IOTFRSPEED			     4096
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM

By jason arneil on Jan 3, 2008

VERSION
-----------------
10.2.0.3.0

PNAME                               PVAL1
------------------------------ ----------
CPUSPEED                              578
MAXTHR                           64568320
MBRC                                   16
MREADTIM                            1.309
SLAVETHR                             5120
SREADTIM                            1.833

By Luke on Jan 3, 2008

VERSION
-----------------
9.2.0.8.0

SQL> select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEED                              616
MAXTHR                             843776
MBRC                                    4
MREADTIM                             .687
SLAVETHR                               -1
SREADTIM                             .562

By Eric on Jan 3, 2008

SQL> select version from v$instance;

VERSION
-----------------
10.2.0.3.0

SQL> select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEED
CPUSPEEDNW                     584,948401
IOSEEKTIM                              10
IOTFRSPEED                           4096
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM                       

SQL> select version from v$instance;

VERSION
-----------------
10.2.0.3.0

SQL> select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEEDNW                     607,634652
IOSEEKTIM                              10
IOTFRSPEED                           4096
SREADTIM
MREADTIM
CPUSPEED
MBRC
MAXTHR
SLAVETHR

By radino on Jan 3, 2008

VERSION
-----------------
10.2.0.3.0

PNAME				    PVAL1
------------------------------ ----------
CPUSPEED			      968
CPUSPEEDNW			    898.4
IOSEEKTIM			   21.735
IOTFRSPEED			 3669.391
MAXTHR				321672192
MBRC				       82
MREADTIM			   11.152
SLAVETHR			   313344
SREADTIM			    3.593

By Ajay on Jan 3, 2008

SQL> select version from v$instance;

VERSION
-----------------
9.2.0.8.0

SQL> select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEED                              424
MAXTHR                           51446784
MBRC                                    9
MREADTIM                             .272
SLAVETHR                               -1
SREADTIM                             .184

By JP on Jan 3, 2008

SQL> select version from v$instance;

VERSION
-----------------
10.2.0.2.0

SQL> select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEED
CPUSPEEDNW                     1204.56116
IOSEEKTIM                              10
IOTFRSPEED                           4096
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM

By Stephan on Jan 3, 2008

VERSION
-----------------
10.2.0.3.0

PNAME                               PVAL1
------------------------------ ----------
CPUSPEEDNW	               2395.05324
IOSEEKTIM	                       10
IOTFRSPEED	                     4096
SREADTIM
MREADTIM
CPUSPEED
MBRC
MAXTHR
SLAVETHR

By Cam on Jan 3, 2008

SQL> select version from v$instance;

VERSION
-----------------
10.2.0.1.0

SQL>  select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEED
CPUSPEEDNW                     713.978495
IOSEEKTIM                              10
IOTFRSPEED                           4096
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM

By Bruce.Zheng on Jan 3, 2008

SQL> select version from v$instance;

VERSION
-----------------
10.2.0.2.0

SQL> select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
SREADTIM                            4.112
MREADTIM                             4.69
CPUSPEED                             1048
MBRC                                    8
MAXTHR                              68608
SLAVETHR                               -1

By Ronnie Doggart on Jan 4, 2008

select version from v$instance;

VERSION
-----------------
10.2.0.2.0

select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEEDNW                        1464,37
IOSEEKTIM                           7,744
IOTFRSPEED                      35262,904
SREADTIM                            9,599
MREADTIM                            5,698
CPUSPEED                             1445
MBRC                                   12
MAXTHR                           49504256
SLAVETHR

By Sokrates on Jan 4, 2008

VERSION
-----------------
10.2.0.2.0

PNAME                               PVAL1
------------------------------ ----------
CPUSPEEDNW
IOSEEKTIM
IOTFRSPEED
SREADTIM                             7.73
MREADTIM                           27.517
CPUSPEED                              751
MBRC                                   18
MAXTHR                           18977792
SLAVETHR                           538624

By John Thompson on Jan 4, 2008

SQL> select version from v$instance;

VERSION
-----------------
10.2.0.3.0

SQL> select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEED                             1080
CPUSPEEDNW                     1082.10461
IOSEEKTIM                              10
IOTFRSPEED                           4096
MAXTHR                           28279808
MBRC
MREADTIM                             .714
SLAVETHR
SREADTIM                            2.222

By Martin W on Jan 4, 2008

SQL>select version from v$instance;

VERSION
-----------------
10.2.0.3.0

SQL> select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEED                              905
CPUSPEEDNW                        970.871
IOSEEKTIM                              10
IOTFRSPEED                           4096
MAXTHR                          318570496
MBRC                                    7
MREADTIM                            1.167
SLAVETHR
SREADTIM                             .429

By Muhammad Riaz on Jan 6, 2008

SQL> select version from v$instance;

VERSION
-----------------
10.2.0.3.0

SQL> select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEEDNW                     846.321923
IOSEEKTIM                              10
IOTFRSPEED                           4096
SREADTIM
MREADTIM
CPUSPEED
MBRC
MAXTHR
SLAVETHR

By M. Coak on Jan 10, 2008

SYS AS SYSDBA=>  select version from v$instance;

VERSION
-----------------
10.2.0.3.0                                                                                                                          

SYS AS SYSDBA=>  select pname,pval1 from sys.aux_stats$ where sname ='SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEED                              789
CPUSPEEDNW                        773.799
IOSEEKTIM                           5.953
IOTFRSPEED                      42943.648
MAXTHR                           11440128
MBRC                                    7
MREADTIM                            1.867
SLAVETHR
SREADTIM                            3.931

By Ed Grimm on Jan 10, 2008

SQL> select version from v$instance;

VERSION
-----------------
10.2.0.3.0

SQL> select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEEDNW                     1928.63071
IOSEEKTIM                              10
IOTFRSPEED                           4096
SREADTIM
MREADTIM
CPUSPEED
MBRC
MAXTHR
SLAVETHR

By Neil on Jan 15, 2008

VERSION
-----------------
10.2.0.1.0       

PNAME                               PVAL1
------------------------------ ----------
CPUSPEEDNW                     1398.59579
IOSEEKTIM                              10
IOTFRSPEED                           4096
SREADTIM                            3.053
MREADTIM                              1.1
CPUSPEED                             1412
MBRC                                    7
MAXTHR                             684032
SLAVETHR

By Ash on Jan 16, 2008

SQL> SELECT version FROM v$instance;

VERSION
-----------------
10.2.0.3.0

SQL> select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEEDNW                     1201,44753
IOSEEKTIM                              10
IOTFRSPEED                           4096
SREADTIM                            2,411
MREADTIM                             ,419
CPUSPEED                             1284
MBRC                                   10
MAXTHR                            7193600
SLAVETHR

By Colin 't Hart on Jan 16, 2008

SQL> SELECT version FROM v$instance;

VERSION
-----------------
10.2.0.3.0

SQL> select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEEDNW                       1589.267
IOSEEKTIM                           4.334
IOTFRSPEED                      38805.566
SREADTIM                            3.171
MREADTIM                            4.295
CPUSPEED                             1584
MBRC                                    9
MAXTHR                             130048
SLAVETHR

By Richa on Jan 24, 2008

VERSION
-----------------
11.1.0.6.0

SQL> select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEEDNW                     324.838093
IOSEEKTIM                              10
IOTFRSPEED                           4096
SREADTIM
MREADTIM
CPUSPEED
MBRC
MAXTHR
SLAVETHR

By Jeroen on Jan 27, 2008

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                     1764,61655
IOSEEKTIM                              10
IOTFRSPEED                           4096
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM

By Michael Seiwert on Mar 3, 2008

SQL> select version from v$instance;

VERSION
----------------------------------------
10.2.0.3.0

SQL> select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEED                             2063
CPUSPEEDNW                     2091.18176
IOSEEKTIM                              10
IOTFRSPEED                           4096
MAXTHR                              13312
MBRC
MREADTIM
SLAVETHR
SREADTIM                                1

By Jimmy Green on Mar 20, 2008

SQL> select version from v$instance;

VERSION
-----------------
10.2.0.3.0

SQL> select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEED
CPUSPEEDNW                     607.475821
IOSEEKTIM                              10
IOTFRSPEED                           4096
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM

By ajayy on Mar 24, 2008

SQL> select version from v$instance;

VERSION
-----------------
10.2.0.1.0

SQL>  select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEED                             1071
CPUSPEEDNW                     713.905386
IOSEEKTIM                              10
IOTFRSPEED                           4096
MAXTHR                           61631488
MBRC                                  105
MREADTIM                            34.52
SLAVETHR                             7168
SREADTIM                           18.006

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

1 Trackback(s)
Jan 3, 2008: Installing Oracle « Oracle Scratchpad

Structured Data: Oracle Database Performance And Scalability Blog

What Are Your System Statistics?

29 Responses to “What Are Your System Statistics?”

1 Trackback(s)

Post a Comment

Disclaimer

Recent Posts

Categories

Recent Comments

Blogroll

Archives

Meta