difference in performance in two environments for same sql

**Serge Rielau** · Oct 28 '08, 04:15 PM

Re: difference in performance in two environments for same sql

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

difference in performance in two environments for same sql

difference in performance in two environments for same sql

Comment