Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Locally Managed Tablespaces - any cons???

Re: Locally Managed Tablespaces - any cons???

From: Howard J. Rogers <howardjr_at_www.com>
Date: Sun, 12 Aug 2001 10:38:19 +1000
Message-ID: <3b75cf23@usenet.per.paradox.net.au>

"Robert Fazio" <rfazio_at_home.com.nospam> wrote in message news:xsjd7.94488$EP6.23254498_at_news1.rdc2.pa.home.com...
>
>
> --
> Robert Fazio
> Senior Technical Analyst
> dbabob_at_yahoo.com
>
> "Howard J. Rogers" <howardjr_at_www.com> wrote in message
> news:3b75b62f_at_usenet.per.paradox.net.au...
> >
> > "Robert Fazio" <rfazio_at_home.com.nospam> wrote in message
> > news:xNYc7.91101$EP6.22111132_at_news1.rdc2.pa.home.com...
> > > From what I have read. Oracle doesn't allow for the system tablespace
 to
 be
> > > LM. Infact, if you read the docs on dbms_space_admin, you will see
 that
 it
> > > won't even allow system to be converted, but the say they will support
 it
 in
> > > the future. The other two RBS and TEMP probably shouldn't be either.
 TEMP
> > > for example is already handled differently if set to type temporary,
 plus
> > > you want the ablity to change the extent management at a later time,
 if
 you
> > > choose LM, then you can't. RBS has the same issues.
> > >
> >
> > I disagree, I'm afraid. Locally managed temp tablespaces are strongly
> > supported and encouraged (indeed, in the 8i DBA course, the syntax
 example
> > for creating a TEMP tablespace is pure LMT... they don't even mention
 the
> > old, DMT, way of doing it.
> >
>
> Sorry, haven't been to DBA course in a while. That may be the case, but
> that doesn't mean
> that I agree with it. First of all Temp is already sort of locally
managed.

No, it's not. The extent allocations that take place within a dictionary managed TEMPORARY tablespace are still handled by UET$ and FET$. Now, the key word TEMPORARY means we don't *continually* have to allocate and deallocate extents, fair enough... but initial sorts after a fresh startup still have to, and if there are multiple initial sorts, then contention for the data dictionary would be an issue.

> There is no dictionary aspect to temp segments from what I do recall from
 my
> past DBA classes.

Not true, if it's dictionary managed.

>That is why you should have your temp tablespace as
> temporary.
>

No, what that does is to *minimise* the visits to the data dictionary. Doesn't eliminate them entirely.

> Consider the tuning aspect for both RBS and TEMP. Temp should always be a
> multiple of the sort_area_size,

Not in dictionary managed tablespace, it shouldn't. It's a multiple of the sort_area_size, PLUS one block. But yes, in locally managed tablespace, we can dispense with that extra block.

> and they should have a fixed extent size.Tough to change if they are
 locally
> managed with a fixed extent size thats wrong.

True enough... which is why extent sizing in locally managed tablespaces (of any sort) is very important to get right... but that's not really an argument for dispensing with their services altogether! In any event, stuffing up your extent sizes with dictionary managed tablespace is not exactly easy to fix for existing segments, either. The point you are making is: choose extent sizes wisely. That's true for either sort of tablespace.

Actually, it's more complicated than that in any case: sort area size is (at least in 8i) session-modifiable. Which means that a one-size-fits-all temporary tablespace is not a good idea in any event, and whatever type of tablespace you go for. If your code goes around modifying sort_area_size, it may need to alter the temporary tablespace to be used, too.

And once you admit of that possibility, you rationale for dictionary managed temp tablespaces starts fraying at the edges.

>Same goes for your RBS.
>

That one I don't follow. RBS needs a fixed extent size: yup, that's why (thank God!) PCTINCREASE is 0 for rollback segments and can't be changed. What does locally managed tablespace absolutely guarantee... er, fixed extent sizes.

> My point isn't that it is a bad idea, I am just a little more cautious
 with
> something that can effect my system that dramatically. I do use LMT, but
 I
> am just not ready to give up the control for those two just yet. Maybe
> soon.

Well, don't upgrade to 9i, then! Rollback segments cease to exist (unless you *want* to work in the old way), to be replaced by Undo Segments that manage themselves. Provided they are created in locally managed tablespace.

>
> > The same is not true for Rollback Segment tablespace, but even there,
 I'd
> > suggest that they are strongly recommended.
> >
> > Consider what LMTs are really good at: making massive extent allocation
 and
> > de-allocation of consistent sizes a trivially easy (and speedy) task.
> > That's a prime requirement for rollback segments, since they have a
> > propensity to grow and shrink all the time. It's also initially true
 for
> > TEMP tablespace, as the temp segments are created by initial sorts.
> >
> > The only restriction of RBS tablespace as locally managed is that you
 must
> > first create a rollback segment in dictionary managed tablespace (any
 one
> > will do, even SYSTEM) to handle the DML that will take place to the new
> > tablespace's bitmap when you create your first locally managed rollback
> > segment. Once that first LMT rollback segment is online, the starter
> > segment can be offlined and dropped, and all others can be created
 normally.
> >
> > Regards
> > HJR
> >
> >
> >
> >
> > > Other than some early bugs related to the LM tablespaces, I do agree.
> > >
> > > --
> > > Robert Fazio
> > > Senior Technical Analyst
> > > dbabob_at_yahoo.com
> > >
> > > "Howard J. Rogers" <howardjr_at_www.com> wrote in message
> > > news:3b745321_at_usenet.per.paradox.net.au...
> > > >
> > > > "Kenneth Koenraadt" <plovmand_at_hotmail.com> wrote in message
> > > > news:3b7197c5.7098447_at_news.mobilixnet.dk...
> > > > >
> > > > > On Tue, 7 Aug 2001 21:20:37 -0600, "Ron Gardiner"
> > > > > <rongardiner_at_powersurfr.com> wrote:
> > > > >
> > > > > >New for 8i (I believe) is locally managed tablespaces...
> > > > > >
> > > > > >From what I've read, sounds like there is no reason NOT to use
 them.
 Can
> > > > > >they be used for all types of TS?... rollback segs, temporary,
 how
 about
> > > > > >system itself?
> > > > > >
> > > > > >Any feedback would be appreciated!
> > > > > >
> > > > > >
> > > > > >
> > > > > Hi Ron,
> > > > >
> > > > > You cannot create a locally managed SYSTEM tablespace on database
> > > > > creation. But you can convert it to LM later with a supplied
 package.
> > > > >
> > > > > The SYSTEM tablespace and the tablespaces for rollback segments
 must
> > > > > have the same extent management type, so if you convert the SYSTEM
> > > > > Tablespace to LMT, rememer to convert your RBS tablespace(s) to
 LMT
 as
> > > > > well.
> > > > >
> > > >
> > > > This is just not true.
> > > >
> > > > Firstly, whilst you may be able to convert SYSTEM into a LMT, it's
 strongly
> > > > advised not to. You may have noticed that SYSTEM also has 50%
 PCTINCREASE,
> > > > which is generally bad, and is autoextensible, which is also usually
> > > > considered not terribly efficient... but the point is, SYSTEM breaks
 all
 the
> > > > rules, and is best left to do so. LMT/DMT is just another example.
> > > >
> > > > Secondly, you appear to suggest that if SYSTEM is DMT, you must have
 DMT
> > > > rollback segment tablespace. Absolutely not true. Provided there
 is
 one
> > > > non-system rollback segment created (and brought online), you can
 create
 a
> > > > LMT rollback tablespace, and create (and online) one LMT'd rollback
> > > > segment... after which, the original DMT'd rollback segment can be
 dropped,
> > > > and all others created without a problem. And all of that can be
 done
> > > > perfectly well with a SYSTEM tablespace that is merrily chugging a
 way
 a
 s
> > > > dictionary-managed.
> > > >
> > > > Incidentally, the SYSTEM tablespace in 9i is still dictionary
 managed,
 and
> > > > there is still no provision for creating it Locally Managed... yet
 another
> > > > indication that locally managed SYSTEM tablespace is not advised,
 nor
> > > > necessary.
> > > >
> > > > Regards
> > > > HJR
> > > >
> > > >
> > > >
> > > >
> > > > > Regards,
> > > > > Kenneth Koenraadt
> > > > > Systems Consultant
> > > > > Oracle DBA
> > > > > plovmand@<no-spam>hotmail.com
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Received on Sat Aug 11 2001 - 19:38:19 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US