Re: Large temp tablespace

Coalesce is not even a valid option for proper temporary tablespace! And even if it was, coalescing has precisely zilch to do with being able to reduce the size of data files.

Honestly!

HJR "Đuro Dretvić" <djuro.dretvic_at_infodom.hr> wrote in message news:9pf3o9$3kr$1_at_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 - 15:22:30 CDT