Re: Large temp tablespace

Thanks Howard

Yo the man, I am a backup DBA that get pushed to the front when a real DBA disappear and with answer like yours I look very smart.

Thanks
Mo

"Howard J. Rogers" wrote:

> "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 - 19:49:42 CDT