AWR信息报告

最新推荐文章于 2023-10-02 12:30:19 发布

ciqu9915

最新推荐文章于 2023-10-02 12:30:19 发布

阅读量219

点赞数

文章标签：数据库运维

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

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

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 <return> 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还是不错的的。所有相关的内容、配置和数据，都可以方便的进行整理汇总，并且一次性显示在我们面前。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-1814274/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17203031/viewspace-1814274/

ciqu9915

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
AWR信息报告

AWR报告是我们研究分析Oracle性能，特别是应用程序工作特性的重要工具手段。进入10g之后，随着CBO的推广、自动作业机制的确立，越来越多的运维人员乃至开发人员将AWR作为分析性能的工具。 ...
复制链接

扫一扫