Re: Temp Tablespace

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 - 12:40:52 CDT