System Statistics - Troubleshooting Oracle Performance - page 190

Table 7-1. Noworkload Statistics Stored in the Data Dictionary

Name Description

CPUSPEEDNW The number of operations per second (in millions) that one CPU is able to process. There's no

default value because CPUSPEEDNW is always based on the result of the synthetic benchmark used
for assessing the speed of the CPU.

IOSEEKTIM Average time (in milliseconds) needed to locate data on disk. The default value is 10.

IOTFRSPEED

Average number of bytes per millisecond that can be transferred from disk. The default value is 4,096.

MBRC Number of blocks read during multiblock read operations. This statistic is set in exadata mode only

(in other words, when gathering_mode is set to exadata ). There is no default value because MBRC is
always set to the value of the db_file_multiblock_read_count initialization parameter.

The only difference between the regular and the exadata noworkload statistics is that in the latter the mbrc
statistic is also set. Specifically, it's set to the value of the db_file_multiblock_read_count initialization parameter.
The aim is to inform the query optimizer that the database engine can efficiently perform large disk I/O operations
and, therefore, can reduce the cost of full scans. Using the exadata gathering mode is essential only when the
db_file_multiblock_read_count initialization parameter isn't explicitly set. When it isn't set, the query optimizer
uses a value of 8 to cost full scans (this behaviour is described in Chapter 9). With such a value, the cost of full scans
is usually much higher than it should be. When using the exadata mode, the situation is completely different. In fact,
mbrc is set to 128 on most systems—therefore the cost of full scans is much lower. It's especially advisable to use the
exadata mode on systems with high disk I/O throughput (like Exadata).

Workload Statistics
Workload statistics are available only when explicitly gathered. To gather them, you can't use an idle system because
the database engine has to take advantage of the regular database load to measure the performance of the disk I/O
subsystem. On the other hand, the method for noworkload statistics is used to measure the speed of the CPU. As shown
in Figure 7-2 , gathering workload statistics is a three-step activity. The idea is that to compute the average time taken
by an operation, it's necessary to know how many times that operation was performed and how much time was spent
executing it. For example, with the following SQL statements, I was able to compute the average time for single-block
reads (6.2 milliseconds) from one of my test databases, in the same way the dbms_stats package would:

SQL> SELECT sum(singleblkrds) AS count, sum(singleblkrdtim)*10 AS time_ms
2 FROM v$filestat;

COUNT TIME_MS
---------- ----------
22893 36760

SQL> REMARK run a benchmark to generate some disk I/O operations...

SQL> SELECT sum(singleblkrds) AS count, sum(singleblkrdtim)*10 AS time_ms
2 FROM v$filestat;

COUNT TIME_MS
---------- ----------
54956 236430