469,268 Members | 1,045 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,268 developers. It's quick & easy.

difference in performance in two environments for same sql

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
Oct 28 '08 #1
1 1907
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
Oct 28 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Sebastian Werner | last post: by
reply views Thread by Michael Rudolph | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.