Re: ORA-03232 and HASH_JOIN_ENABLED

I wonder if it's simply because your tablespace is seriously fragmented even though the total free space is still large. select max(bytes), tablespace_name from dba_free_space group by tablespace_name can tell you that.

According to Oracle manual, the parameter HASH_MULTIBLOCK_IO_COUNT is shown as 0 in v$parameter and Oracle strongly suggests you not manually change it. Instead Oracle computes the value internally dynamically. So the ACTION part of oerr ora 3232 doesn't say you need to change that and only says look at your tablespace NEXT extent size.

A hash join does not do sorting. It may help if you can post the execution plan.

Yong Huang
yhuang_at_indigopool.com

Yuri McPhedran <esiyuri_at_my-deja.com> wrote in message news:8oo2ag$6lc$1_at_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 - 09:42:06 CDT