--snapshot 管理檢視
sysibmadm.snapdbm 例項快照資訊
sysibmadm.snapdb 資料庫快照
sysibmadm.snapdb_memory_pool 資料庫記憶體快照
sysibmadm.snaphadr HADR快照
sysibmadm.snaptab 表監控快照資訊
sysibmadm.snaptab_reorg 表重組快照資訊
sysibmadm.snapdyn_sql 動態SQL語句資訊
sysibmadm.snapappl 應用程式快照資訊
sysibmadm.snapappl_info 應用程式細節資訊
sysibmadm.snaptbsp 表空間快照
sysibmadm.snapcontainer 表空間容器快照

--消耗使用者CPU時間最多的動態SQL
db2 "select * from sysibmadm.snapdyn_sql
order by total_usr_cpu_time desc
"
--查詢表TEST最多的SQL語句
db2 "select stmt_text
from sysibmadm.snapdyn_sql
where stmt_text like '%TEST%'
order by num_executions desc
"
--訪問表TEST的索引頁最多的SQL
db2 "select * from sysibmadm.snapdyn_sql
where stmt_text like '%TEST%'
order by pool_index_p_reads desc
"

--正在執行的執行時間最長的SQL
db2 "select * from sysibmadm.long_running_sql
order by elapsed_time_min desc
"

--執行次數最多的SQL
db2 "select * from sysibmadm.top_dynamic_sql
order by num_executions desc
"
--排序次數最多的SQL
db2 "select * from sysibmadm.top_dynamic_sql
order by stmt_sorts desc
"
--讀取資料行次數最多的SQL
db2 "select * from sysibmadm.top_dynamic_sql
order by rows_read desc
"

--最早未提交的應用
db2 "select ai.appl_status as status ,ai.agent_id as agentid,
substr(ai.primary_auth_id,1,10) as authid,substr(ai.appl_name,1,15) as applname,
int(ap.uow_log_space_used/1024/1024) as "Log_Used_MB" , ap.appl_idle_time,
ap.appl_con_time as "Connect_Since"
from sysibmadm.snapdb db, sysibmadm.snapappl ap,sysibmadm.snapappl_info ai
where ai.agent_id = db.appl_id_oldest_xact and ai.agent_id = ap.agent_id
"

DB2 snapshot 管理檢視

相關文章