Re: 9i serially_reusable packages; 'call to the server is done'

ken quirici wrote:
> Hi,
>
> According to the 9i docs, the global memory for a serially_reusable
> package is stored in SGA and the space is returned when the
> call to the server is done.
>
> While this server call is executing, and 'using' the global memory
> of the package, can any other package access this global
> memory - e.g., to call one of its functions to alter some
> variable in the package's global memory?
>
> If yes, does Oracle allocate the package global memory in
> the 2nd caller's UGA?
>
> Also, what does 'call to the server is done' mean - when the
> session is over? Does that session lock other sessions out
> of the package?
>
> Thanks.
>
> Ken

Don't know about memory details but the call definitely doesn't mean only closing the session. According to
http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14261/seriallyreusable_pragma.htm "An example could be an OCI call to the database or a stored procedure call through a database link."

We've used packages with this pragma for our app server doing session pooling. I.e. for example 100 users where connected to app server, but there were only 10 sessions in Oracle from app server, so each user call might have used another Oracle session. So one of our packages had pragma serially_reusable to store user session specifc info, and this info was initialized for each call. But using this pragma Oracle took care to destroy this session specific info, we hadn't to do that manually so decreasing possible bug count from our side :) There was at least one Oracle bug we encountered using this pragma though, but I cannot remember neither the bug nor the specific version number (some 9.2.X).

Gints Plivna
http://www.gplivna.eu Received on Fri Nov 17 2006 - 04:12:39 CST