--快照监视器开关

快照开关                    DBM参数               快照信息
BUFFERPOOL DFT_MON_BUFPOOL      缓冲池读写信息
LOCK               DFT_MON_LOCK               锁信息
SORT               DFT_MON_SORT               排序堆大小
STATEMENT          DFT_MON_STMT               语句
TABLE               DFT_MON_TABLE          表读写信息
TIMESTAMP          DFT_MON_TIMESTAMP        各种时间信息
UOW               DFT_MON_UOW               事务相关信息

--实例级开启
db2 update dbm cfg using DFT_MON_BUFPOOL on DFT_MON_LOCK on DFT_MON_STMT on DFT_MON_UOW on
db2 update dbm cfg using DFT_MON_TIMESTAMP on DFT_MON_SORT on DFT_MON_TABLE on

--数据库session级开启（db2 terminate 后关闭）
db2 update monitor switches using BUFFERPOOL on

--重置快照开关
db2 reset monitor for db testdb all

--快照开关
db2 get monitor switches

--找到执行时间最长的20条语句
db2 "select rows_read/(num_executions+1) as avg_rows_read,
rows_written/(num_executions+1) as avg_rows_write,
stmt_sorts/(num_executions+1) as avg_sorts,
total_exec_time/(num_executions+1) as avg_exec_time,
substr(stmt_text,1,100) as sql_stmt
from sysibmadm.snapdyn_sql
order by avg_exec_time desc
fetch first 20 rows only
"

取出前10 大最耗资源的SQL语句：
select (TOTAL_USR_CPU_TIME +    TOTAL_SYS_CPU_TIME) as totaltime, NUM_EXECUTIONS,
SORT_OVERFLOWS, TOTAL_SORT_TIME, STMT_TEXT
from sysibmadm.snapdyn_sql
order by totaltime desc fetch first 10 rows only

-------------------------------------------------------------
--snapshot 命令行
     db2 get snapshot for all on $DB
     db2 get snapshot for database manager
     db2 get snapshot for database on $DB
     db2 get snapshot for applications on $DB
     db2 get snapshot for tables on $DB
     db2 get snapshot for tablespaces on $DB
     db2 get snapshot for locks on $DB
     db2 get snapshot for bufferpools on $DB
     db2 get snapshot for dynamic sql on $DB
     db2 get snapshot for application agentid id_num 某一应用的快照

If you notice that there is one particular statement in the output of this SQL that has a long
average execution time and performs three sorts per execution, you can use the Design Advisor
to help tune this statement. If you extract the statement text from the output above, and put it into
the file bad.sql, you can run the Design Advisor from the command line using:

db2advis –d posdb –i bad.sql

If an index will help the performance of the query, the Index Advisor will tell you the definition
of the index or indexes it recommends, as well as the new cost of the query and the percent
improvement in the cost.

C:\temp>db2advis -d posdb -i bad.sql
Using user id as default schema name. Use -n option to specify schema
execution started at timestamp 2006-03-28-12.51.39.570001
found [1] SQL statements from the input file
Recommending indexes...
total disk space needed for initial set [ 0.009] MB
total disk space constrained to [ 33.322] MB
Trying variations of the solution set.
Optimization finished.
2 indexes in current solution
[ 13.0000] timerons (without recommendations)
[ 0.1983] timerons (with current solution)
[98.47%] improvement
--
--
-- LIST OF RECOMMENDED INDEXES
-- ===========================
-- index[1], 0.009MB
CREATE INDEX "DSNOW "."IDX403281751440000" ON "DSNOW "."ORGX" ("C1" ASC) ALLOW
REVERSE SCANS ;
COMMIT WORK ;
RUNSTATS ON TABLE "DSNOW "."ORGX" FOR INDEX "DSNOW "."IDX403281751440000";
COMMIT WORK ;
-- RECOMMENDED EXISTING INDEXES
-- ============================
-- ===========================
--
11 solutions were evaluated by the advisor
DB2 Workload Performance Advisor tool is finished.

--------------------------------------------------------------
--监视器表函数 MON_
db2 "select substr(funcname,1,50) as mon_function from syscat.functions
where funcname like 'MON_GET_%' "

MON_GET_ACTIVITY_DETAILS
MON_GET_APPLICATION_HANDLE
MON_GET_APPLICATION_ID
MON_GET_APPL_LOCKWAIT
MON_GET_BUFFERPOOL
MON_GET_CONNECTION
MON_GET_CONNECTION_DETAILS
MON_GET_CONTAINER
MON_GET_EXTENT_MOVEMENT_STATUS
MON_GET_FCM
MON_GET_FCM_CONNECTION_LIST
MON_GET_INDEX
MON_GET_LOCKS
MON_GET_MEMORY_POOL
MON_GET_MEMORY_SET
MON_GET_PKG_CACHE_STMT
MON_GET_PKG_CACHE_STMT_DETAILS
MON_GET_SERVICE_SUBCLASS
MON_GET_SERVICE_SUBCLASS_DETAILS
MON_GET_TABLE
MON_GET_TABLESPACE
MON_GET_UNIT_OF_WORK
MON_GET_UNIT_OF_WORK_DETAILS
MON_GET_WORKLOAD
MON_GET_WORKLOAD_DETAILS

--最消耗CPU的前10个动态SQL
-- 表函数第一个参数： D 动态；S 静态； NULL 不区分
db2 "select varchar(stmt_text,200) as statment,
num_exec_with_metrics as numExec,
total_cpu_time,
total_cpu_time/num_exec_with_metrics as avg_cpu_time
from table( mon_get_pkg_cache_stmt('D',NULL,NULL,-2)) AS T
where t.num_exec_with_metrics <> 0
order by avg_cpu_time desc
fetch first 10 rows only
"

----------------------------------------------------------------------------------

ITPub博客

DB2快照监视器

最新文章