欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

Oracle 11g r1入门教程:AWR使用

程序员文章站 2024-01-30 17:10:22
...

一、 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: html
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.
输入 num_days 的值: 1
Listing 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 的值: 142
Begin Snapshot Id specified: 142
输入 end_snap 的值: 146
End 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

Oracle 11g r1入门教程:AWR使用