Re: Large temp tablespace

"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 - 03:52:50 CDT