标题: 求教DB2查询效率 [打印本页] 作者: wyaccent 时间: 2006-04-12 21:30 标题: 求教DB2查询效率 为什么同样的bnd包同样的程序,同样的数据,有时候效率很高,有时候效率很低
当一个静态的SQC程序插入很多数据后(百万),然后另外的一个程序再去读取这些数据就会出现这样的情况
请大家发表一下意见。作者: 大梦 时间: 2006-04-13 00:27
runstats一下看看!作者: nuoran 时间: 2006-04-13 09:21
runstats on table and rebind your sqc programs.
or you can alter the table volatile作者: wyaccent 时间: 2006-04-13 19:44
runstats以后效果很明显,但是一个简单的程序先插入,然后再读取,或者是一边读取一边插入,不可能中途调用shell去做runstats,和bnd吧作者: wyaccent 时间: 2006-04-13 19:46
而且我想关于数据量变化请教一下,到底变化多大才有很明显的影响,因为我现在发现有时后插入或者update的数据并不多,可能就20万左右,效率突然下降很明显作者: lizhuo 时间: 2006-04-14 10:49
建议你做个快照监控,看一下性能问题出在哪里?你只说效率低,恐怕没人能帮你作者: wyaccent 时间: 2006-04-14 14:16
我写了一个shell,用get shnpshot监控读取和插入的数量。
我的开发环境里面实际是没有数据的,我用db2expln工具观察包的情况,但是始终看不出什么道道作者: wyaccent 时间: 2006-04-15 18:01
我发一个程序运行的快照大家帮助分析一下:
Application Snapshot
Application handle = 464
Application status = UOW Executing
Status change time = 04/15/2006 18:03:47.229266
Application code page = 819
Application country/region code = 1
DUOW correlation token = *LOCAL.db2ins16.060415095057
Application name = trpredat.x
Application ID = *LOCAL.db2ins16.060415095057
Sequence number = 0008
TP Monitor client user ID =
TP Monitor client workstation name =
TP Monitor client application name =
TP Monitor client accounting string =
Connection request start timestamp = 04/15/2006 17:50:57.717490
Connect request completion timestamp = 04/15/2006 17:50:57.717690
Application idle time =
CONNECT Authorization ID = HBTRAN
Client login ID = hbtran
Configuration NNAME of client =
Client database manager product ID = SQL08020
Process ID of client application = 9863356
Platform of client application = AIX
Communication protocol of client = Local Client
Inbound communication address = *LOCAL.db2ins16
Database name = TRANDBS
Database path = /hbtran/db2dir/db2ins16/NODE0000/SQL00001/
Client database alias = TRANDBS
Input database alias =
Last reset timestamp =
Snapshot timestamp = 04/15/2006 18:03:47.282924
The highest authority level granted =
Direct DBADM authority
Direct CREATETAB authority
Direct BINDADD authority
Direct CONNECT authority
Direct CREATE_NOT_FENC authority
Direct LOAD authority
Direct IMPLICIT_SCHEMA authority
Direct CREATE_EXT_RT authority
Direct QUIESCE_CONN authority
Indirect SYSADM authority
Indirect CREATETAB authority
Indirect BINDADD authority
Indirect CONNECT authority
Indirect IMPLICIT_SCHEMA authority
Coordinating database partition number = 0
Current database partition number = 0
Coordinator agent process or thread ID = 11038734
Agents stolen = 0
Agents waiting on locks = 0
Maximum associated agents = 1
Priority at which application agents work = 0
Priority type = Dynamic
Lock timeout (seconds) = 40
Locks held by application = 42762
Lock waits since connect = 0
Time application waited on locks (ms) = 0
Deadlocks detected = 0
Lock escalations = 0
Exclusive lock escalations = 0
Number of Lock Timeouts since connected = 0
Total time UOW waited on locks (ms) = 0
Total sorts = 17021
Total sort time (ms) = 0
Total sort overflows = 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
Buffer pool data logical reads = 155243169
Buffer pool data physical reads = 0
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool data writes = 0
Buffer pool index logical reads = 156475283
Buffer pool index physical reads = 1
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Buffer pool index writes = 0
Total buffer pool read time (ms) = 8
Total buffer pool write time (ms) = 0
Time waited for prefetch (ms) = 0
Unread prefetch pages = 0
Direct reads = 0
Direct writes = 0
Direct read requests = 0
Direct write requests = 0
Direct reads elapsed time (ms) = 0
Direct write elapsed time (ms) = 0
UOW log space used (Bytes) = 63653449
Previous UOW completion timestamp =
Elapsed time of last completed uow (sec.ms)= 0.000000
UOW start timestamp =
UOW stop timestamp =
UOW completion status =
Open remote cursors = 0
Open remote cursors with blocking = 0
Rejected Block Remote Cursor requests = 0
Accepted Block Remote Cursor requests = 5
Open local cursors = 1
Open local cursors with blocking = 1
Total User CPU Time used by agent (s) = 755.530000
Total System CPU Time used by agent (s) = 2.800000
Host execution elapsed time = 0.000035
Shared high water mark = 0
Total shared overflows = 0
Total shared section inserts = 0
Total shared section lookups = 0
Private high water mark = 195791
Total private overflows = 0
Total private section inserts = 36
Total private section lookups = 50
Most recent operation = Execute
Most recent operation start timestamp = 04/15/2006 18:03:47.229268
Most recent operation stop timestamp =
Agents associated with the application = 1
Number of hash joins = 0
Number of hash loops = 0
Number of hash join overflows = 0
Number of small hash join overflows = 0
Statement type = Static SQL Statement
Statement = Execute
Section number = 3
Application creator = HBTRAN
Package name = TRDBFUNC
Consistency Token = 0AlOVOEW
Package Version ID =
Cursor name =
Statement database partition number = 0
Statement start timestamp = 04/15/2006 18:03:47.229268
Statement stop timestamp =
Elapsed time of last completed stmt(sec.ms)= 0.000000
Total Statement user CPU time = 0.070000
Total Statement system CPU time = 0.000000
SQL compiler cost estimate in timerons = 51
SQL compiler cardinality estimate = 1
Degree of parallelism requested = 1
Number of agents working on statement = 1
Number of subagents created for statement = 1
Statement sorts = 0
Total sort time = 0
Sort overflows = 0
Rows read = 13882
Rows written = 0
Rows deleted = 0
Rows updated = 0
Rows inserted = 0
Rows fetched = 0
Buffer pool data logical reads = 13882
Buffer pool data physical reads = 0
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool index logical reads = 13960
Buffer pool index physical reads = 0
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Blocking cursor = NO
Agent process/thread ID = 11038734
Statement type = Static SQL Statement
Statement = Fetch
Section number = 12
Application creator = HBTRAN
Package name = TRPREDAT
Consistency Token = pAxPVOEW
Package Version ID =
Cursor name = TRSV_X
Statement database partition number = 0
Statement start timestamp = 04/15/2006 17:52:20.910363
Statement stop timestamp = 04/15/2006 18:03:32.133342
Elapsed time of last completed stmt(sec.ms)= 0.001737
Total Statement user CPU time = 0.060000
Total Statement system CPU time = 0.000000
SQL compiler cost estimate in timerons = 13
SQL compiler cardinality estimate = 12
Degree of parallelism requested = 1
Number of agents working on statement = 0
Number of subagents created for statement = 1
Statement sorts = 0
Total sort time = 0
Sort overflows = 0
Rows read = 23575
Rows written = 0
Rows deleted = 0
Rows updated = 0
Rows inserted = 0
Rows fetched = 23575
Buffer pool data logical reads = 1565
Buffer pool data physical reads = 0
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool index logical reads = 0
Buffer pool index physical reads = 0
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Blocking cursor = NO
Agent process/thread ID = 11038734
Agent Lock timeout (seconds) = 40
Memory usage for agent:
Memory Pool Type = Application Control Heap
Current size (bytes) = 16384
High water mark (bytes) = 16384
Configured size (bytes) = 704512
Memory Pool Type = Application Heap
Current size (bytes) = 475136
High water mark (bytes) = 475136
Configured size (bytes) = 1277952作者: wyaccent 时间: 2006-04-15 18:02
多谢了,很急很急,效率不稳定,有时候我断下来把共享内存杀一便再执行就好了,好不容易捕捉到一个慢的时候的快照