Re: How to tune SQL to avoid ORA-03232 ?

Jan

The minimum size of the next extent must be AT LEAST block size * hash_multiblock_io_count. Guessing at these values you could try 256K as a start point (say 8K block * 8 or 16) as this should be safe. This should stop the error.

Next you need to sort out the performance so that it doesn't run forever. Try the suggestions from Billy and myself on removing the disk work -this is either sorting or most likely from the issue you have - storing temporary hash work space to disk that's too big for Oracle to retain in memory. This should be reduced or eliminated if you "fix/tune" the SQL. Also be aware that you need to check that you have indexes on the appropriate columns (otherwise Oracle resorts to hash joins - not that hash joins are a bad thing in the right situations). The FIRST_ROWS hint tends to favour NESTED JOINS rather than HASH JOINS or SORT MERGE JOINS - see explain plan, so this suggests that you might be missing indexes.

Can you list the indexes you have on these tables for us. In addition the explain plan sounds way too long for this query (should be maybe 50 lines). Are you sure that you haven't got two plans in the explain plans PLAN_TABLE for the same statement id ? This depends on how you're generating the plan.

Hope this helps
Andy

"Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message news:aoj7cc$mrg8m$1_at_ID-152732.news.dfncis.de...
> Hi Kenneth,
>
> <kenneth.koenraadt_at_no-spam.hotmail.com> schrieb im Newsbeitrag
> news:3dad12b3.601765_at_news.mobilixnet.dk...
> > Hi Jan,
> >
> > Please post your execution plan. Hard to find your way through the
> > jungle if you don't know where the jungle is....
> Sure You are right, and I would liek to post it, but what I saw after
> EXPLAIN PLAN was the most strange plan I have ever
> seen: it consisted of 5000++ lines (so, useless to post - it was bad
anyway
> ...).
> >
> > 2 things, though, are obvious, even without the execution plan :
> >
> > a)
> >
> > The predicate :
> >
> > To_Number(Substr(To_Char(P.Special_Nr),2,4)).
> >
> > Effectively hinders usage of indexes on the column, unless you have a
> > FBI (Function-based index).
> Ack. And BTW, I did not know _that_ abbreviation yet. >D
> >
> > b) The extent size of 32k is very small, certainly too small for such
> > a huge sort.
> So, what would You suggest ? 100k ? 1M ?
> >
> >
> > - Kenneth Koenraadt
>
> In the meantime, my partner began to rewrite the query allover (he is
better
> in SQL than me ...),
> in order to have smaller temporary tables.
>
> But it seems to me a problem that is more general than depending on this
> query. I observed the same
> error tow times in the past, and I donīt know what else than rewriting SQL
> could be done to avoid ORA-03232.
>
> Is this a common parameter setting error ?
>
> Yours, Jan
>
>
Received on Wed Oct 16 2002 - 03:42:10 CDT