Re: locally managed tablespace & dictionary managed tablespace

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Sat, 4 Jan 2003 14:49:38 +1100
Message-ID: <UHsR9.16323$jM5.44436@newsfeeds.bigpond.com>

"Joel Garry" <joel-garry_at_home.com> wrote in message news:91884734.0301031629.6f1ab745_at_posting.google.com... > "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<TKpQ9.13623$jM5.38635_at_newsfeeds.bigpond.com>...
> > "Joel Garry" <joel-garry_at_home.com> wrote in message
> > news:91884734.0212311144.15b9ed33_at_posting.google.com...
> > > "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
> > news:<BbQP9.12506$jM5.34484_at_newsfeeds.bigpond.com>...
[snip]> > Hi Joel:
> >
> > Well, I was only giving an overview, of course, and not getting into the
> > detail. And there have been reports of several tens of thousands of
extents
> > posing a problem for performance, even with LMTs. And for myself, I
would
> > certainly make use of the 'alter table move' command to fix a segment
that
> > was acquiring that many extents (and bump it into an LMT with a
> > commensurately bigger uniform size).
> >
> > But I'm a bit confused about the Metalink note you quoted, since that
was
> > referring to SMON auotmatically coalescing DMTs in version 8.0.4 -which
> > seems a tad out of date these days.

>
> Well, note the revision date, and that it applies to 7.3 and above,
> and this little tidbit (regarding processes that die before cleaning
> up after themselves, including CREATE INDEX and DROP <object>):
>
> 61997.1> Use tablespaces of type TEMPORARY. Sort segments in these
> 61997.1> tablespaces are not cleaned up. This reduces contention
> 61997.1> on the ST enqueue and also reduces CPU usage by SMON
> 61997.1> **UNLESS** the database is shutdown and restarted. If
> 61997.1> TEMPORARY type tablespaces are in use then SMON will
> 61997.1> clean up its segments after startup following a shutdown.
> 61997.1> In this case large numbers of extents can be a severe problem
> 61997.1> as SMON will not service user "sort segment requests" until
> 61997.1> the cleanup is complete.   If the cleanup is to take a long
> 61997.1> time users will not be able to perform sort operations.
>
> And goes on to describe how to change everyone's temp area so they can
> sort.
>
> Are you saying that doesn't apply to above 8.04?
>

Well, from what I read, the problem only relates to coalescing temporary tablespace which is dictionary managed. I'll stick my neck out here and say that LMT temporary tablespace (ie, 'create temporary tablespace blah tempfile X' rather than 'create tablespace blah datafile X temporary') doesn't suffer from the same problem.

I'm sure Jonathan can chop it (ie, my neck) off if need be.

> Also the bit about maxextents unlimited as an incorrect default might > concern some. Remember, most of the world is not on 9iR2.

Well, a certain Richard Foote wrote here several months ago, arguing that the lack of a maxextents clause in LMT was a problem, largely (if I remember correctly) because the lack of a 'brick wall' across the tracks means that the segment train can keep on rolling with nothing to stop it. I certainly remember a case where a developer of my acquaintence happened to code an infinite loop, and when I got back after a long weekend, discovered that my table had acquired something like 178,000 extents. Having a maxextents set would have cured that problem (by inducing an out of space error and thus breaking the loop).

But again it comes back to the fact that in LMT, a huge number of extents is not a a particular problem. 178,000 of 'em would be, still, I think. But 2000 or 3000 wouldn't be.

So, except for dealing with rogue developers, I've never seen a huge need for maxextents myself, even in DMT. With LMT, I can understand why it went completely.

Regards
HJR
> >
> > Automatic coalescing of DMTs was indeed something to be very concerned
> > about, and one of the main arguments for making sure you always
specified a
> > default storage clause with PCTINCREASE set to zero. But (stands ready
to be
> > corrected) it's not an issue with LMTs as such.

>
> Agreed, but it becomes an issue because of the changes made going to
> LMT, which may or may not be known to be inappropriate.
>

> >
> > But all of that seems rather to encourage the use of LMTs, not worry
about
> > the number of extents issue that a wildly inappropriate use of them
might
> > bring.

>
> Agreed, that is why it is a nit-pick.
>

> >
> > Best regards
> > HJR
> >
> >
> >
> >
> >
> > > Basically, SMON can spin the heck out of your CPU's when coalescing.
> > > Not normally a problem, but can be a bit of a mess when you can't
> > > shutdown, or shutdown abort and then can't come up... just be aware,
> > > as the rest of the post illustrated, _some_ thought must be put into
> > > the defaults.
> > >
> > > For those with metalink access, this is pretty handy (if it works):
> > >
> > >
> >

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab
> > ase_id=NOT&p_id=131854.1
> > >
> > > jg
> > > --
> > > @home is bogus.
> > > For a good time, chmod 222 /etc/passwd; finger; whoami

>
> jg
> --
> @home is bogus.
> If I had 10 billion dollars, I'd probably be worse than Larry.

Received on Fri Jan 03 2003 - 21:49:38 CST