Re: ORA-4030

On Fri, 21 Feb 2003 20:18:50 +0100, Sybrand Bakker wrote:

>>> On 21 Feb 2003 16:13:07 GMT, Chuck <chuckh_at_softhome.net> wrote:
>>>
>>>>Oracle 8.1.7 on Windows 2000 Advanced Server.
>>>>
>>>>I recently increased the sort_area_size on a Win2k instance from 64k
>>>>to 10m. Shortly afterwards I started getting sporadic ORA-4030 (out of
>>>>process memory) errors. There should be plenty of memory. The box has
>>>>3g installed. The oracle.exe process only has 1800m allocated Anyone
>>>>else run into this? How'd you get around it?
>>>
>>>
>>> sort_area_size is a per user limit.
>>> This means all your connected sessions get 10M sort_area_size per
>>> session.
>>
>>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."
>>
>>Since this is primarily an OLTP app and there are never more than 3 or 4
>>sorts (or hash joins for that matter) going on I didn't think it would
>>present an issue. Is there a bug in the way Oracle allocates this memory
>>on Windoze?
>>

> 
> 
> 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).

Ouch! Ouch! Ouch! This is a horrible, horrible myth that should be put down at birth.

It is just simply incorrect. The sort_area_size is used during the initial phase of the sort, true enough. It starts at zero, true enough, and the sort_area_size setting is what it can maximally grow to. But, assuming a sort has written several sort runs down to TEMP, those separate sort runs have then to be merged into a completed sort. It is at this point, during the merge phase, that the sort area can shrink to whatever the sort_area_retained_size is set to. That is, S_A_R_S is there to define the amount of memory used during the merge phase. And (here's the thing!) at the end of the merge phase, the sort area returns to ZERO. Always. Nothing is 'retained' just in case the user fancies doing another sort.

Ever.

BUT... and here's the other thing, whilst the sort area shrinks back to zero, the memory is NOT returned to the operating system (and the bit of Oracle documentation that Chuck quoted doesn't say it will be returned to the O/S, either). Therefore, you could well get out of memory errors when no user has any sort area size: because it was *once* allocated.

Bear in mind that in Windows everything is running inside a single process anyway, and processes have a maximum amount of memory they can reference.

Also bear in mind that if you do any degree of parallel query, or non-parallel joins on unindexed tables, then it is possible that the execution plan will require two sorts to take place simultaneously, and that two lots of sort_area_size will therefore be required (and you should multiple THAT by the degree of parallelism if any). So you could be allocating far more sort_area_size than you were anticipating for this reason alone.

Regards
HJR Received on Fri Feb 21 2003 - 19:53:30 CST