#!/usr/bin/ksh # take a snapshot after specified sleep period for a number of iterations # parameters: (1) database name # (2) directory for output # (3) interval between iterations (seconds) # (4) maximum number of iterations # # Note: You may receive an error about the monitor heap being too small. You may # want to set mon_heap_sz to 2048 while monitoring. if [ $# -ne 4 ] then echo "4 parameters required: dbname output_dir sleep_interval iterations"; exit fi dbname=$1 runDir=$2 sleep_interval=$3 iterations=$4 stat_interval=3 stat_iterations=$(($sleep_interval/$stat_interval)) if [[ -d $runDir ]]; then echo "dir: $runDir already exists, either remove it or 使用 another directory name" exit fi mkdir $runDir cd $runDir db2 update monitor switches using bufferpool on lock on sort on statement on \\ table on uow on # repeat the snapshot loop for the specified iterations let i=1 while [ i -le $iterations ] do if [ $i -le 9 ] then i2="0$i" else i2="$i" fi echo "Iteration $i2 (of $iterations) starting at `date`" vmstat $stat_interval $stat_iterations > vmstat_$i2 iostat $stat_interval $stat_iterations > iostat_$i2 db2 -v reset monitor all sleep $sleep_interval db2 -v get snapshot for dbm > snap_$i2 db2 -v get snapshot for all on $dbname >> snap_$i2 echo "Iteration $i2 (of $iterations) complete at `date`" let i=$i+1 done db2 update monitor switches using bufferpool off lock off sort off statement off \\ table off uow off db2 terminate
清单 4. getsnap.bat (Windows)
@echo off REM REM take a snapshot after specified sleep period for a number of iterations REM parameters: (1) database name REM (2) file name id REM (3) interval between iterations (seconds) REM (4) maximum number of iterations REM REM Note: You may receive an error about the monitor heap being too small. You may REM want to set mon_heap_sz to 2048 while monitoring. :CHECKINPUT IF ""=="%4" GOTO INPUTERROR GOTO STARTPRG :INPUTERROR echo %0 requires 4 parameters: dbname filename_id sleep_interval iterations echo e.g. "getsnap.bat sample 0302 60 3" GOTO END :STARTPRG SET dbname=%1 SET fileid=%2 SET sleep_interval=%3 SET iterations=%4 db2 update monitor switches using bufferpool on lock on sort on statement on table on uow on REM repeat the snapshot loop for the specified iterations SET i=1 :SNAPLOOP IF %i% LSS 10 SET i2=0%i% IF %i% GTR 9 SET i2=%i% echo Starting Iteration %i2% (of %iterations%) db2 -v reset monitor all sleep %sleep_interval% db2 -v get snapshot for dbm > snap%i2%_%fileid% db2 -v get snapshot for all on %dbname% >> snap%i2%_%fileid% echo Completing Iteration %i2% (of %iterations%) SET /a i+=1 IF %i% GTR %iterations% GOTO ENDLOOP GOTO SNAPLOOP :ENDLOOP db2 update monitor switches using bufferpool off lock off sort off statement off table off uow off db2 terminate :END
注意,这两个脚本在行为上稍有不同,但是都可以产生所需的快照输出。
在后面的一些小节中,快照监视可用作寻找 DBM 和 DB 配置参数的最优设置的一种方式。
DB2 v8 Documentation:
Reference ==> System monitor ==> Snapshot monitor
动态 SQL 语句
清单 3和 清单 4中显示的脚本将发出一个 "get snapshot for all on dbname" 命令,该命令包括 "get snapshot for dynamic SQL on dbname" 命令的所有输出。如果您发现不会捕获很多的 SQL 语句,那么可以增加监控的历时。一条语句的输出的 "Dynamic SQL Snapshot Result" 部分看上去如 清单 5所示: 清单 5. 示例动态 SQL 快照
Dynamic SQL Snapshot Result Database name = SAMPLE Database path = C:\\DB2\\NODE0000\\SQL00003\\ Number of executions = 1 Number of compilations = 1 Worst preparation time (ms) = 1624 Best preparation time (ms) = 1624 Internal rows deleted = 0 Internal rows inserted = 0 Rows read = 41 Internal rows updated = 0 Rows written = 0 Statement sorts = 0 Total execution time (sec.ms) = 0.134186 Total user cpu time (sec.ms) = 0.000000 Total system cpu time (sec.ms) = 0.000000 Statement text = select * from sales ...
"Statement text"显 示语句文本。如果注意到了重复的语句,这些语句除了 WHERE 子句中谓词的值有所不同以外,其他地方都是一致的,那么就可以使用参数标记,以避免重新编译语句。这样可以使用相同的包,从而帮助避免重复的语句准备,而 这种准备的消耗是比较大的。还可以将语句文本输入到 Design Advisor 中,以便生成最优的索引。
grep -n " Statement text" snap.out | more
findstr /C:"Statement text" snap.out
缓冲池大小的设置
通过使用 "get snapshot for all on dbname" 可以为数据库上的每个缓冲池生成一个快照。 清单 6展示了那样一个快照: 清单 6. 示例缓冲池快照
Bufferpool Snapshot Bufferpool name = IBMDEFAULTBP Database name = SAMPLE Database path = C:\\DB2\\NODE0000\\SQL00002\\ Input database alias = SAMPLE Snapshot timestamp = 02-20-2004 06:24:45.991065 Buffer pool data logical reads = 370 Buffer pool data physical reads = 54 Buffer pool temporary data logical reads = 0 Buffer pool temporary data physical reads = 0 Buffer pool data writes = 3 Buffer pool index logical reads = 221 Buffer pool index physical reads = 94 Buffer pool temporary index logical reads = 0 Buffer pool temporary index physical reads = 0 Total buffer pool read time (ms) = 287 Total buffer pool write time (ms) = 1 Asynchronous pool data page reads = 9 Asynchronous pool data page writes = 0 Buffer pool index writes = 0 Asynchronous pool index page reads = 0 Asynchronous pool index page writes = 0 Total elapsed asynchronous read time = 0 Total elapsed asynchronous write time = 0 Asynchronous data read requests = 3 Asynchronous index read requests = 0 No victim buffers available = 0 Direct reads = 86 Direct writes = 4 Direct read requests = 14 Direct write requests = 2 Direct reads elapsed time (ms) = 247 Direct write elapsed time (ms) = 56 Database files closed = 0 Data pages copied to extended storage = 0 Index pages copied to extended storage = 0 Data pages copied from extended storage = 0 Index pages copied from extended storage = 0 Unread prefetch pages = 0 Vectored IOs = 3 Pages from vectored IOs = 9 Block IOs = 0 Pages from block IOs = 0 Physical page maps = 0 Node number = 0 Tablespaces using bufferpool = 4 Alter bufferpool information: Pages left to remove = 0 Current size = 250 Post-alter size = 250
BPHR (%) = (1 - (("Buffer pool data physical reads" + "Buffer pool index physical reads") / ("Buffer pool data logical reads" + "Buffer pool index logical reads"))) * 100
当您对一个配置参数作了更改时,就可以用下面的 DB2 CLP 命令查看该设置是否立即生效(在线):
GET DBM CFG SHOW DETAIL
GET DB CFG FOR dbname SHOW DETAIL
例
如,在接下来的情况中,MAX_QUERYDEGREE 和 MAXTOTFILOP 分别增加到了 3 和 19000。如果参数是在线配置的,则
Delayed Value 跟 Current Value 应该是一样的。否则,就需要重新启动实例,或者重新激活数据库。 清单 7. Show Details 实例
Database Manager Configuration Node type = Enterprise Server Edition with local and remote clients Description Parameter Current Value Delayed Value ------------------------------------------------------------------------------------------- Maximum query degree of parallelism (MAX_QUERYDEGREE) = 3 3 Maximum total of files open (MAXTOTFILOP) = 16000 19000
下面的配置参数中,有些是从共享内存分配空间的,所以应该记住 OS 的限制(在 前面已讨论)。您必须确保没有过度分配内存。如果过度分配内存,就会导致操作系统发生换页(page),这对于性能来说是灾难性的。
DB2 v8 Documentation:
Database Manager Snapshot Node name = Node type = Enterprise Server Edition with local and remote clients Instance name = DB2 Number of database partitions in DB2 instance = 1 Database manager status = Active Product name = DB2 v8.1.4.341 Service level = s031027 (WR21326) Private Sort heap allocated = 0 (SHEAPTHRES Private Sort heap high water mark = 1024 Post threshold sorts = 0 and Piped sorts requested = 0 Piped sorts accepted = 0 SORTHEAP)
Start Database Manager timestamp = 02-17-2004 14:24:37.107003 Last reset timestamp = Snapshot timestamp = 02-20-2004 06:19:53.272049 Remote connections to db manager = 0 (MAXAGENTS,MAXAPPLS,MAX_COORDAGENTS)
Remote connections executing in db manager = 0 Local connections = 1 (MAXAGENTS,MAXAPPLS,MAX_COORDAGENTS)
Local connections executing in db manager = 0 Active local databases = 1 (NUMDB) High water mark for agents registered = 8 (MAXAGENTS)
High water mark for agents waiting for a token = 0 Agents registered = 8 (MAXAGENTS)
Agents waiting for a token = 0 Idle agents = 6 (NUM_POOLAGENTS and NUM_INITAGENTS)
Committed private Memory (Bytes) = 46645248 Switch list for db partition number 0 Buffer Pool Activity Information (BUFFERPOOL) = ON 02-20-2004 06:18:57.403336 Lock Information (LOCK) = ON 02-20-2004 06:18:57.403338 Sorting Information (SORT) = ON 02-20-2004 06:18:57.403339 SQL Statement Information (STATEMENT) = ON 02-20-2004 06:18:57.403333 Table Activity Information (TABLE) = ON 02-20-2004 06:18:57.403335 Take Timestamp Information (TIMESTAMP) = ON 02-17-2004 14:24:37.107003 Unit of Work Information (UOW) = ON 02-20-2004 06:18:57.403328 Agents assigned from pool = 26 (NUM_POOLAGENTS and NUM_INITAGENTS) Agents created from empty pool = 10 (NUM_POOLAGENTS and NUM_INITAGENTS) Agents stolen from another application = 0 (MAXAGENTS) High water mark for coordinating agents = 8 Max agents overflow = 0 (MAXAGENTS) Hash joins after heap threshold exceeded = 0 Total number of gateway connections = 0 Current number of gateway connections = 0 Gateway connections waiting for host reply = 0 Gateway connections waiting for client request = 0 Gateway connection pool agents stolen = 0 Node FCM information corresponds to = 2 Free FCM buffers = 4093 Free FCM buffers low water mark = 4087 (FCM_NUM_BUFFERS) Free FCM message anchors = 1279 Free FCM message anchors low water mark = 1276 Free FCM connection entries = 1280 Free FCM connection entries low water mark = 1276 Free FCM request blocks = 2031 Free FCM request blocks low water mark = 2026 Number of FCM nodes = 4 Node Total Buffers Total Buffers Connection (FCM_NUM_BUFFERS) Number Sent Received Status ----------- ------------------ ------------------ ----------------- 0 282 275 Active 1 51 48 Active 2 0 0 Active 3 1 1 Active Memory usage for database manager: Memory Pool Type = Backup/Restore/Util Heap (UTIL_HEAP_SZ*) Current size (bytes) = 16384 High water mark (bytes) = 16384 Maximum size allowed (bytes) = 20660224 Memory Pool Type = Package Cache Heap (PCKCACHESZ*) Current size (bytes) = 327680 High water mark (bytes) = 327680 Maximum size allowed (bytes) = 1071644672 Memory Pool Type = Catalog Cache Heap (CATALOGCACHE_SZ*) Current size (bytes) = 81920 High water mark (bytes) = 81920 Maximum size allowed (bytes) = 1071644672 Memory Pool Type = Buffer Pool Heap Current size (bytes) = 1179648 High water mark (bytes) = 1179648 Maximum size allowed (bytes) = 1071644672 Memory Pool Type = Lock Manager Heap (LOCKLIST*) Current size (bytes) = 278528 High water mark (bytes) = 278528 Maximum size allowed (bytes) = 425984 Memory Pool Type = Database Heap (DBHEAP*) Current size (bytes) = 3342336 High water mark (bytes) = 3342336 Maximum size allowed (bytes) = 6275072 Memory Pool Type = Database Monitor Heap (MON_HEAP_SZ) Current size (bytes) = 180224 High water mark (bytes) = 425984 Maximum size allowed (bytes) = 442368 Memory Pool Type = Other Memory Current size (bytes) = 8060928 High water mark (bytes) = 8159232 Maximum size allowed (bytes) = 1071644672
快照总是显示 Current size (bytes) = High water mark (bytes),因为内存是在数据库激活的时候分配的。 清单 9. 数据库快照
Database Snapshot Database name = SAMPLE Database path = C:\\DB2\\NODE0000\\SQL00002\\ Input database alias = SAMPLE Database status = Active Catalog database partition number = 0 Catalog network node name = Operating system running at database server= NT Location of the database = Local First database connect timestamp = 02-20-2004 06:19:00.847979 Last reset timestamp = Last backup timestamp = Snapshot timestamp = 02-20-2004 06:23:17.252491 High water mark for connections = 1 (MAXAPPLS) Application connects = 1 Secondary connects total = 0 Applications connected currently = 1 (AVG_APPLS) Appls. executing in db manager currently = 0 Agents associated with applications = 1 Maximum agents associated with applications= 1 Maximum coordinating agents = 1 Locks held currently = 0 Lock waits = 0 Time database waited on locks (ms) = 0 Lock list memory in 使用 (Bytes) = 1000 (LOCKLIST and MAXLOCKS) Deadlocks detected = 0 Lock escalations = 0 (LOCKLIST and MAXLOCKS) Exclusive lock escalations = 0 (LOCKLIST and MAXLOCKS) Agents currently waiting on locks = 0 Lock Timeouts = 0 (LOCKTIMEOUT) Number of indoubt transactions = 0 Total Private Sort heap allocated = 0 (SHEAPTHRES and SORTHEAP) Total Shared Sort heap allocated = 0 (SHEAPTHRES_SHR and SORTHEAP) Shared Sort heap high water mark = 0 (SHEAPTHRES_SHR and SORTHEAP)
Total sorts = 0 Total sort time (ms) = 0 Sort overflows = 0 (SORTHEAP) Active sorts = 0 Buffer pool data logical reads = 370 Buffer pool data physical reads = 54 Buffer pool temporary data logical reads = 0 Buffer pool temporary data physical reads = 0 Asynchronous pool data page reads = 9 (NUM_IOSERVERS) Buffer pool data writes = 3 (CHNGPGS_THRESH and NUM_IOCLEANERS) Asynchronous pool data page writes = 0 (CHNGPGS_THRESH and NUM_IOCLEANERS)
Buffer pool index logical reads = 221 Buffer pool index physical reads = 94 Buffer pool temporary index logical reads = 0 Buffer pool temporary index physical reads = 0 Asynchronous pool index page reads = 0 (NUM_IOSERVERS) Buffer pool index writes = 0 (CHNGPGS_THRESH and NUM_IOCLEANERS) Asynchronous pool index page writes = 0 (CHNGPGS_THRESH and NUM_IOCLEANERS) Total buffer pool read time (ms) = 287 Total buffer pool write time (ms) = 1 Total elapsed asynchronous read time = 0 Total elapsed asynchronous write time = 0 Asynchronous data read requests = 3 Asynchronous index read requests = 0 No victim buffers available = 0 LSN Gap cleaner triggers = 0 Dirty page steal cleaner triggers = 0 (CHNGPGS_THRESH) Dirty page threshold cleaner triggers = 0 (CHNGPGS_THRESH) Time waited for prefetch (ms) = 0 (NUM_IOSERVERS) Unread prefetch pages = 0 Direct reads = 86 Direct writes = 4 Direct read requests = 14 Direct write requests = 2 Direct reads elapsed time (ms) = 247 Direct write elapsed time (ms) = 56 Database files closed = 0 (MAXFILOP) Data pages copied to extended storage = 0 Index pages copied to extended storage = 0 Data pages copied from extended storage = 0 Index pages copied from extended storage = 0 Host execution elapsed time = 0.000039 Commit statements attempted = 6 Rollback statements attempted = 1 Dynamic statements attempted = 281 Static statements attempted = 7 Failed statement operations = 1 Select SQL statements executed = 4 Update/Insert/Delete statements executed = 0 DDL statements executed = 2 Internal automatic rebinds = 0 Internal rows deleted = 0 Internal rows inserted = 0 Internal rows updated = 0 Internal commits = 1 Internal rollbacks = 0 Internal rollbacks due to deadlock = 0 Rows deleted = 0 Rows inserted = 0 Rows updated = 0 Rows selected = 336 Rows read = 375 Binds/precompiles attempted = 0 Log space available to the database (Bytes)= 5095757 (LOGPRIMARY and LOGSECOND) Log space used by the database (Bytes) = 4243 Maximum secondary log space used (Bytes) = 0 Maximum total log space used (Bytes) = 6498 (LOGPRIMARY and LOGSECOND) Secondary logs allocated currently = 0 (LOGPRIMARY and LOGSECOND) Log pages read = 0 (LOGBUFSZ) Log pages written = 5 (LOGBUFSZ) Appl id holding the oldest transaction = 38 Package cache lookups = 10 (PKGCACHESZ) Package cache inserts = 8 (PKGCACHESZ) Package cache overflows = 0 (PKGCACHESZ) Package cache high water mark (Bytes) = 191140 (PKGCACHESZ) Application section lookups = 281 Application section inserts = 6 Catalog cache lookups = 18 (CATALOGCACHE_SZ) Catalog cache inserts = 9 (CATALOGCACHE_SZ) Catalog cache overflows = 0 (CATALOGCACHE_SZ) Catalog cache high water mark = 0 (CATALOGCACHE_SZ) Workspace Information Shared high water mark = 0 Corresponding shared overflows = 0 Total shared section inserts = 0 Total shared section lookups = 0 Private high water mark = 21102 Corresponding private overflows = 0 Total private section inserts = 6 Total private section lookups = 6 Number of hash joins = 0 Number of hash loops = 0 Number of hash join overflows = 0 (SORTHEAP) Number of small hash join overflows = 0 (SORTHEAP)