Re: indexes reserve too much extents

Frederic Hoornaert wrote:

> 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
As I said originally, autoextend is a really bad idea.

> MAXSIZE UNLIMITED
And that's even worse. If you must use autoextend, it is practically compulsory (or ought to be) to specify a NEXT (tick) and a MAXSIZE (bzzzt).

> 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
Brave. I dislike huge data files when several smaller ones would do.

>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.

Who cares whether they autoextend every 30 minutes? That's the beauty of locally managed tablespace: one ceases, largely, to care about such matters, since autoextension of the segment is cheap.

>
>>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.

Ah. My mistake. The underscore should have given it away. It's talking about how much of the index is used up, not the setting of the PCTUSED parameter that you can set for tables (and which defaults to 40). Things are a bit clearer now.

What are the values of LF_ROWS and LF_ROW_LEN?

> 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 ?

Either way. My views on ASSM are not shared by everyone, for example. So it is always good to get others' input (ie, post here). But if you prefer it by email, that's fine too.

Regards
HJR Received on Tue May 04 2004 - 05:29:43 CDT