 | |
#11
 
| |||
| |||
|
Re: Help with slow result set -
07-26-2011
, 08:33 AM
|
On 21 jul, 10:27, Bruno Almeida <bruno.alme... (AT) automatos (DOT) com> wrote: On 20 jul, 01:34, Helmut Tessarek <tessa... (AT) evermeet (DOT) cx> wrote: On 19.07.11 12:18 , Bruno Almeida wrote: 1) Sorry about the expression "client db", I could say Customer DB,in other words, is a database installed on customer environment. Understood. 2) The db2set parameters: Ok, what about the dbm and db parameters? Any significant differences? 3) Yes, the statistics are accurate. Ok, then please do a reorg and then a runstats again. 5) Both the databases has the SELF_TUNNING parameter set to AUTOMATIC. [db2inst1@customerEnv ~]$ db2 get db cfg | grep SELF *Self tuning memory * * * * * * * * * *(SELF_TUNING_MEM) = ON [db2inst1@production ~]$ db2 get db cfg | grep SELF *Self tuning memory * * * * * * * * * *(SELF_TUNING_MEM) = ON Hmm, how many instances and how many databases per instance? Anyway, Yonghang suggested to increase the sortheap which is definitely a good idea. You could also try to use optimization guidelines to force a NLJOIN or a HJOIN (depending on which machine you want to try it). -- Helmut K. C. Tessarek DB2 Performance and Development IBM Toronto Lab Thanks Helmut / Yonghang. IŽll try the suggestions tonight and keep you informed. Regards Bruno . Helmut / Yonghang, After applying your suggestions, the running time was: [db2inst1@customerEnv ~]$ time db2 -tf query_customer.sql real * *0m24.898s user * *0m0.021s sys * * 0m0.025s We had a reduction of ~ 1 min, but slow comparing with my production db ( < 1 seg ). Runstats and reog are accurates now and sort heap = 16k. Any ideas? Thanks Bruno Almeida. |
Can you provide more details on the environments?
DPF config? any difference on partition keys? resides on the same or
multiple server? data volume?
#12
| |||
| |||
|
|
On Jul 25, 8:31*am, Bruno Almeida <bruno.alme... (AT) automatos (DOT) com> wrote: On 21 jul, 10:27, Bruno Almeida <bruno.alme... (AT) automatos (DOT) com> wrote: On 20 jul, 01:34, Helmut Tessarek <tessa... (AT) evermeet (DOT) cx> wrote: On 19.07.11 12:18 , Bruno Almeida wrote: 1) Sorry about the expression "client db", I could say Customer DB, in other words, is a database installed on customer environment. Understood. 2) The db2set parameters: Ok, what about the dbm and db parameters? Any significant differences? 3) Yes, the statistics are accurate. Ok, then please do a reorg and then a runstats again. 5) Both the databases has the SELF_TUNNING parameter set to AUTOMATIC. [db2inst1@customerEnv ~]$ db2 get db cfg | grep SELF *Self tuning memory * * * * * * * * * *(SELF_TUNING_MEM) = ON [db2inst1@production ~]$ db2 get db cfg | grep SELF *Self tuning memory * * * * * * * * * *(SELF_TUNING_MEM) = ON Hmm, how many instances and how many databases per instance? Anyway, Yonghang suggested to increase the sortheap which is definitely a good idea. You could also try to use optimization guidelines to force a NLJOINor a HJOIN (depending on which machine you want to try it). -- Helmut K. C. Tessarek DB2 Performance and Development IBM Toronto Lab Thanks Helmut / Yonghang. IŽll try the suggestions tonight and keep you informed. Regards Bruno . Helmut / Yonghang, After applying your suggestions, the running time was: [db2inst1@customerEnv ~]$ time db2 -tf query_customer.sql real * *0m24.898s user * *0m0.021s sys * * 0m0.025s We had a reduction of ~ 1 min, but slow comparing with my production db ( < 1 seg ). Runstats and reog are accurates now and sort heap = 16k. Any ideas? Thanks Bruno Almeida. hi Bruno, Can you provide more details on the environments? DPF config? any difference on partition keys? resides on the same or multiple server? data volume? |
more clue.
btw, I doubt DB2_REDUCED_OPTIMIZATION works here as it's way to kill
the compiling time than running time, and hashjoin is encouraged here
--- but it also make sense you check how much complication time it
costs. run it with db2batch(use -i complete) and the result will show
that in detail.
#13
| |||
| |||
|
|
On Jul 26, 8:33*am, Yonghang Wang <wyh... (AT) gmail (DOT) com> wrote: On Jul 25, 8:31*am, Bruno Almeida <bruno.alme... (AT) automatos (DOT) com> wrote: On 21 jul, 10:27, Bruno Almeida <bruno.alme... (AT) automatos (DOT) com> wrote: On 20 jul, 01:34, Helmut Tessarek <tessa... (AT) evermeet (DOT) cx> wrote: On 19.07.11 12:18 , Bruno Almeida wrote: 1) Sorry about the expression "client db", I could say CustomerDB, in other words, is a database installed on customer environment. Understood. 2) The db2set parameters: Ok, what about the dbm and db parameters? Any significant differences? 3) Yes, the statistics are accurate. Ok, then please do a reorg and then a runstats again. 5) Both the databases has the SELF_TUNNING parameter set to AUTOMATIC. [db2inst1@customerEnv ~]$ db2 get db cfg | grep SELF *Self tuning memory * * * * * * * * * *(SELF_TUNING_MEM) = ON [db2inst1@production ~]$ db2 get db cfg | grep SELF *Self tuning memory * * * * * * * * * *(SELF_TUNING_MEM) = ON Hmm, how many instances and how many databases per instance? Anyway, Yonghang suggested to increase the sortheap which is definitely a good idea. You could also try to use optimization guidelines to force a NLJOIN or a HJOIN (depending on which machine you want to try it). -- Helmut K. C. Tessarek DB2 Performance and Development IBM Toronto Lab Thanks Helmut / Yonghang. IŽll try the suggestions tonight and keep you informed. Regards Bruno . Helmut / Yonghang, After applying your suggestions, the running time was: [db2inst1@customerEnv ~]$ time db2 -tf query_customer.sql real * *0m24.898s user * *0m0.021s sys * * 0m0.025s We had a reduction of ~ 1 min, but slow comparing with my production db ( < 1 seg ). Runstats and reog are accurates now and sort heap = 16k. Any ideas? Thanks Bruno Almeida. hi Bruno, Can you provide more details on the environments? DPF config? any difference on partition keys? resides on the same or multiple server? data volume? plus, config of bufferpool, system memory/storage, etc. more detail, more clue. btw, I doubt DB2_REDUCED_OPTIMIZATION works here as it's way to kill the compiling time than running time, and hashjoin is encouraged here --- but it also make sense you check how much complication time it costs. *run it with db2batch(use -i complete) and the result will show that in detail. |
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.