Re: indexes reserve too much extents

From: Frederic Hoornaert <Frederic.Hoornaert_at_transics.nospam.com>
Date: Tue, 4 May 2004 11:44:32 +0200
Message-ID: <409765d9$0$25077$a0ced6e1@news.skynet.be>

Hello Howard,

This is the data and index tablespace we are using for very large objects :

• ********** TABLESPACES FOR TRANSICS DATA ********** CREATE TABLESPACE "DATA_LARGE" LOGGING DATAFILE 'D:\Oracle\Data\ORA10G\DATA_LARGE_01.ORA' SIZE 2048M REUSE AUTOEXTEND ON NEXT 2048M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128M SEGMENT SPACE MANAGEMENT AUTO ; CREATE TABLESPACE "INDEX_LARGE" LOGGING DATAFILE 'D:\Oracle\Data\ORA10G\INDEX_LARGE_01.ORA' SIZE 6000M REUSE AUTOEXTEND ON NEXT 1024M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64M SEGMENT SPACE MANAGEMENT AUTO ;

so we are indead using ASSM, and not ASM as I mentioned before. extent sizes for data are 128K, 4M, 128M extent sizes for indexes are 64K, 2M, 64M

There is a big leap because we have a lot of static tables (128K), a few tables with few inserts and a short avg record length( 4M) and for the large tables with 20.000 rec. / day and avg rows length of 198 we use 128 M. This way the segments need only to auto-extend once in a while.

> I also don't understand how you can now suddenly have 64M extents given
> the three extent sizes you just quoted above.
>

64Mb is for my index tablespace, the 128K, 4M and 128M is for my indexes

> USED_BLOCKS = 65536
> * 8192 = 536,870,912 bytes assigned
> BTREE_SPACE = 177,058,656
> PCT_USED = 90
These figures come from INDEX_STATS, so it really is PCT_USED and not PCT_FREE as you mention in your reply.

I'll try to rebuild an index in a new, smaller tablespace with the options you specified, and I'll send you the info of INDEX_STATS. Can I send it by email (where) or just post it in the group ?

Thanks,

Frederic Received on Tue May 04 2004 - 04:44:32 CDT