Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> How does schema name affect allocation of PGA memory in dedicated server?

How does schema name affect allocation of PGA memory in dedicated server?

From: Mark H. Zellers <mzellers_at_pacbell.net>
Date: Thu, 21 Jul 2005 06:52:12 GMT
Message-ID: <wCHDe.19441$NU2.10762@newssvr13.news.prodigy.com>

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
SELECT PADDR
INTO PROCESS_ADDR
FROM SYS.V_$SESSION
WHERE AUDSID = SYS_CONTEXT('USERENV', 'SESSIONID'); SELECT PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM, PGA_MAX_MEM INTO PGA_USED, PGA_ALLOC, PGA_FREEABLE, PGA_MAX FROM SYS.V_$PROCESS
WHERE ADDR=PROCESS_ADDR; DBMS_OUTPUT.PUT_LINE('PGA_USED=' || PGA_USED
|| ' PGA_ALLOC=' || PGA_ALLOC
|| ' PGA_FREEABLE=' || PGA_FREEABLE
|| ' PGA_MAX=' || PGA_MAX);

END; Now, as user A, run the following:

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US