AWR-CSDN博客

1.AWR开启，有STATISTICS_LEVEL参数决定，默认TYPICAL ，引用官方说明：

STATISTICS_LEVEL = { ALL | TYPICAL | BASIC }

The default setting of TYPICAL ensures collection of all major statistics required for database self-management functionality and provides best overall performance. The default value should be adequate for most environments.

When the STATISTICS_LEVEL parameter is set to ALL, additional statistics are added to the set of statistics collected with the TYPICAL setting. The additional statistics are timed OS statistics and plan execution statistics.

Setting the STATISTICS_LEVEL parameter to BASIC disables the collection of many of the important statistics required by Oracle Database features and functionality, including:

Automatic Workload Repository (AWR) Snapshots
Automatic Database Diagnostic Monitor (ADDM)
All server-generated alerts
Automatic SGA Memory Management
Automatic optimizer statistics collection
Object level statistics
End to End Application Tracing (V$CLIENT_STATS)
Database time distribution statistics (V$SESS_TIME_MODEL and V$SYS_TIME_MODEL)
Service level statistics
Buffer cache advisory
MTTR advisory
Shared pool sizing advisory
Segment level statistics
PGA Target advisory
Timed statistics
Monitoring of statistics

可以查看select * from v$statistics_level;了解ALL | TYPICAL 都会收集那些信息。

2.AWR策略查看，默认1小时一个快照，10G保留7点，11G保留8天

SNAP_INTERVAL时间间隔， RETENTION 保留天数

select * from dba_hist_wr_control;
select DBID, SNAP_INTERVAL, SNAPINT_NUM, RETENTION from wrm$_wr_control;

3.AWR操作

修改快照的频率和保留时间（单位用分钟）
exec dbms_workload_repository.modify_snapshot_settings(interval=>60, retention=>7*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 (604800) greater than retention (432000)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 174
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 222
ORA-06512: at line 1

查看下baseline保留天数
SQL> select dbid,baseline_name,baseline_type,moving_window_size from dba_hist_baseline;

DBID BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE
---------- ---------------------------------------------------------------- ------------- ------------------
2007794869 SYSTEM_MOVING_WINDOW MOVING_WINDOW 7

先修改MOVING_WINDOW_SIZE
SQL> exec dbms_workload_repository.modify_baseline_window_size(5);

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

创建快照
exec dbms_workload_repository.create_snapshot();

删除快照
exec dbms_workload_repository.drop_snapshot_range(low_snap_id => 107, high_snap_id => 110, dbid => 2007794869);

查看快照
select * from DBA_HIST_SNAPSHOT;

创建基线
exec dbms_workload_repository.create_baseline(start_snap_id =>112 ,end_snap_id => 132,baseline_name => 'pline');

删除基线，此过程参数cascade=>true时将把快照也删掉
exec dbms_workload_repository.drop_baseline(baseline_name => 'pline');

4.生成AWR报告

生成本实例的AWR报告
@$ORACLE_HOME/rdbms/admin/awrrpt.sql

生成某实例的AWR报告
@$ORACLE_HOME/rdbms/admin/awrrpti.sql

生产整个集群的AWR报告
@$ORACLE_HOME/rdbms/admin/awrgrpt.sql

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29339009/viewspace-1466558/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29339009/viewspace-1466558/