Oracle 11g AWR Series 1: Basic concepts of AWR

Description of the concept of automatic workload Repository

Automatic workload repository (AWR) collects, processes, and maintains Performance Statistics for problem diagnosis. The data is stored in both the data block and the memory. The data collected by AWR can be viewed in reports and views.

Statistics collected by AWR include:

1. determine the access path and usage object statistics of the Data Block Segment
 
2. Time Model statistics based on the time usage of database activities can be viewed in the V $ sys_time_model and V $ sess_time_model views
 
3. Some sytem and session statistics collected in the V $ sysstat and V $ sesstat views

4. SQL statements that generate high load on the system according to elapsed time, CPU time, and other conditions

5. Ash statistics-history of recent session activities

By default, the database has enabled AWR to collect statistics, which is controlled by statistics_level initialization parameters. The statistics_level parameter must be set to typical or all to enable AWR statistics collection. The default value is typical. Setting statistics_level to basic will disable many Oracle database functions, including AWR, so this setting is not recommended. When statistics_level is set to basic, you can still use the dbms_workload_repository package to manually capture
AWR statistics. However, many system statistics collected in the memory, such as segment statistics and memory advisor information, will be disabled. In this case, the statistics captured by manual snapshots may not be complete.

1 Snapshot
 
A snapshot is a collection of historical data used by ADDM for performance comparison in a specific period. During the 11 GB period, the Oracle database automatically generates a snapshot of the performance data every hour and keeps the statistical information in the workload database for 8 days. You can also create snapshots manually, but it is not necessary to do so. The statistical information during the snapshot interval is analyzed by the automatic database diagnostic monitor (ADDM.
 
AWR compares the differences between snapshots and determines the SQL statements to be captured based on the impact on the system load. As time goes on, the SQL statements that must be captured will gradually decrease.

2 baseline

Baseline refers to performance data for a specific period of time. The data is retained to compare performance problems with other similar workload periods. Snapshots contained in baseline are excluded from the automatic AWR cleanup process and retained indefinitely.
 
There are multiple types of baseline in Oracle database;

Fixed baseline: Fixed baseline indicates a fixed, continuous time period that you specify. Before creating a fixed baseline, you must carefully consider the time period for choosing a baseline, because this baseline should indicate that the system is running under good performance. You can compare the baseline with other baseline or snapshot captured in poor performance periods in the future.
 
Moving Window baseline: indicates all AWR data that exists during the AWR retention period. It is useful when using an adaptive threshold value because the database can use AWR data during the entire AWR retention period to calculate the value of the metric value.
Oracle database automatically maintains the system-defined moving window baseline. The default window size of the system-defined moving window baseline is the current AWR retention period, which is 8 days by default. If you plan to use an adaptive threshold, consider using a longer moving window, such as 30 days, to accurately calculate the threshold. You can re-adjust the moving window baseline to adjust the size of the moving window to a value smaller than or equal to the retention days of AWR. Therefore, to increase the size of the moving window, you must first increase the AWR retention period.
 
Baseline template: You can use baseline template to create a baseline for a future continuous period of time. Oracle has two baseline templates: single and repeating.

With Single baseline template, you can create a baseline for a separate continuous period in the future. This technology is useful in some situations. For example, if you want to capture AWR data during the next week's scheduled system test, you can create a single baseline template to automatically capture statistics for the time period during which the test occurs.

The repeating baseline template allows you to create and delete baseline based on repeated time plans. This is useful when you want Oracle database to automatically continuously capture statistics for consecutive periods. For example, you may need to capture AWR data every Monday morning for up to one month. In this case, you can create a repeating baseline template to automatically create a baseline every Monday and automatically delete the outdated baseline within the specified expiration period.

For reprint, please indicate the author's source and original article link:

Http://blog.csdn.net/xiangsir/article/details/8648615