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) orALL
to enable statistics gathering by AWR. SettingSTATISTICS_LEVEL
toBASIC
disables many database features, including AWR, and is not recommended. -
CONTROL_MANAGEMENT_PACK_ACCESS
Set to
DIAGNOSTIC+TUNING
(default) orDIAGNOSTIC
to enable automatic database diagnostic monitoring. SettingCONTROL_MANAGEMENT_PACK_ACCESS
toNONE
disables many database features, including ADDM, and is strongly discouraged.
See Also:
-
Oracle Database Reference for more information about the
STATISTICS_LEVEL
initialization parameter -
Oracle Database Reference for more information about the
CONTROL_MANAGEMENT_PACK_ACCESS
initialization parameter
The database statistics collected and processed by AWR include: