Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How does schema name affect allocation of PGA memory in dedicated server?
Mark D Powell 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. <<
>
> This statement is essentially correct.
Beg to differ a tad Mark. The statement implies schema run-time states when in fact it is connection session states.. where a session just happens to use a schema as authentication and for security and default scope.
More a terminology issue than a technical one I realise, but I feel that the distinction needs to be made as there is a single session state irrespective of how many schemas you access via that session. Someone that does not know Oracle may thus interpret that statement incorrectly.
> Every user session gets its own
> private copy of the working storage variables in a package so if 20
> different sessions execute the package then there are 20 copies of the
> package work areas but only one copy of the executible code is
> necessary. This allows variable values set in one call to a packaged
> routine to be used in additonal calls to packaged routines. IBM's
> transaction manager CICS works like this.
In fact, any transaction monitor works like that (including ones on Siemens and other mainframes), and also Windows DLLs (Dynamic Link Libraries) and Unix SOs (Shared Objects).
So what Oracle does with a session and the software applications (PL/SQL stuff) run in that session, and how PGA is used, is no different than the run-time and memory environment achitecture used on numerous other platforms.
> The other approach where only one copy of the working storage is used
> by all users is possible and is the technique used by IBM's IMS
> product. This requires single threading user access to the code and it
> also forces the developer to write code that does not rely on the value
> of any variable that was not passed in.
Exactly! And again, the same method applies to DLLs and SOs. Global memory needs to be allocated (outside the local session's stack and heap space) and access to that memory needs to be serialised via semaphores or some kind of locking/latching method.
The bottom line is that the way Oracle runs PL/SQL code in an Oracle session is no different than the methods used by o/s vendors. Therefore abuse of the PGA that leads to memory errors is simply bad code/design that will cause similar problems if that approach is used in other environments.
-- BillyReceived on Fri Jul 22 2005 - 04:13:23 CDT