Oracle system statistics

Welcome to the Oracle community forum and interact with 2 million technical staff. 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 will use the system statistics to evaluate the execution plan.

Welcome to the Oracle community forum and interact with 2 million technical staff> go to system statistics to 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 will use the system statistics to evaluate the execution plan.

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

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> desc aux_stats $

Is the name empty? Type

---------------------------------------------------------

Sname not null VARCHAR2 (30)

Pname not null VARCHAR2 (30)

PVAL1 NUMBER

Pval VARCHAR2 (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 2

--------------------------------------------------------------------------------

SYSSTATS_INFO STATUS COMPLETED

SYSSTATS_INFO DSTART 09-17-2011

SYSSTATS_INFO DSTOP

SYSSTATS_INFO FLAGS 1

SYSSTATS_MAIN CPUSPEEDNW 1751.75879

SYSSTATS_MAIN IOSEEKTIM 10

SYSSTATS_MAIN IOTFRSPEED 4096

SYSSTATS_MAIN SREADTIM

SYSSTATS_MAIN MREADTIM

SYSSTATS_MAIN CPUSPEED

SYSSTATS_MAIN MBRC

Sname pname PVAL1 pval 2

--------------------------------------------------------------------------------

SYSSTATS_MAIN MAXTHR

SYSSTATS_MAIN SLAVETHR

13 rows have been selected.

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 NameDescriptionInitializationOptions for Gathering or Setting StatisticsUnit

CpuspeedNWRepresents noworkload CPU speed. CPU speed is the average number of CPU cycles in each second. CPU rate At system startupSet gathering_mode = NOWORKLOAD or set statistics manually. Millions/sec.

IoseektimI/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

IotfrspeedI/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

CpuspeedRepresents workload CPU speed. CPU speed is the average number of CPU cycles in each second. CPU rate NoneSet gathering_mode = NOWORKLOAD, INTERVAL, orSTART | STOP, or set statistics manually. Millions/sec.

[1] [2]