Out of Process Memory w/ PL/SQL 8.04

From: ANC <alex_at_gsbalum.uchicago.edu>
Date: Tue, 16 Jun 1998 17:19:27 GMT
Message-ID: <zOxh1.3005$uC.963338_at_pm01nn>



Hello!

We are having the following problem that Oracle nor Digital have been able to resolve ... any assistance would be greatly appreciated since it has halted our development efforts:

We are running into an "out of process memory" error while trying to execute a PL/SQL package procedure using SQLPLUS on a DEC Alpha 4100 Digital Unix server with 512MB RAM. Our SGA is about 15MB and our DB buffers are about 20MB. The swap size is 1,300MB. Taking away the memory used by SGAs of the databases, approximately 400MB is available for any process. We are running Oracle and PL/SQL 8.04, and our Digital UNIX is version 4.0b.

Our procedure reads records from 3 different temporary tables and then writes the clean records to the master table. The total number of records involved is about 275,000. A commit occurs for about every 100 records, so the RBS space is under control.

While executing the procedure, the process uses huge amounts of operating system memory so the system runs out of memory after the program proceses about 80,000 records. The process slows down to halt and just hangs, but no Oracle error or OS error occur. The o.s. memory statistics (vmstat output) and swap statistics show that there are hardly a few free memory pages available and about 45% of the swap space has been consumed. When we observed the SGA, it was constantly showing free space which indicated that the process was not using the SGA memory

Questions


  1. Is there a bug in oracle 8.0.4's management of o.s. memory ??? Or, is there a bug in PL/SQL 8.0.4 ?? (We are able to load 300,000 records of data using SQL*Loader without any problems)
  2. Is the oracle 8.0.4's management of memory, different from the earlier versions ?? (This is to ascertain whether 8.0.4 requires more o.s. memory for its processes to run)
  3. Are there any o.s.related 'C' program calls that we should incorporate in our program to manage the memory ????
  4. Any other suggestions?
Received on Tue Jun 16 1998 - 19:19:27 CEST

Original text of this message