what is oracle awr

awr refers to the "automatic workload data archive". It is a storage warehouse used by Oracle database to collect, manage and maintain statistical data related to the entire operation period and performance of the database. It is the basis for Oracle database performance adjustment and optimization. . The data collected by awr is regularly saved to disk and can be queried from the data dictionary and performance reports generated.

The operating environment of this tutorial: Windows 7 system, Oracle 11g version, Dell G3 computer.

When optimizing and adjusting the performance of the Oracle database, the current status or true state of the entire database during operation can only be checked, known, compared and can be compared when it is completely recorded. Speculate or provide a basis for supporting suggestions for future optimization and adjustment. In the Oracle database, this mechanism is implemented by AWR.

AWR is a storage warehouse used by Oracle database to collect, manage and maintain statistical data related to performance during the entire operation of the database. It is the basis for Oracle database performance adjustment and optimization.

1. What is AWR

AWR is the abbreviation of Automatic Workload Repository, which is called automatic workload data archive in Chinese. Since it is a warehouse and it stores load data, it stores data related to database performance. That is, the overall performance of a specific database or instance during past operations. AWR can collect, process, maintain performance data, and provide adjustment reference. This collected data is periodically saved to disk and can be queried from the data dictionary and performance reports can be generated.

2. Contents of AWR statistics collection and processing

  Object statistics used to determine access and usage of database segments

 Based on the activity time model Statistics, located in the v$sys_time_model and v$sess_time_model views

System and session level statistics collection, located in the v$sesstat and v$systat views

Based on elapsed time and CPU time Loaded sql statement

ASH statistical information, representing the history of recent active sessions

3. 2 important parameters involved in AWR

• statistics_level

ˆ BASIC:

ˆ ˆ only provides the most basic performance data collection functions, and many statistics required for performance baselines are not collected. Oracle does not recommend using this value.

 TYPICAL:

This is the default value. Segment statistics, time statistics, and all recommendation class statistics are collected.

　 ALL：

　　 Collect all typical level data, operating system time statistics and line source execution statistics, etc. It is mostly used in debugging mode and is not recommended for production environments.

• timed_statistics

      Specify whether time-related statistical information is collected. When statistics_level is TYPICAL or ALL, this value is set to true, otherwise, it is set to false. It is recommended to check this parameter and set it to true.

4. Snapshots and baselines

• Snapshots

AWR snapshot is to persist the collected performance data at a certain frequency to disk. The purpose of this persistence is mainly for subsequent analysis or comparison. At the same time, these performance data are also provided to ADDM for performance diagnosis and output of diagnosis results. That is: AWR samples v$active_session_history once every hour, saves the information to disk, and retains it for 8 days (11g default value). Old records will be overwritten after 8 days. This sampling information is saved in the view wrh$_active_session_history. The sampling frequency (1 hour) and retention time (8 days) can be adjusted according to actual conditions.

• Baseline

 Only with data and the ability to compare can we truly reflect the essence of the problem. Then in the Oracle database, we can label the active session historical data during the peak period of the business load, or more precisely, the AWR persistence data. This label is the so-called baseline. Therefore, the baseline is a benchmark report of the overall performance of the database during the peak period of the business in a specific period. Once the subsequent database has performance problems or is running poorly, by using the baseline and comparing it with the performance statistics during the performance problem period, we can get The difference report between the two helps to locate and solve the problem.

Since the baseline depends on the AWR snapshot, when we set the baseline, the corresponding AWR snapshot data is retained. In order to avoid occupying disk space, we can also set a corresponding retention period for the baseline. Once the retention period is reached, it means that the baseline becomes invalid and the corresponding AWR snapshot data will be automatically deleted. Based on the baseline retention strategy, there are several different forms of baselines, as follows:

## ) to create a baseline for subsequent AWR report comparison output results.

     Moving Window Baseline (Moving Window Baseline)

       Oracle Database automatically maintains a system-defined moving window baseline. The default window size for the system-defined moving window baseline is the current AWR retention period, which defaults to eight days. If you plan to use adaptive thresholds, consider using a larger moving window (such as 30 days) so that the thresholds can be calculated accurately. You can resize the moving window baseline by changing the number of days in the moving window to be equal to or less than the number of days in the AWR retention period. Therefore, if you want to increase the moving window, you first need to increase the AWR retention period accordingly. The AWR retention period and the window size of the system-defined moving window baseline are two independent parameters. The AWR retention period must be greater than or equal to the window size of the system-defined moving window baseline.
System-defined baselines provide a default baseline for the OEM performance screen to compare its performance to current database performance.
Note: In Oracle Database 11g, the default retention period for snapshot data has changed from seven days to eight days to ensure that an entire week of performance data is captured.

 Baseline Templates

      Baseline Templates You can use baseline templates to create baselines for future time periods. There are two types of baseline templates: single and repeating.
You can use a single baseline template to create a baseline for a single continuous time period in the future. This method is useful if you know in advance a certain time period that you intend to capture in the future. For example, you may want to capture AWR data for a system test scheduled for the upcoming weekend. In this case, a separate baseline template can be created to automatically capture the time period when the test occurs.

# You can use the recurring baseline template to create and delete baselines based on recurring time schedules. This is useful if you want Oracle Database to automatically capture a continuous time period for which to create a baseline. For example, you might want to capture AWR data every Monday morning for a month. In this case, you can create a recurring baseline template so that baselines are automatically created on a recurring schedule every Monday and old baselines are automatically deleted after a specified expiration interval (such as 1 month).

• Adaptive Thresholds

Adaptive thresholds can help you monitor and detect performance problems with the lowest overhead. Adaptive thresholds automatically set warning and critical alert thresholds for system metrics from statistics derived from metrics captured in a moving window baseline. These statistics are regenerated weekly and may result in new thresholds due to changes in system performance over time.

For example, many databases are an OLTP system during the day, but need to perform some batch processes (such as generating reports) at night. Performance measures of per-transaction response time may be useful during the day for detecting performance degradation issues in OLTP, but this threshold is often too low for batch jobs, triggering alarms frequently. Adaptive thresholds detect such workload patterns and automatically set different thresholds for day and night.

There are two types of adaptive thresholds:

Percent of maximum value: The threshold is calculated as a multiple of the percentage of the maximum value of the data observed in the moving window baseline.

Importance level: The threshold is set to a statistical percentile to observe values ​​above the threshold based on the moving window baseline data to reflect the degree of anomaly. Percentiles can be specified as follows: High (0.95), only 5 out of 100 can exceed this value; Very High (0.99): Only 1 out of 100 can exceed this value; Severe (0.999): 1000 Only 1 clock can exceed this value; extreme (0.9999): only 1 clock in 10,000 can exceed this value.

· Maximum percentage thresholds are useful when a system is designed for peak workloads and you want to trigger an alarm when the current workload approaches or exceeds a previous high. For example, the measurement of the amount of redo generated per second is a typical example.

 Importance level thresholds are useful in situations where a system is stable when it is operating normally, but may fluctuate within a wide range when performance deteriorates. For example, a measure of response time per transaction will be stable on an optimized OLTP system, but may fluctuate widely when performance issues become apparent. Adopt importance level thresholds to trigger alarms when the environment produces abnormal measurement values ​​and system performance.

Author : Leshami

Blog : http://blog.csdn.net/leshami

• Space Consumption

  The following factors can be used to determine the space consumption of AWR:

   The number of active sessions in the system at any given time;

   Snapshot interval. The smaller the time interval, the more snapshots are generated. Frequent, increases the space occupied by the data collected by AWR;

     history data retention time

 By default, snapshots are captured every hour and saved in the database for 8 days. Using these default settings, a typical concurrency system with 10 sessions requires approximately 200-300M of space to store AWR data. However, when reducing the retention time, please note that if there is insufficient data in AWR, it may affect the accuracy and precision of some components and functions: ADDM, SQL Tuning Advisor, Undo Advisor, Segment Advisor.

If possible, Oracle recommends setting the AWR retention time to be large enough to capture at least a complete workload cycle. When your system workload cycle is one week, for example, it is an OLTP workload on weekdays and batch jobs are run on weekends, the default retention time of 8 days does not need to be modified. If your system's peak period is at the end of each month, you may want to change this retention period to 1 month.

In exceptional cases, you can turn off automatic snapshot collection by changing the snapshot interval to 0. In this case, automatic collection of workload and statistics data will be stopped, and many of Oracle Database's automatic management functions will not be available. In addition, you cannot manually create snapshots, so Oracle strongly recommends not turning off automatic collection of snapshots.

5. AWR structure diagram

Recommended tutorial: "Oracle Tutorial"

The above is the detailed content of what is oracle awr. For more information, please follow other related articles on the PHP Chinese website!