Oracle: Ways to analyze AWR reports

AWR's data consists of two main components:

1 The system load and performance statistics stored in memory, mainly through the v$ view query;

2 The Mmon process periodically saves in-memory awr data to the Sysaux tablespace in a snapshot (snapshot) manner, mainly through the dba_* view.

1. Generation of AWR snapshots

By default, a snapshot is automatically generated every hour, saving information for the last 7 days, and can be queried by the following statement:

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 modify the time interval and save time in minutes by using the following statement:

exec dbms_workload_repository.modify_snapshot_settings (interval =>, retention = > 10*24*60);

You can also manually generate snapshots at any time as needed:

exec dbms_workload_repository.create_snapshot;

2. Generation of AWR reports

Run the following command with SYSDBA:

@?/rdbms/admin/awrrpt.sql

3. Analysis of the AWR report

Strategy

Because the AWR report is very long, it is impossible to see the word from beginning to end, to have a choice to see the key part. It is best to compare the AWR report to the normal situation of the system and find the difference.

AWR report using the form of total score, the front is the overall situation of the system, followed by various parts of the details, a start not to fall into details, first analysis of the overall situation of the system, for the following thematic analysis, according to the different concerns, take a jump-type analysis.

Depending on the specific business, determine whether a phenomenon is normal.

Overall state of the system

1 Load Profile: Analysis Department

Understand the overall load status of the system, such as the number of transactions/statements per second, per second/transaction physical reads and writes (physical reads/writes), logical read and write times (Logical reads/writes), SQL statement Parsing (Parse), Especially the number of hard resolution.

2) Instance efficiency percentages: Each index should be close to 100%, except: Execute to Parse (70%) and parse CPU to parse elapsed. If not, the system can determine the performance of the problem, but if the reverse, that is, the system is not completely normal, but also to see the actual situation.

Specific aspects of the situation

1 Top 5 Timed events: Here is a list of the 5 waiting event that consumes the most time, each waiting for a description, for one reason, such as: DB file sequential read indicates a wait by index access, db file scattered Reade indicates that the full table scan access occurs with a wait event.

2 top N sql: According to time consumption, memory consumption, physical I/O, and other sorting, the relevant SQL analysis execution plan

3 If it is a RAC environment, special attention should be paid to the relevant indicators in RAC statistic

4) SGA PGA analysis

5 Analysis of table space, data file I/O

Back to the column page: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/