Oppolzer - Informatik / Blog

H.M. wrote:

Bernd,

as your timings show it's not a good idea to force prefetching
if the clusterratio is low. At runtime there will be a lot of
synch reads involved despite the prefetching IOs.

H.M.

-----Ursprüngliche Nachricht-----
Von: Bernd Oppolzer [mailto:Bernd.Oppolzer@T-ONLINE.DE]
Gesendet: Freitag, 10. Januar 2003 17:15
An: DB2-L@LISTSERV.YLASSOC.COM
Betreff: manip of clusterratio

Hello DB2 experts,

in our site, one of the DBAs does manipulate the statistic values of
SYSINDEXES in a regular manner; he puts 0.99 in the column CLUSTERRATIO of
every non unique index.

We observed that DB2 uses sequential prefetch with an access over one of
these indexes, where, in our opinion, list prefetch should be used. The real
clusterratio value, as computed by RUNSTATS, is 0.67.

The SELECT needed 40 seconds; a similar access with list prefetch involved
due to a multiple index scan needed only 4 seconds.

What do you think of this manipulation of CLUSTERRATIO ? Any suggestions are
welcome.

Regards

Bernd