Link to home
Start Free TrialLog in
Avatar of suhinrasheed
suhinrasheed

asked on

ORA-4030 OUT OF PROCESS MEMORY

Hi,
I have a 10.2.0.1 Oracle database on a windows xp professional machine,service pack 2,Intel dual core processor,2.33ghz cpu and 4 gb ram.

Total size of my development database is 10gb and almost 20 users connect to the db during day time
Total System Global Area  801112064 bytes
Fixed Size                  1251508 bytes
Variable Size             427820876 bytes
Database Buffers          364904448 bytes
Redo Buffers                7135232 bytes

SQL> show parameter sga

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
lock_sga                             boolean                          FALSE
pre_page_sga                         boolean                          FALSE
sga_max_size                         big integer                      764M
sga_target                           big integer                      764M

SQL> show parameter pga

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
pga_aggregate_target                 big integer                      320M

My database was going down every 1 hr with ORA-4030,out of process memory

My windows Virtual Memory for all drives combined is 5000 MB.

Before my database was going down every 20 mnts ,then i read somewhere to reduce PGA_AGGREGATE_TARGET AND SGA size and also incorporate 3gb switch in boot.ini

boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Microsoft Windows XP Professional 3GB" /fastdetect /3GB
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Microsoft Windows XP Professional" /noexecute=optin /fastdetect

All the details furnished above are the current settings.
If i monitor windows performace the PF usage constantly increases and at some point gets saturated and breaks down my system
If i see from windows task manager my oracle.exe memory usage increased by 400kb every sec

Can anybody suggest a good permanent fix for my issue
Avatar of schwertner
schwertner
Flag of Antarctica image

Is there still sufficient memory available?

Windows systems: Check MEM Usage in the performance tab in Task Manager.

Is there an oracle limit set?
As from Oracle Version 9i, there is a parameter implemented which limits the total amount of PGA that can be allocated for an instance.   "Automatic PGA Memory Managment in 9i" provides more information on this issue. The following query can be used to find the total amount of memory allocated to the PGA areas of all sessions:

SQL> select
                sum(value)/1024/1024 Mb
             from
                 v$sesstat s, v$statname n
              where
                  n.STATISTIC# = s.STATISTIC# and
                  name = 'session pga memory';

 The following query can be used to get the memory size as viewed by oracle, however, not including the process stack and code size:


select sum(bytes)/1024/1024 Mb from
      (select bytes from v$sgastat
        union
        select value bytes from
             v$sesstat s,
             v$statname n
        where
             n.STATISTIC# = s.STATISTIC# and
             n.name = 'session pga memory'
       );


 
Avatar of suhinrasheed
suhinrasheed

ASKER

If i check the performance tab in windows manager i can see PF usage going high ,
When there were 21 active sessions in my database the below query gave me 115mb
 select
                sum(value)/1024/1024 Mb
             from
                 v$sesstat s, v$statname n
              where
                  n.STATISTIC# = s.STATISTIC# and
                  name = 'session pga memory';

and the value given by next query is 857 MB

select sum(bytes)/1024/1024 Mb from
      (select bytes from v$sgastat
        union
        select value bytes from
             v$sesstat s,
             v$statname n
        where
             n.STATISTIC# = s.STATISTIC# and
             n.name = 'session pga memory'
       );

My PGA_AGGREGATE_TARGET is still 320 MB
and SGA_TARGET AND SGA_MAX_SIZE IS 764 mb
This what you experience could be caused also by virus.
Check on the performance tab  the processes that consumes
most resources (CPU and RAM).
If you encounter TNSPING process then kill it.

What about the nimber of the Oracle sessions?

select count(*) from v$session;

Every session eats up to 5 MB.
I have around 30 active sessions  which includes sessions opened by background processes.

The top 4 processes which consume memory are

1) oracle.exe
2)svchost.exe
3)mcshield
4)java.exe
Here oracle is the one in which memory usage is increasing other appears constant except mcshield.

I didnt fine tnsnames.exe,but got tnslstnr.exe
When i see the OEM I see that there are many ports which oracle suggests to be closed since they are unneccessarily open.Will this help addressing my issue
ASKER CERTIFIED SOLUTION
Avatar of schwertner
schwertner
Flag of Antarctica image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial