oracle怎么抓aw报告,oracle_11g_AWR1

1、awr报告数据的处理是通过mmon和其辅助进程mmnl进行处理

2、性能报告里的信息都是存放在基于sysaux表空间的一些T表里，所以有时候，可能因为设置问题或purg或其它原因，导致sysaux tablespace不断膨胀，出现性能问题。

3、与性能报告相关的分区表及普通,历史性能信息都会存放在下面的分区表里，如ash历史信息WRH$_ACTIVE_SESSION_HISTORY ，快照信息WRM$_SNAPSHOT 等，这里查出来的T表

不是全部的信息。

SQL> set pagesize 0

SQL> SELECT table_name, COUNT(table_name)

2 FROM dba_tab_partitions

3 WHERE table_name LIKE ‘WRH$_%’

4 GROUP BY table_name;

WRH$_EVENT_HISTOGRAM 3

WRH$_SERVICE_WAIT_CLASS 3

WRH$_FILESTATXS 3

WRH$_LATCH_MISSES_SUMMARY 3

WRH$_OSSTAT 3

WRH$_ROWCACHE_SUMMARY 3

WRH$_SYSTEM_EVENT 3

WRH$_DB_CACHE_ADVICE 3

WRH$_MVPARAMETER 3

WRH$_SEG_STAT 3

WRH$_WAITSTAT 3

WRH$_DLM_MISC 2

WRH$_INST_CACHE_TRANSFER 2

WRH$_LATCH 3

WRH$_ACTIVE_SESSION_HISTORY 3

WRH$_INTERCONNECT_PINGS 2

WRH$_LATCH_CHILDREN 2

WRH$_LATCH_PARENT 2

WRH$_PARAMETER 3

WRH$_SERVICE_STAT 3

WRH$_SGASTAT 3

WRH$_SQLSTAT 3

WRH$_TABLESPACE_STAT 3

WRH$_SYSSTAT 3

WRH$_SYS_TIME_MODEL 3

SQL> SELECT table_name, COUNT(table_name)

2 FROM dba_tables

3 WHERE table_name LIKE ‘WRM$_%’

4 GROUP BY table_name;

TABLE_NAME COUNT(TABLE_NAME)

—————————— —————–

WRM$_BASELINE_TEMPLATE 1

WRM$_COLORED_SQL 1

WRM$_WR_USAGE 1

WRM$_BASELINE 1

WRM$_SNAPSHOT_DETAILS 1

WRM$_WR_CONTROL 1

WRM$_DATABASE_INSTANCE 1

WRM$_SNAPSHOT 1

WRM$_SNAP_ERROR 1

WRM$_BASELINE_DETAILS 1

可以通过查询视图dba_hist_wr_control或(wrm$_wr_control)来查询AWR的采样频率和保留时间。默认为每1小时采样一次，采样信息保留时间为7天

SQL> set linesize 120

SQL> col retention for a20

SQL> col SNAP_INTERVAL for a20

SQL> select DBID, SNAP_INTERVAL, SNAPINT_NUM, RETENTION from wrm$_wr_control;

DBID SNAP_INTERVAL SNAPINT_NUM RETENTION

———- ——————– ———– ——————–

70326461 +00000 01:00:00.0 3600 +00008 00:00:00.0

SQL> select * from dba_hist_wr_control;

DBID SNAP_INTERVAL RETENTION TOPNSQL

———- ——————– ——————– ———-

70326461 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT

–设置快照保留时间为3天，且15分钟收集一次，报错如下

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>15, retention=>3*24*60)

BEGIN dbms_workload_repository.modify_snapshot_settings(interval=>15, retention=>6*24*60); END;

*

ERROR at line 1:

ORA-13541: system moving window baseline size (691200) greater than retention (518400)

ORA-06512: at “SYS.DBMS_WORKLOAD_REPOSITORY”, line 174

ORA-06512: at “SYS.DBMS_WORKLOAD_REPOSITORY”, line 222

ORA-06512: at line 1

移动窗口基线dba_hist_baseline(wrm$_baseline),

SQL> col BASELINE_NAME for a30

SQL> select dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline;

DBID BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE

———- —————————— ————- ——————

70326461 SYSTEM_MOVING_WINDOW MOVING_WINDOW 8

–调整baseline移动窗口时间为2天或5天

SQL> exec DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE (window_size => 5,dbid => 70326461)

PL/SQL procedure successfully completed.

SQL> select dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline;

DBID BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE

———- —————————— ————- ——————

70326461 SYSTEM_MOVING_WINDOW MOVING_WINDOW 5

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>15, retention=>6*24*60)

PL/SQL procedure successfully completed.

SQL> select DBID, SNAP_INTERVAL, SNAPINT_NUM, RETENTION from wrm$_wr_control;

DBID SNAP_INTERVAL SNAPINT_NUM RETENTION

———- ——————– ———– ——————–

70326461 +00000 00:15:00.0 900 +00006 00:00:00.0

SQL> select * from dba_hist_wr_control;

DBID SNAP_INTERVAL RETENTION TOPNSQL

———- ——————– ——————– ———-

70326461 +00000 00:15:00.0 +00006 00:00:00.0 DEFAULT

awr快照信息查看:

dba_hist_snapshot(Sys.WRM$_SNAPSHOT)

dba_hist_snapshot的数据是基于Sys.WRM$_SNAPSHOT此T表获得，且只获取Sys.WRM$_SNAPSHOT.status状态为0的数据。