I’ve added a link to the Script Catalogue to the end of the article. There are two scripts there that can help you identify indexes where a coalesce would be sufficient; both do a lot of work since they read the index one block at a time, but they don’t cause any locking. One of them generates a statistical report of leaf block usage, the other “draws a picture” of the index leaf level.

]]>By: Jithin Sarathhttps://jonathanlewis.wordpress.com/2010/02/28/index-efficiency-2/#comment-37161
Wed, 01 Sep 2010 14:24:40 +0000http://jonathanlewis.wordpress.com/?p=1051#comment-37161Jonathan,
Apologies. I now understand the use of “scaling factor”. It’s been a very long week and I just could not concentrate. Spending some time with the family seems to have helped and I was now casually going thro the code – and it hit me on the face :).

However, the doubt on coalesce still stands though.

]]>By: Jithin Sarathhttps://jonathanlewis.wordpress.com/2010/02/28/index-efficiency-2/#comment-37158
Wed, 01 Sep 2010 11:48:28 +0000http://jonathanlewis.wordpress.com/?p=1051#comment-37158Hi Jonathan,
We are upgrading from 9.2.0.6 to 11gR2 and I was assigned the task of redesigning the index rebuild process. As you have mention at several places across your blog, I work for a company where IT managers force the DBA to have regular index rebuilds. At present the indexes are chosen as per what oracle states %deleted >=30 and height>3.

It takes us approx 8 hours!! to complete the ‘validate structur’e for all indexes (on a daily clone).

Your script caught my attention and I’m trying to use it ti identify worthy candidates for rebuild. On this regard, could you please elaborate on the relevance of the “scaling factor” used?

I’m also trying to decide on what to rebuild and what all to coalesce. Any inputs you have will be great!

The main flaws are in the notes in the script. The biggest, perhaps, is that it doesn’t cope with compression at all. There’s also a problem with rounding errors and approximations when you have a lot of nulls in the columns that make up the index. It’s very easy to get an approximation which is much larger than the final index would actually be.

This looks like it may be one of the bugs associated with ASSM and the difficulty of choosing a moment to update the bitmaps to show that a block is free.

Any updates which do a massive delete followed by inserts run a risk of making indexes bigger than they need to be. This is because Oracle cannot re-use the space in an index block until the transaction has committed – even for rows inserted by the same transaction (except for one special case). The same thing happens with tables, in fact, but the mechanics are different and the effect is much less noticeable. (See https://jonathanlewis.wordpress.com/2009/05/20/lunchtime-quiz/ for more details.)

Since you’re on 10g with materialized views, I’d guess that you are doing atomic refreshes, which means Oracle deletes everything from the MVs and their indexes, then re-inserts, then commits – which fits a critical pattern.

You could experiment by putting a couple of your indexes into a tablespace using freelist management to see the problem “magically” disappears. Alternatively just schedule a “coalesce” on all the problem indexes immediately after the refresh.