Re: Use of TEMP tablespace other than sorting

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:b3v77u$jmo$1$8300dec7_at_news.demon.co.uk...
>
> The original poster says that after allocating
> 48 GIGAbytes of temp storage, he is unable
> to allocate an extent in TEMP for a particular
> six-table query against some reasonably large
> tables.
>
>
> TEMP is a locally managed tablespace with
> extent sizes of 4MB. - so the maxextents
> is unlimited - and the extent count is currently
> about 1,200. We have been told that the
> sort_area_size = 4M, which means the default
> hash_area_size is 8M. We have no information
> about the hash_multiblock_io_count.
>
>
> Can you explain why increasing the extent size
> and decreasing the hash_area_size will make
> any difference ? (Apart from the fact that decreasing
> the hash_area_size MIGHT just be enough to push
> Oracle from doing a hash join to using a different
> join mechanism).

<caveat>
Like Anurag the below experience was just that experience, it did however happen on 8.1.7.0. on 2000 and 8.1.6.x (where x is either 1 or 3) also on 2000. It is not verifiably tested
</caveat>

We had a problem query that gave the self same error message with a temp tablespace that was locally managed and with a uniform extent size that was lower than the sort_area_size. When sorting to disk (which happened every time on a small resultset because the query sorted by no fewer than 12 columns!) we got an error that Oracle couldn't allocate sort_area_size in blocks. This happened consistently, and with a huge temp tablespace and on a test system with only the problem query running. making sure that temp allocated extents that were multiples of sort_area_size eliminated the problem. I have never seen the message on 9i or 8.1.7.4.

I have also seen a similar message with a hash join but that was only on two occasions so perhaps I have assumed unwisely that the behaviour was similar.

I guess an appropriate test is required.

--
Niall Litchfield
Oracle DBA
Audit Commission UK