Howard J. Rogers wrote:
>
> 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
Sadly, I think ASSM is in fact the default if you have a LMT system
tablespace.
hth
connor
--
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
ISBN: 1590592174
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com
Coming Soon! "Oracle Insight - Tales of the OakTable"
"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"
------------------------------------------------------------
Received on Tue Dec 28 2004 - 06:52:13 CST
