Re: Tablespace best practices

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Mon, 27 Dec 2004 17:26:07 +1100
Message-ID: <41cfab00$0$4485$afc38c87@news.optusnet.com.au>

Dave wrote:

> <fitzjarrell_at_cox.net> wrote in message 
> news:1104110030.857422.173540_at_z14g2000cwz.googlegroups.com...
> 

>>Comments embedded.
>>
>>Dave wrote:
>>
>>>"Access" <idmwarpzone_NOSPAM__at_yahoo.com> wrote in message
>>>news:41cf4475$0$339$ba620e4c_at_news.skynet.be...
>>>
>>>>Hi,
>>>>
>>>>I was wondering what the "best practices" are regarding tablespace
>>>>creation
>>>>:
>>>>- separate tablespaces for data/indexes ?
>>>
>>>nope, no need
>>
>>For performace, I agree. For manageability, my opinion differs. I
>>find it easier to manage tables and indexes if the are placed in
>>separate tablespaces; if I lose an index or the entire tablespace it's
>>fairly easy to recreate the tablespace and rebuild the indexes if they
>>are separate from the tables.
>>
>>
>>>>- separate tablespace for each schema ?
>>>
>>>not a bad idea - for manageability
>>>
>>>>- large/medium/small objects in separate tablespaces ?
>>>
>>>not a bad idea, depends how wildly your sizes vary
>>>
>>>>- locally managed with auto or manual segment space management ?
>>>
>>>up to you, auto works fine if you dont care that much
>>>
>>
>>And I prefer to use uniform extents, to virtually eliminate
>>fragmentation.
>>
>>
>>>>Just wanted to hear some pro/contra's ...
>>>>
>>>>
>>>>
>>>>
>>
>>David Fitzjarrell
>>

> 
> 
> there was a paper once (i think by richard foote) which showed how 
> fragmentation was impossible with autollocate - could be wrong though)
> 
> agree about the index thing as well - answer was written in haste (but i bet 
> it was asked in terms of performance) 
> 
> 

I think you and Dave both need to read the original post again at this point:

"locally managed with auto or manual segment space management?"

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 Received on Mon Dec 27 2004 - 00:26:07 CST