AWR报告是我们研究分析Oracle性能，特别是应用程序工作特性的重要工具手段。进入10g之后，随着CBO的推广、自动作业机制的确立，越来越多的运维人员乃至开发人员将AWR作为分析性能的工具。
除了AWR报告本身，Oracle还提供了一些AWR相关的脚本，用于进行辅助操作。本篇主要介绍awr Info脚本。
1、环境介绍
本篇使用Oracle 11gR2进行实验，具体版本为11.2.0.4。
[oracle@SICS-MIGPC-DB ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Sat Oct 10 08:52:31 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 – Production
2、脚本执行
执行的生成脚本是在Oracle客户端。为避免由于版本原因带来的差异问题，笔者建议最好是在Oracle服务器端生成文件。
[oracle@SICS-MIGPC-DB ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Sat Oct 10 09:16:45 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> @?/rdbms/admin/awrinfo.sql
之后要求输入报告名称。
This script will report general AWR information
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Specify the Report File Name
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrinfo.txt. To use this name,
press to continue, otherwise enter an alternative.
Enter value for report_name:
最后会自动输出信息：
(若干输出)
(2b) ASH details (past 1 day)
**********************************
INST MIN_TIME MAX_TIME NUM_SAMPLES NUM_ROWS AVG_ACTIVE
---- ---------------- ---------------- ------------ ------------ ----------
1 10:10:12 (10/09) 08:49:19 (10/10) 8,145 1,604 0.20
**********************************
(2c) ASH sessions (Fg Vs Bg) (past 1 day across all instances in RAC)
**********************************
Foreground % 90.0
Background % 10.0
MMNL % 0.0
End of Report
Report written to awrinfo.txt
在当前目录下，会看到awrinfo.txt文件。下面会分部分进行解析：
3、结果解析
下面分为若干部分进行结果说明。
第一部分：报告信息说明。抽取出AWR配置信息和保留自动删除信息。
~~~~~~~~~~~~~~~
AWR INFO Report
~~~~~~~~~~~~~~~
Report generated at
09:18:28 on Oct 10, 2015 ( Saturday ) in Timezone +08:00
Warning: Non Default AWR Setting!
--------------------------------------------------------------------------------
Snapshot interval is 60 minutes and Retention is 8 days
DB_ID DB_NAME HOST_PLATFORM INST STARTUP_TIME LAST_ASH_SID PAR
------------ --------- ---------------------------------------- ----- ----------------- ------------ ---
* 2746051042 SICSDB SICS-MIGPC-DB - Linux x86 64-bit 1 10:28:41 (01/08) 23726128 NO
第二部分：AWR镜像snapshot信息。依据规则，每次snapshot记录是保存在sysaux表空间里面的。经常会遇到SYSAUX不断增大超过限制的问题，一般都是由于AWR Purge引起的问题。
########################################################
(I) AWR Snapshots Information
########################################################
*****************************************************
(1a) SYSAUX usage - Schema breakdown (dba_segments)
*****************************************************
|
| Total SYSAUX size 3,131.4 MB ( 10% of 32,768.0 MB MAX with AUTOEXTEND ON )
|
| Schema SYS occupies 2,754.2 MB ( 88.0% )
| Schema XDB occupies 126.9 MB ( 4.1% )
| Schema APEX_030200 occupies 84.4 MB ( 2.7% )
| Schema MDSYS occupies 74.3 MB ( 2.4% )
| Schema SYSMAN occupies 46.1 MB ( 1.5% )
| Schema SYSTEM occupies 14.8 MB ( 0.5% )
| Schema ORDDATA occupies 13.6 MB ( 0.4% )
| Schema OLAPSYS occupies 5.1 MB ( 0.2% )
| Schema EXFSYS occupies 3.6 MB ( 0.1% )
| Schema CTXSYS occupies 3.6 MB ( 0.1% )
| Schema WMSYS occupies 3.5 MB ( 0.1% )
| Schema DBSNMP occupies 0.8 MB ( 0.0% )
| Schema ORDSYS occupies 0.4 MB ( 0.0% )
|
********************************************************
(1b) SYSAUX occupants space usage (v$sysaux_occupants)
********************************************************
|
| Occupant Name Schema Name Space Usage
| -------------------- -------------------- ----------------
| SM/AWR SYS 2,290.9 MB
| SM/ADVISOR SYS 276.1 MB
| XDB XDB 126.9 MB
（篇幅原因，有省略……）
|
| Others (Unaccounted space) 66.7 MB
|
--内部schema情况
******************************************
(1c) SYSAUX usage - Unregistered Schemas
******************************************
| This section displays schemas that are not registered
| in V$SYSAUX_OCCUPANTS
|
| Schema APEX_030200 occupies 84.4 MB
|
| Total space 84.4 MB
|
*************************************************************
(1d) SYSAUX usage - Unaccounted space in registered schemas
*************************************************************
|
| This section displays unaccounted space in the registered
| schemas of V$SYSAUX_OCCUPANTS.
|
| Unaccounted space in SYS/SYSTEM -17.8 MB
|
| Total space -17.8 MB
|
--AWR snapshot信息
*************************************
(2) Size estimates for AWR snapshots
*************************************
|
| Estimates based on 60 mins snapshot INTERVAL:
| AWR size/day 284.9 MB (12,155 K/snap * 24 snaps/day)
| AWR size/wk 1,994.1 MB (size_per_day * 7) per instance
|
| Estimates based on 24 snaps in past 24 hours:
| AWR size/day 284.9 MB (12,155 K/snap and 24 snaps in past 24 hours)
| AWR size/wk 1,994.1 MB (size_per_day * 7) per instance
|
**********************************
(3a) Space usage by AWR components (per database)
**********************************
COMPONENT MB % AWR KB_PER_SNAP MB_PER_DAY MB_PER_WEEK TABLE% : INDEX%
--------- --------- ------ ------------ ---------- ----------- ----------------
FIXED 1,208.1 52.7 6,410 150.2 1,051.6 52% : 48%
EVENTS 316.4 13.8 1,679 39.3 275.4 52% : 48%
SQL 218.3 9.5 1,158 27.1 190.0 66% : 34%
SQLPLAN 206.0 9.0 1,093 25.6 179.3 70% : 30%
SPACE 111.5 4.9 592 13.9 97.1 70% : 30%
ASH 29.3 1.3 155 3.6 25.5 86% : 14%
SQLTEXT 3.3 0.1 17 0.4 2.8 92% : 8%
SQLBIND 0.9 0.0 5 0.1 0.8 43% : 57%
RAC 0.6 0.0 3 0.1 0.5 50% : 50%
**********************************
(3b) Space usage within AWR Components (> 500K)
**********************************
COMPONENT MB SEGMENT_NAME - % SPACE_USED SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
FIXED 208.0 WRH$_LATCH.WRH$_LATCH_2746051042_0 - 96% TABLE PARTITION
（篇幅原因，有省略……）
WRH$_ACTIVE_SESSION_HISTORY_PK.WRH$_ACTIVE_2746051042_0 - 86% INDEX PARTITION
SQLTEXT 3.0 WRH$_SQLTEXT - 43% TABLE
SQLBIND 0.5 WRH$_SQL_BIND_METADATA_PK - 31% INDEX
**********************************
(4) Space usage by non-AWR components (> 500K)
**********************************
COMPONENT MB SEGMENT_NAME SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
NON_AWR 192.0 SYS.SYS_LOB0000006417C00038$$ LOBSEGMENT
NON_AWR 120.0 SYS.WRI$_ADV_SQLT_PLANS TABLE
NON_AWR 88.0
（篇幅原因，有省略……）
NON_AWR 0.5 SYSMAN.SYS_LOB0000081752C00004$$ LOBSEGMENT
**********************************
(5a) AWR snapshots - last 50
**********************************
Total snapshots in DB 2746051042 Instance 1 = 193
DBID SNAP_ID INST FLUSH_ELAPSED ENDTM STARTUP_TIME STATUS ERRCNT
---------- ---------- ----- -------------------- ----------------- ----------------- ------ ------
2746051042 6550 1 +00000 00:00:01.6 08:00:47 (10/08) 10:28:41 (01/08) 0 0
（篇幅原因，有省略……）
2746051042 6599 1 +00000 00:00:01.4 09:00:35 (10/10) 10:28:41 (01/08) 0 0
**********************************
(5b) AWR snapshots with errors or invalid
**********************************
no rows selected
**********************************
(5c) AWR snapshots -- OLDEST Non-Baselined snapshots
**********************************
DBID INST SNAP_ID ENDTM STATUS ERROR_COUNT
---------- ----- ---------- ----------------- ------ -----------
2746051042 1 6407 09:00:49 (10/02) 0 0
**********************************
(6) AWR Control Settings - interval, retention
**********************************
DBID LSNAPID LSPLITID LSNAPTIME LPURGETIME FLAG INTERVAL RETENTION VRSN
----------- -------- -------- -------------- -------------- ----- ----------------- ----------------- ----
2746051042 6599 0 10/10 09:00:37 10/10 08:21:15 2 +00000 01:00:00.0 +00008 00:00:00.0 5
**********************************
(7a) AWR Contents - row counts for each snapshots
**********************************
SNAP_ID INST ASH SQL SQBND FILES SEGST SYSEVT
---------- ----- ---------- ---------- ---------- ---------- ---------- ----------
（篇幅原因，有省略…….）
6599 1 4 74 159 14 80 120
**********************************
(7b) AWR Contents - average row counts per snapshot
**********************************
SNAP_COUNT INST ASH SQLSTAT SQLBIND FILES SEGSTAT SYSEVENT
---------- ----- ---------- ---------- ---------- ---------- ---------- ----------
193 1 12.66 74.44 241.96 14 86.03 119.24
**********************************
(7c) AWR total item counts - names, text, plans
**********************************
SQLTEXT SQLPLAN SQLBMETA SEGOBJ DATAFILE TEMPFILE
---------- ---------- ---------- ---------- ---------- ----------
1714 118725 2798 1198 14 1
第三部分：Advisor信息
借助AWR基础数据，会有一系列的advisor框架生成。
########################################################
(II) Advisor Framework Info
########################################################
**********************************
(1) Advisor Tasks - Last 50
**********************************
OWNER/ADVISOR TASK_ID/NAME CREATED EXE_DURATN EXE_CREATN HOW_C STATUS
-------------- -------------------------------- ---------------- ---------- ---------- ----- ------------
SYS/SQL Tuning 1/SYS_AUTO_SQL_TUNING_TASK 11:42:49 (08/24) 5 ########## AUTO COMPLETED
SYS/ADDM 7575/ADDM:2746051042_1_6554 12:01:02 (10/08) 0 0 AUTO COMPLETED
（篇幅原因，有省略……）
SYS/ADDM 7652/ADDM:2746051042_1_6599 09:00:37 (10/10) 0 0 AUTO COMPLETED
**********************************
(2) Advisor Task - Oldest 5
**********************************
OWNER/ADVISOR TASK_ID/NAME CREATED EXE_DURATN EXE_CREATN HOW_C STATUS
-------------- -------------------------------- ---------------- ---------- ---------- ----- ------------
SYS/ADDM 6599/ADDM:2746051042_1_5879 09:00:39 (09/10) 0 0 AUTO COMPLETED
SYS/ADDM 6600/ADDM:2746051042_1_5880 10:00:42 (09/10) 0 0 AUTO COMPLETED
SYS/ADDM 6601/ADDM:2746051042_1_5881 11:00:45 (09/10) 0 0 AUTO COMPLETED
SYS/ADDM 6602/ADDM:2746051042_1_5882 12:00:49 (09/10) 0 0 AUTO COMPLETED
SYS/ADDM 6603/ADDM:2746051042_1_5883 13:00:52 (09/10) 0 0 AUTO COMPLETED
**********************************
(3) Advisor Tasks With Errors - Last 50
**********************************
no rows selected
########################################################
(III) ASH Usage Info
########################################################
**********************************
(1a) ASH histogram (past 3 days)
**********************************
NUM_ACTIVE_SESSIONS NUM_SAMPLES
-------------------- ------------
0000 - 0004 1,842
**********************************
(1b) ASH histogram (past 1 day)
**********************************
NUM_ACTIVE_SESSIONS NUM_SAMPLES
-------------------- ------------
0000 - 0004 1,496
**********************************
(2a) ASH details (past 3 days)
**********************************
INST MIN_TIME MAX_TIME NUM_SAMPLES NUM_ROWS AVG_ACTIVE
---- ---------------- ---------------- ------------ ------------ ----------
1 10:00:34 (10/07) 08:49:19 (10/10) 25,462 1,965 0.08
**********************************
(2b) ASH details (past 1 day)
**********************************
INST MIN_TIME MAX_TIME NUM_SAMPLES NUM_ROWS AVG_ACTIVE
---- ---------------- ---------------- ------------ ------------ ----------
1 10:10:12 (10/09) 08:49:19 (10/10) 8,145 1,604 0.20
**********************************
(2c) ASH sessions (Fg Vs Bg) (past 1 day across all instances in RAC)
**********************************
Foreground % 90.0
Background % 10.0
MMNL % 0.0
End of Report
3、结论
AWR是我们进行性能分析的利器。从AWR本身进行分析的工具，awrinfo还是不错的的。所有相关的内容、配置和数据，都可以方便的进行整理汇总，并且一次性显示在我们面前。
：