[Oracle] AWR Report Analysis Method

[Oracle] AWR Report Analysis Method AWR data mainly consists of two parts: 1) system load and performance statistics stored in the memory, mainly through the v $ View query; 2) the mmon Process regularly saves AWR data in memory to the SYSAUX tablespace using snapshots, which is accessed through the DBA _ * view. 1. by default, a snapshot is automatically generated every hour to save the information of 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 a snapshot at any time as needed: exec dbms_workload_repository.c Reate_snapshot; 2. Run the following command with sysdba to generate the AWR Report :@? /Rdbms/admin/awrrpt. SQL 3. AWR report analysis policy because the AWR report is very long, it is impossible to ignore it from start to end. You must have a choice to see 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 and analyze the topic at the end, 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: The analysis system understands the overall Load Status 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 ), 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. Specific Situation 1) Top 5 Timed Events: the five most time-consuming wait Events are listed here. Each wait description represents 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 statements by time consumption, memory consumption, physical I/O, and execute the plan for related SQL analysis. 3) in the RAC environment, pay special attention to related indicators in RAC Statistic 4) sga pga Analysis 5) analyze tablespace and data file I/O