Re: temporary temp vs permanent temp

From: Mladen Gogala <mgogala_at_earthlink.net>
Date: Tue, 22 Jan 2002 13:58:30 GMT
Message-ID: <pan.2002.01.22.08.58.29.602152.1096@earthlink.net>

On Mon, 21 Jan 2002 09:40:09 -0500, Mark D Powell wrote:

> "G.Ong" <onggs_at_acslink.aone.net.au> wrote in message
> news:<FPS28.6783$N31.292102_at_ozemail.com.au>...

>> Hi,
>> 
>> Our system is 8.1.6.3 on Solaris 2.7.
>> 
>> We used to used to have temporary segments on temporary tablespace (ie
>> those created : create tablespace xxx temporary ). However, because of a
>> number of unable to extend temporary segments, the system DBA recommend
>> using temporary segments on permanent tablespace (because the sort
>> segments are immediately released).
>> 
>> Questions:
>> 1) Is this a valid decision ?
>> 2) If a hash join occurred involving a large table, does it use the same
>> sort segment created for sorts ? (In this case, assuming a temporary
>> temp is used.)
>> 
>> TIA,
>> Ghee

>
> Actually, Ghee, a true temporary tablespace is created using: create
> temporary tablespace temp
> tempfile 'xxxx'
>
> When you use "create tablespace temp temporary" you have told Oracle to
> store your temporary segments in a permanent tablespace but to allocate
> one temporary (sort) segment for the instance and to manage its contents
> locally rather than use the system ST lock to manage separate segments
> for every user which is what "create tablespace temp [permanent]" does by
> default.
>
> Now if you are using a true temporary tablespace there have been a couple
> of bugs that have prevented Oracle from releasing the sort extents used
> by one session for reuse by other sessions and conversion back to 'temp
> temporary' could resolve them, but if you are type 'temp temporary'
> converting to 'temp permanent' is unlikely to fix your problem.
>
> My best guess is you either need more sort space, larger extent sizes, or
> both in order to support your application. And yes Oracle sort-merge
> operations and hash joins use the same temporary segment(s) used to
> support sorting.
>
> Here are two links to articles at the cooperative FAQ on the subject of
> temporary segments that may be of help:
>
> http://www.jlcomp.demon.co.uk/faq/sort_user.html
> http://www.jlcomp.demon.co.uk/faq/fulltemp.html
>
> -- Mark D Powell --

The problem is that blocks in the permanent tablespace are rpotected by while the logging mechanism does not protect temporary tablespaces. That means that using a permanent tablespace will strongly increase your IO . I believe that by distributing temporary tablespaces you can protect yourself from the ill effects of bugs. In my experience, temporary tablespaces are functioning properly. Received on Tue Jan 22 2002 - 07:58:30 CST