[Oracle] Introduction to AWR Report Analysis Methods

Because the AWR report is very long, it is impossible to look at it from the beginning to the end without missing a word, you need to have a choice to look at the key part. It is best to compare it with the AWR report under normal circumstances of the system to find a difference. The following describes how to analyze the AWR report. For more information, see AWR data.

Because the AWR report is very long, it is impossible to look at it from the beginning to the end without missing a word, you need to have a choice to look at the key part. It is best to compare it with the AWR report under normal circumstances of the system to find a difference. The following describes how to analyze the AWR report. For more information, see AWR data.

Because the AWR report is very long, it is impossible to look at it from the beginning to the end without missing a word, you need to have a choice to look at the key part. It is best to compare it with the AWR report under normal circumstances of the system to find a difference. The following describes how to analyze the AWR report. For more information, see

AWR data consists of two parts:
1) system load and performance statistics stored in the memory are mainly queried through the v $ view;
2) The mmon Process regularly saves AWR data in the memory to the SYSAUX tablespace using snapshots, which are accessed through the DBA _ * view.

1. generate an AWR Snapshot
By default, a snapshot is automatically generated every hour to save the information for the last seven days. You can use the following statement to query:
SQL> select SNAP_INTERVAL, RETENTION from dba_hist_wr_control;

SNAP_INTERVAL RETENTION
----------------------------------------------------------
+ 00000 01:00:00. 0 + 00007 00:00:00. 0
You can use the following statement to modify the time interval and storage time (in minutes ):
Exec dbms_workload_repository.modify_snapshot_settings (interval => 30, retention => 10*24*60 );
You can also manually generate snapshots at any time as needed:
Exec dbms_workload_repository.create_snapshot;

2. generate an AWR report
Run the following command with sysdba:
@? /Rdbms/admin/awrrpt. SQL

3. AWR Report Analysis
Policy
Because the AWR report is very long, it is impossible to look at it from the beginning to the end without missing a word, you need to have a choice to look at the key part. It is best to compare it with the AWR report under normal circumstances of the system to find a difference.

The AWR report adopts the form of a total score. The previous part is the overall situation of the system, followed by the details of each part. Do not go into details at the beginning. First, analyze the overall situation of the system, skip analysis is required based on different concerns.
It also depends on the specific business to determine whether a phenomenon is normal.

Overall system status
1) Load Profile: Analysis System
Understand the overall load of the system, such as the number of transactions/statements per second, the number of Physical Reads/Writes per transaction per second (Physical Reads/Writes), and the number of Logical Reads/Writes (Logical Reads/Writes ), SQL statement parsing (Parse), especially the number of hard resolutions.

2) Instance Efficiency Percentages: each Metric should be close to 100%, except for: execute to parse (more than 70%) and parse cpu to parse elapsed. If it does not match, you can basically determine that the system has performance problems. However, if it is the opposite, it does not indicate that the system is completely normal, but also depends on the actual situation.

Details
1) Top 5 Timed Events: the five most time-consuming wait Events are listed here. Each wait description indicates one reason, for example: db file sequential read indicates waiting for access by index, and db file scattered reade indicates waiting for access by full table scan.
2) Top n SQL: sort the SQL analysis execution plans by time consumption, memory consumption, and physical I/O.
3) In the RAC environment, pay special attention to related metrics in RAC Statistic.
4) sga pga Analysis
5) analyze tablespaces and data files I/O