Re: ORA-4030

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

>Sybrand Bakker <gooiditweg_at_nospam.demon.nl> wrote in
>news:srrc5vcde59o0j42jtkts0jdbiv9l2h8vj_at_4ax.com:
>
>> 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).

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.

>> Your Oracle process gets way too much memory. You shouldn't allocate
>> more than one third of physical memory. Allocating more will result in
>> excessive paging. Also by design of the Winblows O/S no process will
>> get more than 2G *ever*, even if you have Advanced Server installed.
>>
>> Hence, you have basically 2 options
>> 1 start reading the Performance Tuning Guide and tuning your
>> application NOW (recommended, you shouldn't even consider increasing
>> the memory usage of Oracle to 1800M)
>> 2 switch to a *real* O/S
>
>I'd love to run this database on Unix, but this is a 3rd party
>application and they will only support it if the database and app are
>both running on Windows.

So you have now several good reasons to either throw the app out of the window and/or sue them
- If the vendor doesn't allow you to run the database on a different platform, you can be quite sure they don't know a damn about Oracle, and they force you to stay locked in a propietary enviroment. I would bet the app was originally developed for Microsucks Sql server, in that case you would do better to throw away Oracle, because the app is not going to run efficiently against Oracle EVER. - They've already demonstrated their application is crap, because of the huge memory footprint, and still it is NOT working without problems. My guess is you have cranked up db_block_buffers and shared_pool_size several times and your app still doesn't work. Only a little snooping the shared pool, noticing the number of hardcoded literals and the number of full table scans going on should provide enough exhibit for a perfect cause in court.

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Fri Feb 21 2003 - 13:18:50 CST