Re: Temp Tablespace

Thanks for the detailed hints. I had already increased the sort_area_size but it didn't seem to help. Every once in a while, the temp tablespace gets 100% filled up and won't restore until a database restart.

Leo

Rniemic wrote:

> Any ORDER BY, GROUP BY, DISTINCT and other sorting operations will cause
> temporary tablespace use. Also, the creation of indexes will also cause
> temporary use.
>
> For the statement that is causing the heavy use you can "alter session set
> sort_area_size = ... (some large number) and then run the statement and the
> sorts will be done in O/S memory.
>
> Setting the sort_area_size in the init.ora file higher will cause the sorts
> (any that need up to the amount of the setting) to be performed in memory
> instead of in the temporary tablespace making whatever you are sorting MUCH
> faster. But beware, the sort_area_size is a per user parameter (allocated only
> when a sort is needed) and is allocated for each user doing a sort (allocated
> from the O/S memory outside the SGA ... unless you are using MTS and then it is
> from inside the SGA). If you set this too high (and a lot of users need to do
> sorts) you can run out of O/S memory ... that's why I suggested the alter
> session command if you know the offending user. Also, if you set the
> sort_area_size, make sure you set the sort_area_retained_size, this is the size
> the memory will be "shrunk" back to after the sort is complete.
>
> Also, if you have a nested statement requiring TWO sorts, each will use the
> value of the sort_area_size potentially using more memory than you expected.
> Hope that helps... If this is still unclear...I wrote it fast...send me a
> message on the unclear part.
>
> Rich Niemiec
> Oracle Performance Tuning Tips and Techniques, Oracle Press
> ISBN: 0078824346
Received on Mon Jun 07 1999 - 00:00:00 CDT