AWR DB 비교 보고서 생성

• STATSPACK 보고서에 비해 강점은 AWR 비교 보고서 생성이 가능.
• AWR 비교 보고서는 기준 구간의 수치와 비교 구간의 수치를 나란히 나열하므로 차이를 한눈에 비교할 수 있게 해준다.
  
• awrddrpt.sql, awrddrpi.sql

SQL> @?/rdbms/admin/awrddrpt.sql

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

   DB Id       DB Id    DB Name      Inst Num Inst Num Instance
----------- ----------- ------------ -------- -------- ------------
 1490765173  1490765173 ORATEST            1        1 ORATEST


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: html

Type Specified:  html


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

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 1490765173        1 ORATEST     ORATEST     dbhost1
* 1490765173        1 ORATEST     ORATEST     dbhost

Database Id and Instance Number for the First Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 1490765173 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.


Enter value for num_days: 1

Listing the last day's Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
ORATEST     ORATEST         38713 15 Dec 2012 00:00      1
                              38714 15 Dec 2012 00:20      1
                              38715 15 Dec 2012 00:40      1
                              38716 15 Dec 2012 01:00      1
                              ...



Specify the First Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 38715
First Begin Snapshot Id specified: 38715

Enter value for end_snap: 38716
First End   Snapshot Id specified: 38716




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

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 1490765173        1 ORATEST     ORATEST     dbhost1
* 1490765173        1 ORATEST     ORATEST     dbhost




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

Using 1490765173 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.


Enter value for num_days2: 2

Listing the last 2 days of Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
ORATEST     ORATEST         38641 14 Dec 2012 00:00      1
                              38642 14 Dec 2012 00:20      1
                              38643 14 Dec 2012 00:40      1
                              ...

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
ORATEST     ORATEST         38697 14 Dec 2012 18:40      1
                              38698 14 Dec 2012 19:00      1
                              ...
                              38706 14 Dec 2012 21:40      1
                              38707 14 Dec 2012 22:00      1
                              38708 14 Dec 2012 22:20      1
                              ...



Specify the Second Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap2: 38706
Second Begin Snapshot Id specified: 38706

Enter value for end_snap2: 38707
Second End   Snapshot Id specified: 38707



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrdiff_1_38715_1_38706.html  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: awr_diff.html

Using the report name awr_diff.html

• 보고서를 대량으로 생성해야 한다면 DBMS_WORKLOAD_REPOSITORY 패키지를 사용하는 것이 편리

SELECT OUTPUT
FROM  TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_DIFF_REPOST_HTML(
                      :DBID1, :INST_ID1, :BEGIN_SNAP1, :END_SNAP1,
                      :DBID2, :INST_ID2, :BEGIN_SNAP2, :END_SNAP2));