Database Reference
In-Depth Information
SQL> SELECT round((236430-36760)/(54956-22893),1) AS avg_tim_singleblkrd
2 FROM dual;
AVG_TIM_SINGLEBLKRD
-------------------
6.2
Figure 7-2.
To gather (compute) system statistics, two snapshots of several performance figures are used
The three steps illustrated in Figure
7-2
are as follows:
A snapshot of several performance figures is taken and stored in the
aux_stats$
data
dictionary table (for these rows, the
sname
column is set to
SYSSTATS_TEMP
). This step
is carried out by setting the
gathering_mode
parameter of the
gather_system_stats
procedure to
start
, as shown in the following command:
1.
dbms_stats.gather_system_stats(gathering_mode => 'start')
2.
The database engine doesn't control the database load. Consequently, enough time to cover
a representative load has to elapse before taking another snapshot. It's difficult to provide
general advice about this waiting time, but it's common to wait at least 5-10 minutes.
A second snapshot is taken. This step is carried out by setting the
gathering_mode
parameter
of the
gather_system_stats
procedure to
stop
, as shown in the following command:
3.
dbms_stats.gather_system_stats(gathering_mode => 'stop')
4.
The system statistics listed in Table
7-2
are computed, based on the performance statistics
of the two snapshots. If one of the disk I/O statistics can't be computed, that statistic is set
to
NULL
. Inability to compute a statistic can occur if the workload did not use either
single-block reads, multiblock reads or parallel processing. For example, if the workload
did not perform any multiblock reads,
mbrc
and
mreadtim
are set to
NULL
.
Table 7-2.
Workload Statistics Stored in the Data Dictionary
Name
Description
CPUSPEED
The number of operations per second (in millions) that one CPU is able to process
SREADTIM
Average time (in milliseconds) needed to perform a single-block read operation
MREADTIM
Average time (in milliseconds) needed to perform a multiblock read operation
MBRC
Average number of blocks read during multiblock read operations
MAXTHR
Maximum disk I/O throughput (in bytes per second) for the whole system