概述

awrddrpt.sql脚本位于$ORACLE_HOME/rdbms/admin目录中。

awrddrpt.sql脚本比较两个时间段的AWR报告。

这个脚本的交互部分最重要的就是需要输入要进行对比的两个awr报告的begin snap_id与end snap_id

一旦收集到四个快照号码，awrddrpt.sql脚本将创建两个AWR报告，然后比较这两个报告。awrddrpt.sql脚本对于比较两个经过时间段内的性能非常有用。

---

示例

我们打算比对3月30号00:00~01:00 和 3月31号00:00~01:00这两个时间段的AWR

以oracle用户登录到数据库主机执行脚本

.....
Connection established.
To escape to local shell, press 'Ctrl+Alt+]'.

Last login: Sat Mar 31 09:48:24 2018 from 172.25.21.204
Oracle Corporation      SunOS 5.10      Generic Patch   January 2005
You have new mail.
##处于安全的考虑，以nolog的方式登录
ocsdb01:[/oracle$]sqlplus /nolog          

SQL*Plus: Release 11.2.0.4.0 Production on Sat Mar 31 11:23:08 2018

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

## 登录特定的用户,输入密码
SQL> connect cc@PR_CC
Enter password: 
Connected.
## 执行awrddrpt脚本
SQL> @?/rdbms/admin/awrddrpt

Current Instance
~~~~~~~~~~~~~~~~

   DB Id       DB Id    DB Name      Inst Num Inst Num Instance
----------- ----------- ------------ -------- -------- ------------
 2143881171  2143881171 CC                  1        1 cc


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'
## 默认html
Enter value for report_type: 

Type Specified:  html


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 2143881171        1 CC           cc           ocsdb01

Database Id and Instance Number for the First Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 2143881171 for Database Id for the first pair of snapshots
Using          1 for Instance Number for the first pair of snapshots


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.

#因为今天是31号,我们只需要输入2天时间范围就可以获取到3月30号00:00~01:00的快照
Enter value for num_days: 2

Listing the last 2 days of Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
cc           CC               10080 30 Mar 2018 00:00      1
                              10081 30 Mar 2018 01:00      1
                              10082 30 Mar 2018 02:00      1
                              10083 30 Mar 2018 03:00      1
                              10084 30 Mar 2018 04:00      1
                              10085 30 Mar 2018 05:00      1
                              10086 30 Mar 2018 06:00      1
                              10087 30 Mar 2018 07:00      1
                              10088 30 Mar 2018 08:00      1
                              10089 30 Mar 2018 09:00      1
                              10090 30 Mar 2018 10:00      1
                              10091 30 Mar 2018 11:00      1
                              10092 30 Mar 2018 12:00      1
                              10093 30 Mar 2018 13:00      1
                              10094 30 Mar 2018 14:00      1
                              10095 30 Mar 2018 15:00      1
                              10096 30 Mar 2018 16:00      1
                              10097 30 Mar 2018 17:00      1
                              10098 30 Mar 2018 18:00      1
                              10099 30 Mar 2018 19:00      1
                              10100 30 Mar 2018 20:00      1
                              10101 30 Mar 2018 21:00      1
                              10102 30 Mar 2018 22:00      1
                              10103 30 Mar 2018 23:00      1
                              10104 31 Mar 2018 00:00      1
                              10105 31 Mar 2018 01:00      1
                              10106 31 Mar 2018 02:00      1
                              10107 31 Mar 2018 03:00      1
                              10108 31 Mar 2018 04:00      1
                              10109 31 Mar 2018 05:00      1
                              10110 31 Mar 2018 06:00      1
                              10111 31 Mar 2018 07:00      1
                              10112 31 Mar 2018 08:00      1
                              10113 31 Mar 2018 09:00      1
                              10114 31 Mar 2018 10:00      1
                              10115 31 Mar 2018 11:00      1



Specify the First Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
## 选择开始的快照
Enter value for begin_snap: 10080
First Begin Snapshot Id specified: 10080
## 选择结束的快照
Enter value for end_snap: 10081
First End   Snapshot Id specified: 10081




Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 2143881171        1 CC           cc           ocsdb01




Database Id and Instance Number for the Second Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Using 2143881171 for Database Id for the second pair of snapshots
Using          1 for Instance Number for the second pair of snapshots


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.

#因为今天是31号,我们只需要输入1天时间范围就可以获取到3月31号00:00~01:00
Enter value for num_days2: 1

Listing the last day's Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
cc           CC               10104 31 Mar 2018 00:00      1
                              10105 31 Mar 2018 01:00      1
                              10106 31 Mar 2018 02:00      1
                              10107 31 Mar 2018 03:00      1
                              10108 31 Mar 2018 04:00      1
                              10109 31 Mar 2018 05:00      1
                              10110 31 Mar 2018 06:00      1
                              10111 31 Mar 2018 07:00      1
                              10112 31 Mar 2018 08:00      1
                              10113 31 Mar 2018 09:00      1
                              10114 31 Mar 2018 10:00      1
                              10115 31 Mar 2018 11:00      1



Specify the Second Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
## 选择开始的快照
Enter value for begin_snap2: 10104
Second Begin Snapshot Id specified: 10104
## 选择结束的快照
Enter value for end_snap2: 10105
Second End   Snapshot Id specified: 10105



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrdiff_1_10080_1_10104.html  To use this name,
press <return> to continue, otherwise enter an alternative.
## 输入awr报告名称
Enter value for report_name: artisan_awr_diff_rpt

Using the report name artisan_awr_diff_rpt

......省略输出过程
......
......

Report written to artisan_awr_diff_rpt
## 退出
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ocsdb01:[/oracle$]

## 当前目录下查找输出的报告
ocsdb01:[/oracle$]ls artisan_awr_diff_rpt*
artisan_awr_diff_rpt.lst
ocsdb01:[/oracle$]

查看报告