Introduction to Oracle AWR

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 Warehouse Management snapshot creation snapshot: dbms_workload_repository.create_snapshot (); Delete snapshot: dbms_workload_repository.drop_snapshot_range (low_snap_id => 22, high_snap_id => 32, dbid => 3310949047 ); the ash data within this time range will also be cleared and modified. snapshot settings: dbms_workload_repository.modify_snapshot_settings (retention => 43200, interval => 30, topnsql => 100, dbid => 3310949047 ); the related time is measured in minutes.

Manage baselines create baselines

Dbms_workload_repository.create_baseline (start_snap_id = & gt; 270,
End_snap_id => 280, baseline_name => 'peak baseline ',
Dbid => 3310949047, expiration => 30 );

Delete baseline

Dbms_workload_repository.drop_baseline (baseline_name => 'peak baseline ',
Cascade => false, dbid => 3310949047); -- If cascade is set to true, the associated snapshots will be deleted at the same time.

Rename a baseline

Dbms_workload_repository.rename_baseline (
Old_baseline_name => 'peak baseline ',
New_baseline_name => 'peak mondays ',
Dbid => 3310949047 );

Show baseline measurement

Select * from table (dbms_workload_repository.select_baseline_metrics (
Baseline_name => 'peak baseline ',
Dbid = & gt; 3310949047,
Instance_num => '1 '));

Modify the window size of the default mobile window baseline

Dbms_workload_repository.modify_baseline_window_size (
Window_size => 30,
Dbid => 3310949047); -- window_size is measured in days. The value must be less than or equal to the retention time of the Current AWR.

Create a single baseline template dbms_workload_repository.create_baseline_template (
Start_time => '2017-04-02 17:00:00 PST ',
End_time => '2017-04-02 20:00:00 PST ',
Baseline_name => 'baseline _ 000000 ',
Template_name => 'template _ 000000', expiration => 30,
Dbid => 3310949047); -- If the expiration parameter is not specified, the created baseline will not expire.

Create duplicate baseline Template

Dbms_workload_repository.create_baseline_template (
Day_of_week => 'monday', hour_in_day => 17,
Duration => 3, expiration => 30,
Start_time => '2017-04-02 17:00:00 PST ',
End_time => '2017-12-31 20:00:00 PST ',
Baseline_name_prefix => 'baseline _ 2009_mondays _',
Template_name => 'template _ 2009_mondays ',
Dbid => 3310949047); -- Duration parameter unit: hour

Delete baseline Template

Dbms_workload_repository.drop_baseline_template (
Template_name => 'template _ 2009_mondays ',
Dbid => 3310949047 );

The Oracle database allows AWR data to be transmitted on different systems. This is useful if you want to analyze AWR data in a separate system. Extract data from the source database: @ $ ORACLE_HOME/rdbms/admin/awrextr. import SQL statements to the target database: @ $ ORACLE_HOME/rdbms/admin/awrload. SQL data is exported and imported using a data pump. Therefore, you also need to create a directoryawr related view v $ active_session_history to display the activity of Active Database sessions, sampling once per second

V $ metric and V $ metric_history

Provides metric data to track system performance. Views are organized into several groups, which are defined in the V $ metricgroup view.

Dba_hist View

History displays history information of active sessions in the memory dba_hist_baseline displays captured baseline information History displays detailed information about a specific baseline dba_hist_baseline_template information about the baseline template dba_hist_database_instance database environment timeline predicts different cache sizes based on historical data physical read dba_hist_dispatcher information about the scheduling process under each snapshot dynamic remastering process Statistics dba_hist_iostat_detail historical I/O information statistics by no type and function sharing server statistics dba_hist_snapshot snapshot information dba_hist_ SQL _plan execution plan dba_hist_wr_control AWR control information

Generate an AWR report to generate a typical AWR report:

1. At the SQL prompt, enter:@$ORACLE_HOME/rdbms/admin/awrrpt.sql2. Specify whether you want an HTML or a text report:Enter value for report_type: textIn this example, a text report is chosen.3. Specify the number of days for which you want to list snapshot IDs.Enter value for num_days: 2A list of existing snapshots for the specified time range is displayed. In thisexample, snapshots captured in the last 2 days are displayed.4. Specify a beginning and ending snapshot ID for the workload repository report:Enter value for begin_snap: 150Enter value for end_snap: 160In this example, the snapshot with a snapshot ID of 150 is selected as thebeginning snapshot, and the snapshot with a snapshot ID of 160 is selected as theending snapshot.5. Enter a report name, or accept the default report name:Enter value for report_name:Using the report name awrrpt_1_150_160In this example, the default name is accepted and an AWR report namedawrrpt_1_150_160 is generated.

Generate a report in the RAC environment: @ $ ORACLE_HOME/rdbms/admin/awrgrpt. SQL: @ $ ORACLE_HOME/rdbms/admin/awrrpti. SQL

Generates the rac awr report for the specified instance @ $ ORACLE_HOME/rdbms/admin/awrkgti. SQL

Generate an AWR report for an SQL statement @ $ ORACLE_HOME/rdbms/admin/awrsqrpt. SQL -- you must specify SQL _id

Generate an AWR report for the SQL statement in the specified instance @ $ ORACLE_HOME/rdbms/admin/awrsqrpi. SQL

Generate AWR comparison report

We can also generate an AWR comparison report to compare the statistical information between two snapshots with the previous normal snapshot.

The process of generating an AWR comparison report is as follows:

1. At the SQL prompt, enter:@$ORACLE_HOME/rdbms/admin/awrddrpt.sql2. Specify whether you want an HTML or a text report:Enter value for report_type: htmlIn this example, an HTML report is chosen.3. Specify the number of days for which you want to list snapshot IDs in the firsttime period.Enter value for num_days: 2A list of existing snapshots for the specified time range is displayed. In thisexample, snapshots captured in the last 2 days are displayed.4. Specify a beginning and ending snapshot ID for the first time period:Enter value for begin_snap: 102Enter value for end_snap: 103In this example, the snapshot with a snapshot ID of 102 is selected as thebeginning snapshot, and the snapshot with a snapshot ID of 103 is selected as theending snapshot for the first time period.5. Specify the number of days for which you want to list snapshot IDs in the secondtime period.Enter value for num_days2: 1A list of existing snapshots for the specified time range is displayed. In thisexample, snapshots captured in the previous day are displayed.6. Specify a beginning and ending snapshot ID for the second time period:Enter value for begin_snap2: 126Enter value for end_snap2: 1277. Enter a report name, or accept the default report name:Enter value for report_name:Using the report name awrdiff_1_102_1_126.txtIn this example, the default name is accepted and an AWR report namedawrdiff_1_102_126 is generated.

Comparison Report under RAC @ $ ORACLE_HOME/rdbms/admin/awrgdrpt. SQL

Specify the instance @ $ ORACLE_HOME/rdbms/admin/awrddrpi. SQL

RAC specified instance @ $ ORACLE_HOME/rdbms/admin/awrgdrpi. SQL

From Performance Tuning Guide