pkg cache stmt包羅永珍

--執行時間長的SQL
select stmt_exec_time, num_executions, stmt_text
from table(mon_get_pkg_cache_stmt(null,null,null,-2)) as s
order by stmt_exec_time desc fetch first 5 rows only;

--CPU時間消耗高的SQL
select stmt_exec_time, num_executions,
(total_cpu_time / 1000) as cpu_time,
stmt_text
from table(mon_get_pkg_cache_stmt(null,null,null,-2)) as s
order by cpu_time desc fetch first 5 rows only;

--IO消耗高的SQL
select stmt_exec_time, num_executions,
(pool_read_time + pool_write_time +
direct_read_time + direct_write_time) as io_time,
stmt_text
from table(mon_get_pkg_cache_stmt(null,null,null,-2)) as t
order by io_time desc fetch first 5 rows only;

--等待時間長的SQL
select total_act_time, total_act_wait_time,
(case when total_act_time > 0
then ((total_act_time - total_act_wait_time) * 100
/ total_act_time)
else 100
end) as relvelocity,
stmt_text
from table (mon_get_pkg_cache_stmt(null,null,null,-2)) as t
order by relvelocity fetch first 5 rows only;

--讀選比大的SQL
select rows_returned, rows_read,
(case when rows_returned > 0
then rows_read / rows_returned
else 0
end) as ratio,
stmt_text as stmt
from table(mon_get_pkg_cache_stmt(null,null,null,-2)) as p
order by ratio desc
fetch first 10 rows only;


--某個SQL的消耗，時間都花在哪些地方了
select p.executable_id, r.metric_name, r.parent_metric_name,
r.total_time_value as time, r.count, p.member
from
(select stmt_exec_time, executable_id
from table(mon_get_pkg_cache_stmt(null,null,null,-2)) as s
order by stmt_exec_time desc fetch first row only) as stmts,
table(mon_get_pkg_cache_stmt_details(null,
stmts.executable_id,
null,
-2)) as p,
table(mon_format_xml_times_by_row(p.details)) as r
order by stmts.executable_id, parent_metric_name desc;

--DB2不斷推出新的監控指標
select
total_backup_time,total_backup_proc_time,total_backups,
total_index_build_time,total_index_build_proc_time,total_indexes_built,
total_col_time,total_col_proc_time
FROM TABLE(MON_GET_CONNECTION(cast(NULL as bigint), -2)) AS t
fetch first 1 rows only with ur;

--Assessing Efficiency of Columnar Query
--Compute the ratio of columnar processing time to overall section processing time to see how much we’re leveraging the columnar runtime
SELECT TOTAL_SECTION_TIME, TOTAL_COL_TIME,
DEC((FLOAT(TOTAL_COL_TIME)/
FLOAT(NULLIF(TOTAL_SECTION_TIME,0)))*100,5,2)
AS PCT_COL_TIME
FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-1)) AS T
WHERE STMT_TEXT = 'SELECT * FROM TEST.COLTAB A, TEST.ROWTAB B WHERE A.ONE = B.ONE';


--監控排序記憶體使用
SELECT SORT_SHRHEAP_ALLOCATED,  -- current
SORT_SHRHEAP_TOP,               -- high watermark
SORT_CONSUMER_SHRHEAP_TOP       -- per consumer hwm
FROM TABLE(MON_GET_DATABASE(-1));

SELECT SORT_SHRHEAP_ALLOCATED,  
SORT_SHRHEAP_TOP,               
SORT_CONSUMER_SHRHEAP_TOP       
FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-1)) AS T;

MON_GET_DATABASE(Database level)
MON_GET_PKG_CACHE_STMT(Query level)
MON_GET_SERVICE_SUBCLASS_STATS(Subclass level)


db2 "describe select * from TABLE(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-1))" | grep -i sort
db2 "describe select * FROM TABLE(MON_GET_DATABASE(-1))" | grep -i sort
db2 "describe select * FROM TABLE(MON_GET_SERVICE_SUBCLASS_STATS(CAST(NULL AS VARCHAR(128)),CAST(NULL AS VARCHAR(128)), -2))" | grep -i sort


--排序溢位
WITH SORT_CONSUMERS(TOTAL_SORT_CONSUMERS,
        TOTAL_SORT_CONSUMER_OVERFLOWS)
                AS (SELECT (TOTAL_SORTS + TOTAL_HASH_JOINS +
        TOTAL_HASH_GRPBYS + TOTAL_OLAP_FUNCS + TOTAL_COL_VECTOR_CONSUMERS)
                AS TOTAL_SORT_CONSUMERS,
        (SORT_OVERFLOWS + HASH_JOIN_OVERFLOWS +
                HASH_GRPBY_OVERFLOWS + OLAP_FUNC_OVERFLOWS)
                AS TOTAL_SORT_CONSUMER_OVERFLOWS
        FROM TABLE(MON_GET_DATABASE(-2)) AS T)
        SELECT TOTAL_SORT_CONSUMER_OVERFLOWS,
                TOTAL_SORT_CONSUMERS,
                CASE WHEN TOTAL_SORT_CONSUMERS > 0 THEN
                DEC((FLOAT(TOTAL_SORT_CONSUMER_OVERFLOWS)/
                        FLOAT(TOTAL_SORT_CONSUMERS)) * 100, 5, 2)
                ELSE
                        NULL
                END AS PCT_SORT_CONSUMER_OVERFLOWS
        FROM SORT_CONSUMERS
WITH UR;


with ops as
( select
(total_sorts + total_hash_joins + total_hash_grpbys)
as sort_ops,
(sort_overflows + hash_join_overflows + hash_grpby_overflows)
as overflows,
sort_shrheap_top as sort_heap_top
from table(mon_get_database(-2)))
select sort_ops,
overflows,
(overflows * 100) / nullif(sort_ops,0) as pctoverflow,
sort_heap_top
from ops;

--監控查詢使用的排序情況
SELECT SORT_SHRHEAP_TOP,
SORT_CONSUMER_SHRHEAP_TOP,
ACTIVE_SORT_CONSUMERS_TOP,
NUM_EXECUTIONS,
(TOTAL_SORTS +
TOTAL_HASH_JOINS +
TOTAL_HASH_GRPBYS +
TOTAL_COL_VECTOR_CONSUMERS) AS SORT_OPS,
(SORT_OVERFLOWS +
HASH_JOIN_OVERFLOWS +
HASH_GRPBY_OVERFLOWS) AS SORT_OVERFLOWS,
(POST_THRESHOLD_SORTS +
POST_THRESHOLD_HASH_JOINS +
POST_THRESHOLD_HASH_GRPBYS +
POST_THRESHOLD_COL_VECTOR_CONSUMERS) AS THROTTLED_SORT_OPS,
SUBSTR(STMT_TEXT,1,255) AS STMT_TEXT
FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-2));