Oracle Performance Analysis 11: System statistics

The cost calculation for the early Oracle query optimizer is based on the physical reading required to execute the SQL statement, which is called the I/O overhead mode (I/O cost model), and the main disadvantage of this method is that the Stompbox read and the multiple block read overhead are equivalent. In Oracle 8i, initialization parameters optimizer_index_caching and Optimizer_index_cost_adj solve this problem, but the default values for parameters apply only to reporting systems and data warehouses. Until Oracle 9i,oracle introduces a new CPU cost model, which eliminates this flaw, it must provide the performance information of the system on which the database engine resides, i.e. system statistics. This article will describe the system statistics and methods of collecting system statistical information.

System Statistical information

System statistics are placed in table aux_stats$, and you can view the data in that view in the following way (requires DBA users or have View permissions on 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 3 sections, which are differentiated using sname:

1) Sysstats_info indicates system statistics status and time
Status is completed when properly collected, and if a problem occurs, it appears as Badstats, in which case the query optimizer will not use statistics; The field can also be: manualgathering and autogathering.

2) Sysstats_main represents the system statistics result set
The information in this section is divided into non-workload statistics (noworkload statistics) and workload statistics (workload statistics), the main difference being the method of measuring I/O subsystem performance is different and is discussed in detail later.
Non-workload statistics are always available after Oracle 10g, including:

CPUSPEEDNW the number of operations a CPU can handle per second, per million/second Ioseektim average disk seek time, per millisecond, default 10IOTFRSPEED average bytes transferred from disk, default is 4096

Workload statistics must be displayed for collection before they are available, including:

Sreadtim average read time of single block of data, per millisecond Mreadtim multi-block data average read time, per millisecond Cpuspeed one CPU per second can handle the number of operations, units million/ Seconds MBRC The average number of read data blocks for a multiple-block read operation MAXTHR The maximum I/O throughput for the entire system (Bytes/sec) Slavethr Average I/O throughput of dependent threads in parallel processing (Bytes/sec)

3) Sysstats_temp is used for computing statistics and is only available when workload statistics are collected

Collecting system statistics

You can collect non-workload statistics or workload statistics by using stored procedures gather_system_stats in the toolkit dbms_stats to collect system statistics.

Non-workload statistics

Starting with Oracle 10g, the default non-workload statistics are always available and are automatically collected the next time the database starts, if explicitly deleted. Collect non-workload statistics to be performed on an idle system, using the following statement:

Begin  Dbms_stats.gather_system_stats (gathering_mode = ' noworkload '); end;

Collecting non-workload statistics takes only a short time, but sometimes multiple collections are required to take effect.

Workload statistics Information

Collecting workload statistics, you cannot use an idle system to evaluate the performance of the I/O subsystem for the database engine to take advantage of the normal database load.
The collection of workload statistics includes the following steps:

1) Execute the snapshot and store the initial values of various performance indicators into the Data dictionary table aux_stats$ (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 a representative load and then take another snapshot, usually not less than 30 minutes;

3) for a second snapshot, use the following statement:

Begin  Dbms_stats.gather_system_stats (gathering_mode = ' stop '); end;

4) Oracle calculates system statistics based on the difference between the two snapshots, and is set to NULL if it cannot be computed.

You can also specify Gathering_mode as interval and specify a time interval to collect workload statistics so that you do not have to stop the snapshot manually, and the following command specifies a second snapshot after 30 minutes:

Begin  Dbms_stats.gather_system_stats (gathering_mode = ' interval ',                                 interval       =); end;

It is important to note that the above statement does not last for 30 minutes, it just starts a job schedule and returns.
Use workload statistics to minimize the collection of non-representative workload information, you can collect multiple days of workload statistics, and then the MBRC, Mreadtim, Sreadtim and cpuspeed indicators averaged, 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 up the new system statistics, stored 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 = +);  Dbms_stats.set_system_stats (pname = ' Maxthr ', pvalue = 1136136192);  Dbms_stats.set_system_stats (pname = ' Slavethr ', pvalue = 16870400); end;

Oracle Performance Analysis 11: System statistics