Re: ORA-4030

Sybrand Bakker <gooiditweg_at_nospam.demon.nl> wrote in news:nbuc5v4pdi8vs8268q2h7kbfmvi45der3h_at_4ax.com:

> On 21 Feb 2003 18:52:16 GMT, Chuck <chuckh_at_softhome.net> wrote:
> 

>>It was my understanding that the 10m was only allocated while sorting
>>and released back to the O/S after the sort was finished and all rows
>>were returned. This is how it's documented in the Oracle 8i
>>reference...
>>
>>"SORT_AREA_SIZE specifies in bytes the maximum amount of memory Oracle
>>will use for a sort. After the sort is complete, but before the rows
>>are returned, Oracle releases memory down to the size specified by the
>>SORT_AREA_RETAINED_SIZE parameter. After the last row is returned,
>>Oracle releases the remainder of the memory."
>>

> No, there is not a bug. You should discriminate between sort_area_size
> and sort_area_retained_size. IIRC the latter parameter by default is
> equal to the former, so unless you set it (inducing paging), the
> memory won't deallocate at all (inducing paging).

This is quite different than what is described in the 8i manuals which indicates that if I set s_a_s to 10m and s_a_r_s to 1m, it will dynamically allocate up to 10m when I try to do a sort (not upon connection), deallocate all but 1m when the sort is done but while returning rows to the app, and finally free up ALL sort memory when the last row is returned. Is the documentation wrong or am I misunderstanding what appears to be spelled out pretty clearly?

> 
> IMO, there should be no reason at all to set the parameter to 10M and
> you would be better off to lower it to 1M. You seem to follow -like
> many many others- the 'more is better' tuning principle. After all,
> you have NO guarantee the number of sorts will be limited to 3 or 4.

At 1m, I get virtually no hash joins on large queries. They all end up being sort/merge or nested loops. If I set s_a_s at the session level for testing purposes to 10m the execution plans change and I start replacing 90% of the other joins with hash joins. I also start seeing my queries return in 1/4 of the time. I have tried smaller sizes but 10m seems to be the threshold at which I start seeing marked improvements in the response time of large queries. Unfortunately I cannot set it at the session level in the production code as we don't have the source code for the app. I could try setting hash_area_size instead of letting it default to 2x sort_area_size, but I thought by increasing sort_area_size it would also eliminate some of the disk sorts and kill two birds with one stone. The documentation does not say anything about when hash_area_size memory is allocated or deallocated. Received on Fri Feb 21 2003 - 13:45:51 CST