--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
"

ITPub博客

DB2 snapshot 管理视图

最新文章