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: Large temp tablespace

Re: Large temp tablespace

From: Ðuro Dretviæ <djuro.dretvic_at_infodom.hr>
Date: Wed, 3 Oct 2001 15:28:19 +0200
Message-ID: <9pf3o9$3kr$1@sunce.iskon.hr>


Use command to coalesce the tablespace temp and reduce it's size, immediately...

Regards, Djuro

"Howard J. Rogers" <howardjr_at_www.com> wrote in message news:3bbad173_at_news.iprimus.com.au...
>
> "Mo" <ranamo_at_bellatlantic.net> wrote in message
> news:3BBA6EE9.E9D7940D_at_bellatlantic.net...
> > Using Oracle 8.1.7
> >
> > We have a database around 6 gig in size (the main tablespace) and using
> > OEM I see that the Temporary tablespace is around 5 gig even when no one
> > is runing any query.
> > I have the impression that a temporary tablespace will deallocate
> > segment when not used and
> > it will only be used when Sort_area_size is not large enough to hold
> > query result.
>
> This is constantly asked: temporary tablespace is used to house temporary
> segments, which are created when the sort_area_size is too small to
> accomodate the requested sort. At the end of the sort, the extents of
that
> temporary segment are *NOT* dropped, but are marked for re-use by other
> sorts.
>
> Net result: if lots of segments get created, and cause the temp datafile
to
> extend, it stays extended.

> It's designed that way so that new sorts don't have to go to the trouble
of
> allocating their own temporary segments (which is relatively slow). Your
> job as a DBA is to determine the aggregate size of the largest set of
> concurrent sorts swapping to disk, double it for safety, and create a
> tablespace of that size. You then sit back and *expect* that entire
> tablespace to be filled with temporary segments. If the tablespace is not
> near 100% full, then you've done something wrong!
>
> >
> > is that large Temporary tablespace is no problem?? and how to fix it if
> > it is a problem?
>
> If you really want to force the deallocation of temporary segments *(and
you
> really don't)* then alter tablespace temp offline; followed by alter
> tablespace temp online; should do it. Otherwise, a shutdown and startup
> will do the deed.
>
> >
> > We have the Sort_area_size = 65k, is that sound small for application
> > that uses SQL heavily
> > our server is 1 CPU, 512M RAM, NT4.
>
> 65K is yet another ridiculous Oracle default. But there's no way of
saying
> that it is, in and of itself, too small (though your temporary tablespace
> troubles suggests that it is way too small). I always go for 1Mb myself
for
> starters, and then tune it. If you run utlbstat and utlestat, and look in
> report.txt, you'll see statistics for the ratio of sorts in memory to
sorts
> on disk. Ideally, that ratio should be around 95%. If it's not, then
it's
> fait to say sort_area_size is too small.
>
> Don't go beserk, though: set sort_area_size to something daft like 100Mb
in
> the init.ora and every user by default potentially gets to chew up 100Mb
of
> memory. Remember that sort_area_size is dynamically adjustable with an
> alter session command, so there is scope for coding an application that is
> about to run a huge and complex report to, as its first action, up
> sort_area_size (and as its last action puts it back to the original
value).
>
> Regards
> HJR
>
>
>
>
> >
> > I have a question about Caching tables and Hints, I guess I'll ask
> > later.
> >
> > Thanks
> > Mo
> >
>
>
Received on Wed Oct 03 2001 - 08:28:19 CDT

Original text of this message

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