ORA-03232 and HASH_JOIN

From: Yuri McPhedran <esiyuri_at_my-deja.com>
Date: Fri, 01 Sep 2000 11:01:45 GMT
Message-ID: <8oo2ag$6lc$1@nnrp1.deja.com>

Hi there Oracle users,

This posting is for information only - I have already found a fix to the problem but it wasted some time and I hope that by sharing the details it will make life a bit easier for some of you.

I was given a fairly complicated select statement across six tables which was causing an "ORA-03232: unable to allocate an extent of 11 blocks from tablespace 3" error every time it was run.

We are using Oracle 8iR2 (8.1.6) on NT, though I am not convinced that this makes much difference.

The Oracle documentation has this to say...

ORA-03232 unable to allocate an extent of string blocks from tablespace string
Cause: An attempt was made to specify a HASH_MULTIBLOCK_IO_COUNT value that is greater than the tablespace's NEXT value. Action: Increase the value of NEXT for the tablespace using ALTER TABLESPACE DEFAULT STORAGE or decrease the value of HASH_MULTIBLOCK_IO_COUNT.

Well, the tablespace "3" message isn't very helpful, and checking V$PARAMETERS I see that HASH_MULTIBLOCK_IO_COUNT=0, so I can't exactly decrease it. There is plenty of free space in every tablespace, so I don't understand why I get this sort of error. Also, the error comes back instantly which suggests that it is not a case of the TEMP tablespace being exceeded. In any case, there is no "ORDER BY" or other clause that would result in a sort.

A quick EXPLAIN PLAN on the statement reveals that there are several HASH JOINS taking place. I therefore decide to rewrite the statement and/or use hints so that a different join method is used. The optimizer stubbornly refuses to do anything other than hash joins, so I have another search in the manuals and come up with the HASH_JOIN_ENABLED init.ora parameter. This can be set at a session level, so I issue a "alter session set hash_join_enabled=false;" statement, then re-run the query & it works fine.

So, that's the fix, but if anybody can explain *why* I got the problem in the first place I would be very interested!

--
Regards
Yuri McPhedran


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Fri Sep 01 2000 - 06:01:45 CDT

ORA-03232 and HASH_JOIN_ENABLED