优化器-系统统计信息

System Statistics

 

The query optimizer used to base its cost estimations on the number of physical reads needed to execute SQL statements. This method is known as the I/O cost model. The main drawback of this method is that single-block reads and multiblock reads are equally costly.Consequently,multiblock read operations, such as full table scans, are artificially favored. Up to Oracle8i,especially in OLTP systems, the initialization parameters optimizer_index_caching and optimizer_index_cost_adj solved this problem (see Chapter 5 for coverage of both initialization parameters). In fact, the default values used to be appropriate for reporting systems and data warehouses only. As of Oracle9i, a new costing method, known as the CPU cost model, is available to address this flaw. To use the CPU cost model, additional information about the performance of the system where the database engine runs, called system statistics, has to be provided to thequery optimizer. Essentially, system statistics supply the following information.

 

•          Performance of the I/O subsystem

•          Performance of the CPU

 

Despite its name, the CPU cost model takes into consideration the cost of physical reads as well. But, instead of basing the I/O costs on the number of physical reads only, the performance

of the I/O subsystem is also considered. Don’t let the name mislead you.

 

In Oracle9i, no system statistics are available per default. This means that, by default, the I/O cost model is used. As of Oracle Database 10g, a default set of system statistics is always available. As a result, by default, the CPU cost model is used. Actually, as of Oracle Database 10g, the only way to use the I/O cost model is to specify the hint no_cpu_costing at the SQL statement level. In all other cases, the query optimizer uses the CPU cost model.

 

There are two kinds of system statistics, noworkload statistics and workload statistics. The main difference between the two is the method used to measure the performance of the I/O subsystem. While the former runs a synthetic benchmark, the latter uses an application benchmark.Before discussing the difference between these two approaches in detail, let’s see how system statistics are stored in the data dictionary.

 

APPLICATION VS. SYNTHETIC BENCHMARK

 

An application benchmark, also called a real benchmark, is based on the workload produced by the normal operation of a real application. Although it usually provides very good information about the real performance of the system running it, because of its nature, it is not always possible to apply it in a controlled manner.

A synthetic benchmark is a workload produced by a program that does no real work. The main idea is

that it should simulate (model) an application workload by executing similar operations. Although it can be easily applied in a controlled manner, usually it will not produce performance figures as good as an application benchmark. Nevertheless, it could be useful for comparing different systems.

 

 

Data Dictionary

 

System statistics are stored in the data dictionary table aux_stats$. Unfortunately, no data dictionary view is available to externalize them. In this table, there are up to three sets of rows that are differentiated by the following values of the column sname:

 

• SYSSTATS_INFO is the set containing the status of system statistics and when they were gathered. If they were correctly gathered, the status is set to COMPLETED. If there is a problem during the gathering of statistics, the status is set to BADSTATS, in which case the system statistics are not used by the query optimizer. Two more values may be seen during the gathering of workload statistics: MANUALGATHERING and AUTOGATHERING. In addition, up to Oracle9i, the status is set to NOWORKLOAD when noworkload statistics were gathered.

 

SQL> SELECT pname, pval1, pval2

2 FROM sys.aux_stats$

3 WHERE sname = 'SYSSTATS_INFO';

 

PNAME        PVAL1   PVAL2

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

STATUS                     COMPLETED

DSTART                     04-04-2007 14:26

DSTOP                       04-04-2007 14:36

FLAGS                1

 

• SYSSTATS_MAIN is the set containing the system statistics themselves. Detailed information about them is provided in the next two sections.

 

SQL> SELECT pname, pval1

2 FROM sys.aux_stats$

3 WHERE sname = 'SYSSTATS_MAIN';

 

PNAME           PVAL1

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

CPUSPEEDNW      1617.6

IOSEEKTIM       10.0

IOTFRSPEED      4096.0

SREADTIM        1.3

MREADTIM        7.8

CPUSPEED        1620.0

MBRC            7.0

MAXTHR          473982976.0

SLAVETHR        1781760.0

 

• SYSSTATS_TEMP is the set containing values used for the computation of system statistics.It is available only while gathering workload statistics.

 

Since 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 are not equally sized or loaded, it must be carefully decided which node the system statistics are to be gathered on.

 

System statistics are gathered with the procedure gather_system_stats in the package dbms_stats. Per the default, the permission to execute it is granted to public. As a result, every user can gather system statistics. Nevertheless, to change the system statistics stored in the data dictionary, the role gather_system_statistics, or direct grants on the data dictionary table aux_stats$, are needed. Per the default, the role gather_system_statistics is provided through the role dba.

 

 

Noworkload Statistics

 

As mentioned earlier, the database engine supports two types of system statistics: noworkload statistics and workload statistics. As of Oracle Database 10g, noworkload statistics are always available. If you explicitly delete them, they are automatically gathered during the next database start-up. In Oracle9i, even if they are gathered, no statistics are stored in the data dictionary. Only the column status in aux_stats$ is set to NOWORKLOAD.

 

You 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 I/O performance, some reads of different sizes are performed on several datafiles of the database.

 

To gather noworkload statistics, you set the parameter gathering_mode of the procedure gather_system_stats to noworkload, as shown in the following example:

 

dbms_stats.gather_system_stats(gathering_mode => 'noworkload')

 

Gathering statistics usually takes less than one minute, and the statistics listed in Table 4-2 are computed. Oddly, sometimes it is necessary to repeat the gathering of statistics more than once; otherwise, the default values, which are also available in Table 4-2, are used. Although it is difficult to know what exactly is happening here, I would conjecture that a kind of sanity check, which discards statistics that make little sense, is occurring.

 

Table 4-2. Noworkload Statistics Stored in the Data Dictionary

 

Name                         Description

 

CPUSPEEDNW           The number of operations per second (in millions) that one CPU is able to process.

IOSEEKTIM               Average time (in milliseconds) needed to locate data on the disk. The default value is 10.

IOTFRSPEED             Average number of bytes per millisecond that can be transferred from the disk.The default value is 4,096.

 

 

 

Workload Statistics

 

Workload statistics are available only when explicitly gathered. To gather them, you cannot use an idle system because the database engine has to take advantage of the regular database load to measure the performance of the I/O subsystem. On the other hand, the same method as for noworkload statistics is used to measure the speed of the CPU. As shown in Figure 4-2, gathering workload statistics is a three-step activity. The idea is that to compute the average time taken by an operation, it is necessary to know how many times that operation was performed and how much time was spent executing it. For example, with the following SQL statements, I was able to compute the average time for single-block reads (6.2 milliseconds) from one of my test databases, in the same way the package dbms_stats would:

 

 

SQL> SELECT sum(singleblkrds) AS count, sum(singleblkrdtim)*10 AS time_ms

2 FROM v$filestat;

 

COUNT TIME_MS

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

22893 36760

 

SQL> REMARK run a benchmark to generate some I/O operations...

 

SQL> SELECT sum(singleblkrds) AS count, sum(singleblkrdtim)*10 AS time_ms

2 FROM v$filestat;

 

COUNT TIME_MS

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

54956 236430

 

SQL> SELECT round((236430-36760)/(54956-22893),1) AS avg_tim_singleblkrd

2 FROM dual;

AVG_TIM_SINGLEBLKRD

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

6.2

 

 

 

 

The three steps illustrated in Figure 4-2 are as follows:

 

1.    A snapshot of several performance figures is taken and stored in the data dictionary table aux_stats$ (for these rows, the column sname is set to SYSSTATS_TEMP). This step is carried out by setting the parameter gathering_mode of the procedure gather_system_stats to start, as shown in the following command:

 

dbms_stats.gather_system_stats(gathering_mode => 'start')

 

2. The database engine does not control the database load. Consequently, enough time to cover a representative load has to be waited for before taking another snapshot. It is difficult to provide general advice about this waiting time, but it is common to wait at least 30 minutes.

 

3. A second snapshot is taken. This step is carried out by setting the parameter gathering_mode of the procedure gather_system_stats to stop, as shown in the following command:

 

dbms_stats.gather_system_stats(gathering_mode => 'stop')

 

4. Then, based on the performance statistics of the two snapshots, the system statistics listed in Table 4-3 are computed. If one of the I/O statistics cannot be computed, it is set to NULL (as of Oracle Database 10g) or -1 (in Oracle9i).

 

Table 4-3. Workload Statistics Stored in the Data Dictionary

Name                   Description

CPUSPEED          The number of operations per second (in millions) that one CPU is able to process

SREADTIM          Average time (in milliseconds) needed to perform a single-block read operation

MREADTIM         Average time (in milliseconds) needed to perform a multiblock read operation

MBRC                  Average number of blocks read during a multiblock read operation

MAXTHR             Maximum I/O throughput (in bytes per second) for the whole system

SLAVETHR         Average I/O throughput (in bytes per second) for a parallel processing slave

 

To avoid manually taking the ending snapshot, it is also possible to set the parameter gathering_mode of the procedure gather_system_stats to interval. With this parameter, the starting snapshot is immediately taken, and the ending snapshot is scheduled to be executed after the number of minutes specified by a second parameter named interval. The following command specifies that the gathering of statistics should last 30 minutes:

 

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

 

Note that the execution of the previous command does not take 30 minutes. It just takesthe starting snapshot and schedules a job to take the ending snapshot. Up to Oracle Database 10g Release 1, the legacy scheduler (the one managed with the package dbms_job) is used. As of Database 10g Release 2, the new scheduler (the one managed with the package dbms_scheduler) is used. You can see the job by querying the views user_jobs and user_scheduler_jobs, respectively.

 

The main problem we have in gathering system statistics is choosing the gathering period. In

fact, most systems experience a load that is anything but constant, and therefore, the evolution of workload statistics, except for cpuspeed, is equally inconstant. Figure 4-3 shows the evolution of workload statistics that I measured on a production system. To produce the charts, I gathered workload statistics for about four days at intervals of one hour. Consult the scripts system_stats_history.sql and system_stats_history_job.sql for examples of the SQL statements I used for that purpose.

 

 

 

 

 

To avoid gathering workload statistics during a period that provides values that are not representative of the load, I see only two approaches. Either we gather workload statistics over a period of several days or we can produce charts like in Figure 4-3 to get values that make sense. I

usually advise the latter, because we also get a useful view of the system at the same time. For example, based on the charts shown in Figure 4-3, I suggest using the average values for mbrc,

mreadtim, sreadtim, and cpuspeed and using the maximum values for maxthr and slavethr. Then, a PL/SQL block like the following one might be used to manually set the workload statistics. Note that before setting the workload statistics with the procedure set_system_stats, the old set of system statistics is deleted with the procedure delete_system_stats.

 

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;

 

 

This method could also be used if different sets of workload statistics are needed for different

periods of the day or week. It must be said, however, that I have never come across a case that

required more than one set of workload statistics.

You may have perceived that I do not advise regularly gathering noworkload statistics.I find it much better to fix their value and consider them as initialization parameters.

 

Impact on the Query Optimizer

 

When system statistics are available, the query optimizer computes two costs: I/O and CPU.Chapter 5 describes how I/O costs are computed for the most important access paths. Very little information is available about the computation of CPU. Nevertheless, we can imagine that the query optimizer associates a cost to every operation in terms of CPU. For example, as pointed out by Joze Senegacnik, beginning in Oracle Database 10g Release 2, Formula 4-1 is used to compute the CPU cost of accessing a column.

 

Formula 4-1. The estimated CPU cost to access a column depends on its position in the table. This formula gives the cost of accessing one row. If several rows are accessed, the CPU cost increases proportionally. Chapter 12 provides further information on why the position of a column is relevant.

 

The following example, which is an excerpt of the script cpu_cost_column_access.sql,shows Formula 4-1 in action. A table with nine columns is created, one row is inserted, and then with the SQL statement EXPLAIN PLAN the CPU cost of independently accessing the nine columns is displayed. Please refer to Chapter 6 for detailed information about this SQL statement.Notice how there is an initial CPU cost of 35,757 to access the table, and then for each subsequent column, a CPU cost of 20 is added. At the same time, the I/O cost is constant. This makes sense because all columns are stored in the very same database block, and therefore the number of physical reads required to read them is the same for all queries.

 

SQL> CREATE TABLE t (c1 NUMBER, c2 NUMBER, c3 NUMBER,2 c4 NUMBER, c5 NUMBER, c6 NUMBER,3 c7 NUMBER, c8 NUMBER, c9 NUMBER);

 

SQL> INSERT INTO t VALUES (1, 2, 3, 4, 5, 6, 7, 8, 9);

 

SQL> EXPLAIN PLAN SET STATEMENT_ID 'c1' FOR SELECT c1 FROM t;

SQL> EXPLAIN PLAN SET STATEMENT_ID 'c2' FOR SELECT c2 FROM t;

SQL> EXPLAIN PLAN SET STATEMENT_ID 'c3' FOR SELECT c3 FROM t;

SQL> EXPLAIN PLAN SET STATEMENT_ID 'c4' FOR SELECT c4 FROM t;

SQL> EXPLAIN PLAN SET STATEMENT_ID 'c5' FOR SELECT c5 FROM t;

SQL> EXPLAIN PLAN SET STATEMENT_ID 'c6' FOR SELECT c6 FROM t;

SQL> EXPLAIN PLAN SET STATEMENT_ID 'c7' FOR SELECT c7 FROM t;

SQL> EXPLAIN PLAN SET STATEMENT_ID 'c8' FOR SELECT c8 FROM t;

SQL> EXPLAIN PLAN SET STATEMENT_ID 'c9' FOR SELECT c9 FROM t;

 

SQL> SELECT statement_id, cpu_cost AS total_cpu_cost,

2 cpu_cost-lag(cpu_cost) OVER (ORDER BY statement_id) AS cpu_cost_1_coll,

3 io_cost

4 FROM plan_table

5 WHERE id = 0

6 ORDER BY statement_id;

 

STATEMENT_ID        TOTAL_CPU_COST  CPU_COST_1_COLL  IO_COST

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

c1                                  35757                                                            3

c2                                 35777                                  20                       3

c3                                 35797                                   20                       3

c4                                 35817                                  20                       3

c5                                 35837                                   20                        3

c6                               35857                                  20                       3

c7                                35877                                   20                       3

c8                              35897                                  20                        3

c9                                 35917                                  20                       3

 

 

The I/O and CPU costs are expressed with different units of measurement. Obviously then, the overall cost of a SQL statement cannot be calculated simply by summing up the costs.To solve this problem, the query optimizer uses Formula 4-2 with workload statistics. Simplyput, the CPU cost is transformed into the number of single-block reads that can be performed per second.

 

cost=io_cost  +  cpu_cost/(cpuspeed*sreadtim*10000)

 

Formula 4-2. The overall costs are based on the I/O costs and the CPU costs.

 

 

To compute the overall cost with noworkload statistics, in Formula 4-2 cpuspeed is replaced by cpuspeednw, and sreadtim is computed using Formula 4-3.

 

sreadtim =ioseektim+db_block_size/iotfrspeed

 

Formula 4-3. If necessary, sreadtim is computed based on noworkload statistics and the block size of the database.

 

 

Generally speaking, if workload statistics are available, the query optimizer uses them and

ignores noworkload statistics. You should be aware that the query optimizer performs several

sanity checks that could disable or partially replace workload statistics.

 

• When either sreadtim, mreadtim, or mbrc is not available, the query optimizer ignores

workload statistics.

 

• When mreadtim is less than or equal to sreadtim, the value of sreadtim and mreadtim is

recomputed using Formula 4-3 and Formula 4-4, respectively.

 

mreadtim= ioseektim+(mbrc-db_block_size)/ iotfrspeed

 

Formula 4-4. The computation of mreadtim based on noworkload statistics and the block

size of the database

 

System statistics make the query optimizer aware of the system where the database engine is running. This means they are essential for a successful configuration. In other words, it is strongly advisable to always use them. I also recommend freezing them in order to have some stability. Of course, in case of major hardware or software changes, they should be recomputed, and as a result, the whole configuration should be checked. For checking purposes, it is also possible to regularly gather them in a backup table (in other words, using the parameters statown and stattab of the procedure gather_system_stats) and verify whether there is a major difference between the current values and the values stored in the data dictionary.

 

本文摘自<>