dbanalys ...do I read well ?

[Olivier_Desmars]

When doing a

proutil dbname -C dbanalys

I get the following informations about the 'INDEX BLOCK' :




INDEX BLOCK SUMMARY FOR AREA "Schema Area" : 6
-------------------------------------------------------
Table Index Fields Levels Blocks Size % Util Factor


1) Does the '% Util' column really indicate the use of the index ? Does it mean that each time a query is made on a table, this index is used ?

2) If it's a pourcentage , what is it related to ? e.g. What does mean 100% or 0% ?

3) Lastly, what's the meaning of the last column (Factor) ?


PS : don't speed too much to answer coz I'm off for one week, going skiing in the French Alps

 

[toby.Harman]

The secret is in the Title - It is an Index Block Analysis. Therefore the %Util refers to the pecentage utilisation of the Index Blocks for that specific index (or how much free space there is for that index)

The Factor (if I remember correctly) refers to the scatter factor which indicates how the index blocks are positioned within the data extents for the area they are in. Are they all over the place (high Scatter factor) or are they all in a block together (low scatter). Low is good since it will improve read-ahead hits from disk and O/s reads. How low will depend on the response times etc that you require.

 

[CtL]

And to beat this poor dead horse further...

Doing a re-index operation will improve (increase the %), sometimes dramatically. Picture it as putting all the sequential index links one after another in the same block. If you then walk through that index chain you get all the related components in a single db block, and if that block is cached then you don't have to go back to disk for several calls, improving performance.

In a badly fragmented chain you are having to jump from one block to another as you walk through the index chain, causing many disk reads, and also causing your cache (-B) to be stuffed with index blocks that are only useful to this one query ("flushing the cache").

 

[Olivier_Desmars]

Thanks for the tips