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
Copyright (c) 1982, 2013, Oracle. All rights reserved.
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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30776559/viewspace-2138526/