System Statistics for the CBO 

This submonitor in the SAP/Oracle Database Monitor lets you collect I/O and CPU statistics for the Cost-Based Optimizer (CBO). This allows the optimizer to generate relevant costs for system-resource plans. For each plan, the CBO optimizer computes estimates for I/O and CPU costs. The collected statistics are:

·        Single block readtime in ms

·        Multiblock readtime in ms

·        CPU speed in MHz

·        Average multiblock_read_count in number of blocks

The system statistics must be collected when the system has an average workload.

The statistics are gathered with the PL/SQL DBMS_STATS package:

·        DBMS_STATS.CREATE_STAT_TABLE – create a user table to collect the statistics

·        DBMS_STATS.GATHER_SYSTEM_STATS – collect statistics for a special time frame

·        DBMS_STATS.IMPORT_SYSTEM_STATS – transfer the data from the user table to the dictionary tables

·        DBMS_STATS.DELETE_SYSTEM_STATS – delete any existing system statistics from the dictionary

Use

You choose Detailed Analyses  ® Additional Functions  ® System Statistics for CBO.

The submonitor only displays information if one of the following is true:

·        System statistics are collected for the system

·        System statistics are activated by import into SYS.AUXSTAT$

You can view history information in this monitor, but only for the tab page System Statistics.

Structure

·        System Statistics

This tab page displays the entire contents of SYS.AUXSTAT$:

Column	Description
SName	Statistics name
PName	Parameter name
PVal1	Parameter value 1
PVal2	Parameter value 2

The entries in the column PName have the following meanings:

PName	Meaning
STATUS	AUTOGATHERING, COMPLETED, or BADSTATS
DSTART	Start time for statistic collection
DSTOP	Stop time for statistic collection
FLAGS	Oracle internal flags
SPREADTM	Wait time to read a single block in milliseconds
MREADTM	Wait time to read a multiblock in milliseconds
CPUSPEED	CPU speed in millions of cycles per second
MBRC	Average multiblock read count for sequential reads, in blocks
MAXTHR	Maximum I/O system throughput, in bytes/sec
SLAVETHR	Average slave I/O throughput, in bytes/sec

 

·        Collected Statistics

This tab page displays all tables that are storing system statistics. These tables were created with the PL/SQL procedure DBMS_STATS.CREATE_STAT_TABLE:

If the SAPR3 user does not have SELECT permissions on this table, the column ACCESS has the entry NO and the system statistics columns are empty.

The columns and types in this table are not relevant as it should be accessed solely through the procedures in the DBMS_STATS package.

Column	Description
Table Name	Table name
Stat_ID	Statistic ID
Version	Oracle internal
Flags	Oracle internal
C1 – C5	Oracle internal
N1 – N12	Oracle internal
D1	Oracle internal
R1 – R2	Oracle internal
CH1	Oracle internal