Re: ORA-4030

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?

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?

> 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. Received on Fri Feb 21 2003 - 12:52:16 CST