Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: ORA-4030

Re: ORA-4030

From: Chuck <chuckh_at_softhome.net>
Date: 25 Feb 2003 20:20:31 GMT
Message-ID: <Xns932D9C101CE07chuckhsofthomenet@130.133.1.4>


yong321_at_yahoo.com (Yong Huang) wrote in
news:b3cb12d6.0302221546.3d181670_at_posting.google.com:

> Chuck <chuckh_at_softhome.net> wrote in message
> news:<Xns9329721F15F91chuckhsofthomenet_at_130.133.1.4>...

>> 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?

>
> Can you look at v$sysstat and v$sesstat for session uga memory and
> memory max, session pga memory and memory max? Maybe you can identify
> other sessions that take too much private memory. Also watch the
> session that gets ORA-4030 for these statistics. When the error
> occurs, does the OS show severe paging or swapping?
>
> Yong Huang

Sure. I monitored a session who's sort_area_size and sort_area_retained_size were set to 50m for the purposes of this test. Prior to sorting 1m rows the session pga memory was 200k. During the sort, it rose to 56m. After the sort completed and all rows were returned, it stayed at 56m. Looks like nothing is not being released.

I repeated this test with the sort_area_size at 50m but the sort_area_retained size at 1m. I got exactly the same result. Received on Tue Feb 25 2003 - 14:20:31 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US