Re: Use of TEMP tablespace other than sorting

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Mon, 3 Mar 2003 09:03:02 -0000
Message-ID: <3e631a46$0$6294$ed9e5944@reading.news.pipex.net>

"Anurag Varma" <avdbi_at_hotmail.com> wrote in message news:v660f8jlmv0q83_at_corp.supernews.com...
> Decrease value of hash_area_size to 4M or drop recreate the temp
tablespace with uniform extent of at least 8M (or 8 * n M).
> I believe thats where the problem lies.
>
> Anurag

That, assuming Rick has a 16k block size, is exactly correct. Oracle is asking for 8m (512 16k blocks) for its hash join. I'd suggest at least initially that he try reducing the hash area, which seems somewhat large to me.

I must admit I've always thought that this behaviour, and the related behaviour of sorts if sort_area_size is set larger than the extent size in a uniform lmt temp tablespace is arguably a bug, perhaps by 11i Oracle will allocate MIN(sort_area_size,extent_size) for sorts.

--
Niall Litchfield
Oracle DBA
Audit Commission UK

--
Niall Litchfield
Oracle DBA
Audit Commission UK

Received on Mon Mar 03 2003 - 03:03:02 CST