1.花费时间排行前十的SQL语句
SELECT
TOTAL_ACT_TIME/NUM_EXEC_WITH_METRICS AS AVG_EXE_TIME,
SUBSTR(STMT_TEXT,1,40) AS SQL
FROM
TABLE(MON_GET_PKG_CACHE_STMT ( NULL, NULL, NULL, -2))
WHERE
NUM_EXEC_WITH_METRICS>=1
ORDER BY
1 desc fetch FIRST 10 rows only
其中MON_GET_PKG_CACHE_STMT是在SYSPROC下的函数内
2.读取的数据和搜索数据的占比(可以理解为返回一条数据需要查询的数据条数,越小越好)
SELECT ROWS_READ_PER_ROWS_RETURNED FROM SYSIBMADM.MON_DB_SUMMARY
3.总体缓存命中率
SELECT TOTAL_BP_HIT_RATIO_PERCENT FROM SYSIBMADM.MON_DB_SUMMARY
4.每个缓冲池数据、索引命中率和预取率
SELECT
SUBSTR(BP_NAME,1,18) AS BP_NAME,
DATA_HIT_RATIO_PERCENT,
INDEX_HIT_RATIO_PERCENT,
PREFETCH_RATIO_PERCENT
FROM
SYSIBMADM.MON_BP_UTILIZATION
5.每一个数据库空间缓冲池和物理内存IOPS
SELECT
SUBSTR(TBSP_NAME,1,20) AS TBSP_NAME,
POOL_READ_TIME/
CASE
WHEN POOL_DATA_P_READS+POOL_INDEX_P_READS+POOL_TEMP_DATA_P_READS+POOL_TEMP_INDEX_P_READS>0
THEN POOL_DATA_P_READS+POOL_INDEX_P_READS+POOL_TEMP_DATA_P_READS+POOL_TEMP_INDEX_P_READS
ELSE 1
END AS AVG_BP_READ_TIME,
POOL_WRITE_TIME/
CASE
WHEN POOL_DATA_WRITES+POOL_INDEX_WRITES>0
THEN POOL_DATA_WRITES+POOL_INDEX_WRITES
ELSE 1
END AS AVG_BP_WRITE_TIME,
DIRECT_READ_TIME /
CASE
WHEN DIRECT_READS/8 > 0
THEN DIRECT_READS/8
ELSE 1
END AS AVG_DIRECT_READ_TIME,
DIRECT_WRITE_TIME /
CASE
WHEN DIRECT_WRITES/8 > 0
THEN DIRECT_WRITES/8
ELSE 1
END AS AVG_DIRECT_WRITE_TIME
FROM
TABLE(MON_GET_TABLESPACE(NULL,NULL))
6.日志缓存满的次数以及平均写入时间
SELECT
NUM_LOG_BUFFER_FULL,
LOG_WRITE_TIME /
CASE
WHEN LOG_WRITES > 0
THEN LOG_WRITES
ELSE 1
END AS AVG_LOG_WRITE_TIME
FROM
TABLE(MON_GET_TRANSACTION_LOG(-2))
7.锁等待次数以及平均等待时间
SELECT
LOCK_WAITS,
LOCK_WAIT_TIME/LOCK_WAITS AS AVG_LOCK_WAIT_TIME
FROM
TABLE(MON_GET_DATABASE(-2))
8.锁超时,锁提升,死锁发生次数
SELECT
LOCK_TIMEOUTS,
LOCK_ESCALS_MAXLOCKS,
LOCK_ESCALS_LOCKLIST,
DEADLOCKS
FROM
TABLE(MON_GET_DATABASE(-2))
9.总排序次数和排序溢出率
SELECT
(SORT_OVERFLOWS * 100) / TOTAL_SORTS AS SORT_OVERFLOW_RATIO,
TOTAL_SORTS
FROM
TABLE(MON_GET_DATABASE(-2))
10.包缓存命中率
SELECT
(PKG_CACHE_LOOKUPS-PKG_CACHE_INSERTS)*100 / PKG_CACHE_LOOKUPS
FROM
TABLE(MON_GET_DATABASE(-2))
11.当前会话数
select * from SYSIBMADM.APPLICATIONS
12.数据库启动时间
SELECT
*
FROM
SYSIBMADM.SNAPDBM
13.数据库创建时间
select * from sysibm.sysversions
14.缓冲池效率
SELECT
BP_NAME,
AVG_WRITE_TIME,
SYNC_WRITES_PERCENT,
AVG_SYNC_WRITE_TIME,
AVG_ASYNC_WRITE_TIME
FROM
SYSIBMADM.MON_BP_UTILIZATION
15.各种耗时元素信息 毫秒
SELECT
SUM(TOTAL_WAIT_TIME) AS WAIT,
SUM(TOTAL_COMPILE_PROC_TIME) AS COMPILE,
SUM(TOTAL_IMPLICIT_COMPILE_PROC_TIME) AS IMP_COMPILE,
SUM(TOTAL_SECTION_PROC_TIME) AS SECTION,
SUM(total_routine_user_code_proc_time) AS ROUTINE_USER_CODE,
SUM(TOTAL_COMMIT_PROC_TIME) AS COMMIT,
SUM(TOTAL_REORG_PROC_TIME) AS REORG,
SUM(TOTAL_RUNSTATS_PROC_TIME) AS RUNSTATS,
SUM(TOTAL_ROLLBACK_PROC_TIME) AS ROLLBACK,
SUM(TOTAL_LOAD_PROC_TIME) AS LOAD,
SUM(total_connect_request_proc_time) AS CONNECT_REQUEST
FROM
TABLE(MON_GET_SERVICE_SUBCLASS('SYSDEFAULTUSERCLASS','SYSDEFAULTSUBCLASS',NULL))
16.日志空间相关信息
SELECT
1.0*TOTAL_LOG_AVAILABLE/1024/1024 AS total_log_available_mb,
1.0*TOTAL_LOG_USED/1024/1024 AS total_log_used_mb,
1.0*SEC_LOG_USED_TOP/1024/1024 AS sec_log_used_top_mb,
1.0*TOT_LOG_USED_TOP/1024/1024 AS total_log_used_top,
1.0*SEC_LOGS_ALLOCATED/1024/1024 AS sec_logs_allocated_mb,
APPL_ID_OLDEST_XACT
FROM
TABLE(SNAP_GET_DB(CAST (NULL AS VARCHAR(128)), -2))