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

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

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.

If the quantity of data that you are storing into the pl/sql tables is large and the packages will be concurrently executed you might want to look at using other options to hold the data such as using a permanently allocated work table, or global temporary tables.

You also did not list your Oracle version so increasing the pga_target might also be an option depending on version and configuration.

HTH -- Mark D Powell -- Received on Thu Jul 21 2005 - 08:46:12 CDT