【效能優化】AWR的手工生成

AWR（Automatic Workload Repository），用於收集特定資料庫的操作統計資訊和其他統計資訊。Oracle以固定的時間間隔（預設為每小時一次）為其所有重要統計資訊和負載資訊執行一次快照，並將這些快照儲存在AWR中。這些資訊在AWR中保留給定的時間（預設為8天），然後被清除。執行快照的頻率及其保留時間都可以自定義，以滿足不同環境的獨特需求。

一、AWR報告的生成

1、使用SYS使用者執行$ORACLE_HOME/rdbms/admin/awrrpt.sql，執行過程中輸入5個引數：報表型別、天數（用來決定哪幾天內的snapshot）、begin_snap、end_snap以及報表名稱。

oracle@C01TEST03:/home/oracle>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri May 5 11:19:39 2017

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@MTH> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

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

DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
3150883311 MTH 1 MTH

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
------------ -------- ------------ ------------ ------------
* 3150883311 1 MTH MTH C01TEST03

Using 3150883311 for database Id
Using 1 for instance number

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 without
specifying a number lists all completed snapshots.

Enter value for num_days: 2

Listing the last 2 days of Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
MTH          MTH               5500 04 May 2017 00:00      1
                               5501 04 May 2017 01:00      1
                               5502 04 May 2017 02:00      1
                               5503 04 May 2017 03:00      1
                               5504 04 May 2017 04:00      1
                               5505 04 May 2017 05:00      1
                               5506 04 May 2017 06:00      1
                               5507 04 May 2017 07:00      1
                               5508 04 May 2017 08:00      1
                               5509 04 May 2017 09:00      1
                               5510 04 May 2017 10:00      1
                               5511 04 May 2017 11:00      1
                               5512 04 May 2017 12:00      1
                               5513 04 May 2017 13:00      1
                               5514 04 May 2017 14:00      1
                               5515 04 May 2017 15:00      1
                               5516 04 May 2017 16:00      1
                               5517 04 May 2017 17:00      1
                               5518 04 May 2017 18:00      1
                               5519 04 May 2017 19:00      1
                               5520 04 May 2017 20:00      1
                               5521 04 May 2017 21:00      1
                               5522 04 May 2017 22:00      1
                               5523 04 May 2017 23:00      1
                               5524 05 May 2017 00:00      1
                               5525 05 May 2017 01:00      1
                               5526 05 May 2017 02:00      1
                               5527 05 May 2017 03:00      1
                               5528 05 May 2017 04:00      1
                               5529 05 May 2017 05:00      1
                               5530 05 May 2017 06:00      1
                               5531 05 May 2017 07:00      1
                               5532 05 May 2017 08:00      1
                               5533 05 May 2017 09:00      1
                               5534 05 May 2017 10:00      1
                               5535 05 May 2017 11:00      1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 5525
Begin Snapshot Id specified: 5525

Enter value for end_snap: 5535
End Snapshot Id specified: 5535

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

Enter value for report_name: MTH_AWR_report.html

Using the report name MTH_AWR_report.html

.....省略......

2、通過修改$ORACLE_HOME/rdbms/admin/awrrpti.sql檔案，將所需額5個引數設定好，該指令碼及可被用作自動生成報表。

二、AWR的取樣間隔及資訊保留時間

1、預設的取樣間隔和資訊保留時間如下：

SYS@MTH> col SNAP_INTERVAL for a30;
SYS@MTH> col RETENTION for a30;
SYS@MTH> select * from dba_hist_wr_control;

DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- ------------------------------ ------------------------------ ----------
3150883311 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT

2、修改取樣間隔和資訊保留時間

SYS@MTH> exec dbms_workload_repository.modify_snapshot_settings(interval=>30,retention=>15*24*60);

PL/SQL procedure successfully completed.

SYS@MTH> select * from dba_hist_wr_control;

DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- ------------------------------ ------------------------------ ----------
3150883311 +00000 00:30:00.0 +00015 00:00:00.0 DEFAULT

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30776559/viewspace-2138526/，如需轉載，請註明出處，否則將追究法律責任。

【效能優化】AWR的手工生成

相關文章