Oracle 11g AWR 系列二：AWR snapshot 的管理

php中文网

发布时间：2016-06-07 15:02:20

1611人浏览过

来源于php中文网

原创

默认情况下，Oracle Database 每小时产生一次快照，并将统计信息在工作负载信息库中保留 8 天。如有必要，您可以使用 DBMS_WORKLOAD_REPOSITORY 包中的一些存储过程手动创建、删除和修改快照。要调用这些存储过程，用户必须授予 DBA 角色。 1、创建快照：您

默认情况下，Oracle Database 每小时产生一次快照，并将统计信息在工作负载信息库中保留 8 天。如有必要，您可以使用 DBMS_WORKLOAD_REPOSITORY 包中的一些存储过程手动创建、删除和修改快照。要调用这些存储过程，用户必须授予 DBA 角色。

1、创建快照：

您可以使用 CREATE_SNAPSHOT 存储过程手动创建快照来捕获非自动生成快照的时间内的统计信息;

Syntax
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(
flush_level IN VARCHAR2 DEFAULT 'TYPICAL');

DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(
flush_level IN VARCHAR2 DEFAULT 'TYPICAL')
RETURN NUMBER;

flush_level 参数
Flush level 可以是 'TYPICAL' 或 'ALL'

BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/

当 flush_level 指定为默认的 typical 时，执行上述命令后实例的快照会立即创建。可以在 dba_hist_snapshot 视图中查看生成的快照。

23:03:53 sys@PROD> select SNAP_ID,BEGIN_INTERVAL_TIME,FLUSH_ELAPSED,SNAP_LEVEL from dba_hist_snapshot order by snap_id;

SNAP_ID BEGIN_INTERVAL_TIME FLUSH_ELAPSED SNAP_LEVEL
---------- ---------------------------------------- ------------------------------ ----------
SNAP_ID BEGIN_INTERVAL_TIME FLUSH_ELAPSED SNAP_LEVEL
---------- ---------------------------------------- ------------------------------ ----------
1 02-MAR-13 02.32.05.000 PM +00000 00:00:05.4 1
2 02-MAR-13 03.00.05.646 PM +00000 00:00:02.6 1
3 02-MAR-13 04.00.16.352 PM +00000 00:00:02.1 1
4 02-MAR-13 05.00.27.934 PM +00000 00:00:03.4 1
5 02-MAR-13 06.00.39.642 PM +00000 00:00:02.4 1
6 02-MAR-13 08.56.46.000 PM +00000 00:00:16.5 1
7 02-MAR-13 09.07.41.504 PM +00000 00:00:03.9 1
8 02-MAR-13 10.00.02.059 PM +00000 00:00:03.0 1
9 02-MAR-13 11.00.10.027 PM +00000 00:00:02.6 1
10 03-MAR-13 12.00.18.390 AM +00000 00:00:03.6 1
11 03-MAR-13 01.00.27.388 AM +00000 00:00:03.1 1
12 03-MAR-13 01.03.04.000 PM +00000 00:00:05.1 1
13 03-MAR-13 01.14.02.919 PM +00000 00:00:03.3 1
14 03-MAR-13 02.00.08.670 PM +00000 00:00:03.3 1
15 03-MAR-13 09.18.52.000 PM +00000 00:00:02.4 1
16 03-MAR-13 09.29.49.558 PM +00000 00:00:01.8 1
17 03-MAR-13 10.00.53.192 PM +00000 00:00:01.9 1
18 04-MAR-13 12.13.38.000 PM +00000 00:00:09.2 1
19 04-MAR-13 12.24.36.782 PM +00000 00:00:04.1 1
20 04-MAR-13 01.00.40.923 PM +00000 00:00:03.2 1
21 04-MAR-13 02.00.49.845 PM +00000 00:00:04.0 1
22 04-MAR-13 03.00.58.970 PM +00000 00:00:02.7 1
23 04-MAR-13 04.00.08.328 PM +00000 00:00:02.9 1
24 04-MAR-13 05.00.17.091 PM +00000 00:00:02.0 1
25 04-MAR-13 06.00.26.037 PM +00000 00:00:01.6 1
26 04-MAR-13 07.00.35.429 PM +00000 00:00:01.9 1
27 06-MAR-13 10.30.05.000 PM +00000 00:00:04.5 1
28 07-MAR-13 09.08.50.000 PM +00000 00:00:09.2 1
29 07-MAR-13 09.19.47.771 PM +00000 00:00:02.0 1
30 07-MAR-13 10.00.53.958 PM +00000 00:00:02.4 1

2. 删除快照

您可以使用 DROP_SNAPSHOT_RANGE 存储过程删除个范围内的快照。要查看快照的 snap_id 和数据库 ID 的列表，请查看 DBA_HIST_SNAPSHOT 视图。例如，您可以删除上例中 snap_id 小于 20 的快照：

DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
low_snap_id IN NUMBER,
high_snap_id IN NUMBER
dbid IN NUMBER DEFAULT NULL);

BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 1,
high_snap_id => 20, dbid => 220853307);
END;
/

23:10:05 sys@PROD> select SNAP_ID,DBID,BEGIN_INTERVAL_TIME,FLUSH_ELAPSED,SNAP_LEVEL from dba_hist_snapshot order by snap_id;

SNAP_ID DBID BEGIN_INTERVAL_TIME FLUSH_ELAPSED SNAP_LEVEL
---------- ---------- ---------------------------------------- ------------------------------ ----------
21 220853307 04-MAR-13 02.00.49.845 PM +00000 00:00:04.0 1
22 220853307 04-MAR-13 03.00.58.970 PM +00000 00:00:02.7 1
23 220853307 04-MAR-13 04.00.08.328 PM +00000 00:00:02.9 1
24 220853307 04-MAR-13 05.00.17.091 PM +00000 00:00:02.0 1
25 220853307 04-MAR-13 06.00.26.037 PM +00000 00:00:01.6 1
26 220853307 04-MAR-13 07.00.35.429 PM +00000 00:00:01.9 1
27 220853307 06-MAR-13 10.30.05.000 PM +00000 00:00:04.5 1
28 220853307 07-MAR-13 09.08.50.000 PM +00000 00:00:09.2 1
29 220853307 07-MAR-13 09.19.47.771 PM +00000 00:00:02.0 1
30 220853307 07-MAR-13 10.00.53.958 PM +00000 00:00:02.4 1

如果不指定 dbid 的值，则将使用本地数据库标示符作为默认的值。

在调用 DROP_SNAPSHOT_RANGE 存储过程时，属于指定快照范围时间段的 Active Session History (ASH) 数据也将清除。

3.修改快照设置
对于指定的数据库 ID，您可以调整快照生成的时间间隔、保留期限和捕获的 Top SQL，但是请注意，这会影响 Oracle Database 诊断工具的精确性。

INTERVAL 设置影响数据库自动生成快照的频率。RETENTION 设置影响数据库在工作负载信息库中存储快照的时间。TOPNSQL 设置影响针对每个 SQL 条件捕获的 Top SQL 的数量(Elapsed Time、CPU Time、Parse Calls、sharable Memory 和 Version Count)。此设置的值不受 statistics_level 和 flush_level 的影响，将会覆盖系统 AWR SQL 收集的默认行为。可以讲次设置的值设置为 MAXIMUM 来捕获 shared SQL area 中的全部 SQL 集合，这么做(将此值设置为一个非常高的值)可能会导致一定的空间和性能问题，因为将会收集和存储更多的数据。要调整这些设置，请使用 MODIFY_SNAPSHOT_SETTING 存储过程。例如：

Syntax
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
retention IN NUMBER DEFAULT NULL,
interval IN NUMBER DEFAULT NULL,
topnsql IN NUMBER DEFAULT NULL,
dbid IN NUMBER DEFAULT NULL);

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
retention IN NUMBER DEFAULT NULL,
interval IN NUMBER DEFAULT NULL,
topnsql IN VARCHAR2,
dbid IN NUMBER DEFAULT NULL);

BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 43200,
interval => 30, topnsql => 100, dbid => 220853307);
END;
/

在本例中，快照的保留期限将设置为 43200 分钟（30天），快照间隔指定未 30 分钟，每个 SQL 条件的 Top SQL 数量指定为 100。如果指定未 NULL，则将保留现有设置。dbid 可选。如果您不指定dbid，则将使用本地数据库标示符作为默认值。您可以通过 DBA_HIST_WR_CONTROL视图查看数据库实例的当前默认值。

col snap_interval for a30
select * from dba_hist_wr_control;

DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- ------------------------------ --------------------------------------------------------------------------- ----------