indexes reserve too much extents

From: Frederic Hoornaert <Frederic.Hoornaert_at_transics.nospam.com>
Date: Mon, 3 May 2004 16:47:50 +0200
Message-ID: <40965b66$0$22824$a0ced6e1@news.skynet.be>

Hello,

I'm having some questions about the space oracle has allocated to my indexes :

Our OLTP application uses a database with the following specifications DATA tablespaces :

data_small : locally managed unfiform size 128k
data_medium : locally managed unfiform size 4M
data_large : locally managed unfiform size 128M

index tablespaces :

index_small : locally managed unfiform size 64k
index_medium : locally managed unfiform size 2M
index_large : locally managed unfiform size 64M    

minimum extents of all tablespaces = 1
DB_BLOCK_SIZE = 8192

index_small contains the indexes on the tables in data_small
index_medium contains the indexes on the tables in data_medium
index_large contains the indexes on the tables in data_large

I have been analyzing the sizes of our tablespaces, and i'm having some difficulties with the size of the indexes. data_large = 6 Gb
index_large = 17.8 Gb

Because 17.8 Gb for only 6 Gb of data seems to be too much, I have analyzed all tables and all indexes. One table with 5.979.487 rows has a size of 163.840 blocks in 10 extents (1 extent = 128M)

    assigned size = 1,25 Gb
This table has 30 indices which use 1.024.000 blocks => 7,8 Gb  

When I take a close look at the data of INDEX_STATS after a VALIDATE INDEX <index_name>, i see that 16 indices have each 1 extent of 64 Mb
1 index has 5 extents = ( 64Mb * 5 ) = 320 Mb 13 indices have each 8 extents = (64 Mb * 8) = 512 Mb.

For the indices that all use 8 extents, something seems to be wrong because for these indices I see that they all use between 50 and 150 Mb (value of index_stats.used_space)

Largest index in tablespace :

extent size : 64 Mb / (8192 byte / block) => 8192 blocks
used blocks : 65536 / (8192 blocks / extent) => 8 extents
btree_space : 177.058.656 byte 

177.058.656 / (8192*8192) = 2,6     

Why are these indexes using 8 extents where is space enough in 3 extents ? For this table, I have 8.388.608.000 bytes assigned where only 1.635.157.943 bytes are needed.

This is the situation after the creation of an empty database, and data import with SQL Loader. No manual manipulation had been done yet.

-- 
Greetz,
Frederic Hoornaert
(Plz remove .nospam from my email address when replying to this message)

Received on Mon May 03 2004 - 09:47:50 CDT