数据库快照是一个只读的静态的数据库。DM 快照功能是基于数据库实现的，每个快照是基于数据库的只读镜像。通过检索快照，可以获取源数据库在快照创建时间点的相关数据信息。
为了方便管理自动工作集负载信息库 AWR（Automatic Workload Repository）的信息，系统为其所有重要统计信息和负载信息执行一次快照，并将这些快照存储在 AWR 中。
用户在使用 DBMS_WORKLOAD_REPOSITORY 包之前，需要提前调用系统过程 SP_INIT_AWR_SYS(1)创建包。DM 数据库在创建该包时，默认创建一个名为 SYSAUX 的表空间，对应的数据文件为 SYSAWR.DBF，该表空间用于存储该包生成快照的数据。如果该包被删除，那么 SYSAUX 表空间也对应地被删除。
AWR 功能默认是关闭的，如果需要开启，则调用 DBMS_WORKLOAD_REPOSITORY.AWR_SET_INTERVAL 过程设置快照的间隔时间。DBMS_WORKLOAD_REPOSITORY 包还负责 snapshot（快照）的管理。
若创建数据库时页大小选择为 4K，不支持 DBMS_WORKLOAD_REPOSITORY 包的相关方法；DM MPP 环境下不支持 DBMS_WORKLOAD_REPOSITORY 包。
23.1 相关方法
- AWR_CLEAR_HISTORY();
清理之前的所有 snapshot 记录。
语法如下：
PROCEDURE AWR_CLEAR_HISTORY();
- AWR_SET_INTERVAL();
设置生成 snapshot 的时间间隔。
语法如下：
PROCEDURE AWR_SET_INTERVAL(
AWR_INTERVAL IN INT DEFAULT 60
);
参数详解
-
AWR_INTERVAL
时间间隔。单位分钟，取值范围为 10~525600，缺省值为 60。参数为 0 时，关闭快照（关闭时参数值为 57816000 分钟(110 年)，是一个无效的值）。
- AWR_REPORT_HTML
生成 html 格式的报告
定义 1：
FUNCTION AWR_REPORT_HTML(
START_SNAP_ID IN INT,
END_SNAP_ID IN INT
)RETURN AWRRPT_ROW_TYPE PIPELINED;
参数详解
- START_SNAP_ID 为起始 snapshot_id。
- End_snap_id 为终止 snapshot_id。
返回值
返回包含报告的全部 html 脚本信息的嵌套表类型 AWRRPT_ROW_TYPE。
把 awr 数据报表生成到指定路径的 html 文件。
定义 2：
PROCEDURE SYS.AWR_REPORT_HTML(
START_ID IN INT,
END_ID IN INT,
DEST_DIR IN VARCHAR(128),
DEST_FILE IN VARCHAR(128)
);
参数详解
- Start_ID 为起始 snapshot_id。
- End_id 为终止 snapshot_id。
- DEST_DIR 为指定生成报告的目标路径。
- DEST_FILE 为指定生成报告的目标文件名，文件名需要以.htm 和.html 结尾。
- AWR_REPORT_TEXT
生成 text 格式的报告
定义 1：
FUNCTION AWR_REPORT_TEXT(
START_SNAP_ID IN INT,
END_SNAP_ID IN INT
) RETURN AWRRPT_ROW_TYPE PIPELINED;
参数详解
- START_SNAP_ID 为起始 snapshot_id
- End_snap_id 为终止 snapshot_id。
返回值
返回包含报告的全部 text 脚本信息的嵌套表类型 AWRRPT_ROW_TYPE。
把 awr 数据报表生成到指定路径的 text 文件。
定义 2：
PROCEDURE SYS.AWR_REPORT_TEXT(
START_ID IN INT,
END_ID IN INT,
DEST_DIR IN VARCHAR(128),
DEST_FILE IN VARCHAR(128)
);
参数详解
- Start_ID 为起始 snapshot_id。
- End_id 为终止 snapshot_id。
- DEST_DIR 为指定生成报告的目标路径。
- DEST_FILE 为指定生成报告的目标文件名，文件名需要以.txt 结尾。
- CREATE_SNAPSHOT
创建一次快照 snapshot
语法如下：
FUNCTION CREATE_SNAPSHOT(
FLUSH_LEVEL IN VARCHAR2 DEFAULT 'TYPICAL'
) RETURN INT;
参数详解
-
FLUSH_LEVEL
'TYPICAL' OR 'ALL'；如果为空，则缺省为'TYPICAL'，该值会影响快照生成数据的大小，如果是'ALL'，则将全部历史数据保存，如果是'TYPICAL'则会刷部分数据，具体在后续会涉及到。
返回值
返回创建的快照 ID 值。
- DROP_SNAPSHOT_RANGE
删除 SNAPSHOT。
语法如下：
PROCEDURE DROP_SNAPSHOT_RANGE(
LOW_SNAP_ID IN INT,
HIGH_SNAP_ID IN INT,
DBID IN INT DEFAULT NULL
);
参数详解
- low_snap_idsnap_id 范围的起始值。
- high_snap_idsnap_id 范围的结束值。
- dbid 表示 snapshot 所在的 db 唯一标识，默认为 NULL，表示当前 db，目前该参数不起作用。
- MODIFY_SNAPSHOT_SETTINGS
设置 snapshot 的属性值。
语法如下：
PROCEDURE MODIFY_SNAPSHOT_SETTINGS(
RETENTION IN INT DEFAULT NULL,
AWR_INTERVAL IN INT DEFAULT NULL,
TOPNSQL IN INT DEFAULT NULL,
DBID IN INT DEFAULT NULL
);
PROCEDURE MODIFY_SNAPSHOT_SETTINGS(
RETENTION IN INT DEFAULT NULL,
AWR_INTERVAL IN INT DEFAULT NULL,
TOPNSQL IN VARCHAR2,
DBID IN INT DEFAULT NULL
);
参数详解
-
RETENTION
表示 snapshot 在数据库中保留的时间，以分钟为单位，最小值为 1 天，最大值为 100 年；如果值为 0，则表示永久保留；如果值为 NULL，则表示本次设置的该值无效，保留以前的旧值。
-
AWR_INTERVAL
表示每次生成 snapshot 的间隔时间，以分钟为单位，最小值为 10 分钟，最大值为 1 年；如果值为 0，则 snapshot 会失效。如果值为 NULL，则表示本次设置的该值无效，保留以前的旧值。
-
topnsql
如果为 NULL，则保留当前设置的值。
如果为 INT 类型，则表示按照 sql 的衡量标准（执行时间，cpu 时间，消耗内存等）获取保存的 sql 个数，最小值为 30，最大值为 50000。
如果为 varchar2 类型，则可以设置如下 3 个值：DEFAULT、MAXIMUM 和 N。DEFAULT 对应值为 100；MAXIMUM 对应值为 50000；值 N 是数字串，但要求转化为 INT 类型之后，值必须在 30 至 50000 之间。
目前该参数不起作用。
-
dbid
表示 snapshot 所在的 db 唯一标识，缺省为 NULL，表示当前 db。目前该参数不起作用。
23.2 系统表
DBMS_WORKLOAD_REPOSITORY 包相关的系统表包括快照信息管理表及快照信息表，这些系统表都只有创建了 DBMS_WORKLOAD_REPOSITORY 包后才能查看。
23.2.1 快照信息管理表
- SYS.WRM$_WR_CONTROL
记录快照的相关控制信息。
| 列名 | 类型 | 说明 |
|---|---|---|
| DBID | INTEGER | 数据库 id（NULL） |
| SNAP_INTERVAL | INTERVAL DAY(5) TO SECOND(1) | 快照间隔 |
| RETENTION | INTERVAL DAY(5) TO SECOND(1) | 快照保留时间（单位:INTERVAL DAY TO SECOND 类型） |
| TOPSQL | INTEGER | 每次快照搜集 sql 条数 |
| STATUS_FLA | INTEGER | AWR 状态（1：开启；0：关闭） |
- SYS.WRM$_SNAPSHOT
记录快照的相关信息。
| 列名 | 类型 | 说明 |
|---|---|---|
| SNAP_ID | INTEGER | 快照 ID |
| DBID | INTEGER | 数据库 ID（NULL） |
| INSTANCE_NUMBER | INTEGER | 实例个数，恒为 1 |
| STARTUP_TIME | DATETIME(6) | 数据库启动时间 |
| BEGIN_INTERVAL_TIME | DATETIME(6) | 开始快照时间 |
| END_INTERVAL_TIME | DATETIME(6) | 结束快照时间 |
| FLUSH_ELAPSED | INTERVAL DAY(5) TO SECOND(1) | 生成快照的时间（NULL） |
| SNAP_LEVEL | INTEGER | 快照等级，恒为 1 |
| STATUS | INTEGER | 快照是否成功生成，0：成功；1：失败（NULL） |
| ERROR_COUNT | INTEGER | 快照生成失败时的错误数（NULL） |
| BL_MOVED | INTEGER | 保留字段（NULL） |
| SNAP_FLAG | INTEGER | 快照生成方式（NULL） |
| SNAP_TIMEZONE | INTERVAL DAY(1) TO SECOND(0) | 快照生成的时区和 UTC 时区的时间差 |
| BEGIN_INTERVAL_TIME_TZ | DATETIME(6) WITH TIME ZONE | 带时区的开始快照时间 |
| END_INTERVAL_TIME_TZ | DATETIME(6) WITH TIME ZONE | 带时区的结束快照时间 |
23.2.2 快照信息表
快照信息表记录快照中数据库的一些具体信息。
- SYS.WRH$_RESOURCE_LIMIT
显示所有快照中表、用户的空间限制信息。
| 列名 | 类型 | 说明 |
|---|---|---|
| SNAP_ID | INTEGER | 快照 ID |
| NAME | VARCHAR(128) | 表/用户名 |
| ID | INTEGER | 表/用户 ID |
| TYPE | VARCHAR(64) | 表/用户类型 |
| SPACE_LIMIT | INTEGER | 空间限制大小，以页为单位 |
| SPACE_USED | INTEGER | 空间实际使用大小，以页为单位 |
- SYS.WRH$_SQL_HISTORY
当 INI 参数 ENABLE_MONITOR=1 时，显示所有快照中执行 sql 的历史记录信息，其中不同快照的 SQL_ID 和 START_TIME 肯定不相同。
| 列名 | 类型 | 说明 |
|---|---|---|
| SNAP_ID | INTEGER | 快照 ID |
| SQL_ID | INTEGER | 当前语句的 SQL ID |
| START_TIME | DATETIME(6) | SQL 执行的起始时间 |
| TIME_USED | BIGINT | SQL 执行所使用时间（单位：usec） |
| BYTES_DYNAMIC_ALLOCED | BIGINT | 动态分配字节数 |
| N_LOGIC_READ | INTEGER | 语句逻辑读的次数 |
| N_PHY_READ | INTEGER | 语句物理读的次数 |
| AFFECTED_ROWS | INTEGER | 语句影响的行数 |
| HARD_PARSE_FLAG | INTEGER | 语句硬解析标记，0：软解析；1：语义解析；2：硬解析 |
| MPP_EXEC_ID | INTEGER | MPP 会话句柄上的执行序号，同一个会话上的每个节点上值相同 |
- SYS.WRH$_SQLTEXT
显示所有快照的缓冲区中的 SQL 语句信息。
| 列名 | 类型 | 说明 |
|---|---|---|
| SNAP_ID | INTEGER | 快照 ID |
| SQL_ID | INTEGER | 语句的 SQL ID |
| N_EXEC | INTEGER | 语句执行次数 |
| SQL_TEXT | TEXT | SQL 语句内容，超过 7168 的串不分行显示，而是显示成一行 |
| SQL_NTH | INTEGER | SQL 语句段号（从 0 开始） |
- SYS.WRH$_SQLTEXT_TMP
SYS.WRH$_SQLTEXT 的辅助表，显示所有快照中缓冲区中的 SQL 语句信息。
| 列名 | 类型 | 说明 |
|---|---|---|
| SNAP_ID | INTEGER | 快照 ID |
| SQL_ID | INTEGER | 语句的 SQL ID |
| SQL_TEXT | TEXT | SQL 语句内容，与 v$SQLTEXT 里的一致，超过 7168 的串分行显示 |
| SQL_NTH | INTEGER | SQL 语句段号（从 0 开始） |
- SYS.WRH$_SESSIONS
显示所有快照中会话的具体信息，如执行的 sql 语句、主库名、当前会话状态、用户名等。
| 列名 | 类型 | 说明 |
|---|---|---|
| SNAP_ID | INTEGER | 快照 ID |
| N_SESSIONS | TEXT | 会话数 |
| N_STMT | INTEGER | 已使用的 STMT 数量之和 |
- SYS.WRH$_SESSION_WAIT_HISTORY
显示所有快照中会话等待事件的历史信息。
| 列名 | 类型 | 说明 |
|---|---|---|
| SNAP_ID | INTEGER | 快照 ID |
| SQL_ID | INTEGER | 语句的 SQL ID |
| EVENT# | SMALLINT | 事件编号 |
| EVENT | VARCHAR(32) | 事件名称 |
| TIME_WAITED_MICRO | INTEGER | 等待时间（单位：微秒） |
| WAIT_CLASS# | SMALLINT | 等待事件类别编号 |
快照信息表共有 37 张表，除了上述 6 张快照信息表与其后缀名相同的动态性能视图在结构上有些许不同，其余的快照信息表仅比对应的动态性能视图多一列名为 SNAP_ID 的整型列，其余结构完全相同，如 SYS.WRH_ARCH_QUEUE仅比VARCH_QUEUE 多一列 SNAP_ID，这里不再赘述，仅给出快照信息表的名称，具体可参考《DM8 系统管理员手册》附录 2。
剩余的快照信息表名称如下：
- SYS.WRH$_ARCH_QUEUE
- SYS.WRH$_BUFFERPOOL
- SYS.WRH$_CKPT_HISTORY
- SYS.WRH$_DATAFILE
- SYS.WRH$_DB_CACHE
- SYS.WRH$_DEADLOCK_HISTORY
- SYS.WRH$_DMSQL_EXEC_TIME
- SYS.WRH$_DM_INI
- SYS.WRH$_HASH_MERGE_USED_HISTORY
- SYS.WRH$_LARGE_MEM_SQLS
- SYS.WRH$_LOCK
- SYS.WRH$_MAL_INFO
- SYS.WRH$_MEM_POOL
- SYS.WRH$_MTAB_USED_HISTORY
- SYS.WRH$_PSEG_ITEMS
- SYS.WRH$_RLOG
- SYS.WRH$_SCP_CACHE
- SYS.WRH$_SESSION_HISTORY
- SYS.WRH$_SESSION_STAT
- SYS.WRH$_SORT_HISTORY
- SYS.WRH$_SQL_NODE_HISTORY
- SYS.WRH$_SQL_PLAN_NODE
- SYS.WRH$_SQL_STAT_HISTORY
- SYS.WRH$_SYSSTAT
- SYS.WRH$_SYSTEMINFO
- SYS.WRH$_SYSTEM_EVENT
- SYS.WRH$_TABLESPACE
- SYS.WRH$_TASK_QUEUE
- SYS.WRH$_TRACE_QUEUE
- SYS.WRH$_VIRTUAL_MACHINE
- SYS.WRH$_WAIT_HISTORY
23.3 创建、检测、删除语句
23.3.1 SP_INIT_AWR_SYS
创建或删除 DBMS_WORKLOAD_REPOSITORY 系统包。
语法如下：
void
SP_INIT_AWR_SYS(
CREATE_FLAG INT
)
参数详解
-
CREATE_FLAG
为 1 时表示创建 DBMS_WORKLOAD_REPOSITORY 包；为 0 表示删除该系统包。
返回值
无
举例说明
创建 DBMS_WORKLOAD_REPOSITORY 系统包。
SP_INIT_AWR_SYS(1);
23.3.2 SF_CHECK_AWR_SYS
系统的 DBMS_WORKLOAD_REPOSITORY 系统包启用状态检测。
语法如下：
int
SF_CHECK_AWR_SYS ()
返回值
0：未启用；1：已启用
举例说明
获得 DBMS_WORKLOAD_REPOSITORY 系统包的启用状态。
SELECT SF_CHECK_AWR_SYS;
23.4 举例说明
用户在使用 DBMS_WORKLOAD_REPOSITORY 包之前，需要提前调用系统过程并设置间隔时间：
SP_INIT_AWR_SYS(1);
下面语句设置间隔为 10 分钟，也可以是其他值：
CALL DBMS_WORKLOAD_REPOSITORY.AWR_SET_INTERVAL(10);
设置成功后，可以使用 CREATE_SNAPSHOT 手动创建快照，也可以等待设置的间隔时间后系统自动创建快照，快照 id 从 1 开始递增：
手动创建快照：
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
查看创建的快照信息，包括快照 id：
SELECT * FROM SYS.WRM$_SNAPSHOT;
查看 snapshot 的 id 在 1~2 范围内的 AWR 分析报告的带 html 格式的内容。然后复制到文本文件中，保存成 html 格式即可查看。
SELECT * FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(1,2));
把 snapshot 的 id 在 1~2 范围内的 AWR 分析报告生成到 c 盘 awr1.html 文件。
SYS.AWR_REPORT_HTML(1,2,'C:\','AWR1.HTML');
通过 DMBS_WORKLOAD_REPOSITORY 包还可以对快照本身做增删改操作。
例 1 删除 id 在 22~32 之间的 snapshot。
CALL DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(22,32);
例 2 修改 snapshot 的间隔时间为 30 分钟、保留时间为 1 天。
CALL DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(1440,30);
查询设置后快照参数。
SELECT * FROM SYS.WRM$_WR_CONTROL;
例 3 创建一次 snapshot。
CALL DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
例 4 清理全部 snapshot。
CALL DBMS_WORKLOAD_REPOSITORY.AWR_CLEAR_HISTORY();
例 5 设置 snapshot 的间隔为 10 分钟。
CALL DBMS_WORKLOAD_REPOSITORY.AWR_SET_INTERVAL(10);
