out of process memory

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:

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
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.
Who is Participating?
mnicorasConnect With a Mentor Commented:

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

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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

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!
Mark GeerlingsDatabase AdministratorCommented:
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).
db_imAuthor Commented:
Thank you. The problem has been solved.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.