请教DB2性能瓶颈问题
在执行一个大数据量并发查询每个查询为700W，6个并发同时查询，完成后进行另外7个查询，总数据量8KW，逐条查询1W条缓存写一次文件。数据大小8GB。 &nbs...显示全部
查看其它 6 个回答kxm327的回答
SQL执行计划：
Estimated Cost = 188263.953125
Estimated Cardinality = 48298.742188
DB2快照：
Database Snapshot
Database name = testdb
Database path = /db2data/testdb_data/db2testdb/NODE0000/SQL00001/MEMBER0000/
Input database alias = testdb
Database status = Active
Catalog database partition number = 0
Catalog network node name = testdb
Operating system running at database server= AIX 64BIT
Location of the database = Local
First database connect timestamp = 04/21/2016 13:17:14.126112
Last reset timestamp = 04/21/2016 13:20:04.296551
Last backup timestamp = 04/20/2016 23:00:01.000000
Snapshot timestamp = 04/21/2016 14:02:28.318149
Number of automatic storage paths = 1
Automatic storage path = /db2data/testdb_data
Node number = 0
State = In Use
File system ID = 9223372234423271425
Storage path free space (bytes) = 37916598272
File system used space (bytes) = 1171049586688
File system total space (bytes) = 1209033293824
High water mark for connections = 222
Application connects = 188
Secondary connects total = 71
Applications connected currently = 151
Appls. executing in db manager currently = 0
Agents associated with applications = 277
Maximum agents associated with applications= 293
Maximum coordinating agents = 174
Number of Threshold Violations = 0
Locks held currently = 0
Lock waits = 47
Time database waited on locks (ms) = 2451
Lock list memory in use (Bytes) = 705152
Deadlocks detected = 0
Lock escalations = 0
Exclusive lock escalations = 0
Agents currently waiting on locks = 0
Lock Timeouts = 0
Number of indoubt transactions = 0
Total Private Sort heap allocated = 0
Total Shared Sort heap allocated = 6
Shared Sort heap high water mark = 263890
Post threshold sorts (shared memory) = 2
Total sorts = 9744
Total sort time (ms) = 99867
Sort overflows = 32
Active sorts = 0
Buffer pool data logical reads = 8090865
Buffer pool data physical reads = 2562336
Buffer pool temporary data logical reads = 15790531
Buffer pool temporary data physical reads = 7226542
Asynchronous pool data page reads = 6987205
Buffer pool data writes = 7293811
Asynchronous pool data page writes = 6427534
Buffer pool index logical reads = 1081258
Buffer pool index physical reads = 302178
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Asynchronous pool index page reads = 204966
Buffer pool index writes = 7
Asynchronous pool index page writes = 1
Buffer pool xda logical reads = 0
Buffer pool xda physical reads = 0
Buffer pool temporary xda logical reads = 0
Buffer pool temporary xda physical reads = 0
Buffer pool xda writes = 0
Asynchronous pool xda page reads = 0
Asynchronous pool xda page writes = 0
Total buffer pool read time (milliseconds) = 1909861
Total buffer pool write time (milliseconds)= 4843587
Total elapsed asynchronous read time = 789477
Total elapsed asynchronous write time = 3173030
Asynchronous data read requests = 350280
Asynchronous index read requests = 8616
Asynchronous xda read requests = 0
No victim buffers available = 12479431
LSN Gap cleaner triggers = 0
Dirty page steal cleaner triggers = 13407
Dirty page threshold cleaner triggers = 48884
Time waited for prefetch (ms) = 237238
Unread prefetch pages = 153532
Direct reads = 4148
Direct writes = 32
Direct read requests = 343
Direct write requests = 3
Direct reads elapsed time (ms) = 596
Direct write elapsed time (ms) = 1
Database files closed = 0
Host execution elapsed time = 12755.164125
Commit statements attempted = 2569
Rollback statements attempted = 0
Dynamic statements attempted = 231385
Static statements attempted = 21247
Failed statement operations = 0
Select SQL statements executed = 24500
Xquery statements executed = 0
Update/Insert/Delete statements executed = 4806
DDL statements executed = 1
Inactive stmt history memory usage (bytes) = 0
Internal automatic rebinds = 0
Internal rows deleted = 0
Internal rows inserted = 0
Internal rows updated = 0
Internal commits = 497
Internal rollbacks = 0
Internal rollbacks due to deadlock = 0
Number of MDC table blocks pending cleanup = 0
Rows deleted = 14
Rows inserted = 193372
Rows updated = 2235
Rows selected = 83119401
Rows read = 2202865207
Binds/precompiles attempted = 0
Log space available to the database (Bytes)= 20347042306
Log space used by the database (Bytes) = 32957694
Maximum secondary log space used (Bytes) = 0
Maximum total log space used (Bytes) = 43052117
Secondary logs allocated currently = 0
Log pages read = 0
Log read time (sec.ns) = 0.000000000
Log pages written = 12105
Log write time (sec.ns) = 21.461123685
Number write log IOs = 7029
Number read log IOs = 0
Number partial page log IOs = 3131
Number log buffer full = 0
Log data found in buffer = 0
Log to be redone for recovery (Bytes) = 31434526
Log accounted for by dirty pages (Bytes) = 31434526
Node number = 0
File number of first active log = 10091
File number of last active log = 10190
File number of current active log = 10091
File number of log being archived = Not applicable
Package cache lookups = 20197
Package cache inserts = 3618
Package cache overflows = 1
Package cache high water mark (Bytes) = 49627013
Application section lookups = 269518
Application section inserts = 24746
Catalog cache lookups = 76162
Catalog cache inserts = 177
Catalog cache overflows = 0
Catalog cache high water mark = 1763411
Catalog cache statistics size = 0
Workspace Information
Number of hash joins = 186
Number of hash loops = 0
Number of hash join overflows = 26
Number of small hash join overflows = 0
Post threshold hash joins (shared memory) = 0
Active hash joins = 0
Number of OLAP functions = 0
Number of OLAP function overflows = 0
Active OLAP functions = 0
Statistic fabrications = 1
Synchronous runstats = 1
Asynchronous runstats = 2
Total statistic fabrication time (milliseconds) = 2
Total synchronous runstats time (milliseconds) = 3