Re: locally managed tablespace & dictionary managed tablespace

"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>...
> > > You don't have to worry
> > > about the number of extents acquired by a segment, so you don't have to
> sit
> > > there for a week worrying about whether you should go for 64K extents or
> 64M
> > > ones. If you pick 64K ones, and the table goes beserk acquiring
> thousands of
> > > extents as a result, who cares? LMTs certainly don't.
> >
> > All the rest of the post was excellent, but there's a bit of a
> > nit-pick with the above. See metalink Note:61997.1.
>
> 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?

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

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