Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> How does schema name affect allocation of PGA memory in dedicated server?
We have a web-based java application that maintains multiple identical schemas. Each schema has a package that is used to evaluate a set of spreadsheets for an individual customer.
We were observing ORA-04030 errors in some which we have traced to the way that PL/SQL allocates and deallocates memory in packages.
The problem can best be illustrated by creating three users, A, B, and C. In the B and C schemas, create the following package:
CREATE OR REPLACE PACKAGE Foobar
AS
TYPE number_idx_tbl IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
store1_table number_idx_tbl; -- PL/SQL indexed table
empty_table number_idx_tbl; -- uninitialized ("empty") version
PROCEDURE foo;
END;
/
CREATE OR REPLACE PACKAGE BODY Foobar
AS
PROCEDURE foo IS
BEGIN
DBMS_RANDOM.SEED(SYSTIMESTAMP);
FOR i IN 1..1000000 LOOP
store1_table(DBMS_RANDOM.RANDOM || '_' || DBMS_RANDOM.RANDOM) := i; -- load
data
END LOOP;
store1_table := empty_table; -- "truncate" the indexed table
DBMS_RANDOM.TERMINATE;
END;
END;
/
In schema A, define the following stored procedure:
CREATE OR REPLACE PROCEDURE show_memory
AS
PROCESS_ADDR VARCHAR2(16);
PGA_USED PLS_INTEGER; PGA_FREEABLE PLS_INTEGER; PGA_ALLOC PLS_INTEGER; PGA_MAX PLS_INTEGER;
BEGIN
show_memory;
B.foobar.foo;
show_memory;
B.foobar.foo;
show_memory;
C.foobar.foo;
show_memory;
END;
When I run this, I see the following results:
Initial: PGA_USED=273969 PGA_ALLOC=1112301 PGA_FREEABLE=327680 PGA_MAX=1112301 After B.foo: PGA_USED=132516805 PGA_ALLOC=133167341 PGA_FREEABLE=0 PGA_MAX=133167341 After 2nd Run of B.foo: PGA_USED=132516805 PGA_ALLOC=133167341 PGA_FREEABLE=0 PGA_MAX=133167341 After 1st Run of C.foo: PGA_USED=264712849 PGA_ALLOC=265484525 PGA_FREEABLE=0 PGA_MAX=265484525
Note that after B.foo is called the second time, the PGA_USED does not increase, but after C.foo is called it does.
This says to me that C.foo cannot use the memory freed by B.foo (unless you call DBMS_SESSION.FREE_UNUSED_USER_MEMORY).
My only guess on why Oracle might behave this way is that it is trying to protect the privacy of data between the different schemas. Received on Thu Jul 21 2005 - 01:52:12 CDT