How to Collect and Display System Statistics (CPU and IO) for CBO use-白红宇

How to Collect and Display System Statistics (CPU and IO) for CBO use

阅读量：7153 次

发布时间：2019-06-29

本文共 12987 字，大约阅读时间需要 43 分钟。

How to Collect and Display System Statistics (CPU and IO) for CBO use (Doc ID 149560.1)

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 and later

Oracle Database - Personal Edition - Version 9.2.0.1 and later

Oracle Database - Standard Edition - Version 9.2.0.1 and later

Information in this document applies to any platform.

Goal

This bulletin explains how new system statistics can be collected and displayed for CBO to use and apprehend CPU and system I/O information. For each plan candidate, the optimizer computes estimates for I/O and CPU costs. You must have DBA privileges or GATHER_SYSTEM_STATISTICS role to update dictionary system statistics.

System statistics should be valid and represent the performance of the system. They are not something that should be frequently gathered because this can encourage plan instability with an unknown or minimal benefit. Often systems will work fine with no system statistics at all. If you do choose to gather system statistics (to capture a particular workload or to profile a particular activity) the main trigger would be that there has been relevant changes to the system (like incorporation of faster CPUs or IO subsystem) - it is a common misconception that gathering is necessary when adding more CPU cores (only faster/slower CPUs may suggest a need to gather fresh system stats).

Note that if System Statistics are gathered (regardless of method), an immediate review of the correctness of the values generated is a must. Values that normally need to be validated are MREADTIM, SREADTIM and MBRC. Validate these against reported Operating System figures. Note that Oracle CPU speed IS NOT the same as hardware CPU speed - the value computed by Oracle is fine. If in doubt of the correctness of any of these values, it is more conservative to DELETE System Stats than the possibility of non-representative values that may adversely affect plan generation and the performance of your queries.

Solution

Workload statistics were introduced in Oracle 9i.

In release 9.0, the following system statistics are gathered:

sreadtim - single block read time

mreadtim - multiblock read time

mbrc - multi-block read count

cpuspeed - CPU speed

In release 9.2 this was extended to include the following in order to set a lower limit for a full table scan (FTS).

maxthr - maximum I/O throughput

slavethr -average slave throughput

In release 10g,11g, and 12c, there are three new parameters available:

cpuspeedNW - Represents noworkload CPU speed

ioseektim - I/O seek time equals seek time + latency time + operating system overhead time.

iotfrspeed - I/O transfer speed is the rate at which an Oracle database can read data in a single read request

Since 10g and 11g parameter names are not related to any particular workload.However, they may be used to supplement workload related system statistics as long as you are are running 10g or 11g, and DBMS_STATS.GATHER_SYSTEM_STATS parameter gathering_mode is set to NOWORKLOAD.

For more detail on all the system statistics parameter names mentioned above, please reference:

Oracle Database Performance Tuning Guide

10g Release 2 (10.2)

Part Number B14211-01

Chapter 14.4 System Statistics

Table 14-2 Optimizer System Statistics in the DBMS_STAT Package

Oracle® Database Performance Tuning Guide

11g Release 2 (11.2)

Part Number E16638-07

Chapter 13.4 System Statistics

Table 13-7 Optimizer System Statistics in the DBMS_STAT Package

Oracle® Database Performance Tuning Guide

12c Release 1 (12.1)

12.5.1 About Gathering System Statistics with DBMS_STATS

Table 12-4 Optimizer System Statistics in the DBMS_STAT Package

How to Set Different System Statistics for the Instance

Note: The information below applies to WORKLOAD related system statistics.System I/O and CPU characteristics depends on many factors and do not stay constant all the time. DBAs must capture statistics in the interval of time when the system has the most common workload.

For example, database applications can process OLTP transactions during the day and run OLAP reports at night:

Gather statistics for OLTP workload
- collect with DBMS_STATS.GATHER_SYSTEM_STATS
- store statistics in a user table (DBMS_STATS.CREATE_STAT_TABLE)

gather other statistics for OLAP workload
- collect with DBMS_STATS.GATHER_SYSTEM_STATS
- store statistics in another user table

activate appropriate OLTP or OLAP statistics when needed
- transfer the statistics for OLTP or OLAP from the user table to the data dictionary through DBMS_STATS.IMPORT_SYSTEM_STATS

This allows the optimizer to generate relevant costs with respect to available system resource plans.

When Oracle generates system statistics, it analyzes system activity in a specified period of time. Unlike table, index, or column statistics, Oracle does not invalidate already parsed SQL statements when system statistics get updated. All new SQL statements are parsed using new statistics.

During application development and test phase, you can set your own system statistics using DBMS_STATS.SET_SYSTEM_STATS setting explicit values into the data dictionary directly.

Example in a Production Environment

Note: For simplicity all examples below use 9.0 related WORKLOAD parameters.If using version 9.2,10g, or 11g you may also create an example using the additional 9.2 WORKLOAD related parameters discussed above in "System Statistics in version 9.0, 9.2,10g, and 11g" section.

Collect System Statistics for OLTP:
1. Create a table for OLTP statistics:

SQL> execute DBMS_STATS.CREATE_STAT_TABLE ('SYS','OLTP_stats','STATS_TBS');

Table SYS.OLTP_STATS will be created in tablespace STATS_TBS.
2. Before system statistics are gathered and set in dictionary, the CPU cost is not computed by the optimizer:

SQL> explain plan for select * from OLTP.TEST where c='AAAHxGAABAAAJS1AEZ';
Explained.
SQL> select operation, options, object_name, cpu_cost, io_cost from plan_table;
OPERATION          OPTIONS              OBJECT_NAME CPU_COST   IO_COST
------------------ -------------------- ------------ ---------- ----------
SELECT STATEMENT                                                         1
INDEX UNIQUE SCAN                       SYS_C002218                      1

3. Collect statistics under OLTP workload and store them in OLTP_STATS table:
There is a need for a job process to be active to do the gathering.
Check the value of the parameter job_queue_processes and if its not set then set it as follows:

SQL> alter system set job_queue_processes=1; -- At least one.
System altered.

Next schedule the job to gather system information as follows:

SQL> execute DBMS_STATS.GATHER_SYSTEM_STATS(gathering_mode => 'INTERVAL', interval => 2, stattab => 'OLTP_stats', statid => 'OLTP');
PL/SQL procedure successfully completed.

4. Display existing System Statistics from OLTP_STATS
- Check the status AUTOGATHERING/COMPLETED/BADSTATS
  Has the stats job terminated, and is it pertinent or not ?
After 1 minute, the job is still running (interval set to 2 minutes).
The status of the job is AUTOGATHERING

SQL> column statid format a7
SQL> column c1 format a13
SQL> column c2 format a16
SQL> column c3 format a16
SQL> select STATID, C1, C2, C3 from oltp_stats;
STATID C1    C2                       C3
--------     -------------------------- ----------------       --------------------
OLTP     AUTOGATHERING 08-09-2001 16:29 08-11-2001 16:29

After 2 minutes, collection is completed but not pertinent (BADSTATS), because there was no workload that could justify any statistics

STATID C1                              C2                      C3
----------- -------------------------- ----------------      --------------------
OLTP      BADSTATS              08-09-2001 16:29 08-09-2001 16:31
5. Start the collection again when the workload is relevant.
SQL> execute DBMS_STATS.GATHER_SYSTEM_STATS(gathering_mode => 'INTERVAL', interval => 2, stattab => 'OLTP_stats', statid => 'OLTP');
PL/SQL procedure successfully completed.

After 2 minutes the status is now COMPLETED and relevant

SQL> select STATID, C1, C2, C3 from oltp_stats;
STATID C1                   C2                      C3
----------- ----------------- -------------------- --------------------
OLTP     COMPLETED 08-09-2001 16:41 08-09-2001 16:43

Collect System Statistics for OLAP

SQL> execute DBMS_STATS.CREATE_STAT_TABLE('SYS','OLAP_stats','STATS_TBS');
PL/SQL procedure successfully completed.
SQL> execute DBMS_STATS.GATHER_SYSTEM_STATS (gathering_mode => 'INTERVAL', interval => 2, stattab => 'OLAP_stats', statid => 'OLAP');
PL/SQL procedure successfully completed.
SQL> select STATID, C1, C2, C3 from olap_stats;
STATID C1 C2 C3
------- ------------- ---------------- ----------------
OLAP COMPLETED 08-09-2001 16:44 08-09-2001 16:46

Initialize the OLTP System Statistics for the CBO
1. Delete any existing system statistics from dictionary:

SQL> execute DBMS_STATS.DELETE_SYSTEM_STATS;
PL/SQL procedure successfully completed.

2. Transfer the OLTP statistics from OLTP_STATS table to the dictionary tables:

SQL> execute DBMS_STATS.IMPORT_SYSTEM_STATS(stattab => 'OLTP_stats', statid => 'OLTP', statown => 'SYS');
PL/SQL procedure successfully completed.

All system statistics are now visible in the data dictionary table:

SQL> select * from aux_stats$;
SNAME    PNAME              PVAL1      PVAL2
-------------------- ------------------ ---------- --------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 08-09-2001 16:40
SYSSTATS_INFO       DSTOP    08-09-2001 16:42
SYSSTATS_INFO FLAGS 0
SYSSTATS_MAIN SREADTIM                7.581
SYSSTATS_MAIN MREADTIM 56.842
SYSSTATS_MAIN       CPUSPEED                  117
SYSSTATS_MAIN       MBRC                        9

If using version 9.2, 10g, or 11g or 12c, the additional parameters will also be displayed.

CPU_COST and IO_COST in PLAN_TABLE table
Different queries will now show different cpu and io costs depending on the kind of work that is being done:

SQL> explain plan for select * from oltp.test where c='AAAHxGAABAAAJS1AEZ';
Explained.
SQL> select operation, options, object_name, cpu_cost, io_cost
from plan_table;
OPERATION          OPTIONS              OBJECT_NAME CPU_COST   IO_COST
------------------ -------------------- ------------ ---------- ----------
SELECT STATEMENT    10500    1
INDEX UNIQUE SCAN                       SYS_C002218 10500    1

SQL> truncate table plan_table;

SQL> explain plan for select * from oltp.test;
Explained.
SQL> select operation, options, object_name, cpu_cost, io_cost
from plan_table;
OPERATION    OPTIONS    OBJECT_NAME CPU_COST   IO_COST
------------------ -------------------- ------------ ---------- ----------
SELECT STATEMENT    2677480 27
INDEX FAST FULL SCAN    SYS_C002218 2677480 27

Example in a Development Environment

In a development environment, you can simulate workload statistics by setting them manually:

Set your own System Statistics for OLTP (or OLAP)
If you delete the statistics first, you need to reinitialize them all in order for CBO to take them into account.You can keep them all and reinitialize one statistic by one for testing in an
accurate method.
SQL> execute DBMS_STATS.DELETE_SYSTEM_STATS;
PL/SQL procedure successfully completed.
SQL> execute DBMS_STATS.SET_SYSTEM_STATS (pname => 'cpuspeed', pvalue => 400);
PL/SQL procedure successfully completed.
SQL> execute DBMS_STATS.SET_SYSTEM_STATS (pname => 'sreadtim', pvalue => 100);
PL/SQL procedure successfully completed.

Note the missing MREADTIM and MBRC:
SQL> select * from sys.aux_stats$;
SNAME                       PNAME              PVAL1      PVAL2
--------------------           ------------------    ----------     --------------------
SYSSTATS_INFO        STATUS                             COMPLETED
SYSSTATS_INFO        DSTART                           09-08-2001 18:06
SYSSTATS_INFO        DSTOP       09-08-2001 18:06
SYSSTATS_INFO    FLAGS     1
SYSSTATS_MAIN        SREADTIM       100
SYSSTATS_MAIN        MREADTIM
SYSSTATS_MAIN        CPUSPEED 400
SYSSTATS_MAIN        MBRC
SQL> truncate table plan_table;
Table truncated.
SQL> explain plan for select * from test where c='1244AAAHxGAABAAAJS1AEZ';
Explained.

Plan does not show cpu costs:

SQL> select operation, options, object_name, cpu_cost, io_cost2 from plan_table;
OPERATION OPTIONS          OBJECT_NAME CPU_COST IO_COST
------------------ -------------------- ---------------------- --------------- -------------
SELECT STATEMENT    1
INDEX UNIQUE SCAN SYS_C002218                                                    1

Initialize the 2 missing statistics:

SQL> execute DBMS_STATS.SET_SYSTEM_STATS (pname => 'mbrc', pvalue => 9);
PL/SQL procedure successfully completed.
SQL> execute DBMS_STATS.SET_SYSTEM_STATS (pname => 'mreadtim', pvalue => 100);
PL/SQL procedure successfully completed.
SQL> select * from sys.aux_stats$;
SNAME           PNAME          PVAL1      PVAL2
--------------------       ------------------ ----------    --------------------
SYSSTATS_INFO    STATUS COMPLETED
SYSSTATS_INFO    DSTART    09-08-2001 18:10
SYSSTATS_INFO    DSTOP    09-08-2001 18:10
SYSSTATS_INFO    FLAGS                      1
SYSSTATS_MAIN   SREADTIM 100
SYSSTATS_MAIN   MREADTIM          100
SYSSTATS_MAIN   CPUSPEED 400
SYSSTATS_MAIN   MBRC     9

Plan now shows CPU cost

SQL> truncate table plan_table;
Table truncated.
SQL> explain plan for select * from test where c='1244AAAHxGAABAAAJS1AEZ';
Explained.
SQL> select operation, options, object_name, cpu_cost, io_cost from plan_table;
OPERATION OPTIONS         OBJECT_NAME CPU_COST IO_COST
------------------ -------------------- ---------------------- ---------------- -------------
SELECT STATEMENT                                                      10500    1
INDEX UNIQUE SCAN SYS_C002218                 10500 1