1.AWR默认的采样间隔和历史快照保留时间 默认是保留七天,采集间隔是1小时,这个信息可以从DBA_HIST_WR_CONTROL视图中获得。 sys@ora10g> col SNAP_INTERVAL for a20 sys@ora10g> col RETENTION for a20 sys@ora10g> select * from dba_hist_wr_control;
3.修改AWR默认的采样间隔和历史快照保留时间之SQL方法 万变不离其宗,任何能从界面上进行修改的内容都可以通过SQL调整的方法来完成。 我们即将使用到的是DBMS_WORKLOAD_REPOSITORY包中的MODIFY_SNAPSHOT_SETTINGS存储过程。 该存储过程的简单描述信息如下。 sec@ora10g> desc DBMS_WORKLOAD_REPOSITORY …… …… PROCEDURE MODIFY_SNAPSHOT_SETTINGS Argument Name Type In/Out Default? ------------------------------ --------------- ------ -------- RETENTION NUMBER IN DEFAULT INTERVAL NUMBER IN DEFAULT TOPNSQL VARCHAR2 IN DBID NUMBER IN DEFAULT
BEGIN * ERROR at line 1: ORA-13510: invalid RETENTION 720, must be in the range (1440, 52560000) ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 85 ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 133 ORA-06512: at line 2
sys@ora10g> BEGIN 2 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( 3 interval => 60, 4 retention => 36501*24*60); 5 END; 6 / BEGIN * ERROR at line 1: ORA-13510: invalid RETENTION 52561440, must be in the range (1440, 52560000) ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 85 ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 133 ORA-06512: at line 2
2)验证INTERVAL参数的取值范围 sys@ora10g> BEGIN 2 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( 3 interval => 9, 4 retention => 7*24*60); 5 END; 6 / BEGIN * ERROR at line 1: ORA-13511: invalid INTERVAL 9, must be in the range (10, 52560000) ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 85 ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 133 ORA-06512: at line 2