db2连接查询索引不起作用的问题

显示全部楼层 · 2014-3-10 14:09:19

遇到一个比较奇怪的问题，测试环境和生产环境上执行相同的SQL语句：
select A.TRAN_ID, A.RISKTYPE,A.TRAN_DATE, A.TRAN_TIME, A.BK, A.BR,
A.PRE_SEQUENCE_ID , A.TLT, B.AC, A.CI_NAME, A.SEQUENCE_ID,
B.TRAN_DATE as RS_TRAN_DATE, B.TRAN_TIME as RS_TRAN_TIME, B.TRAN_CODE
as RE_TRAN_CODE, B.CCY as RE_TRAN_CCY, B.AMT as RE_TRAN_AMT, B.TLT as
RE_TRAN_TLT, A.PROC_FLAG, A.SUP
from AORMS.ARMS_sp20001 A left join AORMS.ARMS_RESOURCE_TRAN B on
A.PRE_SEQUENCE_ID=B.SEQUENCE_ID and A.TRAN_DATE=B.TRAN_DATE
针对查询条件两表都建立了复合索引
测试环境上的用到了索引，见附件aqtout_test.txt的分析结果
生产环境上不能用到ARMS_RESOURCE_TRAN表的索引，见附件aqtout_product.txt的分析结果
烦请各位帮忙分析分析可能存在的原因，谢谢～～

千问 · 2014-3-10 14:09:19

1) db2level is different
2) can you do count(*) on the query on both system to see what 's the real number of rows output?
3) what is the difference with runtime performance between prod and test system?
4) clearly in prod the 2 start/stop key predicates were not pushed down to the build side, and we can see the amount of data is different as well.
also the configuration in prod system is significantly larger than testing one:
prod:

Parallelism:
None

CPU Speed:
3.857478e-007

Comm Speed:
100

Buffer Pool size:
90000

Sort Heap size:
8096

Database Heap size:
5000

Lock List size:
20000

Maximum Lock List:
50

Average Applications:
1

Locks Available:
640000
test:

Parallelism:
None

CPU Speed:
4.408546e-007

Comm Speed:
100

Buffer Pool size:
1000

Sort Heap size:
4096

Database Heap size:
41599

Lock List size:
100

Maximum Lock List:
10

Average Applications:
1

Locks Available:
1020
also the data distribution is very different:
prod:

2) Predicate used in Join

Relational Operator:
Equal (=)

Subquery Input Required:
No

Filter Factor:
0.04

Predicate Text:

--------------

(Q2.TRAN_DATE = Q1.TRAN_DATE)

3) Predicate used in Join

Relational Operator:
Equal (=)

Subquery Input Required:
No

Filter Factor:
0.04

Predicate Text:

--------------

(Q2.PRE_SEQUENCE_ID = Q1.SEQUENCE_ID)

test:

Predicates:

----------

2) Predicate used in Join

Relational Operator:
Equal (=)

Subquery Input Required:
No

Filter Factor:
0.111111

Predicate Text:

--------------

(Q1."TRAN_DATE" = Q2."TRAN_DATE")

3) Predicate used in Join

Relational Operator:
Equal (=)

Subquery Input Required:
No

Filter Factor:
0.00568182

Predicate Text:

--------------

(Q1."SEQUENCE_ID" = Q2."PRE_SEQUENCE_ID")
so i wonder does the data in production has similar data distribution like testing box?
also what runstats did you use? can you send db2look data for the table/index/stats on both system please?
thx