mmap allocations in Oracle

[Oliver White]

I am assisting our DBAs with a problem that has been affecting some of our Oracle jobs recently. It appears to be memory related, and I have observed some very strange behaviour using GlancePlus. We are running Oracle 9.2.0.3 64-bit on HP-UX 11i

The Oracle process repeatedly allocates 16 Mb chunks of memory using mmap() until it reaches 1 Gb, then it releases them all, does a little processing, and starts allocating memory again. After a while the job crashes with an Oracle ORA-4030 error; out of memory.

The memory segments sometimes just have a name of ", and sometimes they are standard libraries or /usr/lib/tztab. What does this mean? See attached screenshot from gpm.

The process always hits a 1 Gb limit in "Other VSS". I can't work out where this comes from. I have increased maxdsiz to 2 Gb, maxdsiz_64bit to 4 Gb and maxtsiz_64bit to 2 Gb. Output from ulimit -a:
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) 4292870144
stack(kbytes) 131072
memory(kbytes) unlimited
coredump(blocks) 4194303
nofiles(descriptors) 1024

- Oliver.

[TwoProc]

Oliver - nothing comes to mind - except what you've already addressed. But, go ahead and check the shmmax and make sure you're not cutting up all of your shared memory segments into a whole bunch of little chunks (ipcs -am command to review this). Not that I really that this is your problem - but I guess could come up with a scenario of how it's possible...

Lastly - did you review all the memory type fixes that are availabe in 9.2.0.4 alone? Not to mention 9.2.0.5 and 9.2.0.6.

9.2.0.4's bug fix list mentioning memory ...
Memory Corruption
2208113 Dump / memory corruption possible using USER FILTER for 10K document
2662683 Heap corruption from schema name overwriting memory in PLSQL
2789595 PGA memory corruption from INDEX SKIP SCAN
2867563 OERI:KSMFRFG2 / memory corruption selecting from an EXTERNAL TABLE

also...
Leak - Memory leak / Growth
2378009 PGA memory leak from APPLY servers when using LOBS
2441734 Win: Setting SQLNET.EXPIRE_TIME wastes stack memory
2523297 PGA JavaVM memory leak using many large objects
2544186 OCCI memory leak using toText() for number to string conversion
2562972 HS agent may leak memory with mixed character sets
2667235 High memory consumption parsing bitmap access with many OR predicates
2686584 PGA memory leak in PQ Query coordinator
2688989 Client memory leak calling SQLALD with size=0
2707096 Server memory leak on long running select over HS in PLSQL block
2707731 Memory leak (in EXTPROC) from CTX_DOC.IFILTER
2763219 Client memory leak using OCIStmtPrepare with TAF connection
2769514 Memory leak calling a TABLE() function / pipeline function in a loop
2786968 Shared pool memory leak / ORA-4031 on repeated invalidate/execute of cursors
2791857 Memory leak / ORA-4030 using Deflator class in Java stored procedure
2805249 SGA shrink may not release memory back to the O/S on Solaris
2810557 PRO client can leak memory when NLS_LOCAL=YES
2817728 Shared pool memory leak / ORA-4031 when collecting segment statistics
2821420 Client memory using CURSOR in JDBC with OCI driver
2823100 OracleConnectionCacheImpl over OCI driver leaks memory fetching Objects
2869329 Memory leak constructing XMLTypes from CLOBS or Strings
2875058 Repeat calls to JSP from PLSQL can leak PGA memory
2880427 Client side memory leak using OCIXMLType APIs
2883743 Pipelined functions leak memory for parameters with default values
2884131 Client memory leak when a REF CURSOR is selected but not used (no getXXX call)
2890022 Memory leak from XML queries within PLSQL
2901439 Memory leak using XDK for C in multithreaded application
2908182 XMLType.transform() leaks memory
2916024 PGA memory leak possible using OUTER JOIN with CONTAINS
2947010 PROCESSCOPY method does not release memory on completion
2958718 Memory leak if schema based document used on client side for printing / DOM operations
2972701 Memory growth executing SQL/XML repeatedly in PL/SQL
2980596 PLSQL FOR LOOP performance hit & memory leak using implicit cursors
3013905 JVM calls can cause PGA growth (in 4Mb chunks)

[B. Hulst]

Hi,

Make sure:
- you allocate the correct amount of memory to oracle.
- The file /stand/system contains a reasonable amount of semaphores. I forgot their exact names but they always come in couple of 3 variables. Setting these incorrect or not at all give weird oracle behavior.

And how much is the oracle SGA?
And how much is your max real memory?
And how much is your max swap memory?

Regards,
Bob

[Steve Steel]

Hi

Use
http://www.oracle.com/technology/documentation/oracle9i.html

to get to

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c08memor.htm#21986

Adding Granules to Components
A database administrator grows a component's SGA use with ALTER SYSTEM statements to modify the initialization parameter values. Oracle takes the new size, rounds it up to the nearest multiple of 16MB, and adds or takes away granules to meet the target size. Oracle must have enough free granules to satisfy the request. If the current amount of SGA memory is less than SGA_MAX_SIZE, then Oracle can allocate more granules until the SGA size reaches SGA_MAX_SIZE.

See Also:
Oracle9i Database Administrator's Guide for information on allocating memory
Oracle Enterprise Manager Administrator's Guide for information on showing the SGA size with Enterprise Manager
SQL*Plus User's Guide and Reference for information on displaying the SGA size with SQL*Plus
Oracle9i Database Reference for information on V$SGASTAT
Your Oracle installation or user's guide for information specific to your operating system

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/toc.htm

should certainly help


Steve Steel