-
Notifications
You must be signed in to change notification settings - Fork 12
Home
Welcome to the eXperDB-Monitoring wiki!
#exclude detail
SELECT /* SELECTCPUMEMINFO */ A.INSTANCE_ID
, C.COLLECT_DT AS REG_DATE
, CASE WHEN C.USER_UTIL_RATE + C.NICE_UTIL_RATE + C.SYS_UTIL_RATE + C.WAIT_UTIL_RATE > 100 THEN 100 ELSE C.USER_UTIL_RATE + C.NICE_UTIL_RATE + C.SYS_UTIL_RATE + C.WAIT_UTIL_RATE END AS CPU_MAIN --PT_03_03_001, PT_04_02_001(AVG)
, 0 as CPU_LOGICAL_ID --PT_04_02_001
, 0 AS CORE_CPU_RATE
--, D.CPU_LOGICAL_ID --PT_04_02_001
--, CASE WHEN D.USER_UTIL_RATE + D.NICE_UTIL_RATE + D.SYS_UTIL_RATE + D.WAIT_UTIL_RATE > 100 THEN 100 ELSE D.USER_UTIL_RATE + D.NICE_UTIL_RATE + D.SYS_UTIL_RATE + D.WAIT_UTIL_RATE END AS CORE_CPU_RATE --PT_04_02_001
--, (SELECT SUM(PROC_CPU_UTIL) FROM TB_BACKEND_RSC RSC WHERE RSC.REG_DATE = B.REG_DATE AND RSC.RSC_REG_SEQ = B.RSC_REG_SEQ) AS PGSQL_UTIL_RATE --PT_04_05_001(CPU_PG)
, C.WAIT_UTIL_RATE --PT_04_05_001(CPU_WAIT)
, CASE WHEN E.MEM_TOTAL_KB != 0 THEN (((E.MEM_TOTAL_KB - (E.MEM_FREE_KB +E.MEM_BUFFERED_KB + E.MEM_CACHED_KB)) / E.MEM_TOTAL_KB ) * 100)::int ELSE E.MEM_TOTAL_KB END AS MEM_USED_RATE --PT_03_03_001, PT_04_02_001(MEM_AVG)
, CASE WHEN E.SWP_TOTAL_KB != 0 THEN (((E.SWP_TOTAL_KB - (E.SWP_FREE_KB + E.SWP_CACHED_KB)) / E.SWP_TOTAL_KB) * 100)::int ELSE E.SWP_TOTAL_KB END AS SWP_USED_RATE --PT_03_03_001(MEM SWAP_RATE), PT_04_05_001(PG_MEMORY)
, (E.MEM_TOTAL_KB / 1024)::int AS MEM_TOTAL_MB --PT_04_05_001(TOTAL MEM)
, (E.MEM_USED_KB / 1024)::int AS MEM_USED_MB --PT_04_05_001(USED MEM)
, (E.MEM_FREE_KB / 1024)::int AS MEM_FREE_MB --PT_04_05_001(FREE MEM)
, (E.SHM_KB / 1024)::int AS SHM_MB --PT_04_05_001(SHARED MEM)
, (E.MEM_BUFFERED_KB / 1024)::int AS MEM_BUFFERED_MB --PT_04_05_001(BUFFERS MEM)
, (E.MEM_CACHED_KB / 1024)::int AS MEM_CACHED_MB --PT_04_05_001(CACHED MEM)
, (E.SWP_TOTAL_KB / 1024)::int AS SWP_TOTAL_MB --PT_04_05_001(SWAP_TOTAL MEM)
, (E.SWP_USED_KB / 1024)::int AS SWP_USED_MB --PT_04_05_001(SWAP_USED MEM)
, (E.SWP_FREE_KB / 1024)::int AS SWP_FREE_MB --PT_04_05_001(SWAP_FREE MEM)
, (E.SWP_CACHED_KB / 1024)::int AS SWP_CACHED_MB --PT_04_05_001(SWAP_CACHED MEM)
, (((E.MEM_TOTAL_KB - (E.MEM_FREE_KB )) / E.MEM_TOTAL_KB ) * 100) AS BUFFER_RATE --PT_03_03_001, PT_04_02_001(MEM_AVG)
, CASE WHEN 0=0 THEN A.HOST_NAME ELSE A.CONN_NAME END AS HOST_NAME
, A.HA_GROUP
, CASE WHEN 0=0 THEN TO_CHAR(A.INSTANCE_ID, '00')||A.HOST_NAME ELSE TO_CHAR(A.INSTANCE_ID, '00')||A.CONN_NAME END AS SORTED_HOST_NAME
FROM TB_INSTANCE_INFO A
, (SELECT MAX(REG_DATE) AS REG_DATE, MAX(REG_TIME) AS REG_TIME, MAX(RSC_REG_SEQ) AS RSC_REG_SEQ, INSTANCE_ID
FROM TB_RSC_COLLECT_INFO
WHERE REG_DATE=TO_CHAR(NOW(),'YYYYMMDD')
AND RSC_REG_SEQ >= (SELECT (LAST_VALUE - (SELECT COUNT(*) * 2 + 1 FROM TB_INSTANCE_INFO)) + 1 AS LAST_VALUE FROM RSC_REG_SEQ)
GROUP BY INSTANCE_ID) B
, TB_CPU_STAT_MASTER C, TB_MEMORY_STAT E
WHERE A.COLLECT_YN = 'Y'
AND A.DELETE_YN = 'N'
--AND A.INSTANCE_ID IN ({0})
AND B.REG_DATE = C.REG_DATE
AND C.REG_DATE = E.REG_DATE
AND A.INSTANCE_ID = B.INSTANCE_ID
AND B.RSC_REG_SEQ = C.RSC_REG_SEQ
AND C.RSC_REG_SEQ = E.RSC_REG_SEQ
#include detail
SELECT /* SELECTCPUMEMINFO */ A.INSTANCE_ID
, C.COLLECT_DT AS REG_DATE
, CASE WHEN C.USER_UTIL_RATE + C.NICE_UTIL_RATE + C.SYS_UTIL_RATE + C.WAIT_UTIL_RATE > 100 THEN 100 ELSE C.USER_UTIL_RATE + C.NICE_UTIL_RATE + C.SYS_UTIL_RATE + C.WAIT_UTIL_RATE END AS CPU_MAIN --PT_03_03_001, PT_04_02_001(AVG)
, 0 as CPU_LOGICAL_ID --PT_04_02_001
, 0 AS CORE_CPU_RATE
--, D.CPU_LOGICAL_ID --PT_04_02_001
--, CASE WHEN D.USER_UTIL_RATE + D.NICE_UTIL_RATE + D.SYS_UTIL_RATE + D.WAIT_UTIL_RATE > 100 THEN 100 ELSE D.USER_UTIL_RATE + D.NICE_UTIL_RATE + D.SYS_UTIL_RATE + D.WAIT_UTIL_RATE END AS CORE_CPU_RATE --PT_04_02_001
--, (SELECT SUM(PROC_CPU_UTIL) FROM TB_BACKEND_RSC RSC WHERE RSC.REG_DATE = B.REG_DATE AND RSC.RSC_REG_SEQ = B.RSC_REG_SEQ) AS PGSQL_UTIL_RATE --PT_04_05_001(CPU_PG)
, C.WAIT_UTIL_RATE --PT_04_05_001(CPU_WAIT)
, CASE WHEN E.MEM_TOTAL_KB != 0 THEN (((E.MEM_TOTAL_KB - (E.MEM_FREE_KB +E.MEM_BUFFERED_KB + E.MEM_CACHED_KB)) / E.MEM_TOTAL_KB ) * 100)::int ELSE E.MEM_TOTAL_KB END AS MEM_USED_RATE --PT_03_03_001, PT_04_02_001(MEM_AVG)
, CASE WHEN E.SWP_TOTAL_KB != 0 THEN (((E.SWP_TOTAL_KB - (E.SWP_FREE_KB + E.SWP_CACHED_KB)) / E.SWP_TOTAL_KB) * 100)::int ELSE E.SWP_TOTAL_KB END AS SWP_USED_RATE --PT_03_03_001(MEM SWAP_RATE), PT_04_05_001(PG_MEMORY)
, (E.MEM_TOTAL_KB / 1024)::int AS MEM_TOTAL_MB --PT_04_05_001(TOTAL MEM)
, (E.MEM_USED_KB / 1024)::int AS MEM_USED_MB --PT_04_05_001(USED MEM)
, (E.MEM_FREE_KB / 1024)::int AS MEM_FREE_MB --PT_04_05_001(FREE MEM)
, (E.SHM_KB / 1024)::int AS SHM_MB --PT_04_05_001(SHARED MEM)
, (E.MEM_BUFFERED_KB / 1024)::int AS MEM_BUFFERED_MB --PT_04_05_001(BUFFERS MEM)
, (E.MEM_CACHED_KB / 1024)::int AS MEM_CACHED_MB --PT_04_05_001(CACHED MEM)
, (E.SWP_TOTAL_KB / 1024)::int AS SWP_TOTAL_MB --PT_04_05_001(SWAP_TOTAL MEM)
, (E.SWP_USED_KB / 1024)::int AS SWP_USED_MB --PT_04_05_001(SWAP_USED MEM)
, (E.SWP_FREE_KB / 1024)::int AS SWP_FREE_MB --PT_04_05_001(SWAP_FREE MEM)
, (E.SWP_CACHED_KB / 1024)::int AS SWP_CACHED_MB --PT_04_05_001(SWAP_CACHED MEM)
, (((E.MEM_TOTAL_KB - (E.MEM_FREE_KB )) / E.MEM_TOTAL_KB ) * 100) AS BUFFER_RATE --PT_03_03_001, PT_04_02_001(MEM_AVG)
, CASE WHEN 0=0 THEN A.HOST_NAME ELSE A.CONN_NAME END AS HOST_NAME
, A.HA_GROUP
, CASE WHEN 0=0 THEN TO_CHAR(A.INSTANCE_ID, '00')||A.HOST_NAME ELSE TO_CHAR(A.INSTANCE_ID, '00')||A.CONN_NAME END AS SORTED_HOST_NAME
FROM TB_INSTANCE_INFO A
, (SELECT MAX(REG_DATE) AS REG_DATE, MAX(REG_TIME) AS REG_TIME, MAX(RSC_REG_SEQ) AS RSC_REG_SEQ, INSTANCE_ID
FROM TB_RSC_COLLECT_INFO
WHERE REG_DATE=TO_CHAR(NOW(),'YYYYMMDD')
AND RSC_REG_SEQ >= (SELECT (LAST_VALUE - (SELECT COUNT(*) * 2 + 1 FROM TB_INSTANCE_INFO)) + 1 AS LAST_VALUE FROM RSC_REG_SEQ)
GROUP BY INSTANCE_ID) B
, TB_CPU_STAT_MASTER C, TB_CPU_STAT_DETAIL D, TB_MEMORY_STAT E
WHERE A.COLLECT_YN = 'Y'
AND A.DELETE_YN = 'N'
--AND A.INSTANCE_ID IN ({0})
AND B.REG_DATE = C.REG_DATE
AND C.REG_DATE = D.REG_DATE
AND D.REG_DATE = E.REG_DATE
AND A.INSTANCE_ID = B.INSTANCE_ID
AND B.RSC_REG_SEQ = C.RSC_REG_SEQ
AND C.RSC_REG_SEQ = D.RSC_REG_SEQ
AND D.RSC_REG_SEQ = E.RSC_REG_SEQ
tuned query
with RC as (
SELECT /* SELECTCPUMEMINFO */ A.INSTANCE_ID
, C.COLLECT_DT AS REG_DATE
, B.reg_date as RDATE
, b.rsc_reg_seq
, CASE WHEN C.USER_UTIL_RATE + C.NICE_UTIL_RATE + C.SYS_UTIL_RATE + C.WAIT_UTIL_RATE > 100 THEN 100 ELSE C.USER_UTIL_RATE + C.NICE_UTIL_RATE + C.SYS_UTIL_RATE + C.WAIT_UTIL_RATE END AS CPU_MAIN --PT_03_03_001, PT_04_02_001(AVG)
, C.WAIT_UTIL_RATE --PT_04_05_001(CPU_WAIT)
, CASE WHEN E.MEM_TOTAL_KB != 0 THEN (((E.MEM_TOTAL_KB - (E.MEM_FREE_KB +E.MEM_BUFFERED_KB + E.MEM_CACHED_KB)) / E.MEM_TOTAL_KB ) * 100)::int ELSE E.MEM_TOTAL_KB END AS MEM_USED_RATE --PT_03_03_001, PT_04_02_001(MEM_AVG)
, CASE WHEN E.SWP_TOTAL_KB != 0 THEN (((E.SWP_TOTAL_KB - (E.SWP_FREE_KB + E.SWP_CACHED_KB)) / E.SWP_TOTAL_KB) * 100)::int ELSE E.SWP_TOTAL_KB END AS SWP_USED_RATE --PT_03_03_001(MEM SWAP_RATE), PT_04_05_001(PG_MEMORY)
, (E.MEM_TOTAL_KB / 1024)::int AS MEM_TOTAL_MB --PT_04_05_001(TOTAL MEM)
, (E.MEM_USED_KB / 1024)::int AS MEM_USED_MB --PT_04_05_001(USED MEM)
, (E.MEM_FREE_KB / 1024)::int AS MEM_FREE_MB --PT_04_05_001(FREE MEM)
, (E.SHM_KB / 1024)::int AS SHM_MB --PT_04_05_001(SHARED MEM)
, (E.MEM_BUFFERED_KB / 1024)::int AS MEM_BUFFERED_MB --PT_04_05_001(BUFFERS MEM)
, (E.MEM_CACHED_KB / 1024)::int AS MEM_CACHED_MB --PT_04_05_001(CACHED MEM)
, (E.SWP_TOTAL_KB / 1024)::int AS SWP_TOTAL_MB --PT_04_05_001(SWAP_TOTAL MEM)
, (E.SWP_USED_KB / 1024)::int AS SWP_USED_MB --PT_04_05_001(SWAP_USED MEM)
, (E.SWP_FREE_KB / 1024)::int AS SWP_FREE_MB --PT_04_05_001(SWAP_FREE MEM)
, (E.SWP_CACHED_KB / 1024)::int AS SWP_CACHED_MB --PT_04_05_001(SWAP_CACHED MEM)
, (((E.MEM_TOTAL_KB - (E.MEM_FREE_KB )) / E.MEM_TOTAL_KB ) * 100) AS BUFFER_RATE --PT_03_03_001, PT_04_02_001(MEM_AVG)
, CASE WHEN 0=0 THEN A.HOST_NAME ELSE A.CONN_NAME END AS HOST_NAME
, A.HA_GROUP
, CASE WHEN 0=0 THEN TO_CHAR(A.INSTANCE_ID, '00')||A.HOST_NAME ELSE TO_CHAR(A.INSTANCE_ID, '00')||A.CONN_NAME END AS SORTED_HOST_NAME
FROM TB_INSTANCE_INFO A
, (SELECT MAX(REG_DATE) AS REG_DATE, MAX(REG_TIME) AS REG_TIME, MAX(RSC_REG_SEQ) AS RSC_REG_SEQ, INSTANCE_ID
FROM TB_RSC_COLLECT_INFO
WHERE REG_DATE=TO_CHAR(NOW(),'YYYYMMDD')
AND RSC_REG_SEQ >= (SELECT (LAST_VALUE - (SELECT COUNT(*) * 2 + 1 FROM TB_INSTANCE_INFO)) + 1 AS LAST_VALUE FROM RSC_REG_SEQ)
GROUP BY INSTANCE_ID) B
, TB_CPU_STAT_MASTER C, TB_MEMORY_STAT E
WHERE A.COLLECT_YN = 'Y'
AND A.DELETE_YN = 'N'
and B.REG_DATE=TO_CHAR(NOW(),'YYYYMMDD')
AND B.REG_DATE = C.REG_DATE
AND B.REG_DATE = E.REG_DATE
AND A.INSTANCE_ID = B.INSTANCE_ID
AND B.RSC_REG_SEQ = C.RSC_REG_SEQ
AND B.RSC_REG_SEQ = E.RSC_REG_SEQ
)
SELECT RC.*
, D.CPU_LOGICAL_ID --PT_04_02_001
, CASE WHEN D.USER_UTIL_RATE + D.NICE_UTIL_RATE + D.SYS_UTIL_RATE + D.WAIT_UTIL_RATE > 100 THEN 100 ELSE D.USER_UTIL_RATE + D.NICE_UTIL_RATE + D.SYS_UTIL_RATE + D.WAIT_UTIL_RATE END AS CORE_CPU_RATE --PT_04_02_001
FROM RC
join TB_CPU_STAT_DETAIL D on D.REG_DATE=TO_CHAR(NOW(),'YYYYMMDD') and RC.RDATE = D.reg_date and RC.rsc_reg_seq = D.rsc_reg_seq
/* SELECTBACKEND /
WITH PGSTA AS (SELECT INSTANCE_ID FROM TB_INSTANCE_INFO WHERE instance_id IN (1,2,3,4) AND (EXTENSIONS & 4) = 0),
PGACT AS (SELECT INSTANCE_ID FROM TB_INSTANCE_INFO WHERE instance_id IN (1,2,3,4) AND (EXTENSIONS & 4) > 0)
SELECT BACKEND. , CASE WHEN (BACKEND.SQL1 = '') THEN Q.QUERY ELSE BACKEND.SQL1 END AS SQL
FROM
(
SELECT A.INSTANCE_ID
, E.COLLECT_DT AS REG_DATE
, ROW_NUMBER() OVER (PARTITION BY A.INSTANCE_ID ORDER BY E.ELAPSED_TIME DESC, E.PROC_CPU_UTIL DESC) AS RNUM
, E.DB_NAME --PT_04_04_001(RESRCUTILPERBACK : DB)
, COALESCE(E.USER_NAME, '') AS USER_NAME
, E.CLIENT_ADDR
, E.CLIENT_APP
, CASE WHEN (E.STATE = 'active') THEN 'active' ELSE 'idle' END AS "STATE"
, E.PROCESS_ID --PT_04_04_001(RESRCUTILPERBACK : PID)
, E.CURRENT_PROC_READ_KB --PT_03_06_001(BACKEND PROCS : MEM USEAGE READ KB)
, E.CURRENT_PROC_WRITE_KB --PT_04_04_001(RESRCUTILPERBACK : MEM USAGE WRITE KB)
, ROUND(E.PROC_CPU_UTIL / 100,2) AS "CPU_USAGE" --PT_03_06_001(BACKEND PROCS : CPU_USAGE PER), PT_04_04_001(RESRCUTILPERBACK : CPU_USAGE PER)
, E.QUERY_START AS "START_TIME" --PT_03_06_001(BACKEND PROCS : QUERY_START)
, CASE WHEN (E.STATE = 'active' AND E.ELAPSED_TIME >= 0 ) THEN E.ELAPSED_TIME ELSE '0' END AS "ELAPSED_TIME" --PT_03_06_001(BACKEND PROCS : )
, E.SQL AS SQL1 --PT_03_06_001(BACKEND PROCS : SQL), PT_04_04_001(RESRCUTILPERBACK : SQL)
, E.ELAPSED_TIME
, CASE WHEN 0=0 THEN A.HOST_NAME ELSE A.CONN_NAME END AS HOST_NAME
, E.CURRENT_PROC_READ_KB + E.CURRENT_PROC_WRITE_KB AS "MEM_USAGE_KB"
, B.ACTV_REG_SEQ
, CD.CODE_NAME AS WAIT_EVENT
, E.QUERYID
FROM TB_INSTANCE_INFO A,
(SELECT MAX(REG_DATE) AS REG_DATE, MAX(ACTV_REG_SEQ) AS ACTV_REG_SEQ, INSTANCE_ID, MAX(REG_TIME) AS REG_TIME
FROM TB_ACTV_COLLECT_INFO
WHERE REG_DATE = '20201117'
--AND ACTV_REG_SEQ >= (SELECT (LAST_VALUE - (SELECT COUNT(*) * 3 FROM TB_INSTANCE_INFO)) + 1 AS LAST_VALUE FROM ACTV_REG_SEQ)
AND reg_time BETWEEN '15:00:00' AND '15:30:00'
GROUP BY INSTANCE_ID) B, TB_BACKEND_RSC E, TB_SYS_CODE CD
WHERE B.REG_DATE = E.REG_DATE
AND A.INSTANCE_ID = B.INSTANCE_ID
AND B.ACTV_REG_SEQ = E.ACTV_REG_SEQ
AND A.INSTANCE_ID IN (SELECT INSTANCE_ID FROM PGSTA)
AND A.COLLECT_YN = 'Y'
AND A.DELETE_YN = 'N'
AND E.SQL != 'BACKGROUND PROC'
AND E.SQL IS NOT NULL
--AND a.INSTANCE_ID = 1
AND COALESCE(E.WAIT_EVENT, 200) = CD.CODE
ORDER BY E.ELAPSED_TIME DESC, E.PROC_CPU_UTIL DESC
) BACKEND,
(SELECT DISTINCT instance_id, QUERYID, QUERY FROM TB_QUERY_INFO) Q
--TB_QUERY_INFO Q
WHERE BACKEND.RNUM <= 8
AND Q.INSTANCE_ID = BACKEND.INSTANCE_ID
AND Q.QUERYID = BACKEND.QUERYID
AND ELAPSED_TIME >= 0
WITH PGSTA AS (SELECT INSTANCE_ID FROM TB_INSTANCE_INFO WHERE (EXTENSIONS & 4) = 0),
PGACT AS (SELECT INSTANCE_ID FROM TB_INSTANCE_INFO WHERE (EXTENSIONS & 4) > 0)
SELECT BACKEND.* , CASE WHEN (BACKEND.SQL1 = '') THEN Q.QUERY ELSE BACKEND.SQL1 END AS SQL
FROM
(SELECT A.INSTANCE_ID
, E.COLLECT_DT AS REG_DATE
, ROW_NUMBER() OVER (PARTITION BY A.INSTANCE_ID ORDER BY E.ELAPSED_TIME DESC, E.PROC_CPU_UTIL DESC) AS RNUM
, E.DB_NAME --PT_04_04_001(RESRCUTILPERBACK : DB)
, COALESCE(E.USER_NAME, '') AS USER_NAME
, E.CLIENT_ADDR
, E.CLIENT_APP
, CASE WHEN (E.STATE = 'active') THEN 'active' ELSE 'idle' END AS "STATE"
, E.PROCESS_ID --PT_04_04_001(RESRCUTILPERBACK : PID)
, E.CURRENT_PROC_READ_KB --PT_03_06_001(BACKEND PROCS : MEM USEAGE READ KB)
, E.CURRENT_PROC_WRITE_KB --PT_04_04_001(RESRCUTILPERBACK : MEM USAGE WRITE KB)
, ROUND(E.PROC_CPU_UTIL / 100,2) AS "CPU_USAGE" --PT_03_06_001(BACKEND PROCS : CPU_USAGE PER), PT_04_04_001(RESRCUTILPERBACK : CPU_USAGE PER)
, E.QUERY_START AS "START_TIME" --PT_03_06_001(BACKEND PROCS : QUERY_START)
, CASE WHEN (E.STATE = 'active' AND E.ELAPSED_TIME >= 0 ) THEN E.ELAPSED_TIME ELSE '0' END AS "ELAPSED_TIME" --PT_03_06_001(BACKEND PROCS : )
, E.SQL AS SQL1 --PT_03_06_001(BACKEND PROCS : SQL), PT_04_04_001(RESRCUTILPERBACK : SQL)
, E.ELAPSED_TIME
, CASE WHEN 0=0 THEN A.HOST_NAME ELSE A.CONN_NAME END AS HOST_NAME
, E.CURRENT_PROC_READ_KB + E.CURRENT_PROC_WRITE_KB AS "MEM_USAGE_KB"
, B.ACTV_REG_SEQ
, CD.CODE_NAME AS WAIT_EVENT
, E.QUERYID
FROM TB_INSTANCE_INFO A,
(SELECT MAX(REG_DATE) AS REG_DATE, MAX(ACTV_REG_SEQ) AS ACTV_REG_SEQ, INSTANCE_ID, MAX(REG_TIME) AS REG_TIME
FROM TB_ACTV_COLLECT_INFO
WHERE REG_DATE = TO_CHAR(NOW(), 'YYYYMMDD')
AND ACTV_REG_SEQ >= (SELECT (LAST_VALUE - (SELECT COUNT(*) * 3 FROM TB_INSTANCE_INFO)) + 1 AS LAST_VALUE FROM ACTV_REG_SEQ)
GROUP BY INSTANCE_ID) B, TB_BACKEND_RSC E, TB_SYS_CODE CD
WHERE B.REG_DATE = E.REG_DATE
AND A.INSTANCE_ID = B.INSTANCE_ID
AND B.ACTV_REG_SEQ = E.ACTV_REG_SEQ
AND A.INSTANCE_ID IN (SELECT INSTANCE_ID FROM PGSTA)
AND A.COLLECT_YN = 'Y'
AND A.DELETE_YN = 'N'
AND E.SQL != 'BACKGROUND PROC'
AND E.SQL IS NOT NULL
AND COALESCE(E.WAIT_EVENT, 200) = CD.CODE
ORDER BY E.ELAPSED_TIME DESC, E.PROC_CPU_UTIL DESC) BACKEND, (SELECT DISTINCT instance_id, QUERYID, QUERY FROM TB_QUERY_INFO) Q --TB_QUERY_INFO Q
WHERE BACKEND.RNUM <= 1000
AND Q.INSTANCE_ID = BACKEND.INSTANCE_ID
AND Q.QUERYID = BACKEND.QUERYID
AND ELAPSED_TIME >= 0
order by instance_id, rnum
(SELECT DISTINCT instance_id, QUERYID, QUERY FROM TB_QUERY_INFO) Q to (SELECT DISTINCT QUERYID, QUERY FROM TB_QUERY_INFO)
------------------------SELECTREPLICATIONCURR
--
WITH MAXS AS (
SELECT INSTANCE_ID, MAX(REPL_REG_SEQ) REPL_REG_SEQ, MAX(COLLECT_DT) COLLECT_DT FROM TB_REPLICATION_INFO
WHERE REG_DATE = TO_CHAR(NOW(),'YYYYMMDD')
--AND INSTANCE_ID IN ({0})
AND COLLECT_DT >= (now() - ((5000/500)::varchar)::INTERVAL)
GROUP BY INSTANCE_ID
)
, MAXR AS
(
SELECT /* SELECTREPLICATIONCURR */
B.INSTANCE_ID,
C.REPL_INSTANCE_ID,
B.COLLECT_DT COLLECT_DT,
C.REPLAY_LAG REPLAY_LAG,
B.REPL_REG_SEQ REPL_REG_SEQ,
--C.REPL_REG_SEQ,
(C.REPLAY_LAG_SIZE / 1024 / 1024) AS REPLAY_LAG_SIZE
FROM TB_INSTANCE_INFO A, MAXS B JOIN TB_REPLICATION_LAG_INFO C
ON B.INSTANCE_ID = C.INSTANCE_ID
AND B.REPL_REG_SEQ = C.REPL_REG_SEQ
WHERE C.reg_date = TO_CHAR(NOW(),'YYYYMMDD')
and A.INSTANCE_ID = B.INSTANCE_ID
AND A.COLLECT_YN = 'Y'
AND A.DELETE_YN = 'N'
ORDER BY B.INSTANCE_ID, REPL_REG_SEQ
)
SELECT A.INSTANCE_ID INSTANCE_ID,
A.COLLECT_DT COLLECT_DT,
B.REPLAY_LAG REPLAY_LAG,
A.REPL_REG_SEQ REPL_REG_SEQ,
B.REPLAY_LAG_SIZE
FROM MAXS A LEFT OUTER JOIN MAXR B
ON A.INSTANCE_ID = B.REPL_INSTANCE_ID
---------------\
SELECT /* SELECTLOCKINFOACCUM */
MAX(C.COLLECT_DT) AS REG_DATE
,DB_NAME
,BLOCKING_USER
,BLOCKING_PID
,(SELECT query FROM TB_QUERY_INFO WHERE REG_DATE = TO_CHAR(NOW(), 'YYYYMMDD') AND INSTANCE_ID IN (1) AND QUERYID = BLOCKING_QUERY LIMIT 1) BLOCKING_QUERY
,BLOCKED_USER
,BLOCKED_PID
,(SELECT query FROM TB_QUERY_INFO WHERE REG_DATE = TO_CHAR(NOW(), 'YYYYMMDD') AND INSTANCE_ID IN (1) AND QUERYID = BLOCKED_QUERY LIMIT 1) BLOCKED_QUERY
,MAX(BLOCKED_DURATION) AS BLOCKED_DURATION
,LOCK_MODE
,QUERY_START
,XACT_START
,MAX(C.ACTV_REG_SEQ) AS ACTV_REG_SEQ
FROM TB_INSTANCE_INFO A, TB_ACTV_COLLECT_INFO B, TB_CURRENT_LOCK C
WHERE B.REG_DATE = '20210112'
AND B.REG_TIME BETWEEN '03:59:00' AND '04:01:00'
AND A.INSTANCE_ID = B.INSTANCE_ID
AND A.INSTANCE_ID IN (1)
AND B.REG_DATE = C.REG_DATE
AND B.ACTV_REG_SEQ = C.ACTV_REG_SEQ
AND A.COLLECT_YN = 'Y'
AND A.DELETE_YN = 'N'
GROUP BY A.INSTANCE_ID, B.REG_DATE, DB_NAME, BLOCKING_USER, XACT_START, blocking_pid, BLOCKING_QUERY, BLOCKED_USER, blocked_pid, BLOCKED_QUERY, LOCK_MODE, QUERY_START, ORDER_NO
ORDER BY B.REG_DATE, DB_NAME, blocking_pid, ORDER_NO, XACT_START LIMIT 1000;
SELECT A.INSTANCE_ID, MAX(A.REPL_REG_SEQ) MAX_REG_SEQ, ha_role
FROM TB_REPLICATION_INFO A
WHERE A.REG_DATE = TO_CHAR(NOW(), 'YYYYMMDD')
AND REPL_REG_SEQ >= (SELECT (LAST_VALUE - (SELECT COUNT(*) * 2 + 5 FROM TB_INSTANCE_INFO)) + 1 AS LAST_VALUE FROM REPL_REG_SEQ)
GROUP BY A.INSTANCE_ID, ha_role
)
SELECT C.INSTANCE_ID --PT_03_01_001
, COLLECT_REG_DATE AS REG_DATE
, B.COLLECT_REG_SEQ AS REG_SEQ
, C.HCHK_NAME AS HCHK_NAME
, CASE WHEN B.VALUE IS NULL THEN 0 ELSE ROUND(B.VALUE,2) END AS VALUE
, (CASE /* FIXED_THRESHOLD ='0' CRITICAL -> 300 WARNING -> 200 NORMAL -> 100 */
WHEN (B.VALUE >= CRITICAL_THRESHOLD AND IS_HIGHER ='0' AND FIXED_THRESHOLD ='0') THEN 300
WHEN (B.VALUE > WARNING_THRESHOLD AND B.VALUE < CRITICAL_THRESHOLD AND IS_HIGHER ='0' AND FIXED_THRESHOLD ='0') THEN 200
WHEN (B.VALUE <= WARNING_THRESHOLD AND IS_HIGHER ='0' AND FIXED_THRESHOLD ='0') THEN 100
WHEN (B.VALUE > WARNING_THRESHOLD AND IS_HIGHER ='1' AND FIXED_THRESHOLD ='0') THEN 100
WHEN (B.VALUE >= CRITICAL_THRESHOLD AND B.VALUE < WARNING_THRESHOLD AND IS_HIGHER ='1' AND FIXED_THRESHOLD ='0') THEN 200
WHEN (B.VALUE < CRITICAL_THRESHOLD AND IS_HIGHER ='1' AND FIXED_THRESHOLD ='0') THEN 300
WHEN (B.VALUE > WARNING_THRESHOLD AND IS_HIGHER ='0' AND FIXED_THRESHOLD ='1') THEN 200
WHEN (B.VALUE <= WARNING_THRESHOLD AND IS_HIGHER ='0' AND FIXED_THRESHOLD ='1') THEN 100
WHEN (B.VALUE > WARNING_THRESHOLD AND IS_HIGHER ='1' AND FIXED_THRESHOLD ='1') THEN 100
WHEN (B.VALUE <= WARNING_THRESHOLD AND IS_HIGHER ='1' AND FIXED_THRESHOLD ='1') THEN 200
WHEN (B.VALUE >= CRITICAL_THRESHOLD AND IS_HIGHER ='0' AND FIXED_THRESHOLD ='2') THEN 300
WHEN (B.VALUE < CRITICAL_THRESHOLD AND IS_HIGHER ='0' AND FIXED_THRESHOLD ='2') THEN 100
WHEN (B.VALUE >= CRITICAL_THRESHOLD AND IS_HIGHER ='1' AND FIXED_THRESHOLD ='2') THEN 100
WHEN (B.VALUE < CRITICAL_THRESHOLD AND IS_HIGHER ='1' AND FIXED_THRESHOLD ='2') THEN 300
WHEN (FIXED_THRESHOLD ='9') THEN 100
WHEN B.VALUE IS NULL THEN 100
ELSE 999
END) HCHK_VALUE
FROM TB_HCHK_COLLECT_INFO B
RIGHT OUTER JOIN TB_HCHK_THRD_LIST C
ON B.INSTANCE_ID = C.INSTANCE_ID
AND B.HCHK_NAME = C.HCHK_NAME
AND B.REG_DATE = TO_CHAR(NOW(), 'YYYYMMDD')
AND B.HCHK_REG_SEQ = (SELECT MAX(X.HCHK_REG_SEQ) FROM TB_HCHK_COLLECT_INFO X WHERE X.REG_DATE = TO_CHAR(NOW(), 'YYYYMMDD'))
, TEMP_MAX_SEQ D
WHERE B.INSTANCE_ID IN (1);
select instance_id,hchk_name,unit,is_higher,warning_threshold,critical_threshold,fixed_threshold from tb_hchk_thrd_list thtl where instance_id = 1
SELECT /* SELECTLOCKINFOACCUM */
--A.INSTANCE_ID
MAX(C.COLLECT_DT) AS REG_DATE,
DB_NAME
,BLOCKING_USER
,BLOCKING_PID
,BLOCKING_QUERY
,BLOCKED_QUERY
,(SELECT query FROM TB_QUERY_INFO WHERE REG_DATE = '20220408' AND INSTANCE_ID IN (50) AND QUERYID = BLOCKING_QUERY LIMIT 1) BLOCKING_QUERY
,BLOCKED_USER
,BLOCKED_PID
,(SELECT query FROM TB_QUERY_INFO WHERE REG_DATE = '20220408' AND INSTANCE_ID IN (50) AND QUERYID = BLOCKED_QUERY LIMIT 1) BLOCKED_QUERY
,MAX(BLOCKED_DURATION) AS BLOCKED_DURATION
,LOCK_MODE
,QUERY_START
,XACT_START
,MAX(C.ACTV_REG_SEQ) AS ACTV_REG_SEQ
FROM TB_INSTANCE_INFO A, TB_ACTV_COLLECT_INFO B, TB_CURRENT_LOCK C
WHERE B.REG_DATE = '20220408'
AND B.REG_TIME between '2022-04-08 13:10' and '2022-04-08 13:15'
AND A.INSTANCE_ID = B.INSTANCE_ID
AND A.INSTANCE_ID IN (50)
AND B.REG_DATE = C.REG_DATE
AND B.ACTV_REG_SEQ = C.ACTV_REG_SEQ
AND A.COLLECT_YN = 'Y'
AND A.DELETE_YN = 'N'
GROUP BY A.INSTANCE_ID, B.REG_DATE, DB_NAME, BLOCKING_USER, XACT_START, blocking_pid, BLOCKING_QUERY, BLOCKED_USER, blocked_pid, BLOCKED_QUERY, LOCK_MODE, QUERY_START, ORDER_NO
-------------=-=-=-=-
SELECT p.pid, now() - a.xact_start AS duration, coalesce(wait_event_type ||'.'|| wait_event, 'f') AS waiting, CASE WHEN a.query ~*'^autovacuum.*to prevent wraparound' THEN 'wraparound' WHEN a.query ~*'^vacuum' THEN 'user' ELSE 'regular' END AS mode, p.datname AS database, p.relid::regclass AS table, p.phase, pg_size_pretty(p.heap_blks_total * current_setting('block_size')::int) AS table_size, pg_size_pretty(pg_total_relation_size(relid)) AS total_size, pg_size_pretty(p.heap_blks_scanned * current_setting('block_size')::int) AS scanned, pg_size_pretty(p.heap_blks_vacuumed * current_setting('block_size')::int) AS vacuumed, round(100.0 * p.heap_blks_scanned / p.heap_blks_total, 1) AS scanned_pct, round(100.0 * p.heap_blks_vacuumed / p.heap_blks_total, 1) AS vacuumed_pct, p.index_vacuum_count, round(100.0 * p.num_dead_tuples / p.max_dead_tuples,1) AS dead_pct FROM pg_stat_progress_vacuum p JOIN pg_stat_activity a using (pid) ORDER BY now() - a.xact_start DESC;
WITH IST AS (SELECT * FROM TB_INSTANCE_INFO WHERE COLLECT_YN = 'Y' AND DELETE_YN='N' AND INSTANCE_ID = 42)
,TTE AS (
SELECT O.INSTANCE_ID, O.REG_DATE, O.OBJT_REG_SEQ, CAST(MAX(T.MAXAGE) AS REAL) M1, MAX(T.COLLECT_DT) COLLECT_DT, relid
FROM IST,
TB_OBJT_COLLECT_INFO O,
TB_TABLE_EXT_INFO T
WHERE O.REG_DATE='20220621'
and O.REG_DATE=T.reg_date
--AND O.OBJT_REG_SEQ = 2583
AND O.OBJT_REG_SEQ = (SELECT MAX(X.OBJT_REG_SEQ) FROM TB_OBJT_COLLECT_INFO X WHERE X.REG_DATE = TO_CHAR(NOW(), 'YYYYMMDD') AND X.INSTANCE_ID=O.INSTANCE_ID )
AND O.OBJT_REG_SEQ = T.OBJT_REG_SEQ
AND O.INSTANCE_ID = IST.INSTANCE_ID
GROUP BY O.REG_DATE,O.OBJT_REG_SEQ,O.INSTANCE_ID, relid
order by m1 desc
limit 1
)
SELECT DB_NAME, M1::NUMERIC(12,0) FROZENMAXAGE, TTE.COLLECT_DT
FROM TTE, TB_TABLE_INFO TTI
WHERE TTE.REG_DATE = TTI.REG_DATE
AND TTE.OBJT_REG_SEQ = TTI.OBJT_REG_SEQ
and tte.relid = tti.relid
LIMIT 1
create table svc_mntg_alarm ( alarm_id varchar(5) not null , alarm_prop varchar(2000) not null , alarm_val varchar(2000) not null , alarm_cntt varchar(2000) , occ_dts timestamp with time zone );
COMMENT ON column svc_mntg_alarm.alarm_id IS '알람ID' ; COMMENT ON column svc_mntg_alarm.alarm_prop IS '알람항목' ; COMMENT ON column svc_mntg_alarm.alarm_val IS '알람값' ; COMMENT ON column svc_mntg_alarm.alarm_cntt IS '알람내용' ; COMMENT ON column svc_mntg_alarm.occ_dts IS '발생일시' ; COMMENT ON TABLE svc_mntg_alarm IS '서비스모니터링알람' ;
alter table svc_mntg_alarm add constraint svc_mntg_alarm_pk primary key (alarm_id);
temp