Re: Using DBMS_SPACE.UNUSED_SPACE to Measure Index Quality, Lock Table?

Answers and comments embedded

Regards,

Sybrand Bakker
Senior Oracle DBA

"Ishay Friedman" <ifriedman_at_hotmail.com> wrote in message news:722d1e1e.0110231009.54897373_at_posting.google.com...
> Hello,
>
> According to all sources that I can find, the best way
> to determine the storage quality of a v8.0.x index is by
> running 'ANALYZE INDEX ....' and checking the ratio of
> DEL_LF_ROWS to LF_ROWS in INDEX_STATS.

Make that
Analyze index .... validate structure

>
> I am not sure whether or not 8i+ has any utilities that allow
> for a better way to determine index quality.

Not really, but it depends on what you understand by index quality. If you want to look at the effectiveness of an index you should consider the clustering factor computed by an ordinary analyze index. You are discussing -though not in full- the storage properties of the index

>
> However, my understanding is that the 'ANALYZE INDEX' command locks
> out the table.

Not true.
Analyze index places a DDL lock which means you can't ALTER the associated table, which does seems reasonable.

Thus, on the systems that I use, the index
> storage quality can also be measured by a routine that uses the
> DBMS_SPACE.UNUSED_SPACE procedure to measure an index's storage
> quality.
>
> The storage quality is given by the ratio of the number of blocks
> needed to store an ideal index (Using space per block, space per index
> entry, and number of table rows) to the actual number of blocks used
> by the index.
>

IIRC
You have
a segment consisting of several blocks.
The btree_space as shown in index_stats, which is by design lower than the # of blocks in dba_segments.
Secondly you have used_space in index_stats, which shows the number of bytes actually used.
I'm not sure and very much doubting whether the results of dbms_space are really equivalent.
I always take the pct_used from index_stats into account as an important indicator of the wellness of the index.

> As far as I can tell, the calculation does not lock out any table.
> (Am I correct?)
>
> Is this method (Assuming that the right parameters are
> passed to DBMS_SPACE.UNUSED_SPACE, etc..) almost as
> accurate as using INDEX_STATS?
>
> Would a DBMS_SPACE.UNUSED_SPACE storage quality of, say, 60%
> approximate a deleted leaf node ratio of 40% (I realize that
> this depends on the number of levels in the index, etc..)
>
> Any other ideas about this method would be appreciated.
>
>
>
> Thanks,
>
> Ishay Friedman
Received on Tue Oct 23 2001 - 14:56:23 CDT