One of our queries is doing list prefetch and it's just hanging there at the fetch operation holding onto locks..The table always goes out of clustering because of the deletes, updates happening on that..I scheduled weekly reorgs and still I face the problem of fetch operation hanging couple of days after the reorg. .the table has only 1000 rows and I placed it in seperate sms tablespace with extent size 32, prefetch size 32..
there is an apar on diabling list prefetch..but this is for siebel pipelined plans
Support & downloads >
IY72977: INTRODUCE OPTION OF DISABLING LIST PREFETCH FOR PIPELINED PLANS
Closed as program error.
When pipelined plans is enabled for Siebel environments, we
introduce the option of disabling list prefetch accesses for
A list prefetch access can be seen in the access plan of an SQL
as (FETCH->RIDSCAN->SORT->IXSCAN) or if you gather a db2exfmt
output for a query, you will see:
PREFETCH: (Type of Prefetch)
Note, list prefetch is a good access in general. But in
specific cases of pipelined plans, avoiding this access can
result in better perforance. To enable this fix, please contact
Siebel or IBM to set the registry to a specific value.
Siebel specific environments or instances where the pipelined
plans registry is set.
Provide additional option to registry variable to avoid list
prefetch accesses in certain cases.
For environments where the pipelined plans registry is set,
avoiding list prefetch through a special setting of the registry
can help performance.
Problem was first fixed in DB2 UDB Version 8, FixPak 11
APAR number IY72977
Reported component name DB2 UDB ESE AIX
Reported component ID 5765F4100
Reported release 810
Status CLOSED PER
Submitted date 2005-06-17
Closed date 2006-02-06
Last modified date 2006-02-06
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Fixed component name DB2 UDB ESE AIX
Fixed component ID 5765F4100
Applicable component levels
R810 PSN UP
R820 PSN UP