Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Index Rebuilds

RE: Index Rebuilds

From: Christian Antognini <Christian.Antognini_at_trivadis.com>
Date: Mon, 31 Jul 2006 00:16:30 +0200
Message-ID: <F2C9CCA71510B442AF71446CAE8AEBAF01A962@MSXVS04.trivadis.com>


Stalin

> LBLKS: 66653
> BlVL: 3
> CF: 679426
> NROWS: 829734
> AVG_DATA_BLK/KEY: 2
>
> Coalescing the index got the lblks to 11k and the desired plan
> however, the performance is still under water.

Coalesce reuse the index structure and puts the freed block in the free list. I.e. I guess you have 55k blocks on the free list (a dump of the segment can be used to confirm this point). Since the FFS reads all the blocks up to the high water mark, also the free blocks are read.

> Any suggestions as to what i should be looking further. I really don't
> want to rebuild the index :)

To decrease the index size you have to rebuild it. In the future, if the number of leaf blocks increase constantly (in the way you described), regular coalesce may "solve" the problem, i.e. let reuse the already allocated index blocks instead of allocating new ones.

HTH
Chris

--
http://www.freelists.org/webpage/oracle-l
Received on Sun Jul 30 2006 - 17:16:30 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US