var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Different explain plan for same SQL
I have dev and QA database with same DB cf and DB cfg setup.
This is version 9.7 on linux.
The SQL has 3 table join.
Table A have 80 million rows
Table B have 100,000 rows
Table C have 120,000 rows
Select * from A join B on A.col1=B.col1
join C on A.col1.C.col1
where B.col2 = 'xyz'
B.col2 = 'xyz' filters 15 million rows from table A
C.col2 ='xyz' filters 5 million rows from table A
On dev server the join starts with C & A and then B
This performs better and returns in less than 1 min.
On QA server the join starts with B & A and then C
This returns in 4 mins
I tried reorg/runstats on both servers. But the plan does not change.
Volume on both servers is identical. Is there are other way to make QA access plan work as DEV?
I can not rewrite the query since it is generated out of cognos tool.
How about comparing output of db2exfmt, as a starting point?
Some parameters which would influence access path showed at the top of the output.
If those were identical, compare more detailes of the output.
DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2009
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool
******************** EXPLAIN INSTANCE ********************
CPU Speed: 7.085164e-007
Comm Speed: 0
Buffer Pool size: 10250
Sort Heap size: 256
Database Heap size: 600
Lock List size: 4096
Maximum Lock List: 22
Average Applications: 1
Locks Available: 28835
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability