oracle11gr1使用教程,Oracle 11g r1入门教程：AWR使用

一、 AWR使用 SQLgt;@?/rdbms/admin/awrrpt.sql Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ Would you like an HTML re

一、 AWR使用

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

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’

输入 report_type 的值:

Type Specified: htmlSpecify 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.

输入 num_days 的值: 1Listing the last day’s Completed Snapshots

Snap

Instance DB Name Snap Id Snap Started Level

———— ———— ——— —————— —–

orcl10g ORCL10G 142 03 7月 2009 08:11 1

143 03 7月 2009 09:00 1

144 03 7月 2009 10:00 1

145 03 7月 2009 11:00 1

146 03 7月 2009 12:01 1

Specify the Begin and End Snapshot Ids

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

输入 begin_snap 的值: 142Begin Snapshot Id specified: 142

输入 end_snap 的值: 146End Snapshot Id specified: 146

Specify the Report Name

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

The default report file name is awrrpt_1_142_146.html. To use this name,

press to continue, otherwise enter an alternative.

输入 report_name 的值:/home/Oracle/awrrpt_1_142_146.html

Report written to /home/oracle/awrrpt_1_142_146.html

二、AWR管理

(1)关闭awr：

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>0);说明：把interval设为0则已经关闭awr功能。

验证：

SQL> select * from dba_hist_wr_control;

DBID SNAP_INTERVAL RETENTION TOPNSQL

———- ——————- ——————- ———-

450641661 +40150 00:00:00.0 +00008 00:00:00.0 DEFAULT

(2)打开awr:

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>30, retention=>5*24*60);

说明：收集间隔时间为30分钟一次，并且保留5天时间(单位都是分钟)如果执行时报错：

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>30, retention=>5*24*60);

BEGIN dbms_workload_repository.modify_snapshot_settings(interval=>30, retention=>5*24*60); END;

*

ERROR at line 1:

ORA-13541: system moving window baseline size (691200) greater than retention (432000)

ORA-06512: at “SYS.DBMS_WORKLOAD_REPOSITORY”, line 89

ORA-06512: at “SYS.DBMS_WORKLOAD_REPOSITORY”, line 137

ORA-06512: at line 1

原因是系统设置的baseline size值(691200)大于此时设置的retention(432000)，，那么此时有两种方法解决：

方法1：增大retention值，也就是增大保存天数，比如修改为：

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>30, retention=>9*24*60);

PL/SQL procedure successfully completed.

验证：

SQL> select * from dba_hist_wr_control;

DBID SNAP_INTERVAL RETENTION TOPNSQL

———- ——————- ——————- ———-

450641661 +00000 00:30:00.0 +00009 00:00:00.0 DEFAULT

(3)查看快照记录：

select * from sys.wrh$_active_session_history