Help with slow result set

#1

Hi all,

DB2 LUW v9.5 FP 7.

My aplication has query that returns some counts about desktops. It
receives a parameter ( CUSTOMER identification ).
This query is executed fast on my production´s DB, but slowly on my
client DB.
I could not find where is the problem, because the access plan are
equals, except a NLJOIN ( production DB ) and a HJOIN ( client DB )

[db2inst1@ client]$ time db2 -tf query_client.sql
....
real 1m30.964s
user 0m0.021s
sys 0m0.025s

[db2inst1@ production ]$ time db2 -tf query_production.sql
....
real 0m0.279s
user 0m0.008s
sys 0m0.022s

The access plan for each environment are here:
http://tracker.automatos.com/cases/accessPlan.tgz

Any ideas?
Regards,

Bruno Almeida

#2

Hello,

Quote:

My aplication has query that returns some counts about desktops. It
receives a parameter ( CUSTOMER identification ).
This query is executed fast on my production´s DB, but slowly on my
client DB.

Ok, what do you mean by client DB? Are both statements executed locally or is
the client DB a client connecting to a remote server DB?
Are the registry variables the same?
db2set -all

Quote:

I could not find where is the problem, because the access plan are
equals, except a NLJOIN ( production DB ) and a HJOIN ( client DB )

Hmm, I have too less information. I could see that there are more rows in the
production database. But this is assuming that the statistics are accurate.
Are they? There are also a lot of overflow records.

Maybe you should do a reorg and runstats on your tables.

Furthermore the number of average applications is 50 on the client DB, which
means that the bufferpool estimation for the plan could be wrong.

But what puzzles me most is that one query is running with UR (client) and the
other one with CS (production). Which is even more confusing, since UR should
be better for perf because of less potential locks.

The databases seem to be different in size and also with regard to several
parameters. What else is different?

--
Helmut K. C. Tessarek
DB2 Performance and Development
IBM Toronto Lab

#3

On 18 jul, 20:49, Helmut Tessarek <tessa... (AT) evermeet (DOT) cx> wrote:

Quote:

Hello,

My aplication has query that returns some counts about desktops. It
receives a parameter ( CUSTOMER identification ).
This query is executed fast on my production s DB, but slowly on my
client DB.

Ok, what do you mean by client DB? Are both statements executed locally or is
the client DB a client connecting to a remote server DB?
Are the registry variables the same?
db2set -all

I could not find where is the problem, because the access plan are
equals, except a NLJOIN ( production DB ) and a HJOIN ( client DB )

Hmm, I have too less information. I could see that there are more rows in the
production database. But this is assuming that the statistics are accurate.
Are they? There are also a lot of overflow records.

Maybe you should do a reorg and runstats on your tables.

Furthermore the number of average applications is 50 on the client DB, which
means that the bufferpool estimation for the plan could be wrong.

But what puzzles me most is that one query is running with UR (client) and the
other one with CS (production). Which is even more confusing, since UR should
be better for perf because of less potential locks.

The databases seem to be different in size and also with regard to several
parameters. What else is different?

--
Helmut K. C. Tessarek
DB2 Performance and Development
IBM Toronto Lab

Hi Helmut,

The aswers are following.

1) Sorry about the expression "client db", I could say Customer DB, in
other words, is a database installed on customer environment.

2) The db2set parameters:

[db2inst1@ production ~]$ db2set -all
[i] DB2_SKIPINSERTED=YES
[i] DB2_USE_ALTERNATE_PAGE_CLEANING=ON
[i] DB2_EVALUNCOMMITTED=YES
[i] DB2_SNAPSHOT_NOAUTH=ON
[i] DB2_SKIPDELETED=YES
[i] DB2_COMPILER_XML_OVERRIDES=1
[i] DB2COMM=tcpip
[i] DB2_PARALLEL_IO=*
[i] DB2AUTOSTART=YES
[g] DB2SYSTEM=TOMATE9
[g] DB2INSTDEF=db2inst1

[db2inst1@customerEnv ~]$ db2set -all
[i] DB2_SKIPINSERTED=YES
[i] DB2_USE_ALTERNATE_PAGE_CLEANING=ON
[i] DB2_EVALUNCOMMITTED=YES
[i] DB2_SNAPSHOT_NOAUTH=ON
[i] DB2_SKIPDELETED=YES
[i] DB2_COMPILER_XML_OVERRIDES=1
[i] DB2COMM=tcpip
[i] DB2_PARALLEL_IO=*
[i] DB2AUTOSTART=YES
[g] DB2SYSTEM=amaral_NEW
[g] DB2INSTDEF=db2inst1
[g] DB2ADMINSERVER=db2as

3) Yes, the statistics are accurate.
[db2inst1@customerEnv ~]$ db2 "select substr(tabname,1,20) as tabname,
stats_time from syscat.tables where tabname in
('TBL_ASSET_DEPARTMENT','TBL_ASSET_MACHINE_ID','TB L_ASSET_SYSINFO','TBL_ASSET_LAST_TIME','TBL_ASSET_ BACKLOG')
and type = 'T'"

TABNAME STATS_TIME
-------------------- --------------------------
TBL_ASSET_DEPARTMENT 2011-07-15-14.18.15.853717
TBL_ASSET_MACHINE_ID 2011-07-18-15.17.18.479780
TBL_ASSET_SYSINFO 2011-07-15-14.18.16.256496
TBL_ASSET_LAST_TIME 2011-07-15-14.18.16.347605
TBL_ASSET_BACKLOG 2011-07-15-14.18.16.472793

5 record(s) selected.

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

Regards
Bruno Almeida

#4

On Jul 18, 2:23*pm, Bruno Almeida <bruno.alme... (AT) automatos (DOT) com> wrote:

Quote:

Hi all,

DB2 LUW v9.5 FP 7.

My aplication has query that returns some counts about desktops. It
receives a parameter ( CUSTOMER identification ).
This query is executed fast on my production´s DB, but slowly on my
client DB.
I could not find where is the problem, because the access plan are
equals, except a NLJOIN ( production DB ) and a HJOIN ( client DB )

[db2inst1@ client]$ time db2 -tf query_client.sql
...
real * *1m30.964s
user * *0m0.021s
sys * * 0m0.025s

[db2inst1@ production ]$ time db2 -tf query_production.sql
...
real * *0m0.279s
user * *0m0.008s
sys * * 0m0.022s

The access plan for each environment are here:http://tracker.automatos.com/cases/accessPlan.tgz

Any ideas?
Regards,

Bruno Almeida

normally Hash Join is much better than NLJoin if you have good memory
** for sorting **.

try the same sortheap size (16k) on your client db, runstats the
tables, and see whether it helps.

#5

On 19.07.11 22:27 , Yonghang Wang wrote:

Quote:

try the same sortheap size (16k) on your client db, runstats the
tables, and see whether it helps.

Also reorg the tables before doing the runstats.

--
Helmut K. C. Tessarek
DB2 Performance and Development
IBM Toronto Lab

#6

On 19.07.11 12:18 , Bruno Almeida wrote:

Quote:

1) Sorry about the expression "client db", I could say Customer DB, in
other words, is a database installed on customer environment.

Understood.

Quote:

2) The db2set parameters:

Ok, what about the dbm and db parameters? Any significant differences?

Quote:

3) Yes, the statistics are accurate.

Ok, then please do a reorg and then a runstats again.

Quote:

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

#7

On 20 jul, 01:34, Helmut Tessarek <tessa... (AT) evermeet (DOT) cx> wrote:

Quote:

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 agood
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 .

#8

On 21 jul, 10:27, Bruno Almeida <bruno.alme... (AT) automatos (DOT) com> wrote:

Quote:

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 definitelya 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.

#9

How about to set DB2_REDUCED_OPTIMIZATION to 'YES'?
It might suppress use of hash-join.

#10

On 25.07.11 13:06 , Tonkuma wrote:

Quote:

How about to set DB2_REDUCED_OPTIMIZATION to 'YES'?
It might suppress use of hash-join.

Good idea, also using an optimization profile might help to see, if the HJOIN
is indeed the problem.

--
Helmut K. C. Tessarek
DB2 Performance and Development
IBM Toronto Lab