DM的学习心得和知识总结（三）|DM数据库DBMS_WORKLOAD_REPOSITORY 包及其性能分析工具AWR
目录结构
注：提前言明 本文借鉴了以下博主、书籍或网站的内容，其列表如下：
1、达梦数据库产品及解决方案，点击前往
2、达梦技术文档，点击前往
3、武汉达梦数据库有限公司 官网首页，点击前往
1、本文内容全部来源于开源社区 GitHub和以上博主的贡献，本文也免费开源（可能会存在问题，评论区等待大佬们的指正）
2、本文目的：开源共享 抛砖引玉 一起学习
3、本文不提供任何资源 不存在任何交易 与任何组织和机构无关
4、大家可以根据需要自行 复制粘贴以及作为其他个人用途，但是不允许转载 不允许商用 （写作不易，还请见谅 💖）
DM数据库DBMS_WORKLOAD_REPOSITORY 包及其性能分析工具AWR
- 文章快速说明索引
- 工作集负载存储库
-
- 包的相关方法
- 相关的系统表
-
- 快照信息管理表
- 快照信息表
- 相关语句使用
- AWR报告的生成
文章快速说明索引
学习目标：
目的：前面我们分享过Oracle的RAT的相关实操，接下来看一下DM数据库DBMS_WORKLOAD_REPOSITORY 包及其性能分析工具AWR！
学习内容：（详见目录）
1、DM数据库DBMS_WORKLOAD_REPOSITORY 包及其性能分析工具AWR
学习时间：
2023年04月10日 20:24:22
学习产出：
1、DM数据库动态性能视图
2、CSDN 技术博客 1篇
注：下面我们所有的学习环境是Centos7+DM V8
[dm8@dbserver bin]$ disql SYSDBA/SYSDBA服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 3.207(ms)
disql V8
SQL> select * from v$version;行号 BANNER
---------- -------------------------
1 DM Database Server 64 V8
2 DB Version: 0x7000b已用时间: 1.787(毫秒). 执行号:4.
SQL>
SQL> select id_code;行号 ID_CODE
---------- ----------------------------
1 1-1-126-20.09.04-126608-ENT 已用时间: 0.435(毫秒). 执行号:22.
SQL>
- 达梦技术文档 链接：https://eco.dameng.com/document/dm/zh-cn/start/index.html
工作集负载存储库
数据库快照是一个只读的静态的数据库。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（快照）的管理。
SQL> SELECT SF_CHECK_AWR_SYS();行号 SF_CHECK_AWR_SYS()
---------- ------------------
1 0已用时间: 0.402(毫秒). 执行号:15.
SQL>
注：
- 若创建数据库时页大小选择为 4K，不支持 DBMS_WORKLOAD_REPOSITORY 包的相关方法
- DM MPP 环境下不支持 DBMS_WORKLOAD_REPOSITORY 包
包的相关方法
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 格式的报告，其语法格式如下：
FUNCTION AWR_REPORT_HTML(START_SNAP_ID IN INT,END_SNAP_ID IN INT
)RETURN AWRRPT_ROW_TYPE PIPELINED;PROCEDURE SYS.AWR_REPORT_HTML(START_ID IN INT, END_ID IN INT, DEST_DIR IN VARCHAR(128), DEST_FILE IN VARCHAR(128)
);
第一种参数解释如下：
-- START_SNAP_ID 为起始 snapshot_id
-- End_snap_id 为终止 snapshot_id-- 返回包含报告的全部 html 脚本信息的嵌套表类型 AWRRPT_ROW_TYPE
-- 把 awr 数据报表生成到指定路径的 html 文件
第二种参数解释如下：
-- Start_ID 为起始 snapshot_id
-- End_id 为终止 snapshot_id
-- DEST_DIR 为指定生成报告的目标路径
-- DEST_FILE 为指定生成报告的目标文件名，文件名需要以.htm 和.html 结尾
AWR_REPORT_TEXT，生成 text 格式的报告，其语法格式如下：
FUNCTION AWR_REPORT_TEXT(START_SNAP_ID IN INT,END_SNAP_ID IN INT
) RETURN AWRRPT_ROW_TYPE PIPELINED;PROCEDURE SYS.AWR_REPORT_TEXT(START_ID IN INT, END_ID IN INT, DEST_DIR IN VARCHAR(128), DEST_FILE IN VARCHAR(128)
);
第一种参数解释如下：
-- START_SNAP_ID 为起始 snapshot_id
-- End_snap_id 为终止 snapshot_id-- 返回值 返回包含报告的全部 text 脚本信息的嵌套表类型 AWRRPT_ROW_TYPE
-- 把 awr 数据报表生成到指定路径的 text 文件
第二种参数解释如下：
-- 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。目前该参数不起作用
相关的系统表
DBMS_WORKLOAD_REPOSITORY 包相关的系统表包括快照信息管理表及快照信息表，这些系统表都只有创建了 DBMS_WORKLOAD_REPOSITORY 包后才能查看。
SQL> SELECT SF_CHECK_AWR_SYS();行号 SF_CHECK_AWR_SYS()
---------- ------------------
1 0已用时间: 0.238(毫秒). 执行号:23.
SQL> SP_INIT_AWR_SYS(1); ## 初始化AWR系统包，即：创建 DBMS_WORKLOAD_REPOSITORY 系统包
DMSQL 过程已成功完成
已用时间: 335.815(毫秒). 执行号:24.
SQL>
SQL> SELECT SF_CHECK_AWR_SYS();行号 SF_CHECK_AWR_SYS()
---------- ------------------
1 1已用时间: 1.764(毫秒). 执行号:25.
SQL>
SQL> select file_name,tablespace_name from dba_data_files;行号 FILE_NAME TABLESPACE_NAME
---------- ---------------------------------------- ---------------
1 /home/dm8/dmdbmsloc/data/DMDB/SYSTEM.DBF SYSTEM
2 /home/dm8/dmdbmsloc/data/DMDB/SYSAWR.DBF SYSAUX
3 /home/dm8/dmdbmsloc/data/DMDB/MAIN.DBF MAIN
4 /home/dm8/dmdbmsloc/data/DMDB/TEMP.DBF TEMP
5 /home/dm8/dmdbmsloc/data/DMDB/ROLL.DBF ROLL已用时间: 188.227(毫秒). 执行号:37.
SQL>
快照信息管理表
SQL> desc SYS.WRM$_WR_CONTROL;行号 NAME TYPE$ NULLABLE
---------- ------------- ---------------------------- --------
1 DBID INTEGER Y
2 SNAP_INTERVAL INTERVAL DAY(5) TO SECOND(1) Y
3 RETENTION INTERVAL DAY(5) TO SECOND(1) Y
4 TOPNSQL INTEGER Y
5 STATUS_FLAG INTEGER Y已用时间: 10.462(毫秒). 执行号:26.
SQL>
记录快照的相关控制信息，字段如下：
| 列名 | 类型 | 说明 |
|---|---|---|
| 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：关闭） |
SQL> desc SYS.WRM$_SNAPSHOT;行号 NAME TYPE$ NULLABLE
---------- ------------------- ----------- --------
1 SNAP_ID INTEGER N
2 DBID INTEGER Y
3 INSTANCE_NUMBER INTEGER Y
4 STARTUP_TIME DATETIME(6) Y
5 BEGIN_INTERVAL_TIME DATETIME(6) Y
6 END_INTERVAL_TIME DATETIME(6) Y
7 SNAP_LEVEL INTEGER Y7 rows got已用时间: 4.559(毫秒). 执行号:27.
SQL>
记录快照的相关信息，字段如下：
| 列名 | 类型 | 说明 |
|---|---|---|
| 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 | 带时区的结束快照时间 |
SQL> select name,id, TYPE$,SUBTYPE$ from SYS.SYSOBJECTS where name like 'WRM$_%';行号 NAME ID TYPE$ SUBTYPE$
---------- --------------- ----------- ------ --------
1 WRM$_SNAPSHOT 1281 SCHOBJ UTAB
2 WRM$_WR_CONTROL 1282 SCHOBJ UTAB已用时间: 1.025(毫秒). 执行号:34.
SQL>
快照信息表
快照信息表记录快照中数据库的一些具体信息。
SQL> desc SYS.WRH$_RESOURCE_LIMIT;行号 NAME TYPE$ NULLABLE
---------- ----------- ------------ --------
1 SNAP_ID INTEGER Y
2 NAME VARCHAR(128) Y
3 ID INTEGER Y
4 TYPE VARCHAR(64) Y
5 SPACE_LIMIT INTEGER Y
6 SPACE_USED INTEGER Y6 rows got已用时间: 1.237(毫秒). 执行号:28.
SQL>
显示所有快照中表、用户的空间限制信息，字段如下：
| 列名 | 类型 | 说明 |
|---|---|---|
| SNAP_ID | INTEGER | 快照 ID |
| NAME | VARCHAR(128) | 表/用户名 |
| ID | INTEGER | 表/用户 ID |
| TYPE | VARCHAR(64) | 表/用户类型 |
| SPACE_LIMIT | INTEGER | 空间限制大小，以页为单位 |
| SPACE_USED | INTEGER | 空间实际使用大小，以页为单位 |
SQL> desc SYS.WRH$_SQL_HISTORY;行号 NAME TYPE$ NULLABLE
---------- --------------------- ----------- --------
1 SNAP_ID INTEGER Y
2 SQL_ID INTEGER Y
3 START_TIME DATETIME(6) Y
4 TIME_USED BIGINT Y
5 BYTES_DYNAMIC_ALLOCED BIGINT Y
6 N_LOGIC_READ INTEGER Y
7 N_PHY_READ INTEGER Y
8 AFFECTED_ROWS INTEGER Y
9 HARD_PARSE_FLAG INTEGER Y
10 EXEC_ID INTEGER Y10 rows got已用时间: 4.670(毫秒). 执行号:29.
SQL>
当 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 会话句柄上的执行序号，同一个会话上的每个节点上值相同 |
SQL> desc SYS.WRH$_SQLTEXT;行号 NAME TYPE$ NULLABLE
---------- -------- ------- --------
1 SNAP_ID INTEGER Y
2 SQL_ID INTEGER Y
3 N_EXEC INTEGER Y
4 SQL_TEXT TEXT Y
5 SQL_NTH INTEGER Y已用时间: 4.297(毫秒). 执行号:30.
SQL>
显示所有快照的缓冲区中的 SQL 语句信息，字段如下：
| 列名 | 类型 | 说明 |
|---|---|---|
| SNAP_ID | INTEGER | 快照 ID |
| SQL_ID | INTEGER | 语句的 SQL ID |
| N_EXEC | INTEGER | 语句执行次数 |
| SQL_TEXT | TEXT | SQL 语句内容，超过 7168 的串不分行显示，而是显示成一行 |
| SQL_NTH | INTEGER | SQL 语句段号（从 0 开始） |
SQL> desc SYS.WRH$_SQLTEXT_TMP;行号 NAME TYPE$ NULLABLE
---------- -------- ------- --------
1 SNAP_ID INTEGER Y
2 SQL_ID INTEGER Y
3 N_EXEC INTEGER Y
4 SQL_TEXT TEXT Y
5 SQL_NTH INTEGER Y已用时间: 5.706(毫秒). 执行号:31.
SQL>
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 开始） |
SQL> desc SYS.WRH$_SESSIONS;行号 NAME TYPE$ NULLABLE
---------- ---------- ------- --------
1 SNAP_ID INTEGER Y
2 N_SESSIONS INTEGER Y
3 N_STMTS INTEGER Y已用时间: 4.323(毫秒). 执行号:32.
SQL>
显示所有快照中会话的具体信息，如执行的 sql 语句、主库名、当前会话状态、用户名等，字段如下：
| 列名 | 类型 | 说明 |
|---|---|---|
| SNAP_ID | INTEGER | 快照 ID |
| N_SESSIONS | TEXT | 会话数 |
| N_STMT | INTEGER | 已使用的 STMT 数量之和 |
SQL> desc SYS.WRH$_SESSION_WAIT_HISTORY;行号 NAME TYPE$ NULLABLE
---------- ----------------- ----------- --------
1 SNAP_ID INTEGER Y
2 SQL_ID INTEGER Y
3 EVENT# SMALLINT Y
4 EVENT VARCHAR(32) Y
5 TIME_WAITED_MICRO INTEGER Y
6 WAIT_CLASS# SMALLINT Y6 rows got已用时间: 4.562(毫秒). 执行号:33.
SQL>
显示所有快照中会话等待事件的历史信息，字段如下：
| 列名 | 类型 | 说明 |
|---|---|---|
| SNAP_ID | INTEGER | 快照 ID |
| SQL_ID | INTEGER | 语句的 SQL ID |
| EVENT# | SMALLINT | 事件编号 |
| EVENT | VARCHAR(32) | 事件名称 |
| TIME_WAITED_MICRO | INTEGER | 等待时间（单位：微秒） |
| WAIT_CLASS# | SMALLINT | 等待事件类别编号 |
注：快照信息表共有 38 张表，除了上述 6 张快照信息表与其后缀名相同的动态性能视图在结构上有些许不同，其余的快照信息表仅比对应的动态性能视图多一列名为 SNAP_ID 的整型列，其余结构完全相同，如 SYS.WRH_ARCH_QUEUE仅比VARCH_QUEUE 多一列 SNAP_ID，这里不再赘述，仅给出快照信息表的名称，具体可参考《DM8 系统管理员手册》附录 2。
SQL> select name,id, TYPE$,SUBTYPE$ from SYS.SYSOBJECTS where name like 'WRH$_%';行号 NAME ID TYPE$ SUBTYPE$
---------- ---------------------------- ----------- ------ --------
1 WRH$_ARCH_QUEUE 1311 SCHOBJ UTAB
2 WRH$_BUFFERPOOL 1286 SCHOBJ UTAB
3 WRH$_CKPT_HISTORY 1289 SCHOBJ UTAB
4 WRH$_DATAFILE 1287 SCHOBJ UTAB
5 WRH$_DB_CACHE 1295 SCHOBJ UTAB
6 WRH$_DEADLOCK_HISTORY 1294 SCHOBJ UTAB
7 WRH$_DMSQL_EXEC_TIME 1302 SCHOBJ UTAB
8 WRH$_DM_INI 1297 SCHOBJ UTAB
9 WRH$_HASH_MERGE_USED_HISTORY 1290 SCHOBJ UTAB
10 WRH$_LARGE_MEM_SQLS 1301 SCHOBJ UTAB
11 WRH$_LOCK 1305 SCHOBJ UTAB行号 NAME ID TYPE$ SUBTYPE$
---------- ------------------------- ----------- ------ --------
12 WRH$_MAL_INFO 1303 SCHOBJ UTAB
13 WRH$_MEM_POOL 1304 SCHOBJ UTAB
14 WRH$_MTAB_USED_HISTORY 1291 SCHOBJ UTAB
15 WRH$_PSEG_ITEMS 1293 SCHOBJ UTAB
16 WRH$_RESOURCE_LIMIT 1315 SCHOBJ UTAB
17 WRH$_RLOG 1308 SCHOBJ UTAB
18 WRH$_SCP_CACHE 1298 SCHOBJ UTAB
19 WRH$_SESSIONS 1319 SCHOBJ UTAB
20 WRH$_SESSION_HISTORY 1306 SCHOBJ UTAB
21 WRH$_SESSION_STAT 1313 SCHOBJ UTAB
22 WRH$_SESSION_WAIT_HISTORY 1320 SCHOBJ UTAB行号 NAME ID TYPE$ SUBTYPE$
---------- --------------------- ----------- ------ --------
23 WRH$_SORT_HISTORY 1312 SCHOBJ UTAB
24 WRH$_SQLTEXT 1317 SCHOBJ UTAB
25 WRH$_SQLTEXT_TMP 1318 SCHOBJ UTAB
26 WRH$_SQL_HISTORY 1316 SCHOBJ UTAB
27 WRH$_SQL_NODE_HISTORY 1300 SCHOBJ UTAB
28 WRH$_SQL_PLAN_NODE 1314 SCHOBJ UTAB
29 WRH$_SQL_STAT_HISTORY 1299 SCHOBJ UTAB
30 WRH$_SYSSTAT 1283 SCHOBJ UTAB
31 WRH$_SYSTEMINFO 1285 SCHOBJ UTAB
32 WRH$_SYSTEM_EVENT 1284 SCHOBJ UTAB
33 WRH$_TABLESPACE 1288 SCHOBJ UTAB行号 NAME ID TYPE$ SUBTYPE$
---------- -------------------- ----------- ------ --------
34 WRH$_TASK_QUEUE 1309 SCHOBJ UTAB
35 WRH$_TRACE_QUEUE 1310 SCHOBJ UTAB
36 WRH$_VIRTUAL_MACHINE 1307 SCHOBJ UTAB
37 WRH$_VPOOL 1296 SCHOBJ UTAB
38 WRH$_WAIT_HISTORY 1292 SCHOBJ UTAB38 rows got已用时间: 0.970(毫秒). 执行号:35.
SQL>
相关语句使用
SP_INIT_AWR_SYS，创建或删除 DBMS_WORKLOAD_REPOSITORY 系统包。其语法格式如下：
void
SP_INIT_AWR_SYS(CREATE_FLAG INT
)
其参数解释如下：
-- CREATE_FLAG-- 为 1 时表示创建 DBMS_WORKLOAD_REPOSITORY 包；为 0 表示删除该系统包
SF_CHECK_AWR_SYS，系统的 DBMS_WORKLOAD_REPOSITORY 系统包启用状态检测，其语法格式如下：
int
SF_CHECK_AWR_SYS ()
其返回值如下：
-- 0：未启用-- 1：已启用
AWR报告的生成
检查是否启用AWR，如下：
SQL> select SF_CHECK_AWR_SYS ();行号 SF_CHECK_AWR_SYS()
---------- ------------------
1 1已用时间: 7.332(毫秒). 执行号:4.
SQL>
查看一下当前的快照信息，如下：
SQL> SELECT * FROM SYS.WRM$_SNAPSHOT;
未选定行已用时间: 1.670(毫秒). 执行号:5.
SQL>
查看一下当前默认的快照收集的控制信息，如下：
SQL> SELECT * FROM SYS.WRM$_WR_CONTROL;行号 DBID SNAP_INTERVAL RETENTION TOPNSQL STATUS_FLAG
---------- ----------- ---------------------------------------- ---------------------------------------- ----------- -----------
1 NULL INTERVAL '0 1:0:0.0' DAY(5) TO SECOND(1) INTERVAL '8 0:0:0.0' DAY(5) TO SECOND(1) 30 0已用时间: 1.989(毫秒). 执行号:6.
SQL>
如上，状态位表示并没有开始收集！
设置时间间隔（10分钟），如下：
SQL> CALL DBMS_WORKLOAD_REPOSITORY.AWR_SET_INTERVAL(10);
DMSQL 过程已成功完成
已用时间: 131.333(毫秒). 执行号:7.
SQL> -- 新的控制信息，如下：
SQL> SELECT * FROM SYS.WRM$_WR_CONTROL;行号 DBID SNAP_INTERVAL RETENTION TOPNSQL STATUS_FLAG
---------- ----------- ----------------------------------------- ---------------------------------------- ----------- -----------
1 NULL INTERVAL '0 0:10:0.0' DAY(5) TO SECOND(1) INTERVAL '8 0:0:0.0' DAY(5) TO SECOND(1) 30 1已用时间: 0.324(毫秒). 执行号:8.
SQL>
如上，设置成功后，可以使用 CREATE_SNAPSHOT 手动创建快照，也可以等待设置的间隔时间后系统自动创建快照，快照 id 从 1 开始递增。
-- 等待了10分钟之后：SQL> SELECT * FROM SYS.WRM$_SNAPSHOT;行号 SNAP_ID DBID INSTANCE_NUMBER STARTUP_TIME BEGIN_INTERVAL_TIME END_INTERVAL_TIME SNAP_LEVEL
---------- ----------- ----------- --------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- -----------
1 1 NULL 1 2023-04-10 22:08:18.000000 NULL 2023-04-10 22:19:37.000000 1已用时间: 0.176(毫秒). 执行号:12.
SQL>
下面执行一些SQL，如下：
[dm8@dbserver ~]$ cd /home/dm8/dmdbmsloc/samples/instance_script/bookshop/UTF-8
[dm8@dbserver UTF-8]$ ll
总用量 56
-rwxr-xr-x 1 dm8 dm8 267 4月 6 21:11 CREATESCHEMA.sql
-rwxr-xr-x 1 dm8 dm8 10212 4月 6 21:11 CREATETABLE.sql
-rwxr-xr-x 1 dm8 dm8 40680 4月 6 21:11 INSERTSQL.sql
[dm8@dbserver UTF-8]$
SQL> start /home/dm8/dmdbmsloc/samples/instance_script/bookshop/UTF-8/CREATESCHEMA.sql
SQL> --创建表空间
CREATE TABLESPACE BOOKSHOP DATAFILE 'BOOKSHOP.DBF' size 150;
操作已执行
已用时间: 15.685(毫秒). 执行号:13.
SQL> /
--创建表空间
CREATE TABLESPACE BOOKSHOP DATAFILE 'BOOKSHOP.DBF' size 150;
第2 行附近出现错误[-3401]:表空间[BOOKSHOP]已存在.
已用时间: 0.160(毫秒). 执行号:0.
SQL> --创建模式
CREATE SCHEMA RESOURCES;
操作已执行
已用时间: 1.243(毫秒). 执行号:14.
SQL> CREATE SCHEMA PERSON;
操作已执行
已用时间: 1.296(毫秒). 执行号:15.
SQL> CREATE SCHEMA SALES;
操作已执行
已用时间: 1.012(毫秒). 执行号:16.
SQL> CREATE SCHEMA PRODUCTION;
操作已执行
已用时间: 1.169(毫秒). 执行号:17.
SQL> CREATE SCHEMA PURCHASING;
操作已执行
已用时间: 0.991(毫秒). 执行号:18.
SQL> CREATE SCHEMA OTHER;
操作已执行
已用时间: 1.146(毫秒). 执行号:19.
SQL>
SQL>
SQL> start /home/dm8/dmdbmsloc/samples/instance_script/bookshop/UTF-8/CREATETABLE.sql
SQL> --创建表
--CREATE PERSON.ADDRESS
create table PERSON.ADDRESS
(
ADDRESSID INT IDENTITY(1,1) PRIMARY KEY,
ADDRESS1 VARCHAR(60) NOT NULL,
ADDRESS2 VARCHAR(60),
CITY VARCHAR(30) NOT NULL,
POSTALCODE VARCHAR(15) NOT NULL
) STORAGE (on BOOKSHOP);
操作已执行
已用时间: 4.575(毫秒). 执行号:20.
...
SQL>
SQL> start /home/dm8/dmdbmsloc/samples/instance_script/bookshop/UTF-8/INSERTSQL.sql
...手动创建快照的命令为：
SQL> DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
HTML 14092 字数 637 段落DMSQL 过程已成功完成
已用时间: 77.515(毫秒). 执行号:289.
SQL>-- 此时的快照SQL> SELECT * FROM SYS.WRM$_SNAPSHOT;行号 SNAP_ID DBID INSTANCE_NUMBER STARTUP_TIME BEGIN_INTERVAL_TIME END_INTERVAL_TIME SNAP_LEVEL
---------- ----------- ----------- --------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- -----------
1 1 NULL 1 2023-04-10 22:08:18.000000 NULL 2023-04-10 22:19:37.000000 1
2 2 NULL 1 2023-04-10 22:08:18.000000 NULL 2023-04-10 22:25:55.000000 1已用时间: 0.699(毫秒). 执行号:290.
SQL>
查看 snapshot 的 id 在 1~2 范围内的 AWR 分析报告的带 html 格式的内容。然后复制到文本文件中，保存成 html 格式即可查看。如下：
SQL> SELECT * FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(1,2));行号 OUTPUT
---------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 <html><head><title>AWR Report for DB: DMDB, Inst: DMDW, Snaps: 1-2</title>
2 <style type="text/css">
3 body.awr {font:bold 10pt arial,helvetica,geneva,sans-serif;color:black; background:white;}
4 pre.awr {font:8pt courier;color:black; background:white;}
5 h1.awr {font:bold 20pt arial,helvetica,geneva,sans-serif;color:#336699;background-color:white;border-bottom:1px solid #cccc99;margin-top:0pt; margin-bottom:0pt;padding:0px 0px 0px 0px;}
6 h2.awr {font:bold 18pt arial,helvetica,geneva,sans-serif;color:#336699;background-color:white;margin-top:4pt; margin-bottom:0pt;}
7 h3.awr {font:bold 16pt arial,helvetica,geneva,sans-serif;color:#336699;background-color:white;margin-top:4pt; margin-bottom:0pt;}
8 li.awr {font: 8pt arial,helvetica,geneva,sans-serif; color:black; background:white;}
9 th.awrnobg {font:bold 8pt arial,helvetica,geneva,sans-serif; color:black; background:white;padding-left:4px; padding-right:4px;padding-bottom:2px}
10 th.awrbg {font:bold 8pt arial,helvetica,geneva,sans-serif; color:white; background:#0066cc;padding-left:4px; padding-right:4px;padding-bottom:2px}
11 td.awrnc {font:8pt arial,helvetica,geneva,sans-serif;color:black;background:white;vertical-align:top;}行号 OUTPUT
---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
12 td.awrc {font:8pt arial,helvetica,geneva,sans-serif;color:black;background:#ffffcc; vertical-align:top;}
...
...
行号 OUTPUT
---------- -----------------------------------------------------
2058 </table><p />
2059 <br /><a class="awr" href="#top">Back To Top</a><p />
2060 <p />End of Report</body></html>2060 rows got已用时间: 186.263(毫秒). 执行号:291.
SQL>
或者把 snapshot 的 id 在 1~2 范围内的 AWR 分析报告生成到 .html 文件。如下：
SQL> CALL SYS.AWR_REPORT_HTML(1,2,'/home/dm8','AWR_1_2.html');
DMSQL 过程已成功完成
已用时间: 230.449(毫秒). 执行号:294.
SQL>
环境清理，如下：
SQL> SELECT * FROM SYS.WRM$_SNAPSHOT;行号 SNAP_ID DBID INSTANCE_NUMBER STARTUP_TIME BEGIN_INTERVAL_TIME END_INTERVAL_TIME SNAP_LEVEL
---------- ----------- ----------- --------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- -----------
1 1 NULL 1 2023-04-10 22:08:18.000000 NULL 2023-04-10 22:19:37.000000 1
2 2 NULL 1 2023-04-10 22:08:18.000000 NULL 2023-04-10 22:25:55.000000 1
3 3 NULL 1 2023-04-10 22:08:18.000000 NULL 2023-04-10 22:29:37.000000 1已用时间: 0.239(毫秒). 执行号:295.
SQL>
SQL> CALL DBMS_WORKLOAD_REPOSITORY.AWR_CLEAR_HISTORY();
DMSQL 过程已成功完成
已用时间: 30.618(毫秒). 执行号:296.
SQL>
SQL> SELECT * FROM SYS.WRM$_SNAPSHOT;
未选定行已用时间: 0.307(毫秒). 执行号:297.
SQL>
SQL> SP_INIT_AWR_SYS(0);
DMSQL 过程已成功完成
已用时间: 389.192(毫秒). 执行号:298.
SQL>
SQL> SELECT SF_CHECK_AWR_SYS();行号 SF_CHECK_AWR_SYS()
---------- ------------------
1 0已用时间: 2.255(毫秒). 执行号:299.
SQL>
SQL> select file_name,tablespace_name from dba_data_files;行号 FILE_NAME TABLESPACE_NAME
---------- ------------------------------------------ ---------------
1 /home/dm8/dmdbmsloc/data/DMDB/SYSTEM.DBF SYSTEM
2 /home/dm8/dmdbmsloc/data/DMDB/BOOKSHOP.DBF BOOKSHOP
3 /home/dm8/dmdbmsloc/data/DMDB/MAIN.DBF MAIN
4 /home/dm8/dmdbmsloc/data/DMDB/TEMP.DBF TEMP
5 /home/dm8/dmdbmsloc/data/DMDB/ROLL.DBF ROLL已用时间: 3.467(毫秒). 执行号:300.
SQL>
注：通过 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);
