[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


out of process memory

Posted on 2002-06-25
Medium Priority
Last Modified: 2008-07-11
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.
Question by:db_im
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

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.


Expert Comment

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

Accepted Solution

mnicoras earned 800 total points
ID: 7109003

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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.


Expert Comment

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

Expert Comment

ID: 7109890

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!
LVL 35

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

Author Comment

ID: 7116401
Thank you. The problem has been solved.

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

649 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