Database Reference
In-Depth Information
CPUSPEED 1992.0
MBRC 21.0
MAXTHR 659158016.0
SLAVETHR 34201600.0
•
SYSSTATS_TEMP
is the set containing values used for the computation of system statistics.
It's available only while gathering workload statistics.
Gathering System Statistics
As just mentioned, the database engine supports two types of system statistics: noworkload statistics and workload
statistics. This section describes not only how they're gathered, but also what information they provide to the query
optimizer and how to decide whether noworkload statistics or workload statistics should be used.
Because a single set of statistics exists for a single database, all instances of a RAC system use the same system
statistics. Therefore, if the nodes aren't equally sized or loaded, you must carefully decide which node the system
statistics are to be gathered on.
Noworkload Statistics
Noworkload statistics are always available. If you explicitly delete them, they're automatically gathered during the
next database start-up. You can gather noworkload statistics on an idle system because the database engine uses a
synthetic benchmark to generate the load used to measure the performance of the system. To measure the CPU speed,
most likely some kind of calibrating operation is executed in a loop. To measure the disk I/O performance, some reads
of different sizes are performed on several data files of the database.
To gather noworkload statistics, you set the
gathering_mode
parameter of the
gather_system_stats
procedure
to
noworkload
, as shown in this example:
dbms_stats.gather_system_stats(gathering_mode => 'noworkload')
In addition, to better support systems having high disk I/O throughput (like Exadata), from version 11.2.0.4
onward (or when a patch implementing the enhancement associated to bug 10248538 is installed) there is another
method for gathering noworkload statistics: by setting the
gathering_mode
parameter to
exadata
, as shown here:
dbms_stats.gather_system_stats(gathering_mode => 'exadata')
In both cases, the gathering usually takes a few minutes, and the statistics listed in Table
7-1
are computed.
Oddly, sometimes it's necessary to repeat the gathering of statistics more than once; otherwise, the default values,
which are also available in Table
7-1
, are used. That is because the measured statistics must pass a sanity check
before being stored. If they don't pass, they're discarded and replaced with the default statistics. Unfortunately, no
information is provided to you when that happens.
