ORACLE AWR

Concept:

Automatic Workload Repository (AWR) is a repository of historical performance data that includescumulative statistics for the system, sessions, individual  SQL statements, segments, and services.These statistics are the foundation of performance tuning. By automating thegathering of database statistics for problem detection and tuning, AWR servesas the foundation for database self-management.

 

 

Thoery:

As shown in Figure 18–8, the databasestores recent AWR statistics in the SGA. By default, theMMON process gathers statistics every hour and creates an AWR snapshot .A snapshot is a set of performance statistics captured at a specific time. The database writes snapshots to the SYSAUX tablespace. AWR manages snapshot space, purgingolder snapshots according to a configurable snapshot retention policy.

An AWR baseline is a collection ofstatistic rates usually taken over a period when the system is performing wellat peak load. You can specify a pair or range of AWR snapshots as a baseline.By using an AWR report to compare statistics captured during a period of badperformance to a baseline, you can diagnose problems.

An automated maintenance infrastructureknown as AutoTask illustrates how Oracle Database uses AWR for self-management.By analyzing AWR data, AutoTask can determine the need for maintenance tasksand schedule them to run in Oracle Scheduler maintenance windows. Examples oftasks include gathering statistics for the optimizer and running the AutomaticSegment Advisor.

 

 

目前Oracle10g之后，AWR报告取代了原先的Statspack报告成为一个主流性能分析报告。通常可以从OEM（Oracle Enterprise Manager Console）平台上生成查看AWR报告。在OEM中，使用图形化方法更加容易。本篇中介绍使用手工脚本方式生成AWR的方法，脱离OEM的限制。

 

 

 

 

生成awr报告时需要注意的问题：
1.可能会出现ora-20200错误，因为数据库中途被关闭过，这时候需要找到中途没有被关闭的时间段（从日志文件中可以找出）

2.生成的文件位于当前的操作目录下，以我的为例就是C:\Users\lenovo

 

 

Start:
C:\Users\lenovo>sqlplus/ as sysdba

 

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 12月 18 20:13:572013

 

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

 

 

连接到:

Oracle Database 11g Enterprise Edition Release11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

 

sys@ORCL>@D:\app\lenovo\product\11.2.0\dbhome_1\RDBMS\ADMIN\awrrpt.sql

(awrrpt.sql位于<oracle_home>\rdbms\admin目录下)

 

Current Instance

~~~~~~~~~~~~~~~~

 

   DBId    DB Name      Inst Num Instance

----------- ------------ --------------------

 1350043201 ORCL                1 orcl

 

 

Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

Would you like an HTML report, or a plaintext report?

Enter 'html' for an HTML report, or 'text'for plain text

Defaults to 'html'

输入 report_type 的值:

 

Type Specified:  html

 

 

Instances in this Workload Repositoryschema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

   DBId     Inst Num DB Name      Instance     Host

------------ -------- ------------------------ ------------

* 1350043201        1 ORCL         orcl         LENOVO-PC

 

Using 1350043201 for database Id

Using          1 for instance number

 

 

Specify the number of days of snapshots tochoose from

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Entering the number of days (n) will resultin the most recent

(n) days of snapshots being listed.  Pressing <return> without

specifying a number lists all completedsnapshots.

 

 

输入 num_days 的值:  7

 

Listing the last 7 days of CompletedSnapshots

 

                                                       Snap

Instance    DB Name        Snap Id    Snap Started    Level

------------ ------------ --------------------------- -----

orcl        ORCL               856 12 12月 201300:00     1

 

                                857 13 12月 201316:43     1

                                858 13 12月 201318:00     1

                                859 13 12月 201319:00     1

                                860 13 12月 2013 20:00     1

                                861 13 12月 201321:00     1

                                862 13 12月 201322:00     1

                                863 13 12月 201323:00     1

 

                                864 14 12月 201309:27     1

                                865 14 12月 201310:00     1

                                866 14 12月 201311:00     1

                                867 14 12月 201312:00     1

                                868 14 12月 201313:00     1

                                869 14 12月 201314:00     1

                                870 14 12月 201315:00     1

                                871 14 12月 201316:00     1

                                872 14 12月 201317:00     1

                                873 14 12月 201318:00     1

                                874 14 12月 201319:00     1

                                875 14 12月 201320:00     1

                                876 14 12月 201321:00     1

                                877 14 12月 2013 22:00     1

                                878 14 12月 201323:00     1

                                879 15 12月 201300:00     1

 

                                880 15 12月 201315:35     1

 

                                881 15 12月 201317:00     1

                                882 15 12月 201318:00     1

                                883 15 12月 201319:00     1

                                884 15 12月 201320:00     1

                                885 15 12月 201321:00     1

                                886 15 12月 201322:00     1

                                887 15 12月 201323:00     1

 

                                888 16 12月 201314:28     1

                                889 16 12月 201315:00     1

                                890 16 12月 201316:00     1

                                891 16 12月 201317:00     1

                                892 16 12月 201318:00     1

                                893 16 12月 201319:00     1

                                894 16 12月 201320:00     1

                                895 16 12月 201321:00     1

                                896 16 12月 201322:00     1

                                897 16 12月 201323:00     1

 

                                898 18 12月 2013 09:13     1

                                899 18 12月 201310:00     1

                                900 18 12月 201311:00     1

                                901 18 12月 201312:00     1

                                902 18 12月 201313:00     1

                                903 18 12月 201314:00     1

                                904 18 12月 201315:00     1

                                905 18 12月 201316:00     1

 

                                                       Snap

Instance    DB Name        Snap Id    Snap Started    Level

------------ ------------ --------------------------- -----

 

orcl        ORCL               906 18 12月 201317:02     1

 

                                907 18 12月 201319:22     1

                               908 18 12月 201320:00     1

 

 

 

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

输入 begin_snap 的值:  888

Begin Snapshot Id specified: 888

 

输入 end_snap 的值:  897

End  Snapshot Id specified: 897

 

 

 

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name isawrrpt_1_888_897.html.  To use this name,

press <return> to continue, otherwiseenter an alternative.

 

输入 report_name 的值:  20131218awr.html

 

Using the report name 20131218awr.html

….

….

End of Report

</body></html>

Report written to 20131218awr.html