Weird access plan behavior

#11

Hi William,

Quote:

Michel, I've seen LIKE and UPPER to be very slow on UTF8 databases.

Database territory = US
Database code page = 819
Database code set = ISO8859-1

Quote:

You still do a LIKE in the second query, but I'd still be interested
in the results of CPU usage of

select count(*) from ASSET_SW_ID where upper(SW_NAME) like '%ACROBAT%'

Here it is:

3) TBSCAN: (Table Scan)
Cumulative Total Cost: 2693.42
Cumulative CPU Cost: 5.06243e+08
Cumulative I/O Cost: 653
Cumulative Re-Total Cost: 117.402
Cumulative Re-CPU Cost: 4.58865e+08
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 25.1504
Estimated Bufferpool Buffers: 653

Predicates:
----------
2) Sargable Predicate
Comparison Operator: Like
Subquery Input Required: No
Filter Factor: 0.1

Predicate Text:
--------------
(UPPER(Q1.SW_NAME) LIKE '%ACROBAT%')

Input Streams:
-------------
1) From Object ASSET.TBL_ASSET_SW_ID

Estimated number of rows: 175675
Number of columns: 2
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q1.$RID$+Q1.SW_NAME

Output Streams:
--------------
2) To Operator #2

Estimated number of rows:
17567.5
Number of columns: 0
Subquery predicate ID: Not
Applicable

Quote:

versus

select count(*) from ASSET_SW_ID where SW_NAME like '%ACROBAT%'

3) TBSCAN: (Table Scan)
Cumulative Total Cost: 2673.42
Cumulative CPU Cost: 4.28068e+08
Cumulative I/O Cost: 653
Cumulative Re-Total Cost: 97.4006
Cumulative Re-CPU Cost: 3.80689e+08
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 26.8954
Estimated Bufferpool Buffers: 653

Predicates:
----------
2) Sargable Predicate
Comparison Operator: Like
Subquery Input Required: No
Filter Factor: 0.00794754

Predicate Text:
--------------
(Q1.SW_NAME LIKE '%ACROBAT%')

Input Streams:
-------------
1) From Object ASSET.TBL_ASSET_SW_ID

Estimated number of rows: 175675
Number of columns: 2
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q1.$RID$+Q1.SW_NAME

Output Streams:
--------------
2) To Operator #2

Estimated number of rows:
1396.18
Number of columns: 0
Subquery predicate ID: Not
Applicable

Quote:

Plus, take the execution plans of those two and look at the
cardinality... DB2 makes assumptions on the filter factor of functions
that are the same for all functions, and if that applies here then the
filter factor is 0.04 for the predicate "upper() like '%...
%'" (operator 5 on the first explain plan).

It seems to be a bit lower on the good query

...

Any thoughts?

Thanks,

#12

Quote:

As far as I understand, you need the UPPER()? Have you thought about a
generated column?

ALTER TABLE ASSET.TBL_ASSET_SW_ID ADD COLUMN SW_NAME_UPPER_GEN
VARCHAR(100) GENERATED ALWAYS AS UPPER(SW_NAME)

(use whatever appropriate instead of VARCHAR(100))

You shouldn't even have to change the query, because DB2 will
automatically chose the generated column.

Be careful though that IDENTITY and GENERATED columns are a pain when
using db2move, if you don't know how to deal with them.- Hide quoted text -

- Show quoted text -

Hi Willem,

Yeah, we have been discussing internally the creation of this
generated column. I have in fact created this column and studied the
access plan.

The new plan has a HIGHER total cost ( ?? ), but somehow seems to have
better response times.

Also, creating this new column would make me change several parts of
my application to read it. This is, of course, time and developemnt
resouce consuming

.

That's why I was trying to understand what caused the bad access plan.

Thanks,
-M

#13

On 2010-11-18 16:09, Michel Esber wrote:
[...]

Quote:

Also, creating this new column would make me change several parts of
my application to read it. This is, of course, time and developemnt
resouce consuming

.

Normally you should not have to do that. The generated column generates
a check constraint like:

CHECK ( SW_NAME_UPPER_GEN = UPPER(SW_NAME) )

and that is *injected* into the query as in:

SELECT DISTINCT A.SW_ID, B.SW_NAME
FROM ASSET.TBL_ASSET_SW A
INNER JOIN ASSET.TBL_ASSET_SW_ID B
ON (A.SW_ID = B.SW_ID)
INNER JOIN ASSET.TBL_ASSET_MACHINE_ID C
ON (A.MACHINE_ID = C.MACHINE_ID)
WHERE C.CUSTOMER_ID = 'CC0D6761CD31F72EA7FCC686437DDA98'
AND UPPER(B.SW_NAME) LIKE '%ACROBAT%'
--
AND SW_NAME_UPPER_GEN = UPPER(SW_NAME)
--
ORDER BY B.SW_NAME

and rewritten as:

SELECT DISTINCT A.SW_ID, B.SW_NAME
FROM ASSET.TBL_ASSET_SW A
INNER JOIN ASSET.TBL_ASSET_SW_ID B
ON (A.SW_ID = B.SW_ID)
INNER JOIN ASSET.TBL_ASSET_MACHINE_ID C
ON (A.MACHINE_ID = C.MACHINE_ID)
WHERE C.CUSTOMER_ID = 'CC0D6761CD31F72EA7FCC686437DDA98'

AND SW_NAME_UPPER_GEN LIKE '%ACROBAT%'

ORDER BY B.SW_NAME

I agree however that generated columns causes some administrative
overhead, but any day soon we will have index on functions ;-)

/Lennart

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Weird access plan behavior

comp.databases.ibm-db2 comp.databases.ibm-db2

Re: Weird access plan behavior - 11-18-2010 , 07:52 AM

Re: Weird access plan behavior - 11-18-2010 , 09:09 AM

Re: Weird access plan behavior - 11-18-2010 , 10:00 AM