Re: Tablespace best practices

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Wed, 29 Dec 2004 00:22:13 +1100
Message-ID: <41d15e0b$0$24378$afc38c87@news.optusnet.com.au>

Connor McDonald wrote:
[snip]

>>
>>Automatic segment space management is otherwise known as ASSM, and if
>>there is one thing safe to say about ASSM it is that it is not just "up
>>to you". And neither does it have anything to do with fragmentation
>>(which is indeed a separate question of uniform versus autoallocate LMT).
>>
>>ASSM is a supremely wonderful way of eliminating contention for the head
>>of a freelist, which is characteristically going to happen in a RAC, but
>>could also happen in an OLTP single-instance-many-CPU situation too.
>>However, it has potentially ENORMOUS costs: full tablescans potentially
>>about 15% longer to complete. Buffer cache, potentially up to 30% full
>>of Oracle administrative overhead. Unless you are suffering from
>>freelist contention, or run a RAC, or are likely to do either, then ASSM
>>most definitely is something to be avoided. The OP should therefore
>>otherwise use segment space management manual (which is still the
>>default, thank Heavens).
>>
>>Incidentally, and on a totally separate subject, Richard's posts showed
>>that fragmentation of auto-allocated LMT was not IMpossible, just that
>>it was very, very difficult to make it happen.
>>
>>Regards
>>HJR

> 
> 
> Sadly, I think ASSM is in fact the default if you have a LMT system
> tablespace.
> 
> hth
> connor

No it's not.

Oracle 9i:

SQL> select extent_management from user_tablespaces

   2 where tablespace_name='SYSTEM';

EXTENT_MAN

---

LOCAL SQL> create tablespace X datafile 'x.dbf' size 5m;

Tablespace created.

SQL> select segment_space_management from

   2 user_tablespaces where tablespace_name='X';

SEGMEN

---

MANUAL Perhaps you could return the favour and test 10g?

Regards
HJR Received on Tue Dec 28 2004 - 07:22:13 CST