如何使用AWR

10G 新特性 同样可采样数据性能和统计  使用表空间sysaux，用户 sys，保留时间 七天

一。查看相关属性

desc dba_hist_wr_control ------>查询保留的天数，及相隔时间的快照

Name                                             Null?    Type
 ----------------------------------------- -------- ----------------------------
 DBID                                      NOT NULL NUMBER
 SNAP_INTERVAL                             NOT NULL INTERVAL DAY(5) TO SECOND(1)
 RETENTION                                 NOT NULL INTERVAL DAY(5) TO SECOND(1)
 TOPNSQL                                            VARCHAR2(10)

 

 

SQL> select snap_interval,retention from dba_hist_wr_control;

SNAP_INTERVAL
---------------------------------------------------------------------------
RETENTION
---------------------------------------------------------------------------
+00000 01:00:00.0
+00007 00:00:00.0

 

二 修改默认值

SQL> begin
  2  dbms_workload_repository.modify_snapshot_settings(
  3  interval=>30,
  4  retention=>20*24*60);
  5  end;
  6  /
SQL> select snap_interval,retention from dba_hist_wr_control;

SNAP_INTERVAL
---------------------------------------------------------------------------
RETENTION
---------------------------------------------------------------------------
+00000 00:30:00.0
+00020 00:00:00.0

三.监控sysaux表空间的剩余值

select upper(f.tablespace_name) "ts_name",
d.tot_gtootte_mb  "ts_bytes(m)",
d.tot_gtootte_mb - f.total_bytes "ts_used(m)",
f.total_bytes "ts_free(m)" ,
round(f.total_bytes/d.tot_gtootte_mb,2)
from  (
select tablespace_name,round(sum(bytes)/(1024 * 1024),2) total_bytes,
round(max(bytes) / (1024 * 1024),2) max_bytes
from sys.dba_free_space  group by tablespace_name) f,
(select dd.tablespace_name,round(sum(dd.bytes)/(1024*1024),2) tot_gtootte_mb
from sys.dba_data_files dd
group by   dd.tablespace_name) d
 where d.tablespace_name=f.tablespace_name;

 

四。得到快照

@?/rdbms/admin/awrrpt/.sql
html /text
begind snap
end snap
report.txt