Monitoring Query

************************ CPU Utilization *****************************

WITH host_cpu AS (SELECT HOST, AVG((TOTAL_CPU_USER_TIME + TOTAL_CPU_SYSTEM_TIME) /
NULLIF((TOTAL_CPU_USER_TIME + TOTAL_CPU_SYSTEM_TIME + TOTAL_CPU_IDLE_TIME +
TOTAL_CPU_WIO_TIME), 0) * 100) AS avg_cpu_usage FROM M_HOST_RESOURCE_UTILIZATION
WHERE SYS_TIMESTAMP > ADD_SECONDS(CURRENT_TIMESTAMP, -300) GROUP BY HOST) SELECT
HOST, CAST(ROUND(avg_cpu_usage, 0) AS INT) || '%' AS CPU_UTILIZATION, CASE WHEN
avg_cpu_usage < 70 THEN 'HEALTHY' WHEN avg_cpu_usage BETWEEN 70 AND 90 THEN 'WARNING'
WHEN avg_cpu_usage > 90 THEN 'CRITICAL' ELSE 'UNKNOWN' END AS STATUS FROM host_cpu ORDER
BY HOST;

************************* Memory Utilization *************************

WITH host_memory AS (SELECT HOST, AVG((USED_PHYSICAL_MEMORY /
NULLIF((FREE_PHYSICAL_MEMORY + USED_PHYSICAL_MEMORY), 0)) * 100) AS avg_mem_usage FROM
M_HOST_RESOURCE_UTILIZATION WHERE SYS_TIMESTAMP > ADD_SECONDS(CURRENT_TIMESTAMP, -
300) GROUP BY HOST) SELECT HOST, CAST(ROUND(avg_mem_usage, 0) AS INT) || '%' AS
MEMORY_UTILIZATION, CASE WHEN avg_mem_usage < 70 THEN 'HEALTHY' WHEN avg_mem_usage
BETWEEN 70 AND 90 THEN 'WARNING' WHEN avg_mem_usage > 90 THEN 'CRITICAL' ELSE 'UNKNOWN'
END AS STATUS FROM host_memory ORDER BY HOST;

************************* Active sessions*************************

WITH active_sessions AS ( SELECT HOST, COUNT(*) AS session_count FROM M_CONNECTIONS
WHERE END_TIME IS NULL AND CONNECTION_TYPE NOT LIKE '%History%' GROUP BY HOST )
SELECT HOST, COALESCE(session_count, 0) AS ACTIVE_SESSIONS, CASE WHEN
COALESCE(session_count, 0) < 50 THEN 'HEALTHY' WHEN COALESCE(session_count, 0) BETWEEN 50
AND 100 THEN 'WARNING' WHEN COALESCE(session_count, 0) > 100 THEN 'CRITICAL' ELSE 'UNKNOWN'
END AS STATUS FROM active_sessions ORDER BY HOST;

//**********************Query Execution Time************************

WITH query_stats AS (SELECT HOST,AVG(CAST(DURATION_MICROSEC AS DECIMAL) /
1000) AS avg_execution_time_ms,MAX(CAST(DURATION_MICROSEC AS DECIMAL) /
1000) AS max_execution_time_ms, COUNT(*) AS total_queries FROM
M_EXPENSIVE_STATEMENTS WHERE START_TIME <
ADD_SECONDS(CURRENT_TIMESTAMP, -3600) GROUP BY HOST) SELECT
HOST,ROUND(avg_execution_time_ms, 2) AS
AVG_EXECUTION_TIME_MS,ROUND(max_execution_time_ms,2)AS
MAX_EXECUTION_TIME_MS, total_queries AS TOTAL_QUERIES, CASE WHEN
avg_execution_time_ms < 100 THEN 'HEALTHY' WHEN avg_execution_time_ms
BETWEEN 100 AND 500 THEN 'WARNING' WHEN avg_execution_time_ms > 500 THEN
'CRITICAL' ELSE 'UNKNOWN' END AS STATUS FROM query_stats ORDER BY
avg_execution_time_ms DESC;
##################top 10 slowest query within 5 mints##############
WITH duration_converted AS (SELECT HOST,APP_USER,STATEMENT_STRING,
CAST(DURATION_MICROSEC AS DOUBLE) / 1000 AS duration_ms, RECORDS,
START_TIME FROM M_EXPENSIVE_STATEMENTS WHERE START_TIME < ADD_SECONDS
(CURRENT_TIMESTAMP, -300)) SELECT TOP 10
HOST,APP_USER,START_TIME,STATEMENT_STRING,ROUND(duration_ms, 2) AS
DURATION_MS,RECORDS FROM duration_converted ORDER BY duration_ms DESC;

Time Readable format

WITH duration_converted AS (SELECT HOST, APP_USER,STATEMENT_STRING,

CAST(DURATION_MICROSEC AS DOUBLE) / 1000 AS duration_ms, RECORDS,
START_TIME,TO_TIMESTAMP(SUBSTR(CAST(START_TIME AS VARCHAR(23)), 1, 17),
'YYYYMMDDHH24MISSFF3') AS START_TIME_READABLE FROM M_EXPENSIVE_STATEMENTS WHERE
START_TIME > ADD_SECONDS(CURRENT_TIMESTAMP, -300))SELECT TOP 10
HOST,APP_USER,START_TIME_READABLE AS START_TIME, STATEMENT_STRING,ROUND(duration_ms,
2) AS DURATION_MS,RECORDS FROM duration_converted ORDER BY duration_ms DESC;

################# slowest q by user within 5 ######################

SELECT APP_USER, AVG(TO_DECIMAL(DURATION_MICROSEC)/1000) AS avg_duration_ms, COUNT(*)

AS query_count,MAX(TO_DECIMAL(DURATION_MICROSEC) / 1000) AS max_duration_ms FROM
M_EXPENSIVE_STATEMENTS WHERE START_TIME > ADD_SECONDS(CURRENT_TIMESTAMP, -300)
GROUP BY APP_USER ORDER BY avg_duration_ms DESC;

*************************Lock wait time in 5 minuts **********************

WITH lock_stats AS (SELECT HOST,LOCK_TYPE,TOTAL_LOCK_WAITS, TOTAL_LOCK_WAIT_TIME,

ROUND(CAST(TOTAL_LOCK_WAIT_TIME AS DOUBLE) / NULLIF(TOTAL_LOCK_WAITS, 0), 2) AS
AVG_WAIT_TIME_MS

--,STATISTICS_TIME -- or whatever the timestamp column is called

FROM M_LOCK_WAITS_STATISTICS WHERE TOTAL_LOCK_WAITS > 0

-- AND STATISTICS_TIME > ADD_SECONDS(CURRENT_TIMESTAMP, -300) -- Last 5 minutes

)SELECT HOST, LOCK_TYPE, TOTAL_LOCK_WAITS AS TOTAL_WAITS, TOTAL_LOCK_WAIT_TIME AS

TOTAL_WAIT_TIME_MS, AVG_WAIT_TIME_MS,CASE WHEN AVG_WAIT_TIME_MS < 50 THEN 'HEALTHY'
WHEN AVG_WAIT_TIME_MS BETWEEN 50 AND 200 THEN 'WARNING' WHEN AVG_WAIT_TIME_MS >
200 THEN 'CRITICAL' ELSE 'UNKNOWN' END AS STATUS

--,STATISTICS_TIME AS LAST_MEASUREMENT
FROM lock_stats ORDER BY AVG_WAIT_TIME_MS DESC;

//**************************New table created within 24 hr ************************

SELECT SCHEMA_NAME, TABLE_NAME, CREATE_TIME, TO_TIMESTAMP(SUBSTR(CAST(CREATE_TIME AS

VARCHAR(23)), 1, 17), 'YYYYMMDDHH24MISSFF3') AS CREATE_TIME_READABLE, TABLE_TYPE,
IS_COLUMN_TABLE, IS_SYSTEM_TABLE, IS_LOGGED FROM TABLES WHERE CREATE_TIME >
ADD_SECONDS(CURRENT_TIMESTAMP, -86400) -- Last 24 hours

AND IS_SYSTEM_TABLE = 'FALSE' -- Exclude system tables

ORDER BY CREATE_TIME DESC;

#################### just count new table with in 24 hr #############################

SELECT COUNT(*) AS NEW_TABLES_COUNT, MAX(TO_TIMESTAMP(SUBSTR(CAST(CREATE_TIME AS

VARCHAR(23)), 1, 17), 'YYYYMMDDHH24MISSFF3')) AS LATEST_CREATION FROM TABLES WHERE
CREATE_TIME > ADD_SECONDS(CURRENT_TIMESTAMP, -86400) AND IS_SYSTEM_TABLE = 'FALSE';

################## new table based on size #######################################

SELECT t.SCHEMA_NAME, t.TABLE_NAME, TO_TIMESTAMP(SUBSTR(CAST(t.CREATE_TIME AS

VARCHAR(23)), 1, 17), 'YYYYMMDDHH24MISSFF3') AS CREATION_TIME, t.TABLE_TYPE, CASE
t.IS_COLUMN_TABLE WHEN 'TRUE' THEN 'Column Store' ELSE 'Row Store' END AS STORE_TYPE,
COALESCE(s.RECORD_COUNT, 0) AS RECORD_COUNT FROM TABLES t LEFT JOIN M_CS_TABLES s ON
t.SCHEMA_NAME = s.SCHEMA_NAME AND t.TABLE_NAME = s.TABLE_NAME WHERE t.CREATE_TIME >
ADD_SECONDS(CURRENT_TIMESTAMP, -86400) AND t.IS_SYSTEM_TABLE = 'FALSE' ORDER BY
t.CREATE_TIME DESC;

********************* Row store Memory Size ********************************** SELECT

SELECT SCHEMA_NAME, TABLE_NAME, RECORD_COUNT, ROUND ((USED_FIXED_PART_SIZE +
USED_VARIABLE_PART_SIZE) / 1024 / 1024 / 1024, 3) AS TOTAL_USED_GB,
ROUND((ALLOCATED_FIXED_PART_SIZE + ALLOCATED_VARIABLE_PART_SIZE) / 1024 / 1024 / 1024, 3)
AS TOTAL_ALLOCATED_GB, ROUND((FIXED_PART_FRAGMENT_SIZE +
VARIABLE_PART_FRAGMENT_SIZE) / 1024 / 1024 / 1024, 3) AS TOTAL_FRAGMENTATION_GB, CASE
WHEN (USED_FIXED_PART_SIZE + USED_VARIABLE_PART_SIZE) / 1024 / 1024 / 1024 < 1 THEN
'HEALTHY' WHEN (USED_FIXED_PART_SIZE + USED_VARIABLE_PART_SIZE) / 1024 / 1024 / 1024
BETWEEN 1 AND 5 THEN 'WARNING' WHEN (USED_FIXED_PART_SIZE + USED_VARIABLE_PART_SIZE) /
1024 / 1024 / 1024 > 5 THEN 'CRITICAL' ELSE 'UNKNOWN' END AS SIZE_STATUS, CASE WHEN
(FIXED_PART_FRAGMENT_SIZE + VARIABLE_PART_FRAGMENT_SIZE) / 1024 / 1024 / 1024 < 0.1 THEN
'HEALTHY' WHEN (FIXED_PART_FRAGMENT_SIZE + VARIABLE_PART_FRAGMENT_SIZE) / 1024 / 1024 /
1024 BETWEEN 0.1 AND 0.5 THEN 'WARNING' WHEN (FIXED_PART_FRAGMENT_SIZE +
VARIABLE_PART_FRAGMENT_SIZE) / 1024 / 1024 / 1024 > 0.5 THEN 'CRITICAL' ELSE 'UNKNOWN' END AS
FRAGMENTATION_STATUS, LOAD_STATUS FROM M_RS_TABLES

-- WHERE TABLE_TYPE = 'ROW'

ORDER BY TOTAL_USED_GB DESC;

******************* *****Column Store Fragmentation ***********************************

SELECT SCHEMA_NAME, TABLE_NAME, READ_COUNT, MEMORY_SIZE_IN_TOTAL AS TOTAL_MEMORY,
IS_DELTA_LOADED, IS_DELTA2_ACTIVE, IS_LOG_DELTA, PERSISTENT_MERGE, CASE WHEN
IS_DELTA_LOADED = 'TRUE' AND IS_DELTA2_ACTIVE = 'TRUE' THEN 'HIGH_FRAGMENTATION' WHEN
IS_DELTA_LOADED = 'TRUE' THEN 'MODERATE_FRAGMENTATION' ELSE 'LOW_FRAGMENTATION' END
AS FRAGMENTATION_STATUS, CASE WHEN IS_DELTA_LOADED = 'TRUE' AND IS_DELTA2_ACTIVE =
'TRUE' THEN 'CRITICAL' WHEN IS_DELTA_LOADED = 'TRUE' THEN 'WARNING' ELSE 'HEALTHY' END AS
FRAGMENTATION_HEALTH FROM M_CS_TABLES WHERE LOAD_UNIT = 'COLUMN' ORDER BY
FRAGMENTATION_HEALTH, READ_COUNT DESC;

***************************Unloaded Tables (Not in Memory) *************************

SELECT SCHEMA_NAME, TABLE_NAME, READ_COUNT, ROUND(MEMORY_SIZE_IN_TOTAL / 1024
/ 1024 / 1024, 3) AS MEMORY_SIZE_GB, LOADED, CASE WHEN LAST_REPLAY_LOG_TIME IS
NOT NULL THEN '' || SUBSTR(LAST_REPLAY_LOG_TIME, 1, 4) || '' ||
SUBSTR(LAST_REPLAY_LOG_TIME, 5, 2) || '' || SUBSTR(LAST_REPLAY_LOG_TIME, 7, 2)
|| ' ' || SUBSTR(LAST_REPLAY_LOG_TIME, 9, 2) || ':' ||
SUBSTR(LAST_REPLAY_LOG_TIME, 11, 2) || ':' || SUBSTR(LAST_REPLAY_LOG_TIME, 13,
2) ELSE 'UNKNOWN' END AS LAST_ACCESS_FORMATTED, CASE WHEN LOADED = 'NO' AND
READ_COUNT > 10000 THEN 'CRITICAL' WHEN LOADED = 'NO' AND READ_COUNT BETWEEN
1000 AND 10000 THEN 'WARNING' WHEN LOADED = 'NO' THEN 'HEALTHY' ELSE 'LOADED'
END AS LOAD_STATUS FROM M_CS_TABLES WHERE LOAD_UNIT = 'COLUMN' AND LOADED =
'NO' ORDER BY READ_COUNT DESC limit 10;

********************************Delta Merge Status**********************************

SELECT SCHEMA_NAME, TABLE_NAME, READ_COUNT,MEMORY_SIZE_IN_TOTAL, -- Delta
Status
CASE WHEN IS_DELTA_LOADED = 'TRUE' AND IS_DELTA2_ACTIVE = 'TRUE' THEN
'CRITICAL'WHEN IS_DELTA_LOADED = 'TRUE' THEN 'WARNING' ELSE 'HEALTHY' END AS
DELTA_STATUS, -- Merge Frequency
CASE WHEN LAST_MERGE_TIME IS NULL THEN 'NEVER' WHEN LAST_MERGE_TIME >
ADD_SECONDS(CURRENT_TIMESTAMP, -3600) THEN 'HOURLY' WHEN LAST_MERGE_TIME >
ADD_SECONDS(CURRENT_TIMESTAMP, -86400) THEN 'DAILY' WHEN LAST_MERGE_TIME >
ADD_SECONDS(CURRENT_TIMESTAMP, -604800) THEN 'WEEKLY'ELSE 'INFREQUENT' END AS
MERGE_FREQUENCY, -- Estimated Merge Duration based on size
CASE WHEN MEMORY_SIZE_IN_TOTAL > 2147483648 THEN 'LONG_DURATION'
WHEN MEMORY_SIZE_IN_TOTAL > 1073741824 THEN 'MEDIUM_DURATION' ELSE
'SHORT_DURATION' END AS ESTIMATED_DURATION,-- Estimated Rows Merged
ROUND(MEMORY_SIZE_IN_TOTAL / 500) AS ESTIMATED_ROWS_MERGED, -- Merge Type
CASE WHEN PERSISTENT_MERGE = 'TRUE' THEN 'PERSISTENT' WHEN IS_LOG_DELTA =
'TRUE' THEN 'LOG_BASED' ELSE 'STANDARD' END AS MERGE_TYPE,-- Resource Usage
CASE WHEN MEMORY_SIZE_IN_TOTAL > 2147483648 THEN 'HIGH_RESOURCE' WHEN
MEMORY_SIZE_IN_TOTAL > 1073741824 THEN 'MEDIUM_RESOURCE'ELSE 'LOW_RESOURCE'
END AS RESOURCE_USAGE, LAST_MERGE_TIME, LAST_REPLAY_LOG_TIME, -- Added column
for Healthy Status based on Merge Delay
CASE WHEN LAST_MERGE_TIME IS NULL THEN 'CRITICAL' WHEN LAST_MERGE_TIME >
ADD_SECONDS(CURRENT_TIMESTAMP, -86400) THEN 'HEALTHY' -- ≤ 1 day
WHEN LAST_MERGE_TIME > ADD_SECONDS(CURRENT_TIMESTAMP, -259200) THEN 'WARNING'
-- 2-3 days
 ELSE 'CRITICAL' -- > 3 days
END AS MERGE_HEALTH_STATUS FROM M_CS_TABLES WHERE LOAD_UNIT = 'COLUMN'
ORDER BY MEMORY_SIZE_IN_TOTAL DESC;

***************************inconsistence table/unused table******************************

SELECT SCHEMA_NAME, TABLE_NAME, READ_COUNT, MEMORY_SIZE_IN_TOTAL,
LAST_REPLAY_LOG_TIME, -- Last Access Analysis
CASE WHEN LAST_REPLAY_LOG_TIME IS NULL THEN 'NEVER_ACCESSED' WHEN
LAST_REPLAY_LOG_TIME < ADD_SECONDS(CURRENT_TIMESTAMP, -7776000) THEN
'OVER_90_DAYS' -- 90 days
WHEN LAST_REPLAY_LOG_TIME < ADD_SECONDS(CURRENT_TIMESTAMP, -15552000)
THEN 'OVER_180_DAYS' -- 180 days
ELSE 'RECENTLY_ACCESSED' END AS ACCESS_AGE, -- Health Status
CASE WHEN LAST_REPLAY_LOG_TIME IS NULL THEN 'CRITICAL' WHEN
LAST_REPLAY_LOG_TIME < ADD_SECONDS(CURRENT_TIMESTAMP, -15552000) THEN
'CRITICAL'
WHEN LAST_REPLAY_LOG_TIME < ADD_SECONDS(CURRENT_TIMESTAMP, -7776000)
THEN 'WARNING' ELSE 'HEALTHY'
END AS ACCESS_STATUS, -- Inconsistency Check
CASE WHEN LAST_CONSISTENCY_CHECK_TIME IS NULL THEN 'NEVER_CHECKED' WHEN
LAST_CONSISTENCY_CHECK_TIME < ADD_SECONDS(CURRENT_TIMESTAMP, -2592000) THEN
'STALE_CONSISTENCY'
ELSE 'CONSISTENT' END AS CONSISTENCY_STATUS FROM M_CS_TABLES WHERE
LOAD_UNIT = 'COLUMN' ORDER BY CASE WHEN LAST_REPLAY_LOG_TIME IS NULL THEN 1
WHEN LAST_REPLAY_LOG_TIME < ADD_SECONDS(CURRENT_TIMESTAMP, -15552000) THEN 2
WHEN LAST_REPLAY_LOG_TIME < ADD_SECONDS(CURRENT_TIMESTAMP, -7776000)
THEN 3 ELSE 4 END, MEMORY_SIZE_IN_TOTAL DESC;

***************************Table Growth Trend**************************************

SELECT SCHEMA_NAME,
TABLE_NAME,
MEMORY_SIZE_IN_TOTAL AS CURRENT_SIZE,
LAST_ESTIMATED_MEMORY_SIZE AS PREVIOUS_SIZE,
ROUND(((MEMORY_SIZE_IN_TOTAL - LAST_ESTIMATED_MEMORY_SIZE) * 100.0 /
NULLIF(LAST_ESTIMATED_MEMORY_SIZE, 0)), 2) AS GROWTH_PERCENT,
CASE
WHEN ((MEMORY_SIZE_IN_TOTAL - LAST_ESTIMATED_MEMORY_SIZE) * 100.0 /
NULLIF(LAST_ESTIMATED_MEMORY_SIZE, 0)) > 20 THEN 'CRITICAL_HIGH_GROWTH'
WHEN ((MEMORY_SIZE_IN_TOTAL - LAST_ESTIMATED_MEMORY_SIZE) * 100.0 /
NULLIF(LAST_ESTIMATED_MEMORY_SIZE, 0)) BETWEEN 10 AND 20 THEN
'WARNING_MEDIUM_GROWTH'
WHEN ((MEMORY_SIZE_IN_TOTAL - LAST_ESTIMATED_MEMORY_SIZE) * 100.0 /
NULLIF(LAST_ESTIMATED_MEMORY_SIZE, 0)) <= 10 THEN 'HEALTHY_LOW_GROWTH'
ELSE 'STABLE'
END AS GROWTH_STATUS,
LAST_ESTIMATED_MEMORY_SIZE_TIME,
DAYS_BETWEEN(CURRENT_TIMESTAMP, LAST_ESTIMATED_MEMORY_SIZE_TIME) AS
DAYS_SINCE_MEASUREMENT
FROM M_CS_TABLES WHERE LOAD_UNIT = 'COLUMN' AND LAST_ESTIMATED_MEMORY_SIZE >0
AND MEMORY_SIZE_IN_TOTAL > LAST_ESTIMATED_MEMORY_SIZE -- Only growing
tables
AND LAST_ESTIMATED_MEMORY_SIZE_TIME > ADD_DAYS(CURRENT_TIMESTAMP, -30) --
Last 30 days measurements
ORDER BY GROWTH_PERCENT DESC;