Re: BLEVEL on bit-mapped indexes
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).
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 [EMAIL PROTECTED] wrote: According tooracle 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,'OKBLEVEL',4, 'OK BLEVEL','BLEVEL HIGH') OK from dba_indexes where owner='owner-name'; When troubleshooting aperformance problem ina data warehousing environmentI found thatsome of theindexes had BLEVEL at 5 or 6. I am curious to know whether these indexesare candidates for rebuild.Should be looking at BLEVEL for bit-mapped indexes at all? I am not thatexperienced in bit-mapped indexes hence posting it to thegroup. Thanks in advance. Govind
RE: BLEVEL on bit-mapped indexes
Govind, Large indexes would tend to be 'taller'. However, I believe you can achieve a flatter structure for the same index by using a larger block size. If you are on 9i (any release?) then you could probably create new tablespaces with larger blocksizes and recreating these indexes therein. Not sure if this applies to BIs though... John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 So WHO is the Reason for the Season?! Write me for details! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- Sent: Monday, December 09, 2002 3:34 PM To: Multiple recipients of list ORACLE-L 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: John Kanagaraj 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).
BLEVEL on bit-mapped indexes
According tooracle 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') OKfrom dba_indexeswhere owner ='owner-name'; When troubleshooting aperformance problem ina data warehousing environmentI found thatsome of the indexes had BLEVEL at 5 or 6. I am curious to know whether these indexesare 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
RE: BLEVEL on bit-mapped indexes
According tooracle 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') OKfrom dba_indexeswhere owner ='owner-name'; When troubleshooting aperformance problem ina data warehousing environmentI found thatsome of the indexes had BLEVEL at 5 or 6. I am curious to know whether these indexesare 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
