PL/SQL 过程已成功完成。
-- 以上存储过程设置每半小时采样一次,并且保留5天 。
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- -------------------- ----------
########## +00000 00:30:00.0 +00005 00:00:00.0 DEFAULT
SQL>
修改awr的保留时间为10天,
SQL> exec dbms_workload_repository.modify_snapshot_settings(retention=>10*24*60);
查询awr的保留时间是否被修改:
SQL> column dbid format 999999999999
SQL> column snap_interval format a20
SQL> column retention format a20
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- -------------------- ----------
########## +00000 01:00:00.0 +00010 00:00:00.0 DEFAULT
1.查看当前的AWR保存策略
select * from dba_hist_wr_control;
860524039,+00 01:00:00.000000,+07 00:00:00.000000,DEFAULT
AWR配置都是通过dbms_workload_repository包进行配置
exec dbms_workload_repository.modify_snapshot_settings(interval=>30, retention=>5*24*60);
exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
select * from sys.wrh$_active_session_history
exec WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id => 22, high_snap_id => 32, dbid => 3310949047);
exec dbms_workload_repository.create_baseline (56,59,'apply_interest_1')
exec DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(baseline_name => ' apply_interest_1', cascade => FALSE);
3.生产AWR报告
$ORACLE_HOME/rdbms/admin/awrrpt.sql
BASIC:AWR统计信息收集和所有自我调整特性都被关闭
TYPICAL:数据库收集部分统计信息,这些信息为典型的数据库监控需要,默认设置
ALL:所有可能统计信息被收集