通过7个问题，了解达梦数据库AWR的使用！
一:如何查看AWR是否已经启用？ 二:如何启用AWR？ 三:启用AWR后，会新增哪些对象？ 四:SYSAWR.DBF 数据文件自动扩展到最大10GB，会影响新的快照生成吗？ 五:AWR快照默认间隔是多少？如何调整？ 六:如何生成AWR报告？ 七:如何关闭AWR？
# 一:如何查看AWR是否已经启用？
检查是否启用AWR,0表示未启用，1表示已启用，默认未启用。
SQL> select SF_CHECK_AWR_SYS; 行号 SF_CHECK_AWR_SYS ---------- ---------------- 1 0
二:如何启用AWR？
启用AWR
SQL> SP_INIT_AWR_SYS(1); DMSQL 过程已成功完成 已用时间: 766.576(毫秒). 执行号:27313. SQL> select SF_CHECK_AWR_SYS; 行号 SF_CHECK_AWR_SYS ---------- ---------------- 1 1 已用时间: 1.989(毫秒). 执行号:387003.
# 三:启用AWR后，会新增哪些对象？
查看日志:
可以看到，新增了SYSAWR.DBF数据文件。
dmdba@CJC-DB-01:/db/dm8/dbms/log$tail -100f dm_CJC_202412.log 2024-12-25 17:20:50.137 [INFO] database P0000006279 T0000000000000009339 backup control file /db/dm8/ctl/dm01.ctl to file /db/dm8/ctl/dm_20241225172050_137077.ctl 2024-12-25 17:20:50.140 [INFO] database P0000006279 T0000000000000009339 backup control file /db/dm8/ctl/dm01.ctl to file /db/dm8/data/cjc/ctl_bak/dm_20241225172050_138752.ctl succeed 2024-12-25 17:20:50.140 [INFO] database P0000006279 T0000000000000009339 fil_set_tsid db->max_used_ts_id [15] 2024-12-25 17:20:50.142 [INFO] database P0000006279 T0000000000000009339 backup control file /db/dm8/ctl/dm01.ctl to file /db/dm8/ctl/dm_20241225172050_142850.ctl 2024-12-25 17:20:50.145 [INFO] database P0000006279 T0000000000000009339 backup control file /db/dm8/ctl/dm01.ctl to file /db/dm8/data/cjc/ctl_bak/dm_20241225172050_144388.ctl succeed 2024-12-25 17:20:50.146 [INFO] database P0000006279 T0000000000000009339 ifun_add_file_low initialize file[0] of ts[15], file_path[/db/dm8/data/cjc/SYSAWR.DBF]! 2024-12-25 17:20:50.151 [INFO] database P0000006279 T0000000000000009339 backup control file /db/dm8/ctl/dm01.ctl to file /db/dm8/ctl/dm_20241225172050_151916.ctl 2024-12-25 17:20:50.155 [INFO] database P0000006279 T0000000000000009339 backup control file /db/dm8/ctl/dm01.ctl to file /db/dm8/data/cjc/ctl_bak/dm_20241225172050_153878.ctl succeed 2024-12-25 17:20:50.155 [INFO] database P0000006279 T0000000000000009339 ifun_add_file_update_ctl_low update dm.ctl while add file[0] to ts[15], file_path[/db/dm8/data/cjc/SYSAWR.DBF] 2024-12-25 17:20:50.157 [INFO] database P0000006279 T0000000000000009339 backup control file /db/dm8/ctl/dm01.ctl to file /db/dm8/ctl/dm_20241225172050_157541.ctl 2024-12-25 17:20:50.160 [INFO] database P0000006279 T0000000000000009339 backup control file /db/dm8/ctl/dm01.ctl to file /db/dm8/data/cjc/ctl_bak/dm_20241225172050_159040.ctl succeed 2024-12-25 17:20:50.166 [INFO] database P0000006279 T0000000000000009339 backup control file /db/dm8/ctl/dm01.ctl to file /db/dm8/ctl/dm_20241225172050_166258.ctl 2024-12-25 17:20:50.169 [INFO] database P0000006279 T0000000000000009339 backup control file /db/dm8/ctl/dm01.ctl to file /db/dm8/data/cjc/ctl_bak/dm_20241225172050_167713.ctl succeed
新增了 SYSAUX 表空间，对应数据文件 SYSAWR.DBF，初始大小 192MB,最大可扩展到10GB。
SQL> select FILE_NAME,BYTES,AUTOEXTENSIBLE,MAXBYTES from dba_data_files where file_name like '%AWR%'; 行号 FILE_NAME BYTES AUTOEXTENSIBLE MAXBYTES ---------- --------------------------- -------------------- -------------- ----------- 1 /db/dm8/data/cjc/SYSAWR.DBF 201326592 YES 10737418240 已用时间: 71.281(毫秒). 执行号:385629.
新增了41张表
SQL> select OWNER,count(*) from dba_tables where TABLESPACE_NAME='SYSAUX'; 行号 OWNER COUNT(*) ---------- ----- -------------------- 1 SYS 41 已用时间: 155.460(毫秒). 执行号:27318.
SQL> select owner,table_name from dba_tables where TABLESPACE_NAME='SYSAUX' order by 2; 行号 owner table_name ---------- ----- ---------------------------- 1 SYS WRH$_ACTIVE_SESSION_HISTORY 2 SYS WRH$_ARCH_QUEUE 3 SYS WRH$_BUFFERPOOL 4 SYS WRH$_CKPT_HISTORY 5 SYS WRH$_DATAFILE 6 SYS WRH$_DB_CACHE 7 SYS WRH$_DEADLOCK_HISTORY 8 SYS WRH$_DM_INI 9 SYS WRH$_DMSQL_EXEC_TIME 10 SYS WRH$_HASH_MERGE_USED_HISTORY 11 SYS WRH$_LARGE_MEM_SQLS 12 SYS WRH$_LOCK 13 SYS WRH$_MAL_INFO 14 SYS WRH$_MEM_POOL 15 SYS WRH$_MTAB_USED_HISTORY 16 SYS WRH$_PSEG_ITEMS 17 SYS WRH$_RESOURCE_LIMIT 18 SYS WRH$_RLOG 19 SYS WRH$_SCP_CACHE 20 SYS WRH$_SESSION_HISTORY 21 SYS WRH$_SESSION_STAT 22 SYS WRH$_SESSION_WAIT_HISTORY 23 SYS WRH$_SESSIONS 24 SYS WRH$_SORT_HISTORY 25 SYS WRH$_SQL_HISTORY 26 SYS WRH$_SQL_NODE_HISTORY 27 SYS WRH$_SQL_PLAN_NODE 28 SYS WRH$_SQL_STAT_HISTORY 29 SYS WRH$_SQLTEXT 30 SYS WRH$_SQLTEXT_TMP 31 SYS WRH$_SYSSTAT 32 SYS WRH$_SYSTEM_EVENT 33 SYS WRH$_SYSTEMINFO 34 SYS WRH$_TABLESPACE 35 SYS WRH$_TASK_QUEUE 36 SYS WRH$_TRACE_QUEUE 37 SYS WRH$_VIRTUAL_MACHINE 38 SYS WRH$_WAIT_HISTORY 39 SYS WRM$_SNAPSHOT 40 SYS WRM$_SNAPSHOT_D 41 SYS WRM$_WR_CONTROL 41 rows got 已用时间: 39.174(毫秒). 执行号:27319.
其中有38个分区表
set pagesize 100 select OWNER,TABLE_NAME,PARTITION_COUNT from DBA_PART_TABLES; 行号 OWNER TABLE_NAME PARTITION_COUNT ---------- ----- ---------------------------- -------------------- 1 SYS WRH$_SYSSTAT 204 2 SYS WRH$_SYSTEM_EVENT 203 3 SYS WRH$_SYSTEMINFO 200 4 SYS WRH$_BUFFERPOOL 200 5 SYS WRH$_DATAFILE 200 6 SYS WRH$_TABLESPACE 200 7 SYS WRH$_CKPT_HISTORY 200 8 SYS WRH$_HASH_MERGE_USED_HISTORY 200 9 SYS WRH$_MTAB_USED_HISTORY 200 10 SYS WRH$_WAIT_HISTORY 200 11 SYS WRH$_PSEG_ITEMS 200 12 SYS WRH$_DEADLOCK_HISTORY 200 13 SYS WRH$_DB_CACHE 200 14 SYS WRH$_DM_INI 200 15 SYS WRH$_SCP_CACHE 200 16 SYS WRH$_SQL_STAT_HISTORY 200 17 SYS WRH$_SQL_NODE_HISTORY 200 18 SYS WRH$_LARGE_MEM_SQLS 200 19 SYS WRH$_DMSQL_EXEC_TIME 200 20 SYS WRH$_MAL_INFO 200 21 SYS WRH$_MEM_POOL 200 22 SYS WRH$_LOCK 200 23 SYS WRH$_SESSION_HISTORY 200 24 SYS WRH$_VIRTUAL_MACHINE 200 25 SYS WRH$_RLOG 200 26 SYS WRH$_TASK_QUEUE 200 27 SYS WRH$_TRACE_QUEUE 200 28 SYS WRH$_ARCH_QUEUE 200 29 SYS WRH$_SORT_HISTORY 200 30 SYS WRH$_SESSION_STAT 200 31 SYS WRH$_SQL_PLAN_NODE 200 32 SYS WRH$_RESOURCE_LIMIT 200 33 SYS WRH$_SQL_HISTORY 200 34 SYS WRH$_SQLTEXT 200 35 SYS WRH$_SQLTEXT_TMP 200 36 SYS WRH$_SESSIONS 200 37 SYS WRH$_SESSION_WAIT_HISTORY 200 38 SYS WRH$_ACTIVE_SESSION_HISTORY 1 38 rows got 已用时间: 5.233(毫秒). 执行号:385606.
查看其中一个分区表:
call sp_tabledef('SYS','WRH$_TABLESPACE');
分区键是 SNAP_ID
PARTITION BY RANGE("SNAP_ID")
行号 COLUMN_VALUE
---------- ---------------------------------------------------------------------------------------------------------------------------------------------
1 CREATE TABLE "SYS"."WRH$_TABLESPACE" ( "SNAP_ID" INT, "ID" INTEGER, "NAME" VARCHAR(32), "CACHE" VARCHAR(5), "TYPE$" TINYINT, "STATUS$" TINYINT, "MAX_SIZE" BIGINT, "TOTAL_SIZE" BIGINT, "FILE_NUM" INTEGER, "ENCRYPT_NAME" VARCHAR(32), "ENCRYPTED_KEY" VARCHAR(125), "COPY_NUM" INTEGER, "SIZE_MODE" VARCHAR(32), "OPT_NODE" INTEGER, "USED_SIZE" BIGINT, "SPACE_LIMIT_CNT" BIGINT, "EXTEND_FAILED_CNT" BIGINT, "N_RESERVE_EXTENTS" INTEGER, "FREE_EXTENTS" INTEGER, "MDF_FREE_COUNT" INTEGER)
2 PARTITION BY RANGE("SNAP_ID") ( PARTITION "PART0" VALUES LESS THAN(1) STORAGE(ON "SYSAUX", CLUSTERBTR) , PARTITION "PART1" VALUES LESS THAN(2) STORAGE(ON "SYSAUX", CLUSTERBTR) , PARTITION "PART2" VALUES LESS THAN(3) STORAGE(ON "SYSAUX", CLUSTERBTR) , ......;
33 rows got
已用时间: 18.872(毫秒). 执行号:385608.
````
数据文件大小增长过快，平均每天增长1GB。
````
dmdba@CJC-DB-01:/home/dmdba$ls -lrth /db/dm8/data/cjc/SYSAWR.DBF -rw-r--r-- 1 dmdba dinstall 5.0G Dec 27 17:32 /db/dm8/data/cjc/SYSAWR.DBF
````
最大到10GB。
````
dmdba@CJC-DB-01:/home/dmdba$ls -lrth /db/dm8/data/cjc/SYSAWR.DBF -rw-r--r-- 1 dmdba dinstall 10G Jan 14 14:59 /db/dm8/data/cjc/SYSAWR.DBF
````
# 四:SYSAWR.DBF 数据文件自动扩展到最大10GB，会影响新的快照生成吗？
无法新增新的快照。
````
SQL> CALL DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(); [-560]:超出[SYSAUX]表空间的空间上限值[10240]MB -560: line 101 -560: DBMS_WORKLOAD_REPOSITORY_DATA_LOW.AWR_TRIG_PROC line 124 -560: DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT line 4603 . 已用时间: 00:00:37.648. 执行号:0.
````
虽然显示失败了，但是 snap_id 增加了。
````
SQL> select snap_id,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from SYS.WRM$_SNAPSHOT; 行号 snap_id BEGIN_INTERVAL_TIME END_INTERVAL_TIME ---------- ----------- -------------------------- ----------------- ...... 892 954 2025-01-14 13:59:46.532316 NULL 893 955 2025-01-14 14:29:46.981927 NULL 894 956 2025-01-14 14:59:46.454320 NULL 895 957 2025-01-14 15:29:46.910500 NULL 896 958 2025-01-14 15:59:46.374035 NULL 896 rows got
````
用新生成的snap_id生成AWR，虽然显示成功了，但实际没有生成AWR。
````
SQL> SYS.AWR_REPORT_HTML(957,958,'/home/dmdba/awr','DM_AWR_20250114.HTML'); DMSQL 过程已成功完成 已用时间: 315.023(毫秒). 执行号:385616.
````
用前面较小的snap_id可以正常生成AWR。
# 五:AWR快照默认间隔是多少？如何调整？
默认1小时
````
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 已用时间: 0.905(毫秒). 执行号:27331.
````
间隔改成30分钟
````
SQL> CALL DBMS_WORKLOAD_REPOSITORY.AWR_SET_INTERVAL(30); SQL> SELECT * FROM SYS.WRM$_WR_CONTROL; 行号 DBID SNAP_INTERVAL RETENTION TOPNSQL STATUS_FLAG ---------- ----------- ----------------------------------------- ---------------------------------------- ----------- ----------- 1 NULL INTERVAL '0 0:30:0.0' DAY(5) TO SECOND(1) INTERVAL '8 0:0:0.0' DAY(5) TO SECOND(1) 30 1 已用时间: 0.522(毫秒). 执行号:27334.
````
# 六:如何生成AWR报告？
手动生成快照:
````
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
````
查询 AWR 快照：
````
select snap_id,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from SYS.WRM$_SNAPSHOT; 行号 snap_id BEGIN_INTERVAL_TIME END_INTERVAL_TIME ---------- ----------- -------------------------- ----------------- ...... 892 954 2025-01-14 13:59:46.532316 NULL 893 955 2025-01-14 14:29:46.981927 NULL 894 956 2025-01-14 14:59:46.454320 NULL 895 957 2025-01-14 15:29:46.910500 NULL 896 958 2025-01-14 15:59:46.374035 NULL 896 rows got
````
创建AWR报告
````
SYS.AWR_REPORT_HTML(快照ID1,快照ID2,'AWR报告存放路径','AWR报告名称.HTLM')：
````
````
SQL> SYS.AWR_REPORT_HTML(957,958,'/home/dmdba/awr','DM_AWR_20250114.HTML'); DMSQL 过程已成功完成 已用时间: 315.023(毫秒). 执行号:385616.
````
# 七:如何关闭AWR？
````
SQL> SP_INIT_AWR_SYS(0); DMSQL 过程已成功完成 已用时间: 00:00:01.322. 执行号:27310.
````
````
dmdba@CJC-DB-01:/db/dm8/dbms/log$tail -100f dm_CJC_202412.log 2024-12-25 17:18:30.642 [INFO] database P0000006279 T0000000000000009339 purg2_for_ts_flag_set begin... 2024-12-25 17:18:30.642 [INFO] database P0000006279 T0000000000000009339 purg2_for_ts_flag_set end! 2024-12-25 17:18:30.654 [INFO] database P0000006279 T0000000000000009339 purg2_for_ts_flag_reset_low from SUSPEND to NORMAL 2024-12-25 17:18:30.658 [INFO] database P0000006279 T0000000000000009339 drop tablespace ts_id = 14, archive flag = 0. 2024-12-25 17:18:30.658 [INFO] database P0000006279 T0000000000000009339 buf4_ts_buffer_fast_pool_clear ts_id: 14, pool_id: 2 begin... 2024-12-25 17:18:30.658 [INFO] database P0000006279 T0000000000000009339 buf4_ts_buffer_fast_pool_clear ts_id: 14, pool_id: 2 end, ret = 1 2024-12-25 17:18:30.658 [INFO] database P0000006279 T0000000000000009339 buf4_ts_buffer_clear_single ts_id: 14, pool_id: 3 begin... 2024-12-25 17:18:30.658 [INFO] database P0000006279 T0000000000000009339 buf4_ts_buffer_clear_single ts_id: 14, pool_id: 3 end, ret = 1 2024-12-25 17:18:30.658 [INFO] database P0000006279 T0000000000000009339 buf4_ts_buffer_clear_single ts_id: 14, pool_id: 3 begin... 2024-12-25 17:18:30.658 [INFO] database P0000006279 T0000000000000009339 buf4_ts_buffer_clear_single ts_id: 14, pool_id: 3 end, ret = 1 2024-12-25 17:18:30.658 [INFO] database P0000006279 T0000000000000009339 buf4_ts_buffer_clear_single ts_id: 14, pool_id: 5 begin... 2024-12-25 17:18:30.658 [INFO] database P0000006279 T0000000000000009339 buf4_ts_buffer_clear_single ts_id: 14, pool_id: 5 end, ret = 1 2024-12-25 17:18:30.658 [INFO] database P0000006279 T0000000000000009339 buf4_ts_buffer_clear_single ts_id: 14, pool_id: 6 begin... 2024-12-25 17:18:30.659 [INFO] database P0000006279 T0000000000000009339 buf4_ts_buffer_clear_single ts_id: 14, pool_id: 6 end, ret = 1 2024-12-25 17:18:30.659 [INFO] database P0000006279 T0000000000000009339 buf4_ts_buffer_clear_low ts_id[14] done, ret = 1 2024-12-25 17:18:30.659 [INFO] database P0000006279 T0000000000000009339 ctl_del_table_space_low SYSAUX, 14 2024-12-25 17:18:30.660 [INFO] database P0000006279 T0000000000000009339 backup control file /db/dm8/ctl/dm01.ctl to file /db/dm8/ctl/dm_20241225171830_660224.ctl 2024-12-25 17:18:30.663 [INFO] database P0000006279 T0000000000000009339 backup control file /db/dm8/ctl/dm01.ctl to file /db/dm8/data/cjc/ctl_bak/dm_20241225171830_662065.ctl succeed
````
关闭AWR时，自动删除了 SYSAUX 表空间和对应的数据文件，清理的非常彻底！
# 疑问:
手动生成两个快照期间，模拟了一个锁阻塞的SQL，但在AWR里的SQL部分没有找到对应的SQL。
###chenjuchao 20250115###
欢迎关注我的公众号《IT小Chen》
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29785807/viewspace-3068125/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
