AWR可以收集的信息有:
- Wait events used to identify performance problems.
- Time model statistics indicating the amount of DB time associated with a process from the
V$SESS_TIME_MODEL
andV$SYS_TIME_MODEL
views. - Active Session History (ASH) statistics from the
V$ACTIVE_SESSION_HISTORY
view. - Some system and session statistics from the
V$SYSSTAT
andV$SESSTAT
views. - Object usage statistics.
- Resource intensive SQL statements.
如何生成AWR Report
sqlplus /nolog
conn system/xxx
(需使用sys/system用户来执行脚本)
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
参数1:
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type:
Type Specified: html
参数2:
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days: 1
参数3:
Listing the last day's Completed Snapshots
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
mc3yd213 MC3YD213 4653 17 Dec 2012 00:00 1
4654 17 Dec 2012 01:00 1
4655 17 Dec 2012 02:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 4653
Begin Snapshot Id specified: 4653
Enter value for end_snap: 4654
End Snapshot Id specified: 4654
参数4:
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_4653_4654.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:xxx.html
AWR Report Demo
How to Read AWR report
reference1:http://www.oracle-base.com/articles/10g/automatic-workload-repository-10g.php#reading-awr-reports
Reference
http://www.oracle-base.com/articles/10g/automatic-workload-repository-10g.php
Automatic Workload Repository (AWR) Reports - Start Point (Doc ID 1363422.1)
Document 276103.1 PERFORMANCE TUNING USING 10g ADVISORS AND MANAGEABILITY FEATURES
Assuming you have the appropriate licenses for AWR, you may gather and examine Automatic Workload Repository (AWR) reports for the system.
Without the appropriate licenses, Statspack provides a legacy solution:
Document 786554.1 How to Read PGA Memory Advisory Section in AWR and Statspack Reports
Document 754639.1 How to Read Buffer Cache Advisory Section in AWR and Statspack Reports.
Document 762526.1 How to Interpret the OS stats section of an AWR report
Document 1466035.1 How to Interpret the "SQL ordered by Physical Reads (UnOptimized)" Section in AWR Reports (11.2 onwards)
This section covers the setup of AWR and troubleshooting of problems related to that:
AWR SetupDocument 296765.1 Solutions for possible AWR Library Cache Latch Contention Issues in Oracle 10g
Document 560204.1 MMON TRACE SHOWS KEWRAFC: FLUSH SLAVE FAILED, AWR ENQUEUE TIMEOUT
Document 329984.1 Usage and Storage Management of SYSAUX tablespace occupants SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER