Oracle system statistics

System statistics mainly describe some features related to system hardware, such as the performance and utilization of cpu and io systems. This information is very important for the query optimizer. During the execution plan selection process, the oracle optimizer uses the system statistics to evaluate the cost of the execution plan. Therefore, accurate system statistics can help the optimizer make the right choice.

We can collect system STATISTICS through the DBMS_STATS.GATHER_SYSTEM_STATS process. There are two ways to collect: workload statistics is on a database system with real system load, collect System information for a certain period of time. Non-load STATISTICS (noworkload statistics) the oracle database simulates system load based on a certain method (such as continuous read disk) to obtain system STATISTICS.

We can see from the materials from various parties and the official oracle documents that we 'd better collect system statistics by means of system load. The collection interval should be at least 30 minutes. If conditions permit, it is best to collect data multiple times and use it as the best choice. DBMS_STATS.GATHER_SYSTEM_STATS is used to collect system statistics. You can also set some information options manually, which will be shown in the following example.

Oracle's system statistics are finally stored in the aux_stats $ data base table. First, let's take a look at the structure of the table:

SQL> is the name of desc aux_stats $ empty? Type ----------------------------- -------- ------------------ sname not null VARCHAR2 (30) pname not null VARCHAR2 (30) PVAL1NUMBER PVAL2VARCHAR2 (255)

The amount of data stored in the aux_stats $ table is very limited, as shown in figure

SQL> select * from aux_stats $; SNAME PNAME PVAL1 pval -------------------- restart ---------- complete SYSSTATS_INFO STATUS COMPLETEDSYSSTATS_INFO DSTART 09-17-2011 10: 21SYSSTATS_INFO DSTOP 09-17-2011 10: 21SYSSTATS_INFO FLAGS 1SYSSTATS_MAIN CPUSPEEDNW 1751.75879SYSSTATS _ main ioseektim 10SYSSTATS_MAIN IOTFRSPEED 4096SYSSTATS_MAIN SREADTIMSYSSTATS_MAIN MREADTIMSYSSTAT S_MAIN CPUSPEEDSYSSTATS_MAIN mbrcsname pname PVAL1 pval -------------------------------------- ---------- rows whose SYSSTATS_MAIN MAXTHRSYSSTATS_MAIN SLAVETHR has been selected 13.

The sname fields can be SYSSTATS_INFO, SYSSTATS_MAIN, or SYSSTATS_TEMP.

SYSSTATS_INFO: system statistics Status

SYSSTATS_MAIN: system statistics

SYSSTATS_TEMP: used to temporarily store intermediate data during system statistics collection

For the meaning of the pname field, the oracle official documentation provides a detailed explanation:

Parameter Name	Description	Initialization	Options for Gathering or Setting Statistics	Unit
CpuspeedNW	Represents noworkload CPU speed. CPU speed is the average number of CPU cycles in each second. CPU Rate	At system startup	Set gathering_mode = NOWORKLOAD or set statistics manually.	Millions/sec.
Ioseektim	I/O seek time equals seek time + latency time + operating system overhead time. seek time	At system startup 10 (default)	Set gathering_mode = NOWORKLOAD or set statistics manually.	MS
Iotfrspeed	I/O transfer speed is the rate at which an Oracle database can read data in the single read request. data Transmission rate	At system startup 4096 (default)	Set gathering_mode = NOWORKLOAD or set statistics manually.	Bytes/MS
Cpuspeed	Represents workload CPU speed. CPU speed is the average number of CPU cycles in each second. CPU Rate	None	Set gathering_mode = NOWORKLOAD, INTERVAL, orSTART | STOP, or set statistics manually.	Millions/sec.
Maxthr	Maximum I/O throughput is the maximum throughput that the I/O subsystem can deliver. Maximum input/output throughput	None	Set gathering_mode = NOWORKLOAD, INTERVAL, orSTART | STOP, or set statistics manually.	Bytes/sec.
Slavethr	Slave I/O throughput is the average parallel slave I/O throughput. average throughput of subordinate Processes	None	Set gathering_mode = INTERVAL or START | STOP, or set statistics manually.	Bytes/sec.
Sreadtim	Single block read time is the average time to read a single block randomly. Single block read time	None	Set gathering_mode = INTERVAL or START | STOP, or set statistics manually.	MS
Mreadtim	Multiblock read is the average time to read a multiblock sequentially. Multiblock read time	None	Set gathering_mode = INTERVAL or START | STOP, or set statistics manually.	MS
Mbrc	Multiblock count is the average multiblock read count sequentially. Number of database blocks read at a time	None	Set gathering_mode = INTERVAL or START | STOP, or set statistics manually.	Blocks

Note: In the new version of oracle (10 Gb), non-workload statistics are always available. If no non-workload statistics exist, they are automatically collected during instance initialization.

After the system statistics are updated, oracle does not set resolved SQL statements to invalid, but the new SQL statements use information system statistics.

Let's take a look at the non-workload statistics.

Non-workload statistics include io trasfer speed, io seek time, and cpu speed, that is, the blue mark area in the table above.

Non-workload statistics are collected during idle time. It reads all data files randomly to test the system IO performance and test the CPU speed. By default, oracle uses non-workload statistics. However, if we collect workload statistics, oracle will give priority to workload statistics.

Oracle automatically collects non-workload statistics at the first startup. If you need to manually collect non-workload statistics, you can use the DBMS_sTATS.GATHER_SYSTEM_STATS process without parameters.Collecting non-workload statistics will generate loads on the system. The duration of running depends on the performance of the database server. Due to the internal oracle detection mechanism, non-workload statistics may not overwrite the default value. In this case, we can try multiple times or manually set the statistics.

Let's take a look at the workload statistics.

Workload statistics include single-block read time, multi-block read Time, And mbrc, which are marked in red in the preceding table.

Oracle does not impose additional system load on the database system when collecting workload statistics, because oracle obtains the workload system statistics by querying the statistical information in the statistical period, such as v $ sysstat v $ sesstat.

Oracle computing workload statistics are achieved through some counters, which are changed only when oracle buffer cach completes some operations. Therefore, these counters not only reflect the input and output delay of the disk, it also reflects the waiting events related to latch and task conversion. From this we can infer that if the bottleneck of a system is in the io system, oracle will give priority to an execution plan with a small io dependency when generating an execution plan based on workload statistics.

We can collect workload system statistics in the following two ways:

Run the DBMS_STATS.GATHER_SYSTEM_STATS ('start') procedure at the beginning of the workload window, then the DBMS_STATS.GATHER_SYSTEM_STATS ('stop') procedure at the end of the workload window.

Run DBMS_STATS.GATHER_SYSTEM_STATS ('interval ', interval => N) where N is the number of minutes when statistics gathering is stopped automatically.

When we collect workload statistics, oracle will collect MBRC values, but if the oracle cannot collect mbrc and MREADTIM values (for example, in this case, oracle uses the initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT to evaluate the cost of full table scan. If this initialization parameter is not specified, oracle uses 8 instead. I will introduce DB_FILE_MULTIBLOCK_READ_COUNT specifically.