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 -> Re: How does schema name affect allocation of PGA memory in dedicated server?

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

From: Billy <vslabs_at_onwe.co.za>
Date: 21 Jul 2005 00:30:09 -0700
Message-ID: <1121931009.063335.125320@g47g2000cwa.googlegroups.com>


Mark H. Zellers wrote:

> 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.

As I've said in Metalink...:

PGA is not owned by a schema. It is owned by a server process and resides in the physical data segment of that process.

B.FOO is not C.FOO. There are two programs. B.FOO and C.FOO. The fact that they contain the exact code is irrelevant. They are physically two different objects in the Oracle database.

The real question is how is the PGA is used? Packages are treated the same way as any others programs. They have a code segment. They have a data segment. The packages' data segment resides in the PGA of the session that executes them.

Thus, when a package malloc's, it needs memory and grows its data segment. Therefore it uses PGA memory.

Now for the difference - we are used to a program releasing its data segment when terminating. This is what happens at o/s level. But this is not the case in Oracle. The data segment remains allocated in the PGA even when the package terminates.

Why? Two primary reasons.

So you execute package B.FOO - it allocates a data segment in the PGA. You execute package C.FOO. It too allocates a data segment in the PGA. You rub B.FOO again - as it has an already allocated data segment, it does not need to allocate that memory again.

The technical implementation and reasoning why Oracle choose to do it this way is sound. How the session is used, and how that session's PGA is used (or abused), it the decision of the developer of the application code that will be executed in that session.

It also illustrates a fundemental concept of writing a single package and have that used by any number of users - as oppose to making a distinct copy of that package in each schema.

--
Billy
Received on Thu Jul 21 2005 - 02:30:09 CDT

Original text of this message

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