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

Thanks for your reply, Gints.

Gints Plivna wrote:
> 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.
> >

I looked a little more at the documentation. They gave an example of a BEGIN-block executing in SQLPLUS as a 'call to the server'. I would then assume that each statement in SQLPLUS is a 'call to the server' - each separate SELECT, EXECUTE, @<name>, etc.

So I'm thinking it works sort of like this:

a normal package, NOT serially-reusable, is initialized at the start of each session by a user that has access to that package - either as owner or thru granted rights.

a serially reusable package is initialized at the start of each 'call to the server' - that is, if you execute 10 SELECT statements in SQLPLUS, a serially reusable package gets reinitialized for each such SELECT (assuming they're not grouped in a block or in a stored procedure).

This sound right? Or does a package only get initialized at first reference (remember I'm talking about a non-serially-reusable package here)?

My reading also revealed that the SGA has a pool of multiple 'copies' of a serially reusable package, which it allocates as needed.

But what if you run out? Or does it keep allocating? Does it know how many it started with so it flushes the 'extras' from memory?

I sure am revealing profound ignorance of the deep and dark recesses of the Oracle server!

Thanks.

Ken

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

I don't quite follow this. Aren't you saying more than one user uses the
same session? How does THAT work?

Thanks again.

Ken

> 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 - 20:49:03 CST