Shulou(Shulou.com)06/01 Report--

Oracle 12cR2

Performance optimization documentation.

Https://docs.oracle.com/database/122/TDPPT/oracle-database-performance-method.htm#TDPPT007

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.

-collect AWR snapshots every hour

The following initialization parameters are relevant for AWR:

Here are two parameters related to AWR.

STATISTICS_LEVEL

Set this parameter to TYPICAL (default) or ALL to enable statistics gathering by AWR. Setting STATISTICS_LEVEL to BASIC disables many database features, including AWR, and is not recommended.

-- setting the above parameter to basic will disable many database features, including AWR. Setting basic is not recommended.

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 database features, including ADDM, and is strongly discouraged.

-- disabling this parameter disables many database features, including ADDM, and is not recommended.

SQL > show parameter statistics_level-the default value does not need to be modified

NAME TYPE VALUE

Statistics_level string TYPICAL

SQL > show parameter control_management-the default value does not need to be modified

NAME TYPE VALUE

-

Control_management_pack_access string DIAGNOSTIC+TUNING

SQL >

As we all know, the AWR information we collected is related to those parameters, which can be set by default and do not need to be modified.

It is recommended that you read the official English documents, learn English to learn skills, oh yes!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.