--快照監視器開關

快照開關                    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
"

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

DB2快照監視器

相關文章