Difference between ANALYZE INDEX VALIDATE STRUCTURE and DBMS_SPACE

[Former Member]

Dear Experts,

Note 771929 suggests various kind of methods to measure index fragmentation.

Among these methods, while ANALYZE INDEX VALIDATE STRUCTURE locks the index table in question, DBMS_SPACE (OBJECT_SPACE_USAGE_TBF) function doesn't lock the index table.

What does the difference mean?

Which is more accurate, therefore reliable way to determine index fragmentation?

In the note, ANALYZE INDEX VALIDATE STRUCTURE collects information for both storage quality and leaf row quality. On the other hand, DBMS_SPACE seems to answer storage quality only. If so, I want to use the former way. But since the former way locks index tables, I would like to use DBMS_SPACE as the next best way.....

Our system is ECC 6.0 and Oracle 10.2.0.2.0.

Many Thanks,

Norifumi OTANI

[Former Member · Accepted solution]

Dear Lars,

Thank you for your quick reply.

I would like to understand your reply, and answer your question, step by step.

Firstly, could you tell me a little bet more detail about "data management blocks"?

My assumption is that index-data blocks are used for root, blanch, leaf node etc,. But data management blocks?

Many Thanks,

Norifumi OTANI

[lbreddemann · Accepted solution]

> Firstly, could you tell me a little bet more detail about "data management blocks"?

>

> My assumption is that index-data blocks are used for root, blanch, leaf node etc,. But data management blocks?

Ok, I'd been a bit sloppy here with the description, so let's see if I can make this a bit clearer.

Seen from the angle of "pure B-tree" structure you're right.

There is a root page and eventually branch and leaf pages.

However these pages need to be stored into Oracle blocks.

To figure out which blocks are still available for new inserts, oracle maintains lists of free blocks for each segment.

These freelists also need to be stored somewhere - so oracle takes a block (like the segment header) from the segment and puts the information there.

So the DBMS_SPACE package does read these blocks to figure out how many blocks are really used/free.

regards,

Lars

[Former Member · Accepted solution]

Dear Lars,

Thank you very much for your clear answer!

Because I have to concentrate on fixing a system trouble, I need few days to reply you....

I will come back ASAP.

Many thanks,

Norifumi

[Former Member · Accepted solution]

Dear Lars,

Regarding your comment that CBO approach works well in many cases, I have a question.

Due to the parameter stats_change_threshold (default: 50%), BRCONNECT only creates new statistics for the table, which more than 50% of data have been changed. Is this threshold mechanism also applicable for the index-table?

If it is true, do you force BW to create complete statistics of index-tables, before executing SQL command attached in note 771929?

Many thanks for your further suggestion.

Best Regards,

Norifumi

[lbreddemann · Accepted solution]

> Due to the parameter stats_change_threshold (default: 50%), BRCONNECT only creates new statistics for the table, which more than 50% of data have been changed. Is this threshold mechanism also applicable for the index-table?

Hi again.

There is no such thing as "index-tables".

You're talking about indexes.

And yes, the 50% threshold applies to indexes as well.

But this really shouldn't be an issue.

The logic here is: the statistics would only change when the data has changed.

So the statistics should be "good enough".

Anyway: it's really not necessary to constantly monitor all indexes and rebuild them.

There are only special (pretty well known) indexes that need to be rebuilt due to extreme usage patterns (e.g. RFC tables with right-most inserts and rather random left-side deletes).

> If it is true, do you force BW to create complete statistics of index-tables, before executing SQL command attached in note 771929?

For BW you usually won't ever need to rebuild indexes, since the bigger ones are most often dropped and recreated during the load processes.

regards,

Lars

[lbreddemann]

> What does the difference mean?

It means that DBMS_SPACE can be used safely while the system is up and running.

This method does not actually touch index-data blocks but just the space management blocks.

ANALYZE INDEX on the other hand focuses on the B*TREE structure of the index storage and for that the actual index data blocks need to be read and locked for a while.

> Which is more accurate, therefore reliable way to determine index fragmentation?

The problem here is that you need to define what precisely you mean by "fragmentation".

As you already found out yourself, ANALYZE INDEX considers more information and can therefore be more 'reliable'.

Why don't you just go for the "CBO"-approach described in the note.

For many cases it works pretty well.

A general note: you usually don't want to check on each and every index all the time.

Just focus on those where you have reason to believe that space savings will be worth the effort of checking it.

Rebuilding indexes just to save a few percent of space can be safely considered a waste of time.

B*Trees are specifically designed to 'waste' space...

regards,

Lars