Re: BLEVEL on bit-mapped indexes

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

Govind.Arumugam_at_alltel.com 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
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?=
  INET: mln_at_miracleas.dk

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: ListGuru_at_fatcity.com (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).