Re: ORA-03232 and HASH_JOIN_ENABLED

Thanks for the "heads up", I really hope that somebody can answer this. Mike

"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 - 13:08:09 CDT