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.

40 comments

  1. Todd
    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
    
  2. jason arneil

    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
    
    
    
    
    			
  3. Luke
    VERSION
    -----------------
    10.2.0.3.0
    
    PNAME                               PVAL1
    ------------------------------ ----------
    CPUSPEED                              578
    MAXTHR                           64568320
    MBRC                                   16
    MREADTIM                            1.309
    SLAVETHR                             5120
    SREADTIM                            1.833
    
  4. Pingback: Installing Oracle « Oracle Scratchpad
  5. Eric
    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
    
  6. radino
    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
    
    
  7. Ajay
    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
    
  8. JP
    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
    
  9. Stephan
    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
    
  10. Cam
    VERSION
    -----------------
    10.2.0.3.0
    
    PNAME                               PVAL1
    ------------------------------ ----------
    CPUSPEEDNW	               2395.05324
    IOSEEKTIM	                       10
    IOTFRSPEED	                     4096
    SREADTIM
    MREADTIM
    CPUSPEED
    MBRC
    MAXTHR
    SLAVETHR
    
    
  11. Bruce.Zheng
    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
    
  12. Ronnie Doggart
    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
    
  13. Sokrates
    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
    
  14. John Thompson
    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
    
  15. Martin W
    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
    
  16. Muhammad Riaz
    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
    
  17. M. Coak
    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
    
  18. Ed Grimm
    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
    
  19. Neil
    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
    
    
  20. Ash
    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
    
  21. Colin 't Hart
    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
    
  22. Richa
    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
    
  23. Jeroen
    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
    
  24. Michael Seiwert
    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
    
  25. Jimmy Green
    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
    
  26. ajayy
    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
    
  27. RJarertt
    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

  28. Chris Adkin

    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

  29. Greg Rahn

    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.

  30. Nir Carasso
    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                             1985
    CPUSPEEDNW                       1588.616
    IOSEEKTIM                           4.876
    IOTFRSPEED                      67665.085
    MAXTHR                          152200192
    MBRC                                   33
    MREADTIM                            1.747
    SLAVETHR                         32113664
    SREADTIM                            2.021
    
    9 rows selected.
    
  31. Leigh Riffel
    SQL > SELECT version FROM v$instance;
    VERSION
    -----------------
    11.1.0.7.0
    
    SQL > select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';
    PNAME                               PVAL1
    ------------------------------ ----------
    CPUSPEEDNW                       2138.081
    IOSEEKTIM                           5.776
    IOTFRSPEED                      36145.224
    SREADTIM                            2.724
    MREADTIM                            3.547
    CPUSPEED                             2081
    MBRC                                   31
    MAXTHR                          176668672
    SLAVETHR                           764928
    9 rows selected.
    

    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.

  32. Sokrates

    hmmm, upgraded to

    select version from v$instance;
    
    VERSION
    ---------------------------------------------------
    11.2.0.2.0
    
    collected NOWORKLOAD and 24-hour-WORKLOAD - system stats and got
    
    select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';
                    
    PNAME		      PVAL1
    ---------------- ----------
    CPUSPEEDNW	       2493
    IOSEEKTIM	          5
    IOTFRSPEED	      43707
    SREADTIM	  60207,956
    MREADTIM	  232679,32
    CPUSPEED	       2498
    MBRC		         16
    MAXTHR		  329274368
    SLAVETHR	    2839552
    

    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?

  33. Dominic Brooks

    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

    SQL> select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';
    
    PNAME                               PVAL1
    ------------------------------ ----------
    CPUSPEEDNW                            717
    IOSEEKTIM                              17
    IOTFRSPEED                           8707
    SREADTIM                         76000.74
    MREADTIM                        90191.077
    CPUSPEED                              745
    MBRC                                   16
    MAXTHR                          188917760
    SLAVETHR                           509952
    
    9 rows selected.
    
    SQL>
    
  34. Dominic Brooks

    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.

  35. Anonymous

    Datawarehouse

    15g sga, 55g ram, 12 cpu with 6 cores

    VERSION
    -----------------
    11.2.0.3.0
    
    SNAME                          PNAME                               PVAL1 
    ------------------------------ ------------------------------ ---------- 
    SYSSTATS_MAIN                  CPUSPEEDNW                            508
    SYSSTATS_MAIN                  IOSEEKTIM                               5
    SYSSTATS_MAIN                  IOTFRSPEED                          48803
    SYSSTATS_MAIN                  SREADTIM                            1.003
    SYSSTATS_MAIN                  MREADTIM                            2.638
    SYSSTATS_MAIN                  CPUSPEED                              342
    SYSSTATS_MAIN                  MBRC                                    5
    SYSSTATS_MAIN                  MAXTHR                          632393728
    SYSSTATS_MAIN                  SLAVETHR                           621568
    
  36. Anonymous
    SQL> select version from v$instance;
    
    VERSION
    -----------------
    11.2.0.2.0
    
    SQL> select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';
    
    PNAME                               PVAL1
    ------------------------------ ----------
    CPUSPEEDNW                     2946.74556
    IOSEEKTIM                              10
    IOTFRSPEED                           4096
    SREADTIM
    MREADTIM
    CPUSPEED
    MBRC
    MAXTHR
    SLAVETHR
    
    9 rows selected.
    
  37. Aaron

    Using a ramdisk at work to perform a bunch of batch upgrades.

    SQL> select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';
    
    PNAME                               PVAL1
    ------------------------------ ----------
    CPUSPEEDNW                           2487
    IOSEEKTIM                              10
    IOTFRSPEED                           4096
    SREADTIM                             .004
    MREADTIM                             .052
    CPUSPEED                             2649
    MBRC                                   37
    MAXTHR                         1147832320
    SLAVETHR                         34621440
    
    9 rows selected.
    

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s