difference in performance in two environments for same sql

[db2admin]

hello,

We have 2 environments one is test which is one BCU with 8 partitions
+ admin/catalog node server ( smaller BCU ) and other prod is 3 BCU
with 24 partitions + admin/catalog node server ( smaller BCU ) .

I have sql which shows higher cost in test but run faster and does not
drive CPU 100% and same SQL shows lower cost on prod but takes 5 times
longer and drives CPU to 100%

If i use optimization level 3 on prod, then this SQL on prod runs same
way as on test with same time frame. default optimization level is 5
for both environments.

why same sql with same amount of data in tables but different
databases shows different explain plans and runs differently ? can
optimization level be specified different for just one SQL in
application ?

regards,
db2admin

[Serge Rielau]

db2admin wrote:

hello,

We have 2 environments one is test which is one BCU with 8 partitions
+ admin/catalog node server ( smaller BCU ) and other prod is 3 BCU
with 24 partitions + admin/catalog node server ( smaller BCU ) .

I have sql which shows higher cost in test but run faster and does not
drive CPU 100% and same SQL shows lower cost on prod but takes 5 times
longer and drives CPU to 100%

If i use optimization level 3 on prod, then this SQL on prod runs same
way as on test with same time frame. default optimization level is 5
for both environments.

why same sql with same amount of data in tables but different
databases shows different explain plans and runs differently ? can
optimization level be specified different for just one SQL in
application ?

Take a look at explain on both machines and compare the explain headers?
Do any metric differ (IO bandwidth, CPU speed, etc...)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab