-
Notifications
You must be signed in to change notification settings - Fork 3
/
Copy pathdm_job_config.yml
161 lines (161 loc) · 6.82 KB
/
dm_job_config.yml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
---
jobs:
# - 达梦
- name: "dm"
interval: '5s'
connections:
- 'dm://[USERNAME]:[PASSWORD]@[HOST]:[PORT]'
queries:
# 数据库qps
- name: "qps"
help: "DM QPS"
labels:
- "qps"
values:
- "stat_val"
query: |
select stat_val from v$sysstat where name = 'select statements';
# 表空间使用率 %
- name: "table_space_vacancy"
help: "DM table space vacancy"
labels:
- "tablespace_name"
values:
- "VACANCY"
query: |
SELECT A.TABLESPACE_NAME as tablespace_name, (100-ROUND((B.FREE / A.TOTAL * 100), 3)) AS VACANCY FROM ( SELECT TABLESPACE_NAME, SUM(BYTES)/ 1024 / 1024 AS TOTAL FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A, ( SELECT TABLESPACE_NAME, SUM(BYTES)/ 1024 / 1024 AS FREE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) B WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME;
# 数据库tps
- name: "tps"
help: "DM TPS"
labels:
- "tps"
values:
- "stat_val"
query: |
select stat_val from v$sysstat where name = 'transaction total count';
# 集群延迟时间 ms
- name: "cluster_delay_time"
help: "DM cluster delay time"
labels:
- "cluster_delay_time"
values:
- "timediff"
query: |
select TIMESTAMPDIFF(SQL_TSI_SECOND,APPLY_CMT_TIME,LAST_CMT_TIME) timediff from v$rapply_stat;
# 最大连接数
- name: "mx_connection"
help: "DM max connection"
labels:
- "max_conn"
values:
- "para_value"
query: |
select para_value from v$dm_ini where para_name = 'MAX_SESSIONS';
# 锁数量
- name: "total_lock"
help: "DM total lock number"
labels:
- "total_lock"
values:
- "COUNT"
query: |
SELECT COUNT(*) COUNT FROM V$LOCK;
# 死锁数量
- name: "dead_lock"
help: "DM total dead lock number"
labels:
- "dead_lock"
values:
- "DEADLOCKNUM"
query: |
SELECT COUNT(*) DEADLOCKNUM FROM V$LOCK WHERE BLOCKED=1;
# 事务数量
- name: "transaction_number"
help: "DM transaction number"
labels:
- "transaction_number"
values:
- "TRXNUM"
query: |
SELECT COUNT(*) TRXNUM FROM V$TRXWAIT;
# 线程数
- name: "total_thread"
help: "DM total thread number"
labels:
- "total_thread"
values:
- "THREADSNUM"
query: |
SELECT COUNT(*) THREADSNUM FROM V$THREADS;
# 表空间总大小
- name: "table_total_space"
help: "DM table total space"
labels:
- "tablespace_name"
values:
- "TOTAL_SIZE"
query: |
SELECT F.TABLESPACE_NAME as tablespace_name, T.TOTAL_SPACE AS TOTAL_SIZE FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BLOCKS * (SELECT PARA_VALUE / 1024 FROM V$DM_INI WHERE PARA_NAME = 'GLOBAL_PAGE_SIZE') / 1024)) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / 1048576)) TOTAL_SPACE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) T WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
# 表空间空闲大小
- name: "table_free_space"
help: "DM table free space"
labels:
- "tablespace_name"
values:
- "FREE_SIZE"
query: |
SELECT F.TABLESPACE_NAME as tablespace_name, F.FREE_SPACE AS FREE_SIZE FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BLOCKS * (SELECT PARA_VALUE / 1024 FROM V$DM_INI WHERE PARA_NAME = 'GLOBAL_PAGE_SIZE') / 1024)) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / 1048576)) TOTAL_SPACE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) T WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME;
# 数据库会话数
- name: "session_count"
help: "DM session count"
labels:
- "session_count"
values:
- "COUNT"
query: |
SELECT COUNT(SESS_ID) AS COUNT FROM V$SESSIONS WHERE STATE IN ('IDLE', 'ACTIVE');
# 数据库空闲会话数
- name: "free_session_count"
help: "DM free session count"
labels:
- "free_session_count"
values:
- "COUNT"
query: |
SELECT COUNT(SESS_ID) AS COUNT FROM V$SESSIONS WHERE STATE = 'IDLE';
# 数据库活跃会话数
- name: "active_session_count"
help: "DM active session count"
labels:
- "active_session_count"
values:
- "COUNT"
query: |
SELECT COUNT(SESS_ID) AS COUNT FROM V$SESSIONS WHERE STATE = 'ACTIVE';
# 慢执行数量（大于2s）
- name: "slow_sql_num"
help: "DM slow sql number"
labels:
- "slow_sql_num"
values:
- "NUM"
query: |
SELECT COUNT(*) NUM FROM ( SELECT SESS_ID, SQL_TEXT, DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) Y_EXETIME, SF_GET_SESSION_SQL(SESS_ID) FULLSQL, CLNT_IP FROM V$SESSIONS WHERE STATE = 'ACTIVE' ) WHERE Y_EXETIME >= 2;
# 内存池总量（MB）
- name: "zone_total_space"
help: "DM zone total space"
labels:
- "zone_type"
values:
- "TOTAL_VAL"
query: |
SELECT ZONE_TYPE as zone_type,TOTAL_VAL FROM ( SELECT 'HJ ZONE' AS ZONE_TYPE, ( SELECT SUM(STAT_VAL) FROM V$SYSSTAT WHERE ID IN (114, 115)) AS CURR_VAL, ( SELECT STAT_VAL FROM V$SYSSTAT WHERE ID IN (145)) AS RES_VAL, ( SELECT STAT_VAL FROM V$SYSSTAT WHERE ID IN (144)) AS TOTAL_VAL FROM DUAL UNION ALL SELECT 'HAGR ZONE', ( SELECT SUM(STAT_VAL) FROM V$SYSSTAT WHERE ID IN (116)),( SELECT STAT_VAL FROM V$SYSSTAT WHERE ID IN (143)),( SELECT STAT_VAL FROM V$SYSSTAT WHERE ID IN (142)) FROM DUAL UNION ALL SELECT 'SORT ZONE', ( SELECT SUM(STAT_VAL) FROM V$SYSSTAT WHERE ID IN (178)), NULL, ( SELECT STAT_VAL FROM V$SYSSTAT WHERE ID IN (177)) FROM DUAL);
# 内存池已使用量（MB）
- name: "zone_used_space"
help: "DM zone used space"
labels:
- "zone_type"
values:
- "CURR_VAL"
query: |
SELECT ZONE_TYPE as zone_type,CURR_VAL FROM ( SELECT 'HJ ZONE' AS ZONE_TYPE, ( SELECT SUM(STAT_VAL) FROM V$SYSSTAT WHERE ID IN (114, 115)) AS CURR_VAL, ( SELECT STAT_VAL FROM V$SYSSTAT WHERE ID IN (145)) AS RES_VAL, ( SELECT STAT_VAL FROM V$SYSSTAT WHERE ID IN (144)) AS TOTAL_VAL FROM DUAL UNION ALL SELECT 'HAGR ZONE', ( SELECT SUM(STAT_VAL) FROM V$SYSSTAT WHERE ID IN (116)),( SELECT STAT_VAL FROM V$SYSSTAT WHERE ID IN (143)),( SELECT STAT_VAL FROM V$SYSSTAT WHERE ID IN (142)) FROM DUAL UNION ALL SELECT 'SORT ZONE', ( SELECT SUM(STAT_VAL) FROM V$SYSSTAT WHERE ID IN (178)), NULL, ( SELECT STAT_VAL FROM V$SYSSTAT WHERE ID IN (177)) FROM DUAL);