Oracle Performance Analysis 11: system statistics

Oracle Performance Analysis 11: system statistics

The overhead calculation of the early Oracle query optimizer was based on the physical reads required to execute SQL statements. This method is called the I/O overhead mode (I/O cost model ), the main disadvantage of this method is that the overhead of Single-block read and multi-block read is equivalent. In Oracle 8i, the initialization parameters optimizer_index_caching and optimizer_index_cost_adj solve this problem, but the default values of the parameters are only applicable to the reporting system and data warehouse. Until Oracle 9i, ORacle introduced a new CPU overhead mode (CPU cost model) to eliminate this defect, but it must provide the performance information of the system where the database engine is located, that is, system statistics. This article describes system statistics and methods for collecting system statistics.

System statistics

The system statistics are stored in the aux_stats $ table. You can view the data in the view using the following method (you need the DBA user or have the permission to view the table ):

select * from aux_stats$;SNAMEPNAMEPVAL1PVAL2---------------------------------------------------------------------SYSSTATS_INFOSTATUSCOMPLETEDSYSSTATS_INFODSTART08-30-2005 15:04SYSSTATS_INFODSTOP08-30-2005 15:04SYSSTATS_INFOFLAGS1SYSSTATS_MAINCPUSPEEDNW484.974958263773SYSSTATS_MAINIOSEEKTIM10SYSSTATS_MAINIOTFRSPEED4096SYSSTATS_MAINSREADTIMSYSSTATS_MAINMREADTIMSYSSTATS_MAINCPUSPEEDSYSSTATS_MAINMBRCSYSSTATS_MAINMAXTHRSYSSTATS_MAINSLAVETHR

The data in the table consists of three parts, which are distinguished by SNAME:

1) SYSSTATS_INFO indicates the system statistics Status and time
When STATUS is correctly collected, it is COMPLETED. If a problem occurs, it is displayed as BADSTATS. In this case, the query optimizer does not use statistics. This field can also be MANUALGATHERING and AUTOGATHERING.

2) SYSSTATS_MAIN indicates the system statistics result set.
This part of the information is divided into non-workload statistics (noworkload statistics) and workload statistics (workload statistics), the main difference between the two is that the method to measure the performance of the I/O subsystem is different, we will discuss it later.
Non-workload statistics are always provided after Oracle 10 Gb, including:

The number of operations that a CPU can process in one second in CPUSPEEDNW. The IOSEEKTIM average disk seek time per million times/second, in milliseconds. The default value is 10 iotfrspeed, which indicates the average number of bytes transmitted from the disk per millisecond. The default value is 4096.

The workload statistics must be displayed and collected before they can be used, including:

Average reading time of a single piece of data in SREADTIM, average reading time of multiple pieces of data in milliseconds in MREADTIM, and operands that can be processed by one CPU in milliseconds in CPUSPEED in one second, the average number of data blocks read by MBRC in one multi-block read operation per second MAXTHR maximum I/O throughput of the entire system (Bytes/second) average I/O throughput of slave threads in SLAVETHR Parallel Processing (Bytes/s)

3) SYSSTATS_TEMP is used to calculate system statistics. It is available only when workload statistics are collected.

Collect System statistics

Use the Stored Procedure gather_system_stats in the Toolkit dbms_stats to collect system statistics, which can collect non-workload statistics or workload statistics.

Non-workload statistics

Starting from Oracle 10 Gb, the default non-workload statistics are always provided. If it is explicitly deleted, it will be automatically collected the next time the database is started. The following statement is used to collect non-workload statistics on idle systems:

begin  dbms_stats.gather_system_stats(gathering_mode => 'noworkload');end;

Collecting non-workload statistics takes a short time, but sometimes it takes several times to collect the statistics.

Workload statistics

Collect workload statistics and do not use idle systems, so that the database engine should use normal database load to evaluate the performance of the I/O subsystem.
To collect workload statistics, follow these steps:

1) execute the snapshot and store the initial values of various performance indicators to the data dictionary table aux_stats $ (the column SNAME is set to SYSSTATS_TEMP ). Use the following method to start:

begin  dbms_stats.gather_system_stats(gathering_mode => 'start');end;

2) Wait for enough time to generate another snapshot when generating a representative load. The wait time is usually not less than 30 minutes;

3) take the second snapshot and use the following statement:

begin  dbms_stats.gather_system_stats(gathering_mode => 'stop');end;

4) Oracle calculates the system statistics based on the difference between two snapshots. If the calculation fails, it is set to null.

You can also specify gathering_mode as interval and specify the interval to collect workload statistics. In this way, you do not need to manually stop the snapshot. The following command specifies the second snapshot after 30 minutes:

begin  dbms_stats.gather_system_stats(gathering_mode => 'interval',                                 interval       => 30);end;

Note that the preceding statement does not last for 30 minutes, but is returned after a job is scheduled.
When using workload statistics, you should try to avoid collecting non-representative workload information. You can collect workload statistics for multiple days, and then average the mbrc, mreadtim, sreadtim, and cpuspeed metrics, maxthr and slavethr take the maximum value, then use the delete_system_stats process to delete the old system statistics, and then use set_system_stats to set the new system statistics. The stored procedure is as follows:

begin  dbms_stats.delete_system_stats();  dbms_stats.set_system_stats(pname => 'CPUSPEED', pvalue => 772);  dbms_stats.set_system_stats(pname => 'SREADTIM', pvalue => 5.5);  dbms_stats.set_system_stats(pname => 'MREADTIM', pvalue => 19.4);  dbms_stats.set_system_stats(pname => 'MBRC', pvalue => 53);  dbms_stats.set_system_stats(pname => 'MAXTHR', pvalue => 1136136192);  dbms_stats.set_system_stats(pname => 'SLAVETHR', pvalue => 16870400);end;