Oppolzer - Informatik / Blog


Blog-Hauptseite      Neuester Artikel      Älterer Artikel      Neuerer Artikel      Älterer gleiche Kategorie      Neuerer gleiche Kategorie

DB2-L - Antwort auf Frage wg. Manipulation von CLUSTERRATIO

Subject:

Re: manip of clusterratio

From:

Bernd Oppolzer <bernd.oppolzer@T-ONLINE.DE>

Reply-To:

DB2 List <db2-l@lists.idug.org>

Date:

2003.01.10 17:36:00


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

Blog-Hauptseite      Neuester Artikel      Älterer Artikel      Neuerer Artikel      Älterer gleiche Kategorie      Neuerer gleiche Kategorie