Introduction to OracleAWR

AutomaticWorkloadRepository (AWR) collects, processes, and maintains performance system information, providing powerful help for performance tuning problem detection.

Automatic Workload Repository (AWR) collects, processes, and maintains performance system information, providing powerful help for performance tuning problem detection.

Automatic Workload Repository (AWR) collects, processes, and maintains performance system information, providing powerful help for performance tuning problem detection.

Statistics collected and processed by AWR include:

1. segment statistics;

2. Time Model statistics (in the v $ sys_time_model and v $ sess_time_model views );

3. Some system and session statistics collected in the v $ sysstat and v $ sesstat views;

4. Some SQL statements with the highest load in the system are counted based on the execution time, cpu time, number of executions, and other criteria.

5. ASH statistics, including historical records of recent session activities

If the system parameter STATISTICS_LEVEL is set to TYPICAL or ALL, AWR is enabled by default to collect database statistics. The default value of this parameter is TYPICAL. If you set it to BASIC, many Oracle database functions will be disabled, including AWR. However, you can still manually obtain AWR statistics through the DBMS_WORKLOAD_REPOSITORY package. However, because many statistics (such as segment statistics and memory advisor information) in the memory are disabled, the statistics obtained from snapshots may be incomplete.

Snapshot (snapshots)

A snapshot is a collection of historical data within a specific time range, and performance comparison is performed by ADDM. By default, the database generates a snapshot every hour and keeps the statistics for 8 days in the workload warehouse. You can also create snapshots manually, but this is usually unnecessary. The information between these snapshots is then handed over to the Automatic Database Diagnostic Monitor (ADDM) for analysis.

AWR compares the information difference between snapshots, and then obtains some SQL statements that have the greatest impact on system load, rather than obtaining all SQL statements, reducing the statistical time.

Baselines)

A baseline contains performance data of a specific time range, which is used to compare performance problems with other similar time periods. Snapshots in the baseline are excluded by the automatic AWR purge process and retained indefinitely.

Oracle databases contain three types of baselines:

Fixed Baselines)

A fixed baseline is equivalent to a fixed and continuous time range specified in the past. Consider this period of time carefully before creating a fixed baseline, because the baseline represents an ideal state of the system. Then, you can use this baseline and other baselines or snapshots within a certain time range to analyze performance degradation.

Moving Window Baseline)

Moving the window baseline is equivalent to all AWR data that exists during the AWR retention period. This is useful when using an adaptive threshold, because the database can use all AWR data during AWR retention to calculate the measurement threshold. The oracle database automatically maintains a system-defined mobile window baseline. The default window size of the system-defined mobile window baseline is equal to the current AWR retention time. The default window size is 8 days. If you want to use the adaptive threshold, you can consider using a larger moving window, such as 30 days, you can calculate the threshold more accurately. You can change the size of the window to be moved. The value must be equal to or less than the number of days that AWR retains. Therefore, to increase the size of the moving window, you must first increase the AWR retention time.

Baseline Templates)

You can create a baseline as a baseline template that can be used for a consecutive time period in the future. There are two types of baseline templates: single and duplicate. You can create a single baseline template for baselines in a separate continuous period in the future. This technology is useful if you want to prepare for a future period in advance. For example, if you plan to perform a system test over the weekend and prepare to obtain AWR data, you can create a single baseline template, it is used to automatically obtain data within the specified time range during the test. You can also use the repeated baseline template to create or delete a repeated time plan. This is useful when you want to automatically obtain a continuous time range. For example, you may want to obtain AWR data every Monday morning in a month. In this case, you can create a duplicate baseline template to automatically create a baseline for each Monday, the expired baseline is automatically deleted after an expiration time (for example, one month) is set.

Adaptive Thresholds)

The Adaptive Threshold helps you monitor and detect performance problems at the lowest cost. The adaptive threshold can be used to automatically set the thresholds for alarms and critical alarms from the statistical information obtained from the metric values captured by the baseline in the mobile window. These statistics are generated every week, and new thresholds may be generated due to changes in system performance over time.

For example, many databases are an OLTP system during the day, and some batch processes (such as generating reports) need to be executed at night ). The performance measurement of each transaction response time may be useful for detecting OLTP performance degradation issues during the day, but this threshold is often too low for batch work and frequent alerts are triggered. The adaptive threshold can detect this workload mode and automatically set different thresholds for the day and night.

There are two types of adaptive thresholds:

Maximum percentage: the threshold value is measured in multiples of the maximum value,

Importance level: the threshold value is set to a statistical percentile to observe the above threshold value based on the moving window baseline data to reflect the degree of abnormality. The percentile can be specified as the following: high (0.95), 100 of which only 5 can exceed this value; very high (0.99): 100 of which only 1 can exceed this value; serious (0.999): Only one in 1000 minutes can exceed this value; extreme (0.9999): Only one in 10000 minutes can exceed this value.

When a system is designed for peak workloads and you want to trigger an alarm when the current workload approaches or exceeds the previous high value, the maximum percentage threshold will be very useful. For example, the measurement that generates redo volume per second is a typical example.

The importance level threshold is useful in the following scenarios: when the system runs normally, it performs very stably, but may fluctuate in a large range when the performance gets worse. For example, the measurement of the response time of each transaction is stable in an optimized OLTP system, but may fluctuate greatly when the performance problem is highlighted. The importance threshold is used to trigger an alarm when the environment generates abnormal metric values and system performance.

Space Consumption)

The following factors can be used to determine the space consumption of AWR: Number of active sessions in the system at any given time; snapshot interval, the smaller the time interval, the more frequent snapshots are generated, increase the space occupied by data collected by AWR; retention time of historical data

By default, snapshots are captured hourly and saved in the database for 8 days. With these default settings, a system with a typical concurrency of 10 sessions needs about-MB to store AWR data. However, when reducing the retention time, note that if the data in AWR is insufficient, the accuracy and accuracy of some components and functions may be affected: ADDM, SQL Tuning Advisor, Undo Advisor, and Segment Advisor.

If possible, we recommend that you set enough AWR retention time to capture at least one full workload cycle. When your system workload cycle is one week, for example, the workday is an OLTP workload, and you run Batch jobs on weekends, the default 8-day retention time does not need to be modified. If the peak period of your system is at the end of each month, you may need to change the retention period to one month.

In exceptional cases, you can change the snapshot interval to 0 to disable automatic snapshot collection. In this case, the automatic collection of workload and statistics will be stopped, and the automatic management functions of many Oracle databases will not be available. In addition, you cannot create snapshots manually. Therefore, Oracle strongly recommends that you do not disable automatic snapshot collection.

Manage workload warehouses

Manage snapshots

Create a snapshot: DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();

Delete snapshot: DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 22, high_snap_id => 32, dbid => 3310949047); ASH data within this time range will also be cleared