How to Create Statistics for the Oracle dictionary & calculate system Statistics?

Administering, that is, creating and updating statistics (table and index statistics) for the normal user schemas, such as the SAP schemas SAPR3, SAP, SAPDB, occurs in the SAP environment with BR*Tools (to be precise: with brconnect).

As of 10g, Oracle database statistics can also manage completely automatically. This is implemented in the database as the DBMS_SCHEDULER job GATHER_STATS_JOB. However, this method is not yet supported in the SAP environment, and is therefore deactivated to avoid conflicts with the brconnect methods. In new SAP installations as of SAP Kernel 7.00, this automatic function is deactivated by SAPINST.

The GATHER_STATS_JOB is created both when you upgrade your database to 10g and when you create a 10g database:
SQL>SELECT job_name, owner, enabled, state
FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME = 'GATHER_STATS_JOB'

To deactivate the automatic Oracle statistics management, use the following command:
sqlplus / as sysdba
SQL>EXECUTE DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB')

Check that the job is deactivated:
SQL>SELECT job_name, owner, enabled, state
FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME = 'GATHER_STATS_JOB'

The command that reactivates the job is:
SQL>EXECUTE DBMS_SCHEDULER.ENABLE('GATHER_STATS_JOB')

Table monitoring
To establish which table statistics are no longer exact enough ('stale'), the table monitoring feature exists, which calculates the approximate number of inserts, updates, and deletes for each table (and also takes truncation into account). Table monitoring must be active in the SAP environment.Table monitoring is active in Oracle database 10g as a default if the parameter STATISTICS_LEVEL is set to the default value TYPICAL . In the views *_TAB_MODIFICATIONS, you can see an overview of the number of changes in a table since the statistics were last created.

#1:
If statistics_level = basic, table monitoring is deactivated. This is not supported in the SAP environment.
If statistics_level = typical, table monitoring is active.
STATISTICS_LEVEL is not set by default (see 830576).

STATISTICS_LEVEL | Table Monitoring | Note/
| DBA_TABLES.MONITORING | Recommendation
-----------------+-----------------------+------------------------
BASIC | NO | Not allowed
TYPICAL | YES | default, specified
(FULL) | YES | Only for diagnosis/support

#2:
As of Oracle database 10.1, MONITORING|NOMONITORING is obsolete/ineffective (deprecated), and you can ignore it using the following commands:
SQL>ALTER TABLE MONITORING|NOMONITORING;
SQL>CREATE TABLE MONITORING|NOMONITORING;


Statistics for the Oracle dictionary

Oracle database 10g still only supports the cost-based optimizer (CBO), while the rule-based optimizer (RBO) is no longer supported.

This means that, from now on, accesses to the Oracle dictionary are only optimized by the CBO. Therefore, statistics must also be created for the Oracle dictionary now. This primarily concerns the database schemata SYS, SYSTEM. Up to Oracle9i -unlike now - no statistics were normally created for the schema or user SYS, SYSTEM, and the access optimization to the Oracle dictionary occurred using the RBO.


Support using brconnect

As of 7.00 patch level 15, the brconnect will support the creation of Oracle dictionary statistics using the methods described below. We recommend that you replace older versions of brconnect with a newer version.

The SQL command that brconnect uses to create Oracle database statistics is as follows:

SQL>begin DBMS_STATS.GATHER_DICTIONARY_STATS(
ESTIMATE_PERCENT => NULL,
METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO',
GRANULARITY => 'ALL',
CASCADE => TRUE,
OPTIONS => 'GATHER',
NO_INVALIDATE => FALSE);
end;
/

This ensures that the system creates current statistics, which are highly accurate, for all objects of the Oracle dictionary.
Specify NO_INVALIDATE=FALSE so that new statistics are soon used.
If you do not specify ESTIMATE_PERCENT and OPTIONS, the statistics may be too inaccurate (Oracle bug 5017702). See 138639 also.

The relevant call using brconnect (see 863811) is as follows:
brconnect -u / -c -f stats -t oradict_stats

Recommended: Create new dictionary statistics when the Oracle Dictionary may have changed considerably, for example: - After installing a database patchset
- after an SAP upgrade,
- if a further SAP System has been installed (MCOD),

Otherwise, you should create dictionary statistics once a quarter.


Statistics on 'Fixed Objects'

Part of the Oracle dictionary consists of X$ tables, which are known as 'fixed objects'. These 'dynamic performance tables' contain information about current database activity. We also recommend that you create statistics for these tables. To do this, enter:

SQL>begin
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(NO_INVALIDATE => FALSE);
end;
/

The relevant call using brconnect (see 863811) is as follows:
brconnect -u / -c -f stats -t oradict_stats

Create the statistics when the database is carrying a typical and representative load.


Deleting dictionary statistics:
The commands for deleting Oracle dictionary statistics are as follows:
SQL>begin
DBMS_STATS.DELETE_DICTIONARY_STATS (NO_INVALIDATE => FALSE);
DBMS_STATS.DELETE_FIXED_OBJECTS_STATS (NO_INVALIDATE => FALSE);
end;
/


System statistics

System statistics describe the attributes of the hardware of the system, for example, I/O performance or processor rate. These performance figures (in the form of statistics) provide the CBO with a more precise calculation of CPU costs and I/O costs and assist in finding a better access plan. Recommended: You should therefore always determine these statistics.

To determine the system statistics, call:
SQL>begin
DBMS_STATS.GATHER_SYSTEM_STATS;
end;
/

The database authorization GATHER_SYSTEM_STATISTICS is required for this operation. This is contained in the current version of SAPDBA role.

The relevant call using brconnect (see 601395) is as follows:
brconnect -u / -c -f stats -t system_stats

You see the result of the determined system statistics here:
SQL>select * from sys.aux_stats$;
See the Oracle documentation for an explanation of the individual figures.

Recommended: You should create new system statistics
- initially after creating the database
- after creating new tablespaces
- after a database upgrade to Oracle database 10g
- when you change the hardware of the DB server
(other/more CPUs, more hard disks, faster network)
- or simply once a quarter

New SQL statements are parsed with the new system statistics, and SQL that is already parsed remains valid.