DB2 8.2 Performance drop on Query compared to 7.1

73blazer

We are migrating a customer from Version 7.1 FP3, to Version 8.2 (8.1 FP8).
For the most part, things are faster, but there is one query that is
much much slower, and it is a query that is used all the time.

select ATTR1,ATTR2,ATTR3,ATTR4 from physical.part_list
where S_PART_NUMBER like '%KJS%'

The widlcard before and after seems to be hosing it, but for this
particular piece of the application, this type of query is neccessary.

On Version 7.1, this query takes about 1.5 seconds (for 36 returns out
of 120,000 things in the table)
On Version 8.2, this query takes 13.8 seconds. (A copy of the production
database)
Most other queries are the same or faster.
I've improved my query by using

select ATTR1,ATTR2,ATTR3,ATTR4 from physical.part_list
where posstr(S_PART_NUMBER,'KJS')>0

This query is around 3.5 seconds. Much better, but still slower than
version 7.1 at 1.5 seconds

Is there anything I can do to improve that time to get it back to 1.5
seconds? What happened in DB2 8? I was reading one article that was
saying the optimization engine was changed such that 8 out of 10 queries
will be faster. Is the double wildcard one of the 2 that is slower?

DB2 8.1FP8 (8.2) on AIX 5.2 ML04 with fixes
Optimization value of the default 5

Ken

Nov 12 '05 #1

Serge Rielau

Ken,

Have you compared the explains (best db2exfmt) for the two queries?
This should be the starting point.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #2

Mark A

"73blazer" <yo**@ma.com> wrote in message
news:Lu********************@centurytel.net...

We are migrating a customer from Version 7.1 FP3, to Version 8.2 (8.1
FP8).
For the most part, things are faster, but there is one query that is much
much slower, and it is a query that is used all the time.

select ATTR1,ATTR2,ATTR3,ATTR4 from physical.part_list
where S_PART_NUMBER like '%KJS%'

The widlcard before and after seems to be hosing it, but for this
particular piece of the application, this type of query is neccessary.

On Version 7.1, this query takes about 1.5 seconds (for 36 returns out of
120,000 things in the table)
On Version 8.2, this query takes 13.8 seconds. (A copy of the production
database)
Most other queries are the same or faster.
I've improved my query by using

select ATTR1,ATTR2,ATTR3,ATTR4 from physical.part_list
where posstr(S_PART_NUMBER,'KJS')>0

This query is around 3.5 seconds. Much better, but still slower than
version 7.1 at 1.5 seconds

Is there anything I can do to improve that time to get it back to 1.5
seconds? What happened in DB2 8? I was reading one article that was saying
the optimization engine was changed such that 8 out of 10 queries will be
faster. Is the double wildcard one of the 2 that is slower?

DB2 8.1FP8 (8.2) on AIX 5.2 ML04 with fixes
Optimization value of the default 5

Ken

First, do a reorg of the table and all its indexes.

Then make sure you execute runstats and get distribution stats on key
columns and S_PART_NUMBER if it is not an indexed column.

If that does not help, you should evaluate the bufferpools and make sure the
index is in a similar buffer pool as V7 (with same objects as before). It is
possible that in your old system, the index was more likely to be resident
in the bufferpool and not flushed out by other pages.

Also, check the tablespace attributes (extent size, prefetch, etc) of the
index (assuming that S_PART_NUMBER is indexed).

Nov 12 '05 #3

73blazer

Serge Rielau wrote:

Ken,

Have you compared the explains (best db2exfmt) for the two queries?
This should be the starting point.

Cheers
Serge

In looking at the exlplains for both, the optimized statment is the
same. Bufferpools match, optimization plan is the same, everything looks
ok. I don't see anything there out of wack. Just slower. The cost on 8.2
is 32458 compared to 78334 on 7.1, so shouldn't 7.1 be slower? CPU speed
is also slower on the 7.1 database. (The 8.2 database is a 4-way PWR5
AIX machine, the 7.1 database is a 4-way pwr3 machine, I run all the
statments from db2adm on the repective machine)
Is there anything special to look for in there.

Ken

Nov 12 '05 #4

73blazer

Mark A wrote:

"73blazer" <yo**@ma.com> wrote in message
news:Lu********************@centurytel.net...

We are migrating a customer from Version 7.1 FP3, to Version 8.2 (8.1
FP8).
For the most part, things are faster, but there is one query that is much
much slower, and it is a query that is used all the time.

select ATTR1,ATTR2,ATTR3,ATTR4 from physical.part_list
where S_PART_NUMBER like '%KJS%'

The widlcard before and after seems to be hosing it, but for this
particular piece of the application, this type of query is neccessary.

On Version 7.1, this query takes about 1.5 seconds (for 36 returns out of
120,000 things in the table)
On Version 8.2, this query takes 13.8 seconds. (A copy of the production
database)
Most other queries are the same or faster.
I've improved my query by using

select ATTR1,ATTR2,ATTR3,ATTR4 from physical.part_list
where posstr(S_PART_NUMBER,'KJS')>0

This query is around 3.5 seconds. Much better, but still slower than
version 7.1 at 1.5 seconds

Is there anything I can do to improve that time to get it back to 1.5
seconds? What happened in DB2 8? I was reading one article that was saying
the optimization engine was changed such that 8 out of 10 queries will be
faster. Is the double wildcard one of the 2 that is slower?

DB2 8.1FP8 (8.2) on AIX 5.2 ML04 with fixes
Optimization value of the default 5

Ken

First, do a reorg of the table and all its indexes.

Then make sure you execute runstats and get distribution stats on key
columns and S_PART_NUMBER if it is not an indexed column.

If that does not help, you should evaluate the bufferpools and make sure the
index is in a similar buffer pool as V7 (with same objects as before). It is
possible that in your old system, the index was more likely to be resident
in the bufferpool and not flushed out by other pages.

Also, check the tablespace attributes (extent size, prefetch, etc) of the
index (assuming that S_PART_NUMBER is indexed).

I've done the runstats and reorg. The tablespace attributes are the
same. Bufferpools are exactly the same. indexing is the same.

Ken

Nov 12 '05 #5

Mark A

"73blazer" <yo**@ma.com> wrote in message
news:bu********************@centurytel.net...

I've done the runstats and reorg. The tablespace attributes are the same.
Bufferpools are exactly the same. indexing is the same.

Ken

I will assume you did the reorg first, then the runstats. Reorg and runstats
syntax is different between V7 and V8, so make sure you check the syntax and
make sure you reorg the indexes also (they automatically get reorged in
version 7).

Nov 12 '05 #6

73blazer

Mark A wrote:

"73blazer" <yo**@ma.com> wrote in message
news:bu********************@centurytel.net...

I've done the runstats and reorg. The tablespace attributes are the same.
Bufferpools are exactly the same. indexing is the same.

Ken

I will assume you did the reorg first, then the runstats. Reorg and runstats
syntax is different between V7 and V8, so make sure you check the syntax and
make sure you reorg the indexes also (they automatically get reorged in
version 7).

Thanks for the suggestions. Yes reorg then runstat.
I did the re-org on indexes explicitly. It wasn't done before, I didn't
know it wasn't automatically done in 8.2.
But, it didn't help. All times I give are average of 5 times run, the
avg time went up by 0.1 seconds. Still in the 14 seconds range.

Nov 12 '05 #7

Serge Rielau

73blazer wrote:

Mark A wrote:

"73blazer" <yo**@ma.com> wrote in message
news:bu********************@centurytel.net...

I've done the runstats and reorg. The tablespace attributes are the
same. Bufferpools are exactly the same. indexing is the same.

Ken

I will assume you did the reorg first, then the runstats. Reorg and
runstats syntax is different between V7 and V8, so make sure you check
the syntax and make sure you reorg the indexes also (they
automatically get reorged in version 7).

Thanks for the suggestions. Yes reorg then runstat.
I did the re-org on indexes explicitly. It wasn't done before, I didn't
know it wasn't automatically done in 8.2.
But, it didn't help. All times I give are average of 5 times run, the
avg time went up by 0.1 seconds. Still in the 14 seconds range.

Can you post the two plans?

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #8

73blazer

Serge Rielau wrote:

73blazer wrote:

Mark A wrote:

"73blazer" <yo**@ma.com> wrote in message
news:bu********************@centurytel.net...

I've done the runstats and reorg. The tablespace attributes are the
same. Bufferpools are exactly the same. indexing is the same.

Ken


I will assume you did the reorg first, then the runstats. Reorg and
runstats syntax is different between V7 and V8, so make sure you
check the syntax and make sure you reorg the indexes also (they
automatically get reorged in version 7).

Thanks for the suggestions. Yes reorg then runstat.
I did the re-org on indexes explicitly. It wasn't done before, I
didn't know it wasn't automatically done in 8.2.
But, it didn't help. All times I give are average of 5 times run, the
avg time went up by 0.1 seconds. Still in the 14 seconds range.

Can you post the two plans?

Cheers
Serge

I was watching in nmon, and it seems on my 8.2 instance that 1 CPU is
being pegged out for the duration of the query. Where on the 7.2
machine, that doesn't seem to happen, but it's hard to tell there
because the query comes back very quickly

here are the db2exfmt outputs for the queries:

DB2 Universal Database Version 7.2, 5622-044 (c) Copyright IBM Corp.
1991, 2001
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool

******************** EXPLAIN INSTANCE ********************

DB2_VERSION: 07.02.0
SOURCE_NAME: SQLC2D01
SOURCE_SCHEMA: NULLID
EXPLAIN_TIME: 2005-04-14-19.10.54.089552
EXPLAIN_REQUESTER: PRDTST

Database Context:
----------------
Parallelism: None
CPU Speed: 6.691544e-07
Comm Speed: 0
Buffer Pool size: 200000
Sort Heap size: 647
Database Heap size: 3422
Lock List size: 1467
Maximum Lock List: 6
Average Applications: 50
Locks Available: 9946

Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability

---------------- STATEMENT 1 SECTION 201 ----------------
QUERYNO: 28
QUERYTAG: CLP
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1

Original Statement:
------------------
select S_PART_NUMBER,S_TYPE
from physical.part_list
where S_PART_NUMBER like '%KEN%'
Optimized Statement:
-------------------
SELECT Q1.S_PART_NUMBER AS "S_PART_NUMBER", Q1.S_TYPE AS "S_TYPE"
FROM PHYSICAL.PART_LIST AS Q1
WHERE (Q1.S_PART_NUMBER LIKE '%KEN%')

Access Plan:
-----------
Total Cost: 78834.3
Query Degree: 1
NO TABLE INFORMATION AVAILABLE (ONLY EXPLAIN SNAPSHOT)

DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp.
1991, 2002
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool

******************** EXPLAIN INSTANCE ********************

DB2_VERSION: 08.02.1
SOURCE_NAME: SQLC2E06
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2005-04-15-09.14.16.538703
EXPLAIN_REQUESTER: DB2864

Database Context:
----------------
Parallelism: None
CPU Speed: 6.723442e-07
Comm Speed: 100
Buffer Pool size: 200000
Sort Heap size: 647
Database Heap size: 3422
Lock List size: 1467
Maximum Lock List: 6
Average Applications: 1
Locks Available: 5633

Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability

---------------- STATEMENT 1 SECTION 201 ----------------
QUERYNO: 1
QUERYTAG: CLP
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1

Original Statement:
------------------
select S_PART_NUMBER,S_TYPE
from physical.part_list
where S_PART_NUMBER like '%KEN%'
Optimized Statement:
-------------------
SELECT Q1.S_PART_NUMBER AS "S_PART_NUMBER", Q1.S_TYPE AS "S_TYPE"
FROM PHYSICAL.PART_LIST AS Q1
WHERE (Q1.S_PART_NUMBER LIKE '%KEN%')

Access Plan:
-----------
Total Cost: 28265.9
Query Degree: 1
NO TABLE INFORMATION AVAILABLE (ONLY EXPLAIN SNAPSHOT)

Nov 12 '05 #9

73blazer

Serge Rielau wrote:

73blazer wrote:

Mark A wrote:

"73blazer" <yo**@ma.com> wrote in message
news:bu********************@centurytel.net...

I've done the runstats and reorg. The tablespace attributes are the
same. Bufferpools are exactly the same. indexing is the same.

Ken


I will assume you did the reorg first, then the runstats. Reorg and
runstats syntax is different between V7 and V8, so make sure you
check the syntax and make sure you reorg the indexes also (they
automatically get reorged in version 7).

Thanks for the suggestions. Yes reorg then runstat.
I did the re-org on indexes explicitly. It wasn't done before, I
didn't know it wasn't automatically done in 8.2.
But, it didn't help. All times I give are average of 5 times run, the
avg time went up by 0.1 seconds. Still in the 14 seconds range.

Can you post the two plans?

Cheers
Serge

I am appreciating all the help.
It seems with all the table re-orgs and index re-orgs I've done now (I
didn't realize indexes are no longer done automatically in 8, so I did
them now) that my posstr method is down to .35 seconds. The %STRING%
method is still 11.7 seconds.
0.35, I'm very happy. But still wondering why the like '%STRING%' is
drastically slower on 8.

Ken

Nov 12 '05 #10

Serge Rielau

? Well, how can we state that the optimizer plan is the same if it
wasn't dumped?

Let's start from scratch:
1. ensure you have the explain tables:
cd sqllib/misc
db2 connect to <db>
db2 -tvf EXPLAIN.DDL
now:
db2 explain plan for <sqlstatement>
db2exfmt -d <db> -o <filename>.exfmt
<accept all defaults>

You should see a graph :-)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #11

Mark A

"73blazer" <yo**@ma.com> wrote in message
news:09********************@centurytel.net...

I am appreciating all the help.
It seems with all the table re-orgs and index re-orgs I've done now (I
didn't realize indexes are no longer done automatically in 8, so I did
them now) that my posstr method is down to .35 seconds. The %STRING%
method is still 11.7 seconds.
0.35, I'm very happy. But still wondering why the like '%STRING%' is
drastically slower on 8.

Ken

When you reorg the table, do you have a clustered index defined, or do you
specify an index name in the reorg command?

Nov 12 '05 #12