DBSUMMARY プロシージャー - システムおよびアプリケーションのパフォーマンス・メトリックの要約レポートの生成
DBSUMMARY プロシージャーは、システムおよびアプリケーションのパフォーマンス・メトリックを要約するテキスト形式のモニター・レポートを生成します。
DB 要約レポートには、データベース全体に関する詳細なモニター・データと、それぞれの接続、ワークロード、サービス・クラス、データベース・メンバーに関する主要なパフォーマンス指標が含まれます。
構文
パラメーター
- monitoring_interval
- オプションのタイプ INTEGER の入力引数。モニタリング・データが報告されるまでの収集期間 (秒) を指定します。 例えばモニタリング間隔 30 を指定した場合、ルーチンは表関数を呼び出して、30 秒待機した後、再び表関数を呼び出します。 その後、DBSUMMARY プロシージャーは (この期間中の変化を反映する) 差異を計算します。 引数 monitoring_interval を指定しない場合、または NULL を指定した場合のデフォルト値は 10 です。 有効な入力の範囲は 0 から 3600 (つまり最大で 1 時間) までの整数値です。
許可
以下の特権が必要です。
- MONREPORT モジュールに対する EXECUTE 特権
例
以下の例は DBSUMMARY プロシージャーを呼び出すさまざまな方法を示しています。
最初の例は、30 秒間隔のデータを表すレポートを生成します。
call monreport.dbsummary(30);次の例は、10 秒間隔のデータを表すレポートを生成します (デフォルト値)。
call monreport.dbsummary; Result set 1
--------------
TEXT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Monitoring report - database summary
--------------------------------------------------------------------------------
Database: SAMPLE
Generated: 04/06/2010 13:35:24
Interval monitored: 10
================================================================================
Part 1 - System performance
Work volume and throughput
--------------------------------------------------------------------------------
Per second Total
--------------------- -----------------------
TOTAL_APP_COMMITS 0 2
ACT_COMPLETED_TOTAL 0 9
APP_RQSTS_COMPLETED_TOTAL 0 6
TOTAL_CPU_TIME = 2649800
TOTAL_CPU_TIME per request = 441633
Row processing
ROWS_READ/ROWS_RETURNED = 97 (685/7)
ROWS_MODIFIED = 117
Wait times
--------------------------------------------------------------------------------
-- Wait time as a percentage of elapsed time --
% Wait time/Total time
--- ----------------------------------
For requests 19 3434/17674
For activities 10 1203/11613
-- Time waiting for next client request --
CLIENT_IDLE_WAIT_TIME = 70566
CLIENT_IDLE_WAIT_TIME per second = 7056
-- Detailed breakdown of TOTAL_WAIT_TIME --
% Total
--- ---------------------------------------------
TOTAL_WAIT_TIME 100 3434
I/O wait time
POOL_READ_TIME 23 805
POOL_WRITE_TIME 8 280
DIRECT_READ_TIME 3 131
DIRECT_WRITE_TIME 3 104
LOG_DISK_WAIT_TIME 10 344
LOCK_WAIT_TIME 0 18
AGENT_WAIT_TIME 0 0
Network and FCM
TCPIP_SEND_WAIT_TIME 0 0
TCPIP_RECV_WAIT_TIME 0 0
IPC_SEND_WAIT_TIME 0 0
IPC_RECV_WAIT_TIME 0 0
FCM_SEND_WAIT_TIME 0 0
FCM_RECV_WAIT_TIME 6 212
WLM_QUEUE_TIME_TOTAL 0 0
CF_WAIT_TIME 32 1101
RECLAIM_WAIT_TIME 2 98
SMP_RECLAIM_WAIT_TIME 3 118
Component times
--------------------------------------------------------------------------------
-- Detailed breakdown of processing time --
% Total
---------------- --------------------------
Total processing 100 14240
Section execution
TOTAL_SECTION_PROC_TIME 2 365
TOTAL_SECTION_SORT_PROC_TIME 0 0
Compile
TOTAL_COMPILE_PROC_TIME 0 17
TOTAL_IMPLICIT_COMPILE_PROC_TIME 2 294
Transaction end processing
TOTAL_COMMIT_PROC_TIME 0 36
TOTAL_ROLLBACK_PROC_TIME 0 0
Utilities
TOTAL_RUNSTATS_PROC_TIME 0 0
TOTAL_REORGS_PROC_TIME 0 0
TOTAL_LOAD_PROC_TIME 0 0
Buffer pool
--------------------------------------------------------------------------------
Buffer pool hit ratios
Type Ratio Formula
--------------- --------------- ----------------------------------------------
Data 100 (1-(0+0-0)/(27+0))
Index 100 (1-(0+0-0)/(24+0))
XDA 0 (1-(0+0-0)/(0+0))
COL 0 (1-(0+0-0)/(0+0))
LBP Data 100 (27-0)/(27+0)
LBP Index 0 (0-0)/(24+0)
LBP XDA 0 (0-0)/(0+0)
LBP COL 0 (0-0)/(0+0)
GBP Data 0 (0 - 0)/0
GBP Index 0 (0 - 0)/0
GBP XDA 0 (0 - 0)/0
GBP COL 0 (0 - 0)/0
I/O
--------------------------------------------------------------------------------
Buffer pool reads
POOL_DATA_L_READS = 27
POOL_TEMP_DATA_L_READS = 0
POOL_DATA_P_READS = 0
POOL_TEMP_DATA_P_READS = 0
POOL_ASYNC_DATA_READS = 0
POOL_INDEX_L_READS = 24
POOL_TEMP_INDEX_L_READS = 0
POOL_INDEX_P_READS = 0
POOL_TEMP_INDEX_P_READS = 0
POOL_ASYNC_INDEX_READS = 0
POOL_XDA_L_READS = 0
POOL_TEMP_XDA_L_READS = 0
POOL_XDA_P_READS = 0
POOL_TEMP_XDA_P_READS = 0
POOL_ASYNC_XDA_READS = 0
POOL_COL_L_READS = 0
POOL_TEMP_COL_L_READS = 0
POOL_COL_P_READS = 0
POOL_TEMP_COL_P_READS = 0
POOL_ASYNC_COL_READS = 0
Buffer pool pages found
POOL_DATA_LBP_PAGES_FOUND = 27
POOL_ASYNC_DATA_LBP_PAGES_FOUND = 0
POOL_INDEX_LBP_PAGES_FOUND = 0
POOL_ASYNC_INDEX_LBP_PAGES_FOUND = 0
POOL_XDA_LBP_PAGES_FOUND = 0
POOL_ASYNC_XDA_LBP_PAGES_FOUND = 0
POOL_COL_LBP_PAGES_FOUND = 0
POOL_ASYNC_COL_LBP_PAGES_FOUND = 0
Buffer pool writes
POOL_DATA_WRITES = 0
POOL_XDA_WRITES = 0
POOL_INDEX_WRITES = 0
POOL_COL_WRITES = 0
Direct I/O
DIRECT_READS = 620
DIRECT_READ_REQS = 15
DIRECT_WRITES = 0
DIRECT_WRITE_REQS = 0
Log I/O
LOG_DISK_WAITS_TOTAL = 0
Locking
--------------------------------------------------------------------------------
Per activity Total
------------------------------ ----------------------
LOCK_WAIT_TIME 2 18
LOCK_WAITS 22 2
LOCK_TIMEOUTS 0 0
DEADLOCKS 0 0
LOCK_ESCALS 0 0
Routines
--------------------------------------------------------------------------------
Per activity Total
------------------------ ------------------------
TOTAL_ROUTINE_INVOCATIONS 0 1
TOTAL_ROUTINE_TIME 1117 10058
TOTAL_ROUTINE_TIME per invocation = 10058
Sort
--------------------------------------------------------------------------------
TOTAL_SORTS = 5
SORT_OVERFLOWS = 0
POST_THRESHOLD_SORTS = 0
POST_SHRTHRESHOLD_SORTS = 0
Network
--------------------------------------------------------------------------------
Communications with remote clients
TCPIP_SEND_VOLUME per send = 0 (0/0)
TCPIP_RECV_VOLUME per receive = 0 (0/0)
Communications with local clients
IPC_SEND_VOLUME per send = 137 (1101/8)
IPC_RECV_VOLUME per receive = 184 (1106/6)
Fast communications manager
FCM_SEND_VOLUME per send = 3475 (31277/9)
FCM_RECV_VOLUME per receive = 2433 (131409/54)
Other
--------------------------------------------------------------------------------
Compilation
TOTAL_COMPILATIONS = 4
PKG_CACHE_INSERTS = 11
PKG_CACHE_LOOKUPS = 13
Catalog cache
CAT_CACHE_INSERTS = 74
CAT_CACHE_LOOKUPS = 112
Transaction processing
TOTAL_APP_COMMITS = 2
INT_COMMITS = 2
TOTAL_APP_ROLLBACKS = 0
INT_ROLLBACKS = 0
Log buffer
NUM_LOG_BUFFER_FULL = 0
Activities aborted/rejected
ACT_ABORTED_TOTAL = 0
ACT_REJECTED_TOTAL = 0
Workload management controls
WLM_QUEUE_ASSIGNMENTS_TOTAL = 0
WLM_QUEUE_TIME_TOTAL = 0
Db2 utility operations
--------------------------------------------------------------------------------
TOTAL_RUNSTATS = 0
TOTAL_REORGS = 0
TOTAL_LOADS = 0
================================================================================
Part 2 - Application performance drill down
Application performance database-wide
--------------------------------------------------------------------------------
TOTAL_CPU_TIME TOTAL_ TOTAL_APP_ ROWS_READ +
per request WAIT_TIME % COMMITS ROWS_MODIFIED
---------------------- ----------- ------------- ----------------------------
441633 19 2 802
Application performance by connection
--------------------------------------------------------------------------------
APPLICATION_ TOTAL_CPU_TIME TOTAL_ TOTAL_APP_ ROWS_READ +
HANDLE per request WAIT_TIME % COMMITS ROWS_MODIFIED
------------- ------------------- ----------- ------------- -------------
180 0 0 0 0
65711 495970 46 1 566
131323 324379 43 1 222
Application performance by service class
--------------------------------------------------------------------------------
SERVICE_ TOTAL_CPU_TIME TOTAL_ TOTAL_APP_ ROWS_READ +
CLASS_ID per request WAIT_TIME % COMMITS ROWS_MODIFIED
-------- ------------------- ----------- ------------- -------------
11 0 0 0 0
12 0 0 0 0
13 440427 19 2 802
Application performance by workload
--------------------------------------------------------------------------------
WORKLOAD_ TOTAL_CPU_TIME TOTAL_ TOTAL_APP_ ROWS_READ +
NAME per request WAIT_TIME % COMMITS ROWS_MODIFIED
------------- ---------------------- ----------- ------------- -------------
SYSDEFAULTADM 0 0 0 0
SYSDEFAULTUSE 410174 45 2 788
================================================================================
Part 3 - Member level information
- I/O wait time is
(POOL_READ_TIME + POOL_WRITE_TIME + DIRECT_READ_TIME + DIRECT_WRITE_TIME).
TOTAL_CPU_TIME TOTAL_ RQSTS_COMPLETED_ I/O
MEMBER per request WAIT_TIME % TOTAL wait time
------ ---------------------- ----------- ---------------- -----------------
0 17804 0 9 10
1 108455 47 14 866
2 74762 41 13 441
267 record(s) selected.
Return Status = 0