Performance monitoring (tools and techniques)

Currently active connections :

db2 "SELECT agent_state, event_state, event_object || ':' || event_type event,  COUNT(*) count FROM TABLE(WLM_GET_SERVICE_CLASS_AGENTS('', '',CAST(NULL AS BIGINT),-2)) AS agent,      TABLE(MON_GET_CONNECTION(cast(NULL as bigint), -2)) AS conn WHERE agent.application_handle = conn.application_handle GROUP BY agent_state, event_state, event_object, event_type ORDER BY 1,2,3"

Connections overview :

db2 "with conn as (select varchar(client_hostname,20) hostname,count(*) connections from TABLE(MON_GET_CONNECTION(cast(NULL as bigint), -2)) AS t group by client_hostname with ur) 
select * from conn union all select 'TOTAL_CONNECTIONS',sum(connections) from conn order by 2 with ur"

Memory pools (except PRIVATE) :

db2 "SELECT varchar(memory_set_type, 20) AS set_type,
       varchar(memory_pool_type,20) AS pool_type,
       varchar(db_name, 20) AS dbname,
       memory_pool_used,
       memory_pool_used_hwm
FROM TABLE( 
       MON_GET_MEMORY_POOL(NULL, CURRENT_SERVER, -2)) t where t.memory_set_type <> 'PRIVATE' with ur"

Show lock chain :

db2 "select substr(ai_h.primary_auth_id,1,10) as Holder_APP_ID, \
ai_h.agent_id as HOLDER_APP_HANDLE, substr(lw.authid,1,10) as Waiter_APP_ID, \
lw.agent_id as WAITER_APP_HANDLE, lw.lock_mode , lw.lock_object_type , \
substr(lw.tabname,1,10) as TabName,substr(lw.tabschema,1,10) as Schema, \
timestampdiff(2,char(lw.snapshot_timestamp-lw.lock_wait_start_time)) as waiting_s \
 from sysibmadm.lockwaits lw , sysibmadm.snapappl_info \
ai_h where lw.agent_id_holding_lk = ai_h.agent_id with ur"

Show me the recommendations :

db2 AUTOCONFIGURE USING
MEM_PERCENT 60
WORKLOAD_TYPE MIXED
NUM_STMTS 500
ADMIN_PRIORITY BOTH
IS_POPULATED YES
NUM_LOCAL_APPS 0
NUM_REMOTE_APPS 20
ISOLATION RR
BP_RESIZEABLE YES
APPLY NONE

Lock waits :

db2 "select SNAPSHOT_TIMESTAMP, AGENT_ID, AGENT_ID_HOLDING_LK from icrica1p.snapshot_lockwait order by SNAPSHOT_TIMESTAMP"

Statements :

db2 "select SNAPSHOT_TIMESTAMP,AGENT_ID, PACKAGE_NAME from icrica1p.snapshot_statement where AGENT_ID = 106"

Snaphots provide the Key Performance Indicators. Instance level monitor switches must be turned on :

DFT_MON_BUFPOOL - collection of buffer pool monitoring data

Snapshots can be taken for various stuff :

db2 get snapshot for database on <DBNAME>

Memory monitoring :

db2 "SELECT varchar(memory_set_type, 20) AS set_type,varchar(memory_pool_type,20) AS pool_type, varchar(db_name, 20) AS dbname,memory_pool_used, memory_pool_used_hwm FROM TABLE(MON_GET_MEMORY_POOL(NULL,CURRENT_SERVER, -2))"

Identify "hot" table spaces

1. Total Physical I/Os

(Buffer pool data physical reads + Buffer pool index physical reads + Buffer pool xda physical reads + Buffer pool temporary data physicalreads) + (Direct reads * 512 ) / table space page size

db2 "select substr(TBSP_NAME,1,15), ((POOL_DATA_P_READS+POOL_INDEX_P_READS+POOL_XDA_P_READS+POOL_TEMP_DATA_P_READS)+(DIRECT_READS*512)/TBSP_PAGE_SIZE) PHYSICAL_IO from SYSIBMADM.SNAPTBSP where tbsp_name not like 'SYS%' and tbsp_name not like '%TEMP%'"

DB2ANALYZE

#!/usr/bin/env ksh
. ~/.profile

DB=DBWCPROD
SCHEMA=DATAUSER

INPUT=$1
OUTPUT_EXPLAIN=`echo ${INPUT} | sed 's/sql/explain/g'`
OUTPUT_ADVIS=`echo ${INPUT} | sed 's/sql/advis/g'`

echo "Processing INPUT SQL ${INPUT} :"
db2 connect to ${DB} >/dev/null
db2 set current schema ${SCHEMA}
db2 set current explain mode explain
db2 -tvf ${INPUT} 

# explain
db2exfmt -d ${DB} -g -o ${OUTPUT_EXPLAIN} -w -1 -n % -s % -# 0

# advis
db2advis -d ${DB} -m IC -n ${SCHEMA} -q ${SCHEMA} -i ${INPUT} > ${OUTPUT_ADVIS}

db2 set current explain mode no
db2 terminate

Create the DB2 Design Advisor / Explain tables in current database :

db2 -tvf $INSTHOME/sqllib/misc/EXPLAIN.DDL

or

db2 "CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN', 'C', CAST (NULL AS VARCHAR(128)), CAST (NULL AS VARCHAR(128)))"

Explain and Access plan :

db2 SET CURRENT EXPLAIN MODE EXPLAIN
db2 <sqlstmt>
db2 SET CURRENT EXPLAIN MODE NO

# or 

db2 "EXPLAIN ALL FOR <sqlstmt>"

Format the captured Explain information :

db2exfmt -d <DB> [ -u <userid> <pwd> ] -g -o expl_<STMT>.out -w -1 -n % -s % -# 0

Explain Static SQL (package) (also shows included SQL statements) :

db2expln -database <DB> -schema <SCHEMA> -package <PACKAGE> -output expl_<PACKAGE>.txt

DB2 Advisor :

db2advis -d ${DB} -n ${SCHEMA} -q ${SCHEMA} -i ${INPUT} > ${OUTPUT_ADVIS}

Index regression analysis based on proposed indexes :

1)Disable explain evaluation with non-existent indexes :“”

db2 "update advise_index set use_index = 'N' where exists= 'N'"

2) Enable only specific index from advisor output :

db2 "update advise_index set use_index = 'Y' where name = '<INDEX>'"

3) Set explain to evaluate indexes mode :

db2 set current explain mode evaluate indexes
db2 -tvf <QUERY>

4) Export new explain plan

db2exfmt -d <DB> -1 -o <QUERY_OUTPUT>.txt

Index read efficiency :

db2 "WITH SUM_TAB (SUM_RR) AS (
        SELECT FLOAT(SUM(ROWS_READ))
        FROM TABLE(MON_GET_PKG_CACHE_STMT ( 'D', NULL, NULL, -2)) AS T)
SELECT
        EXECUTABLE_ID,
        ROWS_READ,
        DECIMAL(100*(FLOAT(ROWS_READ)/SUM_TAB.SUM_RR),5,2) AS PCT_TOT_RR,
        ROWS_RETURNED,
        CASE
            WHEN ROWS_RETURNED > 0 THEN
                DECIMAL(FLOAT(ROWS_READ)/FLOAT(ROWS_RETURNED),10,2)
            ELSE -1
        END AS READ_EFFICIENCY,
        NUM_EXECUTIONS
    FROM TABLE(MON_GET_PKG_CACHE_STMT ( 'D', NULL, NULL, -2)) AS T, SUM_TAB
    ORDER BY ROWS_READ DESC FETCH FIRST 5 ROWS ONLY WITH UR"

Show me problematic SQLs ( > 9.7) from package cache :

db2 "WITH SUM_TAB (SUM_RR, SUM_CPU, SUM_EXEC, SUM_SORT, SUM_NUM_EXEC) AS (
        SELECT  FLOAT(SUM(ROWS_READ)),
                FLOAT(SUM(TOTAL_CPU_TIME)),
                FLOAT(SUM(STMT_EXEC_TIME)),
                FLOAT(SUM(TOTAL_SECTION_SORT_TIME)),
                FLOAT(SUM(NUM_EXECUTIONS))
            FROM TABLE(MON_GET_PKG_CACHE_STMT ( 'D', NULL, NULL, -2)) AS T
        )
SELECT
        EXECUTABLE_ID,
        ROWS_READ,
        DECIMAL(100*(FLOAT(ROWS_READ)/SUM_TAB.SUM_RR),5,2) AS PCT_TOT_RR,
        TOTAL_CPU_TIME,
        DECIMAL(100*(FLOAT(TOTAL_CPU_TIME)/SUM_TAB.SUM_CPU),5,2) AS PCT_TOT_CPU,
        STMT_EXEC_TIME,
        DECIMAL(100*(FLOAT(STMT_EXEC_TIME)/SUM_TAB.SUM_EXEC),5,2) AS PCT_TOT_EXEC,
        TOTAL_SECTION_SORT_TIME,
        DECIMAL(100*(FLOAT(TOTAL_SECTION_SORT_TIME)/SUM_TAB.SUM_SORT),5,2) AS PCT_TOT_SRT,
        NUM_EXECUTIONS,
        DECIMAL(100*(FLOAT(NUM_EXECUTIONS)/SUM_TAB.SUM_NUM_EXEC),5,2) AS PCT_TOT_EXEC,
        DECIMAL(FLOAT(STMT_EXEC_TIME)/FLOAT(NUM_EXECUTIONS),10,2) AS AVG_EXEC_TIME
    FROM TABLE(MON_GET_PKG_CACHE_STMT ( 'D', NULL, NULL, -2)) AS T, SUM_TAB
    WHERE DECIMAL(100*(FLOAT(ROWS_READ)/SUM_TAB.SUM_RR),5,2) > 10
        OR DECIMAL(100*(FLOAT(TOTAL_CPU_TIME)/SUM_TAB.SUM_CPU),5,2) >10
        OR DECIMAL(100*(FLOAT(STMT_EXEC_TIME)/SUM_TAB.SUM_EXEC),5,2) >10
        OR DECIMAL(100*(FLOAT(TOTAL_SECTION_SORT_TIME)/SUM_TAB.SUM_SORT),5,2) >10
        OR DECIMAL(100*(FLOAT(NUM_EXECUTIONS)/SUM_TAB.SUM_NUM_EXEC),5,2) >10
    ORDER BY ROWS_READ DESC FETCH FIRST 20 ROWS ONLY WITH UR"

Show me problematic SQLs from package cache ( < 9.7 ) :

WITH SUM_TAB (SUM_RR, SUM_CPU, SUM_EXEC, SUM_SORT, SUM_NUM_EXEC) AS (
        SELECT  FLOAT(SUM(ROWS_READ)),
                FLOAT(SUM(TOTAL_USR_CPU_TIME)) + FLOAT(SUM(TOTAL_SYS_CPU_TIME)),
                FLOAT(SUM(TOTAL_EXEC_TIME)),
                FLOAT(SUM(TOTAL_SORT_TIME)),
                FLOAT(SUM(NUM_EXECUTIONS))
            FROM SYSIBMADM.SNAPDYN_SQL AS T
        )
SELECT
        SUBSTR(STMT_TEXT,1,10) as STATEMENT,
        ROWS_READ,
        DECIMAL(100*(FLOAT(ROWS_READ)/SUM_TAB.SUM_RR),5,2) AS PCT_TOT_RR,
        TOTAL_USR_CPU_TIME + TOTAL_SYS_CPU_TIME as TOTAL_CPU_TIME,
        DECIMAL(100*((FLOAT(TOTAL_USR_CPU_TIME)+FLOAT(TOTAL_SYS_CPU_TIME))/SUM_TAB.SUM_CPU),5,2) AS PCT_TOT_CPU,
        TOTAL_EXEC_TIME,
        DECIMAL(100*(FLOAT(TOTAL_EXEC_TIME)/SUM_TAB.SUM_EXEC),5,2) AS PCT_TOT_EXEC,
        TOTAL_SORT_TIME,
        DECIMAL(100*(FLOAT(TOTAL_SORT_TIME)/SUM_TAB.SUM_SORT),5,2) AS PCT_TOT_SRT,
        NUM_EXECUTIONS,
        DECIMAL(100*(FLOAT(NUM_EXECUTIONS)/SUM_TAB.SUM_NUM_EXEC),5,2) AS PCT_TOT_EXEC,
        DECIMAL(FLOAT(TOTAL_EXEC_TIME)/FLOAT(NUM_EXECUTIONS),10,2) AS AVG_EXEC_TIME
    FROM SYSIBMADM.SNAPDYN_SQL AS T, SUM_TAB
    WHERE DECIMAL(100*(FLOAT(ROWS_READ)/SUM_TAB.SUM_RR),5,2) > 10
        OR DECIMAL(100*((FLOAT(TOTAL_USR_CPU_TIME)+FLOAT(TOTAL_SYS_CPU_TIME))/SUM_TAB.SUM_CPU),5,2)  >10
        OR DECIMAL(100*(FLOAT(TOTAL_EXEC_TIME)/SUM_TAB.SUM_EXEC),5,2) >10
        OR DECIMAL(100*(FLOAT(TOTAL_SORT_TIME)/SUM_TAB.SUM_SORT),5,2) >10
        OR DECIMAL(100*(FLOAT(NUM_EXECUTIONS)/SUM_TAB.SUM_NUM_EXEC),5,2) >10
    ORDER BY ROWS_READ DESC FETCH FIRST 20 ROWS ONLY WITH UR;

Get execution IDs by total statement exec time :

db2 "select executable_id,TOTAL_STMT_EXEC_TIME from sysibmadm.MON_PKG_CACHE_SUMMARY order by TOTAL_STMT_EXEC_TIME desc fetch first 10 rows only with ur"

Get statement from executable_id :

db2 -x "SELECT varchar(STMT_TEXT,3000) STMT FROM TABLE(MON_GET_PKG_CACHE_STMT(null, x'01000000000000007D0000000000000000000000020020081126172409987719', null, -2)) with ur"

DB2BATCH Query response time

db2batch -d <DB> -f <INPUT.SQL> -q on -o r 0

Optimization Classes :

0 - NLJoin (no indexes)
1 -
2 - Greedy Joins (use it for simple joins)
3 -
5 -
7 -
9 - A lot of thinking (never use it in production)

Automatic Maintenance

Update automatic backup job :

CALL SYSPROC.AUTOMAINT_SET_POLICY ( 'AUTO_BACKUP', BLOB('<?xml version="1.0" encoding="UTF-8"?><DB2AutoBackupPolicy xmlns="http://www.ibm.com/xmlns/prod/db2/autonomic/config"><BackupOptions mode="Offline"><BackupTarget><DiskBackupTarget><PathName>/db2/db2inst1/home</PathName></DiskBackupTarget></BackupTarget></BackupOptions><BackupCriteria numberOfFullBackups="1" timeSinceLastBackup="24" logSpaceConsumedSinceLastBackup="6400"/></DB2AutoBackupPolicy> ') );

Check event monitors :

db2 "select varchar(evmonname,30) as evmonname, target_type,EVENT_MON_STATE(evmonname) as state,autostart  from syscat.eventmonitors with ur"

Create new event monitor :

db2 "CREATE EVENT MONITOR <MON> FOR <EVENT> WRITE TO TABLE"

Connection Event monitor with filter :

db2 "create event monitor ld_conn for connections where appl_name <> 'KUDDB2' write to table"

Enable/Disable Event monitor :

db2 set event monitor DB2DETAILDEADLOCK state 0

Create & Activate the LD_CONN event monitor :

for db in `db2 list db directory | grep -p Indirect | grep alias | awk '{print $NF}'`; do db2 connect to ${db} >/dev/null;db2 "create event monitor ld_conn for connections where appl_name <> 'KUDDB2' write to table";db2 set event monitor LD_CONN state 1;db2 "select varchar(current schema,9) instance, varchar(current server, 9) database, substr(evmonname,1,30) as evmonname, EVENT_MON_STATE(evmonname) as state from syscat.eventmonitors with ur";db2 terminate >/dev/null; done

Check status of Event monitors in multiple databases :

for db in `db2 list db directory | grep -p Indirect | grep alias | awk '{print $NF}'`; do db2 connect to ${db};db2 "select substr(evmonname,1,30) as evmonname, EVENT_MON_STATE(evmonname) as state from syscat.eventmonitors with ur"; done

Export from Connection event monitor :

db2 "export to aixreprod01_connections.csv of del select substr(AUTH_ID,1,20) USER, substr(appl_name,1,20) APPLICATION, char(CLIENT_NNAME,30) HOSTNAME,conn_time TIME from CONNHEADER_LD_CONN order by conn_time with ur"

Activity monitor (with filtering) :

 CREATE EVENT MONITOR DBAACTIVITIES
       FOR ACTIVITIES
       WRITE TO TABLE
       ACTIVITY (TABLE ACTIVITY_DBAACTIVITIES
                 IN TS32K_DBA
                 PCTDEACTIVATE 100),
       ACTIVITYMETRICS (TABLE ACTIVITYMETRICS_DBAACTIVITIES
                        IN TS32K_DBA
                        PCTDEACTIVATE 100),
       ACTIVITYSTMT (TABLE ACTIVITYSTMT_DBAACTIVITIES
                     IN TS32K_DBA
                     PCTDEACTIVATE 100),
       ACTIVITYVALS (TABLE ACTIVITYVALS_DBAACTIVITIES
                     IN TS32K_DBA
                     PCTDEACTIVATE 100),
       CONTROL (TABLE CONTROL_DBAACTIVITIES
                IN TS32K_DBA
                PCTDEACTIVATE 100)
       MANUALSTART@
       
       create table dba_metrics.config (attr varchar(100),value varchar(100),enabled char(1)) in ts32k_dba@
       
       create unique index dba_metrics.I_CONFIG_U01 on dba_metrics.config (attr,value) include (enabled)@
       
       CREATE OR REPLACE TRIGGER DBA_METRICS.T_CONNECTION_FILTER AFTER INSERT ON DBA_METRICS.MON_CONNECTIONS_DETAILS
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
WHEN (N.HOSTNAME IN (SELECT VALUE FROM DBA_METRICS.CONFIG WHERE ATTR = 'filter_activities' AND ENABLED='1'))
BEGIN ATOMIC
	CALL SYSPROC.WLM_SET_CONN_ENV(N.APPLICATION_HANDLE,'<collectactdata>WITH DETAILS, SECTION</collectactdata>'); 
END@

CREATE OR REPLACE TRIGGER dba_metrics.T_CONFIG_DISABLE_FILTER AFTER UPDATE ON DBA_METRICS.CONFIG
REFERENCING OLD AS O NEW AS N 
FOR EACH ROW MODE DB2SQL
WHEN (N.ENABLED = '0')
BEGIN ATOMIC
	DECLARE app_handle BIGINT;

	FOR v AS
		select distinct application_handle FROM TABLE(MON_GET_CONNECTION(cast(NULL as bigint), -2)) AS t where t.CLIENT_HOSTNAME = o.value and o.attr = 'filter_activities'
	DO 
		set app_handle = v.application_handle;
		CALL SYSPROC.WLM_SET_CONN_ENV(app_handle,'<collectactdata>NONE</collectactdata>');
	END FOR;
END@

Sort utilization :

db2 "SELECT APPLICATION_HANDLE,
sum(TOTAL_SORTS) as SUM_TOTAL_SORTS,
sum(TOTAL_SECTION_SORTS) as SUM_TOTAL_SECTION_SORTS,
sum(SORT_OVERFLOWS) as SUM_SORT_OVERFLOWS,
sum(POST_THRESHOLD_SORTS) as SUM_POST_THRESHOLD_SORTS,
decimal(avg(TOTAL_SECTION_PROC_TIME)/1000.0,8,2) as AVG_TOTAL_SECTION_PROC_TIME_SEC,
decimal(avg(TOTAL_SECTION_SORT_PROC_TIME)/1000.0,8,2) as
AVG_TOTAL_SECTION_SORT_PROC_TIME_SEC,
sum(TOTAL_HASH_JOINS) as SUM_TOTAL_HASH_JOINS,
sum(TOTAL_HASH_LOOPS) as SUM_TOTAL_HASH_LOOPS,
sum(HASH_JOIN_OVERFLOWS) as SUM_HASH_JOIN_OVERFLOWS,
sum(ROWS_MODIFIED) as SUM_ROWS_MODIFIED,
sum(POOL_DATA_WRITES) as SUM_POOL_DATA_WRITES
FROM TABLE(MON_GET_CONNECTION(cast(NULL as bigint), -2)) AS t
GROUP BY APPLICATION_HANDLE with ur"

Read ratios :

db2 "WITH READ_METRICS as (
SELECT APPLICATION_HANDLE,
sum(ROWS_READ) as ROWS_READ,
sum(POOL_DATA_L_READS) as POOL_DATA_L_READS,
sum(POOL_INDEX_L_READS) as POOL_INDEX_L_READS,
sum(POOL_TEMP_DATA_L_READS+POOL_TEMP_INDEX_L_READS) as POOL_TEMP_L_READS
FROM TABLE(MON_GET_CONNECTION(cast(NULL as bigint), -2)) AS m
group by application_handle
)
select r.APPLICATION_HANDLE, r.ROWS_READ,
case when POOL_DATA_L_READS+POOL_TEMP_L_READS > 0 then
decimal(r.ROWS_READ*1.00/(POOL_DATA_L_READS+POOL_TEMP_L_READS), 8,2) end as
ROWS_READ_PER_POOL_L_READ,
POOL_DATA_L_READS, POOL_INDEX_L_READS, POOL_TEMP_L_READS,
varchar(STMT_TEXT,100) as STMT_TEXT
from READ_METRICS r left outer join SYSIBMADM.MON_CURRENT_SQL s
ON r.APPLICATION_HANDLE = s.APPLICATION_HANDLE
order by pool_data_l_reads desc with ur"

BP read efficiency :

db2 "SELECT SUBSTR(BP_NAME, 1, 15) AS BP_NAME, TOTAL_PHYSICAL_READS, AVERAGE_READ_TIME_MS,TOTAL_ASYNC_READS,TOTAL_SYNC_READS,PERCENT_SYNC_READS,ASYNC_NOT_READ_PERCENT FROM SYSIBMADM.BP_READ_IO with ur"

Page cleaning (pS Castout) rates :

db2 "select
current timestamp as Time,
case when sum(w.TOTAL_APP_COMMITS) < 100 then null else
cast( float(sum(b.POOL_DATA_WRITES+b.POOL_INDEX_WRITES))
/ sum(w.TOTAL_APP_COMMITS) as decimal(6,1)) end
as \"BP wrt / UOW\",
case when sum(b.POOL_DATA_WRITES+b.POOL_INDEX_WRITES) < 1000 then null else
cast( float(sum(b.POOL_WRITE_TIME))
/ sum(b.POOL_DATA_WRITES+b.POOL_INDEX_WRITES) as decimal(5,1)) end
as \"ms / BP wrt\"
from table(mon_get_workload(null,null)) as w,
table(mon_get_bufferpool(null,null)) as b"

Generate SQLs :

dsqgen -input ../query_templates/templates.lst -directory ../query_templates -dialect db2 -scale 10 -verbose

Generate DATA :

dsdgen -SCALE 10 -dir ~/DATA -verbose

Disk speed :

#!/bin/bash
 
myfile=$1
 
echo "Creating test file $myfile using direct I/O"
dd if=/dev/zero of=$myfile bs=1024M count=12 oflag=direct
 
sync;sync;sync;echo 3 > /proc/sys/vm/drop_caches
 
echo "Single Direct I/O writer"
( dd if=/dev/zero of=$myfile bs=64K count=196608 conv=notrunc oflag=direct > thread1.out 2>&1 ) &
 
wait
cat thread1.out
 
echo "Two Direct I/O writer"
( dd if=/dev/zero of=$myfile bs=64K count=98304 conv=notrunc oflag=direct > thread1.out 2>&1 ) &
( dd if=/dev/zero of=$myfile bs=64K count=98304 seek=98304 conv=notrunc oflag=direct > thread2.out 2>&1 ) &
 
wait
cat thread1.out thread2.out

Performance monitoring (tools and techniques)

Connections

Memory

Locks

Autoconfigure

Snapshots

Monitoring

Identify "hot" table spaces

DB2 Design advisor / Explain / Access plans

Query costs

Optimization Classes & Profiles

Automatic Maintenance

Automatic backups

Event monitors

Sorting

Bufferpools

TPC-DS Benchmark

My wiki