DISABLING LIST PREFETCH VIA OPTHINTS

Jeff L'Italien

DISABLING LIST PREFETCH VIA OPTHINTS
Can anyone tell me if they have been able to disable list prefetch via the use
of optimizer hints? We have a situation where an application is performing an
update of the last 4 index columns of a 9 index key while matching on the first
5 columns. The application performs a significant amount of updates per day,
and due to list prefetch, the dynamic prefetch engine is unable to get
triggered. By being able to have list prefetch turned off, I feel confident
that the CPU time of the application can be reduced substantially (up to 40%),
as I have benchmarked the same process without having the updated columns
participate as part of the key. During the benchmarking process, the list
prefetch went away, being replaced by a large amount of dynamic prefetches, as
well as a reduction in the number of getpages. Any help would be appreciated.

Regards,
Jeff L'Italien
American Express



James Kwan

Re: DISABLING LIST PREFETCH VIA OPTHINTS
(in response to Jeff L'Italien)
Jeff,

One way I will do is to play around with the catalog stat to come up with an
access path which does not use List Prefetch. Copy the content of PLAN_TABLE
to you production PLAN_TABLE. Then use Optimize Hints Yes to instruct it to
use the 'old' access path. Try to see if this works.

James Kwan

IBM Certified Expert

In a message dated 1/7/02 1:11:46 PM Central Standard Time,
[login to unmask email] writes:


> Can anyone tell me if they have been able to disable list prefetch via the
> use
> of optimizer hints? We have a situation where an application is performing
> an
> update of the last 4 index columns of a 9 index key while matching on the
> first
> 5 columns. The application performs a significant amount of updates per
> day,
> and due to list prefetch, the dynamic prefetch engine is unable to get
> triggered. By being able to have list prefetch turned off, I feel confident
> that the CPU time of the application can be reduced substantially (up to
> 40%),
> as I have benchmarked the same process without having the updated columns
> participate as part of the key. During the benchmarking process, the list
> prefetch went away, being replaced by a large amount of dynamic prefetches,
> as
> well as a reduction in the number of getpages. Any help would be
> appreciated.
>
> Regards,
> Jeff L'Italien
> American Express
>


Jeff L'Italien

Re: DISABLING LIST PREFETCH VIA OPTHINTS
(in response to James Kwan)
James,

Unfortunately, this is a situation where tweaking of the stats doesn't really
help out. In issuing a select with the same predicate, no list prefetch is
invoked. In both situations, the access path taken is against the clustering
index of the table (approx 98% clustered), but apparently, because index
columns are being updated, the optimizer wants list prefetch to be activated.
To take your idea one step further, I'm wondering if the PREFETCH column of the
PLAN_TABLE can be updated to remove the "L" value, then use this in conjunction
with the rebind. Unfortunately, since OPTHINT is currently disabled across our
subsystems, I'm unable to test out this theory.

Regards,
Jeff L'Italien
American Express



From: "James Kwan" <[login to unmask email]>@RYCI.COM> on 01/07/2002 06:42 PM EST

Please respond to "DB2 Data Base Discussion List" <[login to unmask email]>

Sent by: "DB2 Data Base Discussion List" <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: Re: DISABLING LIST PREFETCH VIA OPTHINTS


Jeff,

One way I will do is to play around with the catalog stat to come up with an
access path which does not use List Prefetch.  Copy the content of PLAN_TABLE
to you production PLAN_TABLE.  Then use Optimize Hints Yes to instruct it to
use the 'old' access path.  Try to see if this works.

James Kwan

IBM Certified Expert

In a message dated 1/7/02 1:11:46 PM Central Standard Time,
[login to unmask email] writes:


Can anyone tell me if they have been able to disable list prefetch via the use
of optimizer hints?  We have a situation where an application is performing an
update of the last 4 index columns of a 9 index key while matching on the first
5 columns.  The application performs a significant amount of updates per day,
and due to list prefetch, the dynamic prefetch engine is unable to get
triggered.  By being able to have list prefetch turned off, I feel confident
that the CPU time of the application can be reduced substantially (up to 40%),
as I have benchmarked the same process without having the updated columns
participate as part of the key.  During the benchmarking process, the list
prefetch went away, being replaced by a large amount of dynamic prefetches, as
well as a reduction in the number of getpages.  Any help would be appreciated.

Regards,
Jeff L'Italien
American Express







Terry Purcell

Re: DISABLING LIST PREFETCH VIA OPTHINTS
(in response to Jeff L'Italien)
Jeff,

Unless you have equals predicates (or IS NULL) on all columns of the index
key, then you are stuck with list prefetch, multi-index access (which
includes list prefetch), or a tablespace scan.

This is to avoid the possibility of an UPDATE causing an index entry to be
moved within the index tree, such that it qualifies more than once within
the same WHERE clause. This is known within IBM as the "Halloween" problem,
because it was discovered by Pat Selinger on Halloween many years ago.

So, unfortunately you cannot use OPTHINTS to disable list prefetch. Any
attempt to disable prefetch with VPSEQT=0 will probably result in a
tablespace scan.

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Jeff A L'Italien
Sent: Tuesday, January 08, 2002 8:27 AM
To: [login to unmask email]
Subject: Re: DISABLING LIST PREFETCH VIA OPTHINTS


James,

Unfortunately, this is a situation where tweaking of the stats doesn't
really
help out. In issuing a select with the same predicate, no list prefetch is
invoked. In both situations, the access path taken is against the
clustering
index of the table (approx 98% clustered), but apparently, because index
columns are being updated, the optimizer wants list prefetch to be
activated.
To take your idea one step further, I'm wondering if the PREFETCH column of
the
PLAN_TABLE can be updated to remove the "L" value, then use this in
conjunction
with the rebind. Unfortunately, since OPTHINT is currently disabled across
our
subsystems, I'm unable to test out this theory.

Regards,
Jeff L'Italien
American Express



From: "James Kwan" <[login to unmask email]>@RYCI.COM> on 01/07/2002 06:42 PM
EST

Please respond to "DB2 Data Base Discussion List" <[login to unmask email]>

Sent by: "DB2 Data Base Discussion List" <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: Re: DISABLING LIST PREFETCH VIA OPTHINTS


Jeff,

One way I will do is to play around with the catalog stat to come up with an
access path which does not use List Prefetch. Copy the content of
PLAN_TABLE
to you production PLAN_TABLE. Then use Optimize Hints Yes to instruct it to
use the 'old' access path. Try to see if this works.

James Kwan

IBM Certified Expert

In a message dated 1/7/02 1:11:46 PM Central Standard Time,
[login to unmask email] writes:


Can anyone tell me if they have been able to disable list prefetch via the
use
of optimizer hints? We have a situation where an application is performing
an
update of the last 4 index columns of a 9 index key while matching on the
first
5 columns. The application performs a significant amount of updates per
day,
and due to list prefetch, the dynamic prefetch engine is unable to get
triggered. By being able to have list prefetch turned off, I feel confident
that the CPU time of the application can be reduced substantially (up to
40%),
as I have benchmarked the same process without having the updated columns
participate as part of the key. During the benchmarking process, the list
prefetch went away, being replaced by a large amount of dynamic prefetches,
as
well as a reduction in the number of getpages. Any help would be
appreciated.

Regards,
Jeff L'Italien
American Express





================






James Kwan

Re: DISABLING LIST PREFETCH VIA OPTHINTS
(in response to Terry Purcell)
Jeff,

Very interesting theory to test if you can update the explain table. Another
theory that might work is to use a similar statement which has different
access path. With QUERYNO option to point to this statement, you might fool
the optimizer. Unfortunatelly, I can't test this since I don't have
DB2/OS390 at home. If someone can test it, please let us know.

James Kwan

IBM Certified Expert


In a message dated 1/8/02 8:57:09 AM Central Standard Time,
[login to unmask email] writes:


> James,
>
> Unfortunately, this is a situation where tweaking of the stats doesn't
> really
> help out. In issuing a select with the same predicate, no list prefetch is
> invoked. In both situations, the access path taken is against the
> clustering
> index of the table (approx 98% clustered), but apparently, because index
> columns are being updated, the optimizer wants list prefetch to be
> activated.
> To take your idea one step further, I'm wondering if the PREFETCH column of
> the
> PLAN_TABLE can be updated to remove the "L" value, then use this in
> conjunction
> with the rebind. Unfortunately, since OPTHINT is currently disabled across
> our
> subsystems, I'm unable to test out this theory.
>
> Regards,
> Jeff L'Italien
> American Express