A high blevel on a bitmap can often suggest that a reasonable amount of dml is occurring on the underlying table which is pretty much a no-no. bitmaps are very prone to ballooning in size wildly when lots of dml is applied.
Jonathan Lewis showed me a test case where a table with just a few hundred rows had its bitmap blossom to 600M and a blevel of 10 with a tiny number of updates to the table. hth connor --- Mogens_Nørgaard <[EMAIL PROTECTED]> wrote: > 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 > > > > ===== Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net "GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day" __________________________________________________ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
