pkg cache stmt包罗万象

最新推荐文章于 2023-08-28 10:54:00 发布

cmljts0270

最新推荐文章于 2023-08-28 10:54:00 发布

阅读量341

点赞数

文章标签： runtime 数据库

--执行时间长的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));