Re: Out of Process Memory with PL/SQL 8.04

ANC wrote:
>
> 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?

Hi !

I guess you're not running multithreaded server, and therefore the UGA is allocated outside of the SGA.
I guess, again, that your procedure is using pl/sql tables. And, yet a wild guess, the type of the tables are declared either as

   1 the %Rowtype of a table or
   2 unconstrained variables, such as Varchar2 instead of Varchar2(20).

You must constraints on the attributes of the table, Oracle initially allocates enough space to store the longets possible values. Constraints on the types will cost, though, Oracle will have to do typechecking.

Example:
type tabtype1 is table of varchar2(100) index by binary_integer; type tabtype2 is table of varchar2 index by binary_integer;

mytab1 tabtype1;
mytab2 tabtype2;

This will allocate about 100K UGA:
For a in 1..1000 loop

   mytab1(a):=NULL;
end loop;

This will allocate 2MB UGA (at least in Oracle 7): For a in 1..1000 loop

   mytab2(a):=NULL;
end loop;
Unconstrained Varchar2 will claim 2K UGA in Oracle7. Maybe more in Oracle8?

The danger of the %Rowtype is of course that it is very convenient way to declare the table, but almost always you use just a few of the attributes and thus waste a lot of space.

If I'm not right yet, then a last guess: You may be using pl/sql tables without knowing it! Many triggers use 'before row'-triggers to store the values temporarely in pl/sql-tables, and then parse through them in an 'after statement'-trigger. This is done to overcome problems with mutating and constraining triggers.

You wil have to check the use of pl/sql-tables and maybe have your application delete them after every 1000 iteration or so. This will not release the memory to the operating system, but it will release the memory to be used again by your own process.

You may also consider the procedure called something like 'Release_Free_Space' in the, hm, was it dbms_utility package. (I've never used it). This will cost performance.

Another way to check your sessions UGA memory usage: select *
from v$sesstat
where sid=7 -- substitute with your own session's sid and STATISTIC# in (15, 16, 20, 21);

Regards,
Njål Received on Fri Jun 26 1998 - 21:42:20 CDT