awr 使用之一 常用操作_awrvbc

os: centos 7.4
db: oracle 12.1.0.2

awr 的全名是 Automatic Workload Repository (AWR),是 oracle 分析数据库的重要工具(可以简单理解为 statspack 的升级版),主要负责收集、处理、维护性能统计信息,用于检查和分析性能问题.

The infrastructure that provides services to Oracle Database components to collect, maintain, and use statistics for problem detection and self-tuning.

相关的参数

$ sqlplus / as sysdba;
SQL> set lines 500;
SQL> set pages 500;
SQL> 
SQL> 
SQL> show parameter statistics_level;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
statistics_level		     string	 TYPICAL

SQL>
SQL>
SQL> show parameter timed_statistics;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
timed_statistics		     boolean	 TRUE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

statistics_level 需要设置为 TYPICAL or ALL.默认值为 TYPICAL.如果设置为 BASIC ,将会禁止oracle许多特性,包括 awr

当前 awr 的保存策略

SQL> 
SQL> col SNAP_INTERVAL format a20;
col RETENTION format a20;
SQL>
SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL	RETENTION	     TOPNSQL	    CON_ID
---------- -------------------- -------------------- ---------- ----------
1432589571 +00000 01:00:00.0	+00008 00:00:00.0    DEFAULT		 0

1
2
3
4
5
6
7
8
9
10

默认的是每隔1小时做一个快照,保留8天,一般情况下不用修改.

如何查看快照?执行下面sql

SQL> select * 
from dba_hist_snapshot dhs
where 1=1
order by dhs.snap_id desc
;

1
2
3
4
5
6
7

修改 awr 配置

调整快照间隔,保留时长(分钟)

SQL> begin
  dbms_workload_repository.modify_snapshot_settings(
    interval=>30,
    retention=>20*24*60
  );
end;
/

1
2
3
4
5
6
7
8

上面的修改表示每30分钟做一次snapshot,保留最近20天的快照信息.

SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL	RETENTION	     TOPNSQL	    CON_ID
---------- -------------------- -------------------- ---------- ----------
1432589571 +00000 00:30:00.0	+00020 00:00:00.0    DEFAULT		 0

1
2
3
4
5
6

一般情况下间隔1小时做一次快照足够,缩短间隔时间会导致awr变大,消耗额外的表空间.

关闭 snapshot 自动捕捉

interval 设置为0就会关闭 snapshot 的自动捕捉

SQL> begin
  dbms_workload_repository.modify_snapshot_settings(
    interval=>0,
    retention=>20*24*60
  );
end;
/

1
2
3
4
5
6
7
8

手动生成快照

SQL> begin
  dbms_workload_repository.create_snapshot();
end;
/

1
2
3
4
5

删除旧的快照

有时需要紧急删除旧的快照释放空间.

SQL> begin
  dbms_workload_repository.drop_snapshot_range(
    low_snap_id => 1,
	high_snap_id => 3,
	dbid => 1432589571
  );
end;
/

1
2
3
4
5
6
7
8
9

参考:
https://docs.oracle.com/database/121/REFRN/GUID-16B23F95-8644-407A-A6C8-E85CADFA61FF.htm#REFRN10214
https://docs.oracle.com/database/121/REFRN/GUID-1F65183A-5F77-42E1-8BC1-294910F3A731.htm#REFRN10218

https://docs.oracle.com/database/121/TGDBA/gather_stats.htm#TGDBA186