Re: locally managed tablespace & dictionary managed tablespace

From: Joel Garry <joel-garry_at_home.com>
Date: 9 Jan 2003 11:46:20 -0800
Message-ID: <91884734.0301091146.ebf76df@posting.google.com>

joel-garry_at_home.com (Joel Garry) wrote in message news:<91884734.0301061738.2eaa09ba_at_posting.google.com>...

Sorry to be replying to myself, but this has moved out of the realm of the academic for me...

> "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

A source is Note:50380.1

> is due to TEMPORARY with growing sorts, as the sort segment never > shrinks while the instance is up.

What I'm seeing is the result of a possible runaway sort with LMT temp ts. The big question is, what do you do with a full ts (besides shutting down the production database)? Right now, only 1 extent is being used, most sorts are in memory, but the sort segment is many gigabytes. The drop_segment event described in Note:1070504.6 does not seem to be working (the note is unclear - does it not work if _any_ segment is locked?). 8.1.7 on hp-ux 11.

More generally, how do you decide how big to make the sort ts? It has to be a judgement call, but how can you make such a judgement without knowing ahead of time how much sort an application with new reports will reasonably want? How do you keep runaway sorts from determining the size (especially if you have too much data for a complete test database)? I know which report did this, it was the only thing running and got an ora 1652, but running it a second time (with all the extents already allocated) completed (maybe Oracle is slow [at least 3 hours] about deallocating unused extents from a previous sort?). Jonathan referred to some bugs in http://www.jlcomp.demon.co.uk/faq/fulltemp.html does anyone know which bugs those are? Oh, and thanks Jonathan for explaining the bit about why I was wasting an almost-extent at the end of the ts.

> 

> >
> > 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

jg

--
@home is bogus.
There's only so many hours in a weekend.

Received on Thu Jan 09 2003 - 13:46:20 CST