Re: TEMPORARY tablespace problem -- newbie problems

Rob,

Make sure that you temporary tablespace (Only applies from Oracle 7.3 onwards) is of type "temporary" therefore exclusively being used for sorts rather than of type "permanent".

By making it of type "temporary" eliminates serialization of space management operations involved in the allocation and de-allocation of sort space.
All operations that use sorts (including joins, index builds, ordering, grouping etc.. ) benefit from temporary tablespaces.

Multiple transactions which need a sort on disk, can share the same sort segment, however, they cannot share the same extent. The sort segment expands by allocating new extents. The sort extents are NOT de-allocated while the instance is running, but are marked as FREE and can be RE-USED as required. Therefore, the sort segment grows to a certain steady state.

Because extents do not have to be allocated and de-allocated after each operation, you will benefit an overall database performance increase.

To monitor sort usage look at the v$sort_usage and v$sort_segment views.

The background process SMON actually de-allocates the sort segment after the instance has been started and the database has been opened. Thus, after the database has been opened, SMON may be seen to consume large amounts of CPU as it first de-allocates the (extents from the) temporary segment, and after that performs free space coalescing of the free extents created by the temporary segment cleanup. This behavior will be exaggerated if the temporary tablespace, in which the sort segment resides, has inappropriate (small) default NEXT storage parameters.

I hope this helps.

Roger.

"Rob Diaz" <rdiaz_at_ebudgets.com> wrote in message news:rww15.10246$pd4.229690_at_news-east.usenetserver.com...
> Hi. I am new to Oracle and am having a problem with the Temporary
> tablespace. It seems that Oracle is never reusing any of the space it
> allocates for a process. The reason I say this is that we keep running
 out
> of space on the tablespace (if we allow unlimited extents, it fills the
> disk). Some of the queries we run involve long, complicated joins on
> millions of records, which should (and does) allocate a large temporary
> space.
>
> Anyway, the users keep getting locked up due to the lack of temporary
 space.
> Is there some setting I have to use to tell oracle to free up the space
 when
> it finishes the query? I have tried using the "alter tablespace temporary
> coalesce" command and it does not seem to help.
>
> My assumption was that the temporary tablespace was, in fact, temporary
 and
> that when a query or process completed it would release any objects that
> Oracle created, thereby freeing the space for use by another process. Is
> this wrong?
>
> Any help would be appreciated, as I am being hammered by a large client on
> this.
>
> Thanks.
>
> Rob
>
>
Received on Wed Jun 14 2000 - 00:00:00 CDT