What Are Your System Statistics?

January 2nd, 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.

10gR2, 11gR1, Execution Plans, Optimizer, Oracle, Performance, SQL Tuning, Statistics

Leave a comment

Trackbacks
January 3rd, 2008: Installing Oracle « Oracle Scratchpad

Comments

Todd

January 2nd, 2008 at 15:20 | #1

Reply | Quote

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

jason arneil

January 3rd, 2008 at 01:12 | #2

Reply | Quote

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

Luke

January 3rd, 2008 at 01:35 | #3

Reply | Quote

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

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

Eric

January 3rd, 2008 at 07:11 | #4

Reply | Quote

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

radino

January 3rd, 2008 at 08:12 | #5

Reply | Quote

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

Ajay

January 3rd, 2008 at 10:33 | #6

Reply | Quote

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

January 3rd, 2008 at 12:17 | #7

Reply | Quote

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

Stephan

January 3rd, 2008 at 14:15 | #8

Reply | Quote

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

Cam

January 3rd, 2008 at 16:35 | #9

Reply | Quote

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

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

Bruce.Zheng

January 3rd, 2008 at 18:49 | #10

Reply | Quote

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

Ronnie Doggart

January 4th, 2008 at 01:49 | #11

Reply | Quote

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

Sokrates

January 4th, 2008 at 04:27 | #12

Reply | Quote

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

John Thompson

January 4th, 2008 at 10:17 | #13

Reply | Quote

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

Martin W

January 4th, 2008 at 10:30 | #14

Reply | Quote

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

Muhammad Riaz

January 6th, 2008 at 05:07 | #15

Reply | Quote

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

M. Coak

January 10th, 2008 at 07:06 | #16

Reply | Quote

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

Ed Grimm

January 10th, 2008 at 07:28 | #17

Reply | Quote

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

Neil

January 15th, 2008 at 15:21 | #18

Reply | Quote

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

Ash

January 16th, 2008 at 05:24 | #19

Reply | Quote

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

Colin ‘t Hart

January 16th, 2008 at 07:20 | #20

Reply | Quote

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

Richa

January 24th, 2008 at 10:51 | #21

Reply | Quote

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

Jeroen

January 27th, 2008 at 16:19 | #22

Reply | Quote

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

Michael Seiwert

March 3rd, 2008 at 04:35 | #23

Reply | Quote

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

Jimmy Green

March 20th, 2008 at 06:24 | #24

Reply | Quote

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

ajayy

March 24th, 2008 at 17:40 | #25

Reply | Quote

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

RJarertt

May 18th, 2008 at 19:00 | #26

Reply | Quote

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

Chris Adkin

May 25th, 2008 at 11:25 | #27

Reply | Quote

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
Greg Rahn

May 28th, 2008 at 14:39 | #28

Reply | Quote

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.

Nir Carasso

March 23rd, 2009 at 04:32 | #29

Reply | Quote

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.

Nir Carasso

March 23rd, 2009 at 04:38 | #30

Reply | Quote

@Richa
I Richa’s IOSEEKTIM parameter is very interesting -is it possible to know what type of storage is being used ?

Oracle 11g: Real-Time SQL Monitoring Using DBMS_SQLTUNE.REPORT_SQL_MONITOR Oracle Myth Busting: Show, Don’t Tell

What Are Your System Statistics?

Trackbacks

Comments

Disclaimer

Tweets

Most Visited Posts

Recent Posts

Categories

Recent Comments

Blogroll

Links

Archives

Meta