Oppolzer - Informatik / Blog

G.S. wrote:

Whenever possible the Optimizer Hints should be used to alter access paths, not
catalogue manipulation. Before Optimizer Hints we did have a dynamic SQL program
which would alter the catalogue statistics, bind the program and alter them back
to what they were. You are defeating the purpose of the optimizer if it cannot
see the correct catalogue statistics.

Bernd Oppolzer wrote:

> 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
>
>
>