Solved

out of process memory

Posted on 2002-06-25
7
982 Views
Last Modified: 2008-07-11
Hi,
We are running several Oracle DBs (8.1.7) under Win2000 (all SPs applyed). 2 CPUs/2 GBt RAM.
And every server is periodically crashing with a nice "out of process memory when trying to allocate ???? bytes" error message. It looks like it is only a metter of time (from hours to days) before this happens.
SQL statements and stored procedures usually take 30+ minutes to execute, so the servers are very havily used. And are processing huge amounts of data.
The following is from init.ora file:

# MEMORY PARAMETERS
db_block_buffers = 93750
#orig shared_pool_size = 314368000
shared_pool_size = 204800000
large_pool_size = 76800000
log_buffer = 76800000
sort_area_size = 76800000
 
sort_area_retained_size = 76800000
#pre_page_sga = true
 
# BLOCK SIZE PARAMETER
db_block_size = 8192
 
java_pool_size = 6144000
 
Can anyone tell me what can be wrong with those memory settings for a 2 CPU/2 GBt RAM machine? If it is not the memory setting, than what can be the origin of the problem?
And, Mem Size is ~1.9G, VM Size is ~1.9 G before Oracle dyes

Thank you.
0
Comment
Question by:db_im
7 Comments
 

Expert Comment

by:netbanker
ID: 7108723
log_buffer = 76800000
2M is enough in usual case, larger than 3M will be meaningless

large_pool_size = 76800000
set higher value only if you use MTS,or 1M

You should tune your share_pool by take a oracle book with u. and start tuning from it.





0
 

Expert Comment

by:pbybee
ID: 7108894
The key here is whether or not you are using MTS,
multi threaded server.

If you are using MTS the PGA (which is the error
message you note) is located in teh Shared Pool.

If you are not using MTS the PGA is contained in
the Dedicated Server process.

So, if you are using MTS increase the Shared Pool.

If you are not using MTS decrease the SGA size giving
back memory for Dedicated Server processes.
Large Pool, Log Buffer appear large if MTS is not being
used.  
0
 
LVL 3

Accepted Solution

by:
mnicoras earned 200 total points
ID: 7109003
Hi,

sort_area_size it's enormous!!! And sort_area_retained_size should be much less than sort_area_size, because it's the amount of memeory kept by the session after sorting .... Each Oracle session use this amount of memory!!! If you are in dedicated mode soon the computer raise the memory error. Your database should be slow enough because of heavy swap! Also what the other experts suggest it's correct.

The buffer cache is 750MB, the shared_pool is 200MB, large pool 75 MB, log buffer 75 MB, each new oracle session eats 75 MB .... so in total, after 10 session the oracle should raised the memory problem because also the real mem. and swap it's full!!!!

large_pool_size = 0 if you don't use MTS
log_buffer = 2 MB
sort_area_size = 2 MB
sort_area_retained_size = 256 KB

good luck and best regards,
Marius Nicoras
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 9

Expert Comment

by:konektor
ID: 7109833
1. if u r in MTS mode and don't need, drop all MTS_xxx parameter from init.ora to switch to dedicated mode and restart
2. do u use oracle jobs? (table dba_jobs)
if so, check init parameter job_queue_processes.
jobs allocate memory over SGA, even if u r in dedicated mode. the max allocated amount of memory equals to  sort_area_size. after using, memory is deallocated down to sort_area_retained_size. BUT ..... not on all operating systems, and oracle dealocates this memory only for current process (jobs create os processes, in W2000 threads), not for system!
u can :
log as sys
alter system set job_queue_processes = 0
wait a while, all running jobs ends
alter system set job_queue_processes = <original value>
this frees allocated memory of jobs
3. if users allocate huge amount of memory, create consumer groups and limit mem usage for users
0
 
LVL 3

Expert Comment

by:p_yaroslav
ID: 7109890
Hi!

You problem may arise from two parameters:
1. sort_area_size (sort_area_retained_size) must be about 1M if you use OLTP system.
2. large_pool_size must be zero if you doesn't use MTS.

The best description for your problem was given by  mincoras.

Best regards!
Yaroslav.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 7112006
I agree with Marius, your sort_area_size value is *WAY* too large!  That memory for each user session is in addition to the SGA, it is not part of the SGA.  I did quite a bit of testing of that value in our 8.1.7 system (on WindowsNT) found that setting that to about 2MB (2097152 bytes) is best in our system.  Depending on the kind and number of sorts your users do, you may be able to set that smaller (like 1MB) and still get good results.

Also, your shared_pool_size looks unusually large.  Are you sure that you need 200MB for that?  If you are using Java, it will need to be at least 60MB, otherwise it may be better to have it smaller (like maybe 48MB).
0
 

Author Comment

by:db_im
ID: 7116401
Thank you. The problem has been solved.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now