Gathering Database Statistics Using the Automatic Workload Repository

Database statistics provide information about the type of load on the database and the internal and external resources used by the database. To accurately diagnose performance problems with the database using ADDM, statistics must be available.

A cumulative statistic is a count such as the number of block reads. Oracle Database generates many types of cumulative statistics for the system, sessions, and individual SQL statements. Oracle Database also tracks cumulative statistics about segments and services. Automatic Workload Repository (AWR) automates database statistics gathering by collecting, processing, and maintaining performance statistics for database problem detection and self-tuning purposes.

By default, the database gathers statistics every hour and creates an AWR snapshot, which is a set of data for a specific time that is used for performance comparisons. The delta values captured by the snapshot represent the changes for each statistic over the time period. Statistics gathered by AWR are queried from memory. The gathered data can be displayed in both reports and views.

The following initialization parameters are relevant for AWR:

• STATISTICS_LEVEL

  Set this parameter to TYPICAL (default) or ALL to enable statistics gathering by AWR. Setting STATISTICS_LEVEL to BASIC disables many Oracle Database features, including AWR, and is not recommended. To learn more about the STATISTICS_LEVEL parameter, see Oracle Database Reference.

• CONTROL_MANAGEMENT_PACK_ACCESS

  Set to DIAGNOSTIC+TUNING (default) or DIAGNOSTIC to enable automatic database diagnostic monitoring. Setting CONTROL_MANAGEMENT_PACK_ACCESS to NONE disables many Oracle Database features, including ADDM, and is strongly discouraged.

To learn more about these initialization parameters, see Oracle Database Reference.

The database statistics collected and processed by AWR include:

• Time Model Statistics

• Wait Event Statistics

• Session and System Statistics

• Active Session History Statistics

• High-Load SQL Statistics