By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,237 Members | 1,228 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,237 IT Pros & Developers. It's quick & easy.

DB2 CLI Performance Probelm

P: n/a
So a friend an I inadvertanly came across this problem while diagnosing
another:

I have a table:
create table test.test (g_id int not null, g_width int, g_height int);
create index testtest on test.test (g_id);

I have about 5000 rows in this table

A query run through CLI C program using the prep/exec method containing
an IN clause with 7 (or more) things in the IN clause produced much much
slower times than with 6 items, or than using just execute with 6 or 7
items.

Observe
======
$ ./tbread wwwdb

Connecting to wwwdb...
Connected to wwwdb.

PERFORMING A BASIC SELECT USING 6 HARD-CODED ELEMENTS IN 'IN()' CLAUSE:
SELECT g_width FROM test.test WHERE g_id IN (1,2,3,4,5,6);

QueryTime= 0.008340 microseconds.
QueryTime= 0.000692 microseconds.
QueryTime= 0.000642 microseconds.

PERFORMING A BASIC SELECT USING 7 HARD-CODED ELEMENTS IN 'IN()' CLAUSE:
SELECT g_width FROM test.test WHERE g_id IN (1,2,3,4,5,6,7);

QueryTime= 0.003725 microseconds.
QueryTime= 0.000657 microseconds.
QueryTime= 0.000641 microseconds.

PERFORMING A SELECT PREP/EXEC USING 6 PARAMETER MARKERS IN 'IN()' CLAUSE
SELECT g_width FROM test.test WHERE g_id IN (?,?,?,?,?,?);
Param Array: {1 2 3 4 5 6}

QueryTime= 0.004480 microseconds.
QueryTime= 0.001044 microseconds.
QueryTime= 0.000718 microseconds.

PERFORMING A SELECT PREP/EXEC USING 7 PARAMETER MARKERS IN 'IN()' CLAUSE
SELECT g_width FROM test.test WHERE g_id IN (?,?,?,?,?,?,?);
Param Array: {1 2 3 4 5 6 7}

QueryTime= 0.022552 microseconds.
QueryTime= 0.019068 microseconds.
QueryTime= 0.018980 microseconds.
=========

Never mind the word microseconds there, it's supposed to be seconds.

Anyhow...
So I paired it down to this:
These tables have a cron job to have runstats/reorg run against them on
a regular basis.
The runstats command I usally use is:

runstats on table test.test with distribution and detailed indexes all

which is the cause of the problem.
If I just run (before any other runstats are run)

runstats on table test.test

I don't see the performance problem.

Also, once you run a runstats with distribution and detailed indexes
all, you get screwed. THe performance problem sticks. Only way to
correct it is to drop the index entierly and re-create it. Then run a
regular runstats.

BTW, this is DB2 8.1 FP13 on AIX 5.3ml05+, w/ a 64 bit instance.
I also tried with 32 bit instance, same problem.
Any insight why that would be causing a problem, would that be expected?
I've used that command because an applicaton I support had it printed in
their documentation as the recomended DB2 maintance command.
Is there any recommended runstats command
Anybody got any easy way to re-create all my indexes?

Thanks any help!

Ken
Oct 13 '06 #1
Share this Question
Share on Google+
24 Replies


P: n/a
"73blazer" <73******@yoyoma.comwrote in message
news:0J******************************@centurytel.n et...
So a friend an I inadvertanly came across this problem while diagnosing
another:

I have a table:
create table test.test (g_id int not null, g_width int, g_height int);
create index testtest on test.test (g_id);

I have about 5000 rows in this table

A query run through CLI C program using the prep/exec method containing an
IN clause with 7 (or more) things in the IN clause produced much much
slower times than with 6 items, or than using just execute with 6 or 7
items.
If you want DB2 to ignore the statistics and use the default statistics, you
can alter the table to VOLATILE. This will strongly encourage DB2 to use any
available index. I would not do this in all cases, especially if you have
queries that will actually run faster with a table scan.
Oct 13 '06 #2

P: n/a
73blazer wrote:
So a friend an I inadvertanly came across this problem while diagnosing
another:

I have a table:
create table test.test (g_id int not null, g_width int, g_height int);
create index testtest on test.test (g_id);

I have about 5000 rows in this table

A query run through CLI C program using the prep/exec method containing
an IN clause with 7 (or more) things in the IN clause produced much much
slower times than with 6 items, or than using just execute with 6 or 7
items.

Observe
======
$ ./tbread wwwdb

Connecting to wwwdb...
Connected to wwwdb.

PERFORMING A BASIC SELECT USING 6 HARD-CODED ELEMENTS IN 'IN()' CLAUSE:
SELECT g_width FROM test.test WHERE g_id IN (1,2,3,4,5,6);

QueryTime= 0.008340 microseconds.
QueryTime= 0.000692 microseconds.
QueryTime= 0.000642 microseconds.

PERFORMING A BASIC SELECT USING 7 HARD-CODED ELEMENTS IN 'IN()' CLAUSE:
SELECT g_width FROM test.test WHERE g_id IN (1,2,3,4,5,6,7);

QueryTime= 0.003725 microseconds.
QueryTime= 0.000657 microseconds.
QueryTime= 0.000641 microseconds.

PERFORMING A SELECT PREP/EXEC USING 6 PARAMETER MARKERS IN 'IN()' CLAUSE
SELECT g_width FROM test.test WHERE g_id IN (?,?,?,?,?,?);
Param Array: {1 2 3 4 5 6}

QueryTime= 0.004480 microseconds.
QueryTime= 0.001044 microseconds.
QueryTime= 0.000718 microseconds.

PERFORMING A SELECT PREP/EXEC USING 7 PARAMETER MARKERS IN 'IN()' CLAUSE
SELECT g_width FROM test.test WHERE g_id IN (?,?,?,?,?,?,?);
Param Array: {1 2 3 4 5 6 7}

QueryTime= 0.022552 microseconds.
QueryTime= 0.019068 microseconds.
QueryTime= 0.018980 microseconds.
=========

Never mind the word microseconds there, it's supposed to be seconds.

Anyhow...
So I paired it down to this:
These tables have a cron job to have runstats/reorg run against them on
a regular basis.
The runstats command I usally use is:

runstats on table test.test with distribution and detailed indexes all

which is the cause of the problem.
If I just run (before any other runstats are run)

runstats on table test.test

I don't see the performance problem.

Also, once you run a runstats with distribution and detailed indexes
all, you get screwed. THe performance problem sticks. Only way to
correct it is to drop the index entierly and re-create it. Then run a
regular runstats.

BTW, this is DB2 8.1 FP13 on AIX 5.3ml05+, w/ a 64 bit instance.
I also tried with 32 bit instance, same problem.
Any insight why that would be causing a problem, would that be expected?
I've used that command because an applicaton I support had it printed in
their documentation as the recomended DB2 maintance command.
Is there any recommended runstats command
Anybody got any easy way to re-create all my indexes?

Thanks any help!

Ken
I paired it down a bit furthur,
It's the "AND INDEXES ALL" that does it.
WITH DISTRIBUTION and DETAILED keywords don't matter.

Oct 13 '06 #3

P: n/a
Mark,

I'm the co-discoverer of this problem on Ken's system.

Thanks for the suggestion, VOLATILE may or may not work here, I'll
let Ken determine that, but it sounds like a work-around, not a true
diagnosis & fix.

My main concern here is that the stats are up-to-date and it seems
like the DB2 Optimizer is choosing a bad plan. If we have to resort to
VOLATILE to 'fool' the Optimizer, then I'd say that is definitely the
case.

My second concern is that the problem only appears if we do a
separate prep/exec of the statement... direct execution runs fine. So
why would the Optimizer be selecting a bad plan for the exact same
statement only when prep/execute is used?

Serge, any input?

Thanks.

Mark A wrote:
"73blazer" <73******@yoyoma.comwrote in message
news:0J******************************@centurytel.n et...
So a friend an I inadvertanly came across this problem while diagnosing
another:

I have a table:
create table test.test (g_id int not null, g_width int, g_height int);
create index testtest on test.test (g_id);

I have about 5000 rows in this table

A query run through CLI C program using the prep/exec method containing an
IN clause with 7 (or more) things in the IN clause produced much much
slower times than with 6 items, or than using just execute with 6 or 7
items.

If you want DB2 to ignore the statistics and use the default statistics, you
can alter the table to VOLATILE. This will strongly encourage DB2 to use any
available index. I would not do this in all cases, especially if you have
queries that will actually run faster with a table scan.
Oct 13 '06 #4

P: n/a

Larry Menard wrote:
Mark,

I'm the co-discoverer of this problem on Ken's system.

Thanks for the suggestion, VOLATILE may or may not work here, I'll
let Ken determine that, but it sounds like a work-around, not a true
diagnosis & fix.

My main concern here is that the stats are up-to-date and it seems
like the DB2 Optimizer is choosing a bad plan. If we have to resort to
VOLATILE to 'fool' the Optimizer, then I'd say that is definitely the
case.

My second concern is that the problem only appears if we do a
separate prep/exec of the statement... direct execution runs fine. So
why would the Optimizer be selecting a bad plan for the exact same
statement only when prep/execute is used?

Serge, any input?
Could you check your bind options? Perhaps you bound the program to the
database with certain bind options that are causing this behaviour
>
Thanks.

Mark A wrote:
"73blazer" <73******@yoyoma.comwrote in message
news:0J******************************@centurytel.n et...
So a friend an I inadvertanly came across this problem while diagnosing
another:
>
I have a table:
create table test.test (g_id int not null, g_width int, g_height int);
create index testtest on test.test (g_id);
>
I have about 5000 rows in this table
>
A query run through CLI C program using the prep/exec method containing an
IN clause with 7 (or more) things in the IN clause produced much much
slower times than with 6 items, or than using just execute with 6 or 7
items.
>
If you want DB2 to ignore the statistics and use the default statistics, you
can alter the table to VOLATILE. This will strongly encourage DB2 to use any
available index. I would not do this in all cases, especially if you have
queries that will actually run faster with a table scan.
Oct 13 '06 #5

P: n/a
fy***@hotmail.com wrote:
Larry Menard wrote:
>Mark,

I'm the co-discoverer of this problem on Ken's system.

Thanks for the suggestion, VOLATILE may or may not work here, I'll
let Ken determine that, but it sounds like a work-around, not a true
diagnosis & fix.

My main concern here is that the stats are up-to-date and it seems
like the DB2 Optimizer is choosing a bad plan. If we have to resort to
VOLATILE to 'fool' the Optimizer, then I'd say that is definitely the
case.

My second concern is that the problem only appears if we do a
separate prep/exec of the statement... direct execution runs fine. So
why would the Optimizer be selecting a bad plan for the exact same
statement only when prep/execute is used?

Serge, any input?

Could you check your bind options? Perhaps you bound the program to the
database with certain bind options that are causing this behaviour
> Thanks.
My thinking as well. Especially look out for REOPT(ONCE/ALWAYS) and the
optimization level. Also the defaults for ambiguous cursor are different
(=always use READ ONLY)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Oct 13 '06 #6

P: n/a
Serge Rielau wrote:
fy***@hotmail.com wrote:
>Larry Menard wrote:
>>Mark,

I'm the co-discoverer of this problem on Ken's system.

Thanks for the suggestion, VOLATILE may or may not work here, I'll
let Ken determine that, but it sounds like a work-around, not a true
diagnosis & fix.

My main concern here is that the stats are up-to-date and it seems
like the DB2 Optimizer is choosing a bad plan. If we have to resort to
VOLATILE to 'fool' the Optimizer, then I'd say that is definitely the
case.

My second concern is that the problem only appears if we do a
separate prep/exec of the statement... direct execution runs fine. So
why would the Optimizer be selecting a bad plan for the exact same
statement only when prep/execute is used?

Serge, any input?


Could you check your bind options? Perhaps you bound the program to the
database with certain bind options that are causing this behaviour
>> Thanks.

My thinking as well. Especially look out for REOPT(ONCE/ALWAYS) and the
optimization level. Also the defaults for ambiguous cursor are different
(=always use READ ONLY)

Cheers
Serge
Um...ok...I'm a bit out of my league here, how do I check my bind options?
I used the sample program "bldapp" to build it, I could only find the
following statments contained in the directory where I built it:
db2 prep $1.sqc bindfile
db2 prep $1.sqC bindfile
db2 prep $1.sqb bindfile target mfcob CALL_RESOLUTION DEFERRED
db2 bind $1.bnd

I initially noticed the problem using a php application which was using
the ibm_db2 driver.
Oct 13 '06 #7

P: n/a
Serge Rielau wrote:
fy***@hotmail.com wrote:
>Larry Menard wrote:
>>Mark,

I'm the co-discoverer of this problem on Ken's system.

Thanks for the suggestion, VOLATILE may or may not work here, I'll
let Ken determine that, but it sounds like a work-around, not a true
diagnosis & fix.

My main concern here is that the stats are up-to-date and it seems
like the DB2 Optimizer is choosing a bad plan. If we have to resort to
VOLATILE to 'fool' the Optimizer, then I'd say that is definitely the
case.

My second concern is that the problem only appears if we do a
separate prep/exec of the statement... direct execution runs fine. So
why would the Optimizer be selecting a bad plan for the exact same
statement only when prep/execute is used?

Serge, any input?


Could you check your bind options? Perhaps you bound the program to the
database with certain bind options that are causing this behaviour
>> Thanks.

My thinking as well. Especially look out for REOPT(ONCE/ALWAYS) and the
optimization level. Also the defaults for ambiguous cursor are different
(=always use READ ONLY)

Cheers
Serge
OPtimization level is 5
I have tried them all though, 0-9, times change slightly, but the
variance with 7 parameters vs 6 is still very present.
Oct 13 '06 #8

P: n/a
I've found something else I think is odd...

On Ken's server the stats don't look correct to me. COUNT(*) says
there are 5693 rows, but COLCARD says there are 5924.

I exported an IXF copy of his table and imported in onto my system
(Windows XP) and the two metrics agree... COUNT(*) and COLCARD both say
5693.

Is is normal that the two metrics don't match on his system?

73blazer wrote:
Serge Rielau wrote:
fy***@hotmail.com wrote:
Larry Menard wrote:

Mark,

I'm the co-discoverer of this problem on Ken's system.

Thanks for the suggestion, VOLATILE may or may not work here, I'll
let Ken determine that, but it sounds like a work-around, not a true
diagnosis & fix.

My main concern here is that the stats are up-to-date and it seems
like the DB2 Optimizer is choosing a bad plan. If we have to resort to
VOLATILE to 'fool' the Optimizer, then I'd say that is definitely the
case.

My second concern is that the problem only appears if we do a
separate prep/exec of the statement... direct execution runs fine. So
why would the Optimizer be selecting a bad plan for the exact same
statement only when prep/execute is used?

Serge, any input?
Could you check your bind options? Perhaps you bound the program to the
database with certain bind options that are causing this behaviour

Thanks.
My thinking as well. Especially look out for REOPT(ONCE/ALWAYS) and the
optimization level. Also the defaults for ambiguous cursor are different
(=always use READ ONLY)

Cheers
Serge

OPtimization level is 5
I have tried them all though, 0-9, times change slightly, but the
variance with 7 parameters vs 6 is still very present.
Oct 14 '06 #9

P: n/a
Sorry, ignore the stats problem, my bad. I was comparing two different
tables. His stats do seem OK.

This sample app was shamelessly stolen from the
'sqllib/samples/cli/tbread.c' sample... no funky prep or bind options,
no isolation level change, no optimization level change.

I added FOR READ ONLY to the queries, but still see a significant
degradation between running a 7-item IN() clause versus a 6-item IN()
clause when run using separate PREP and EXECUTE on his server... it
goes from .3 seconds to almost .5 seconds.

Connecting to wwwdb...
Connected to wwwdb.

PERFORMING A BASIC SELECT USING 6 HARD-CODED ELEMENTS IN 'IN()' CLAUSE:

QueryTime= 1979.192048 microseconds.
QueryTime= 305.707848 microseconds.
QueryTime= 317.961082 microseconds.

PERFORMING A BASIC SELECT USING 7 HARD-CODED ELEMENTS IN 'IN()' CLAUSE:

QueryTime= 1959.370535 microseconds.
QueryTime= 309.060230 microseconds.
QueryTime= 306.061804 microseconds.

PERFORMING A SELECT PREP/EXEC USING 6 PARAMETER MARKERS IN 'IN()'
CLAUSE

QueryTime= 1483.087934 microseconds.
QueryTime= 306.815531 microseconds.
QueryTime= 306.897385 microseconds.

PERFORMING A SELECT PREP/EXEC USING 7 PARAMETER MARKERS IN 'IN()'
CLAUSE

QueryTime= 1594.755758 microseconds.
QueryTime= 486.443389 microseconds.
QueryTime= 484.352062 microseconds.

Still appreciate any other ideas...

Thanks, folks.

Larry Menard wrote:
I've found something else I think is odd...

On Ken's server the stats don't look correct to me. COUNT(*) says
there are 5693 rows, but COLCARD says there are 5924.

I exported an IXF copy of his table and imported in onto my system
(Windows XP) and the two metrics agree... COUNT(*) and COLCARD both say
5693.

Is is normal that the two metrics don't match on his system?

73blazer wrote:
Serge Rielau wrote:
fy***@hotmail.com wrote:
>
>Larry Menard wrote:
>>
>>Mark,
>>>
>> I'm the co-discoverer of this problem on Ken's system.
>>>
>> Thanks for the suggestion, VOLATILE may or may not work here, I'll
>>let Ken determine that, but it sounds like a work-around, not a true
>>diagnosis & fix.
>>>
>> My main concern here is that the stats are up-to-date and it seems
>>like the DB2 Optimizer is choosing a bad plan. If we have to resort to
>>VOLATILE to 'fool' the Optimizer, then I'd say that is definitely the
>>case.
>>>
>> My second concern is that the problem only appears if we do a
>>separate prep/exec of the statement... direct execution runs fine. So
>>why would the Optimizer be selecting a bad plan for the exact same
>>statement only when prep/execute is used?
>>>
>> Serge, any input?
>>
>>
>Could you check your bind options? Perhaps you bound the program to the
>database with certain bind options that are causing this behaviour
>>
>> Thanks.
>
My thinking as well. Especially look out for REOPT(ONCE/ALWAYS) and the
optimization level. Also the defaults for ambiguous cursor are different
(=always use READ ONLY)
>
Cheers
Serge
OPtimization level is 5
I have tried them all though, 0-9, times change slightly, but the
variance with 7 parameters vs 6 is still very present.
Oct 14 '06 #10

P: n/a
More info... I added another test... prep/execute with hard-coded
values (no parameter markers).

The two new cases run fast. So the problem is specifically with the
use of parameter markers... it becomes about 60% degraded when we use a
7th parameter marker.

PERFORMING A BASIC SELECT USING 6 HARD-CODED ELEMENTS IN 'IN()' CLAUSE:

QueryTime= 331.731268 microseconds.
QueryTime= 305.966540 microseconds.
QueryTime= 309.388204 microseconds.

PERFORMING A BASIC SELECT USING 7 HARD-CODED ELEMENTS IN 'IN()' CLAUSE:

QueryTime= 306.828102 microseconds.
QueryTime= 307.236814 microseconds.
QueryTime= 307.346604 microseconds.

PERFORMING A SELECT PREP/EXEC USING 6 HARD-CODED ELEMENTS IN 'IN()'
CLAUSE

QueryTime= 305.131239 microseconds.
QueryTime= 306.647074 microseconds.
QueryTime= 305.932179 microseconds.

PERFORMING A SELECT PREP/EXEC USING 7 HARD-CODED ELEMENTS IN 'IN()'
CLAUSE

QueryTime= 305.685499 microseconds.
QueryTime= 308.008141 microseconds.
QueryTime= 304.038362 microseconds.

PERFORMING A SELECT PREP/EXEC USING 6 PARAMETER MARKERS IN 'IN()'
CLAUSE

QueryTime= 307.512268 microseconds.
QueryTime= 306.889004 microseconds.
QueryTime= 306.440623 microseconds.

PERFORMING A SELECT PREP/EXEC USING 7 PARAMETER MARKERS IN 'IN()'
CLAUSE

QueryTime= 500.895226 microseconds.
QueryTime= 501.699797 microseconds.
QueryTime= 497.745943 microseconds.

Larry Menard wrote:
Sorry, ignore the stats problem, my bad. I was comparing two different
tables. His stats do seem OK.

This sample app was shamelessly stolen from the
'sqllib/samples/cli/tbread.c' sample... no funky prep or bind options,
no isolation level change, no optimization level change.

I added FOR READ ONLY to the queries, but still see a significant
degradation between running a 7-item IN() clause versus a 6-item IN()
clause when run using separate PREP and EXECUTE on his server... it
goes from .3 seconds to almost .5 seconds.

Connecting to wwwdb...
Connected to wwwdb.

PERFORMING A BASIC SELECT USING 6 HARD-CODED ELEMENTS IN 'IN()' CLAUSE:

QueryTime= 1979.192048 microseconds.
QueryTime= 305.707848 microseconds.
QueryTime= 317.961082 microseconds.

PERFORMING A BASIC SELECT USING 7 HARD-CODED ELEMENTS IN 'IN()' CLAUSE:

QueryTime= 1959.370535 microseconds.
QueryTime= 309.060230 microseconds.
QueryTime= 306.061804 microseconds.

PERFORMING A SELECT PREP/EXEC USING 6 PARAMETER MARKERS IN 'IN()'
CLAUSE

QueryTime= 1483.087934 microseconds.
QueryTime= 306.815531 microseconds.
QueryTime= 306.897385 microseconds.

PERFORMING A SELECT PREP/EXEC USING 7 PARAMETER MARKERS IN 'IN()'
CLAUSE

QueryTime= 1594.755758 microseconds.
QueryTime= 486.443389 microseconds.
QueryTime= 484.352062 microseconds.

Still appreciate any other ideas...

Thanks, folks.

Larry Menard wrote:
I've found something else I think is odd...

On Ken's server the stats don't look correct to me. COUNT(*) says
there are 5693 rows, but COLCARD says there are 5924.

I exported an IXF copy of his table and imported in onto my system
(Windows XP) and the two metrics agree... COUNT(*) and COLCARD both say
5693.

Is is normal that the two metrics don't match on his system?

73blazer wrote:
Serge Rielau wrote:
fy***@hotmail.com wrote:

Larry Menard wrote:
>
>Mark,
>>
> I'm the co-discoverer of this problem on Ken's system.
>>
> Thanks for the suggestion, VOLATILE may or may not work here, I'll
>let Ken determine that, but it sounds like a work-around, not a true
>diagnosis & fix.
>>
> My main concern here is that the stats are up-to-date and it seems
>like the DB2 Optimizer is choosing a bad plan. If we have to resort to
>VOLATILE to 'fool' the Optimizer, then I'd say that is definitely the
>case.
>>
> My second concern is that the problem only appears if we do a
>separate prep/exec of the statement... direct execution runs fine. So
>why would the Optimizer be selecting a bad plan for the exact same
>statement only when prep/execute is used?
>>
> Serge, any input?
>
>
Could you check your bind options? Perhaps you bound the program to the
database with certain bind options that are causing this behaviour
>
> Thanks.

My thinking as well. Especially look out for REOPT(ONCE/ALWAYS) and the
optimization level. Also the defaults for ambiguous cursor are different
(=always use READ ONLY)

Cheers
Serge
>
OPtimization level is 5
I have tried them all though, 0-9, times change slightly, but the
variance with 7 parameters vs 6 is still very present.
Oct 14 '06 #11

P: n/a
Larry,

Did you compare the plans?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Oct 14 '06 #12

P: n/a
Hi, Serge

I didn't suspect a plan difference because the statement is basically
the same... I didn't think it would make a difference whether
hard-coded values or parameter markers are used.

On my own (smaller) database I've confirmed that the plans are
identical, but I don't see the problem on my own server... only on his.
I've sent him the steps to dump the plans on his server.

Thanks.

Serge Rielau wrote:
Larry,

Did you compare the plans?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Oct 15 '06 #13

P: n/a
Serge Rielau wrote:
Larry,

Did you compare the plans?

Cheers
Serge
Here's the links to the 2 different plans, output from db2exfmt:
I've never deciphered these before, so not sure what to look for in here.

7 Hard coded values:
http://www.snyderworld.org/ftp/explain_7hc.txt
7 Parameter markers:
http://www.snyderworld.org/ftp/explain_7pm.txt
Oct 18 '06 #14

P: n/a
Original Statement:
------------------
SELECT g_width
FROM test.test
WHERE g_id IN (?,?,?,?,?,?,?)
Optimized Statement:
-------------------
SELECT Q4.G_WIDTH AS "G_WIDTH"
FROM
(SELECT DISTINCT Q2.$C0
FROM
(SELECT $INTERNAL_FUNC$()
FROM (VALUES 1, 2, 3, 4, 5, 6, 7) AS Q1) AS Q2) AS Q3, TEST.TEST
AS Q4
WHERE (Q4.G_ID = Q3.$C0)

How did the parameter markers turn into (1, 2, 3, 4, ...)?
Did you use REOPT(once) or REOPT(always)?

The plan makes sense for parameter markers because because the SORT will
remove duplicate values.

Cheers
serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Oct 18 '06 #15

P: n/a
Serge Rielau wrote:
Original Statement:
------------------
SELECT g_width
FROM test.test
WHERE g_id IN (?,?,?,?,?,?,?)
Optimized Statement:
-------------------
SELECT Q4.G_WIDTH AS "G_WIDTH"
FROM
(SELECT DISTINCT Q2.$C0
FROM
(SELECT $INTERNAL_FUNC$()
FROM (VALUES 1, 2, 3, 4, 5, 6, 7) AS Q1) AS Q2) AS Q3, TEST.TEST
AS Q4
WHERE (Q4.G_ID = Q3.$C0)

How did the parameter markers turn into (1, 2, 3, 4, ...)?
Did you use REOPT(once) or REOPT(always)?

The plan makes sense for parameter markers because because the SORT will
remove duplicate values.

Cheers
serge
Where do I check for these REPOT options? I did not use them as far as I
know?
Oct 18 '06 #16

P: n/a
In article <Gd******************************@centurytel.net >,
yo**@ma.com says...
Serge Rielau wrote:
Original Statement:
------------------
SELECT g_width
FROM test.test
WHERE g_id IN (?,?,?,?,?,?,?)
Optimized Statement:
-------------------
SELECT Q4.G_WIDTH AS "G_WIDTH"
FROM
(SELECT DISTINCT Q2.$C0
FROM
(SELECT $INTERNAL_FUNC$()
FROM (VALUES 1, 2, 3, 4, 5, 6, 7) AS Q1) AS Q2) AS Q3, TEST.TEST
AS Q4
WHERE (Q4.G_ID = Q3.$C0)

How did the parameter markers turn into (1, 2, 3, 4, ...)?
Did you use REOPT(once) or REOPT(always)?

The plan makes sense for parameter markers because because the SORT will
remove duplicate values.

Cheers
serge

Where do I check for these REPOT options? I did not use them as far as I
know?
You're using the default reopt option, you can find it in the PRECOMPILE
PROGRAM (PREP) docs at http://tinyurl.com/uglwt.
It states that the REOPT NONE option is the default value, you might
want to try the REOPT ONCE or REOPT ALWAYS options.
You need to change the embprep.bat file, you need to add this statement
to the line after the :cob label
'db2 prep %1.sqb bindfile target mfcob CALL_RESOLUTION DEFERRED'

Hope this helps.

Oct 18 '06 #17

P: n/a
The commands I sent Ken were:

db2 connect to gallery2 user g2user using g2pwd
db2 explain plan for <SELECT stmt with 7 hard-coded values>
db2 connect reset
db2exfmt -d gallery2 -e g2user -g TIC -u g2user g2pwd -o
explain.out.7hc (just press enter to take all other defaults)
db2 connect to gallery2 user g2user using g2pwd
db2 explain plan for <SELECT stmt with 7 parameter markers>
db2 connect reset
db2exfmt -d gallery2 -e g2user -g TIC -u g2user g2pwd -o
explain.out.7pm (just press enter to take all other defaults)

I didn't specify any REOPT when I did this on my system, and both plans
came out identical.

Gert: I don't know how COBOL build scripts got into this conversation.
:-)

Serge:
How did the parameter markers turn into (1, 2, 3, 4, ...)?
Why are you asking us? We're hoping you can tell us. :-)
The plan makes sense for parameter markers because because the SORT will
remove duplicate values.
What 'sort'? Who asked for a sort?
Gert van der Kooij wrote:
In article <Gd******************************@centurytel.net >,
yo**@ma.com says...
Serge Rielau wrote:
Original Statement:
------------------
SELECT g_width
FROM test.test
WHERE g_id IN (?,?,?,?,?,?,?)
>
>
Optimized Statement:
-------------------
SELECT Q4.G_WIDTH AS "G_WIDTH"
FROM
(SELECT DISTINCT Q2.$C0
FROM
(SELECT $INTERNAL_FUNC$()
FROM (VALUES 1, 2, 3, 4, 5, 6, 7) AS Q1) AS Q2) AS Q3, TEST.TEST
AS Q4
WHERE (Q4.G_ID = Q3.$C0)
>
How did the parameter markers turn into (1, 2, 3, 4, ...)?
Did you use REOPT(once) or REOPT(always)?
>
The plan makes sense for parameter markers because because the SORT will
remove duplicate values.
>
Cheers
serge
Where do I check for these REPOT options? I did not use them as far as I
know?

You're using the default reopt option, you can find it in the PRECOMPILE
PROGRAM (PREP) docs at http://tinyurl.com/uglwt.
It states that the REOPT NONE option is the default value, you might
want to try the REOPT ONCE or REOPT ALWAYS options.
You need to change the embprep.bat file, you need to add this statement
to the line after the :cob label
'db2 prep %1.sqb bindfile target mfcob CALL_RESOLUTION DEFERRED'

Hope this helps.
Oct 18 '06 #18

P: n/a
In article <11*********************@h48g2000cwc.googlegroups. com>,
la**********@gmail.com says...
The commands I sent Ken were:

db2 connect to gallery2 user g2user using g2pwd
db2 explain plan for <SELECT stmt with 7 hard-coded values>
db2 connect reset
db2exfmt -d gallery2 -e g2user -g TIC -u g2user g2pwd -o
explain.out.7hc (just press enter to take all other defaults)
db2 connect to gallery2 user g2user using g2pwd
db2 explain plan for <SELECT stmt with 7 parameter markers>
db2 connect reset
db2exfmt -d gallery2 -e g2user -g TIC -u g2user g2pwd -o
explain.out.7pm (just press enter to take all other defaults)

I didn't specify any REOPT when I did this on my system, and both plans
came out identical.

Gert: I don't know how COBOL build scripts got into this conversation.
:-)
Hi Larry,

This is what Ken posted in one of his previous replies:
>
Um...ok...I'm a bit out of my league here, how do I check my bind options?
I used the sample program "bldapp" to build it, I could only find the
following statments contained in the directory where I built it:
db2 prep $1.sqc bindfile
db2 prep $1.sqC bindfile
db2 prep $1.sqb bindfile target mfcob CALL_RESOLUTION DEFERRED
db2 bind $1.bnd
Because of his 'target mfcob' specified it should be a MicroFocus COBOL
routine. Otherwise I can't understand why Ken built his programs from
this directory.
Oct 18 '06 #19

P: n/a
Hi, Gert.

That must have been for a different problem... I don't remember it from
this problem. With this problem we never touched COBOL and never used
bindfiles... we discovered the problem in a PHP app and then manually
determined it was actually in CLI, so we created a test CLI program
(based on 'tbread.C') to repro it.
Gert van der Kooij wrote:
In article <11*********************@h48g2000cwc.googlegroups. com>,
la**********@gmail.com says...
The commands I sent Ken were:

db2 connect to gallery2 user g2user using g2pwd
db2 explain plan for <SELECT stmt with 7 hard-coded values>
db2 connect reset
db2exfmt -d gallery2 -e g2user -g TIC -u g2user g2pwd -o
explain.out.7hc (just press enter to take all other defaults)
db2 connect to gallery2 user g2user using g2pwd
db2 explain plan for <SELECT stmt with 7 parameter markers>
db2 connect reset
db2exfmt -d gallery2 -e g2user -g TIC -u g2user g2pwd -o
explain.out.7pm (just press enter to take all other defaults)

I didn't specify any REOPT when I did this on my system, and both plans
came out identical.

Gert: I don't know how COBOL build scripts got into this conversation.
:-)

Hi Larry,

This is what Ken posted in one of his previous replies:

Um...ok...I'm a bit out of my league here, how do I check my bind options?
I used the sample program "bldapp" to build it, I could only find the
following statments contained in the directory where I built it:
db2 prep $1.sqc bindfile
db2 prep $1.sqC bindfile
db2 prep $1.sqb bindfile target mfcob CALL_RESOLUTION DEFERRED
db2 bind $1.bnd

Because of his 'target mfcob' specified it should be a MicroFocus COBOL
routine. Otherwise I can't understand why Ken built his programs from
this directory.
Oct 19 '06 #20

P: n/a
And as for REOPT, I did read about it in the docs for the EXPLAIN
statement when I was figuring out how to dump the access plan for these
queries. The doc for the EXPLAIN statement says:

--------------------------------
This clause indicates that the specified explainable statement is to be
reoptimized using the values for host variables, parameter markers, or
special registers that were previously used to reoptimize this
statement with REOPT ONCE.
--------------------------------

But this query has *never* been previously optimized with REOPT ONCE.
Is it a chicken-and-egg thing? The above doc quote doesn't help at
all.

Larry Menard wrote:
Hi, Gert.

That must have been for a different problem... I don't remember it from
this problem. With this problem we never touched COBOL and never used
bindfiles... we discovered the problem in a PHP app and then manually
determined it was actually in CLI, so we created a test CLI program
(based on 'tbread.C') to repro it.
Gert van der Kooij wrote:
In article <11*********************@h48g2000cwc.googlegroups. com>,
la**********@gmail.com says...
The commands I sent Ken were:
>
db2 connect to gallery2 user g2user using g2pwd
db2 explain plan for <SELECT stmt with 7 hard-coded values>
db2 connect reset
db2exfmt -d gallery2 -e g2user -g TIC -u g2user g2pwd -o
explain.out.7hc (just press enter to take all other defaults)
db2 connect to gallery2 user g2user using g2pwd
db2 explain plan for <SELECT stmt with 7 parameter markers>
db2 connect reset
db2exfmt -d gallery2 -e g2user -g TIC -u g2user g2pwd -o
explain.out.7pm (just press enter to take all other defaults)
>
I didn't specify any REOPT when I did this on my system, and both plans
came out identical.
>
Gert: I don't know how COBOL build scripts got into this conversation.
:-)
>
Hi Larry,

This is what Ken posted in one of his previous replies:
>
Um...ok...I'm a bit out of my league here, how do I check my bind options?
I used the sample program "bldapp" to build it, I could only find the
following statments contained in the directory where I built it:
db2 prep $1.sqc bindfile
db2 prep $1.sqC bindfile
db2 prep $1.sqb bindfile target mfcob CALL_RESOLUTION DEFERRED
db2 bind $1.bnd
>
Because of his 'target mfcob' specified it should be a MicroFocus COBOL
routine. Otherwise I can't understand why Ken built his programs from
this directory.
Oct 19 '06 #21

P: n/a
Larry Menard wrote:
Hi, Gert.

That must have been for a different problem... I don't remember it from
this problem. With this problem we never touched COBOL and never used
bindfiles... we discovered the problem in a PHP app and then manually
determined it was actually in CLI, so we created a test CLI program
(based on 'tbread.C') to repro it.
Gert van der Kooij wrote:
>>In article <11*********************@h48g2000cwc.googlegroups. com>,
la**********@gmail.com says...
>>>The commands I sent Ken were:

db2 connect to gallery2 user g2user using g2pwd
db2 explain plan for <SELECT stmt with 7 hard-coded values>
db2 connect reset
db2exfmt -d gallery2 -e g2user -g TIC -u g2user g2pwd -o
explain.out.7hc (just press enter to take all other defaults)
db2 connect to gallery2 user g2user using g2pwd
db2 explain plan for <SELECT stmt with 7 parameter markers>
db2 connect reset
db2exfmt -d gallery2 -e g2user -g TIC -u g2user g2pwd -o
explain.out.7pm (just press enter to take all other defaults)

I didn't specify any REOPT when I did this on my system, and both plans
came out identical.

Gert: I don't know how COBOL build scripts got into this conversation.
:-)

Hi Larry,

This is what Ken posted in one of his previous replies:

>>>Um...ok...I'm a bit out of my league here, how do I check my bind options?
I used the sample program "bldapp" to build it, I could only find the
following statments contained in the directory where I built it:
db2 prep $1.sqc bindfile
db2 prep $1.sqC bindfile
db2 prep $1.sqb bindfile target mfcob CALL_RESOLUTION DEFERRED
db2 bind $1.bnd

Because of his 'target mfcob' specified it should be a MicroFocus COBOL
routine. Otherwise I can't understand why Ken built his programs from
this directory.

We started with samples directory for CLI. Edited tbread.c for what we
wanted to run thru DB2, and typed bldapp
What bldapp does, I don't know. Binds then compiles C? Well,I know it
compiles,but what binding it does,I'm not entirely sure.
Those statments were just contained within the samples directory itself,
It's hard for me to tell which ones get run and which ones do not.
Oct 19 '06 #22

P: n/a
Ken,

"bldapp" is just a very short script... 'cat' it and see what it
does.

It only does prepping of the source files if they contain Static SQL
(and are therefore named "*.sqc". "Tbread.c" is not prepped because it
contains only Dynamic SQL. By definition, CLI is purely Dynamic SQL,
so a pure CLI program (like "tbread") will never need to be prepped.

Serge, you still there? :-) What 'sort' are you talking about?
73blazer wrote:
Larry Menard wrote:
Hi, Gert.

That must have been for a different problem... I don't remember it from
this problem. With this problem we never touched COBOL and never used
bindfiles... we discovered the problem in a PHP app and then manually
determined it was actually in CLI, so we created a test CLI program
(based on 'tbread.C') to repro it.
Gert van der Kooij wrote:
>In article <11*********************@h48g2000cwc.googlegroups. com>,
la**********@gmail.com says...

The commands I sent Ken were:

db2 connect to gallery2 user g2user using g2pwd
db2 explain plan for <SELECT stmt with 7 hard-coded values>
db2 connect reset
db2exfmt -d gallery2 -e g2user -g TIC -u g2user g2pwd -o
explain.out.7hc (just press enter to take all other defaults)
db2 connect to gallery2 user g2user using g2pwd
db2 explain plan for <SELECT stmt with 7 parameter markers>
db2 connect reset
db2exfmt -d gallery2 -e g2user -g TIC -u g2user g2pwd -o
explain.out.7pm (just press enter to take all other defaults)

I didn't specify any REOPT when I did this on my system, and both plans
came out identical.

Gert: I don't know how COBOL build scripts got into this conversation.
:-)
Hi Larry,

This is what Ken posted in one of his previous replies:
Um...ok...I'm a bit out of my league here, how do I check my bind options?
I used the sample program "bldapp" to build it, I could only find the
following statments contained in the directory where I built it:
db2 prep $1.sqc bindfile
db2 prep $1.sqC bindfile
db2 prep $1.sqb bindfile target mfcob CALL_RESOLUTION DEFERRED
db2 bind $1.bnd
Because of his 'target mfcob' specified it should be a MicroFocus COBOL
routine. Otherwise I can't understand why Ken built his programs from
this directory.

We started with samples directory for CLI. Edited tbread.c for what we
wanted to run thru DB2, and typed bldapp
What bldapp does, I don't know. Binds then compiles C? Well,I know it
compiles,but what binding it does,I'm not entirely sure.
Those statments were just contained within the samples directory itself,
It's hard for me to tell which ones get run and which ones do not.
Oct 20 '06 #23

P: n/a
The difference is obvious. The seven distinct values uses a nested loop
join technique, using an index, to locate the rows to be retrieved. The
parameter value query uses a hash (scan) join to give the same result
set. The valued query, with 14 values will do almost double the I/O work
to retrieve the rows from the test.test table than was done for seven
values. The parameter query will do the same I/O work for the test.test
table with any number of parameters in the "in" clause.

Once this difference is understood, combined with your observation that
index distribution statistics will alter the query run time, a
reasonable guess can be made as to what happened.

Once distribution statistics have been run, the optimizer has more
information about the data content and can make "better" guesses as to
the amount of I/O needed to perform a query. When going from six to
seven parameters, the optimizer decided that enough rows would need to
be retrieved (index + data) that it would probably be faster to scan the
table using a hash join than to attempt to retrieve seven groups
(assuming a non-unique index) of rows using an index. The optimizer
makes different assumptions about the number of rows retrieved for
parameters and fixed data values. This will account for the execution
time differences you observed. Unfortunately, in this specific case, it
turns out that the index retrieval is still faster.

You can verify this conclusion by running an explain for the query with
six parameters and comparing it to the seven parameter query explain.
I'd expect you to see the join technique change. When you ran your
tests, you should also have tried tests using more than seven values (ie
10 & 20) to see if other patterns arose in the timings.

Phil Sherman

73blazer wrote:
Serge Rielau wrote:
>Larry,

Did you compare the plans?

Cheers
Serge

Here's the links to the 2 different plans, output from db2exfmt:
I've never deciphered these before, so not sure what to look for in here.

7 Hard coded values:
http://www.snyderworld.org/ftp/explain_7hc.txt
7 Parameter markers:
http://www.snyderworld.org/ftp/explain_7pm.txt
Oct 20 '06 #24

P: n/a
Hi Phil.

Thanks for the detailed explanation. This is the first time I've
ever dealt with access plans, and I don't really know much about what's
better than the other.

So if I understand you correctly:

1) The optimizer is selecting a different (more general) plan for
parameter markers because it doesn't know how 'scattered' the values
will be at runtime (whereas with hard-coded values it knows them right
up front).

2) Specifying "WITH DISTRIBUTION" on the runstats command will assist
the optimizer in making a better choice in view of the above.

If that's true, then Ken could you please update your
"gallery2/modules/core/classes/Db2Storage.class::_getOptimizeStatement()"
as follows:

function _getOptimizeStatement() {
$optimizeCommand = 'CALL ADMIN_CMD (\'RUNSTATS ON TABLE ' .
$this->_username . '.%s WITH DISTRIBUTION ON ALL COLUMNS AND INDEXES
ALL\')';
return $optimizeCommand;
}

Then go into Gallery's "Site Admin", "Maintenance", then run the
"Optimize database" task. This will recreate all of Gallery's indexes.

Then see if the problem still exists.
Phil Sherman wrote:
The difference is obvious. The seven distinct values uses a nested loop
join technique, using an index, to locate the rows to be retrieved. The
parameter value query uses a hash (scan) join to give the same result
set. The valued query, with 14 values will do almost double the I/O work
to retrieve the rows from the test.test table than was done for seven
values. The parameter query will do the same I/O work for the test.test
table with any number of parameters in the "in" clause.

Once this difference is understood, combined with your observation that
index distribution statistics will alter the query run time, a
reasonable guess can be made as to what happened.

Once distribution statistics have been run, the optimizer has more
information about the data content and can make "better" guesses as to
the amount of I/O needed to perform a query. When going from six to
seven parameters, the optimizer decided that enough rows would need to
be retrieved (index + data) that it would probably be faster to scan the
table using a hash join than to attempt to retrieve seven groups
(assuming a non-unique index) of rows using an index. The optimizer
makes different assumptions about the number of rows retrieved for
parameters and fixed data values. This will account for the execution
time differences you observed. Unfortunately, in this specific case, it
turns out that the index retrieval is still faster.

You can verify this conclusion by running an explain for the query with
six parameters and comparing it to the seven parameter query explain.
I'd expect you to see the join technique change. When you ran your
tests, you should also have tried tests using more than seven values (ie
10 & 20) to see if other patterns arose in the timings.

Phil Sherman

73blazer wrote:
Serge Rielau wrote:
Larry,

Did you compare the plans?

Cheers
Serge
Here's the links to the 2 different plans, output from db2exfmt:
I've never deciphered these before, so not sure what to look for in here.

7 Hard coded values:
http://www.snyderworld.org/ftp/explain_7hc.txt
7 Parameter markers:
http://www.snyderworld.org/ftp/explain_7pm.txt
Oct 21 '06 #25

This discussion thread is closed

Replies have been disabled for this discussion.