Re: BLEVEL on bit-mapped indexes

[Mogens N�rgaard]

I used to preach the same rules of thumb: If blevel > 4, if average filling of leaf blocks < 50, etc., etc. These days, as I become older and older and more and more bitter, I'm inclined to say: If you're having a problem with a SQL statement or a business function or a report or whatever - and if the problem is time spent waiting for IO - then it's worth checking if the IO occurs on the index or the table (possible with the segment stats available in 9.2) - and if there's a lot of extra IO happening to the index and it's possible to rebuild it so that fewer blocks will be read by Oracle - then it will help.

Also, of course, if the system in general needs to have lower IO usage because of some disk/SAN-problem it's worth checking where the IO happens. If it's on an index it's worth finding out whether a rebuild will help or not.

Mogens

[EMAIL PROTECTED] wrote:

According to oracle documentation or metalink sources,  if the BLEVEL were to be more than 4, it is recommended to rebuild the index.

 

select index_name, blevel,
decode(blevel,0,'OK BLEVEL',1,'OK BLEVEL', 2,'OK BLEVEL',3,'OK BLEVEL',4,
'OK BLEVEL','BLEVEL HIGH') OK
from dba_indexes
where owner = '<owner-name>';

 

When troubleshooting a performance problem in a data warehousing environment I found that some of the indexes had BLEVEL at 5 or 6.

 

I am curious to know whether these indexes are candidates for rebuild. Should be looking at BLEVEL for bit-mapped indexes at all?  I am not that experienced in bit-mapped indexes hence posting it to the group.

 

Thanks in advance.

 

Govind