Re: locally managed tablespace & dictionary managed tablespace

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<UHsR9.16323$jM5.44436_at_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.

Ahh, that is why this dead horse keeps zombifying. Do you have a source on that? From the note, one would infer the potential problem is due to TEMPORARY with growing sorts, as the sort segment never shrinks while the instance is up.

>
> 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
Thanks guys.

jg

--
@home is bogus.
Substitute LUN's for disks and OFA still works.