Re: How much load will this SQL cause

Jim:

'Re-compile' was probably a wrong choice of words, I was referring to changes made to procedure logic without altering the number or type of arguments.

Since the application server uses cached connections and it is a 24x7 application you could have a connection which is a few days old if a Procedure is modified after the connect time the timestamp is invalidated and we get the following error:

ORA-06508 PL/SQL: could not find program unit being called

Cause: An attempt was made to call a stored program that could not be found. The program may have been dropped or incompatibly modified, or have compiled with errors.

Action: Check that all referenced programs, including their package bodies, exist and are compatible.

If the call is repeated immediately from the same connection it will work.

This is a known issue.

Hence, the problem is not that the procedure is compiled with errors but that the procedure is invalidated since the timestamp has changed.

I believe my best bet is to switch to Signature mode.

Thanks,
Anil

"Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message news:<PjMQ7.24246$Yy.298163_at_rwcrnsc53>...
> In a production system why would you have stored procedures that needed to
> be recompiled?
> I can see in a development system the need to recompile procedures, but the
> developer should be making sure their procedure compiles.
> When you migrate the code to production you can list those that are invalid
> and compile them. If they won't compile then you should fix them
> (presumably you would have fixed them in the development or test
> environments before you migrated them to production) prior to making them
> live in the production system.
>
> Jim
> "Anil Asher" <anilasher_at_hotmail.com> wrote in message
> news:d24b10ea.0112090658.5015c4da_at_posting.google.com...
> > OK ! The reason why I need to do this is for our application server
> > which uses cached connections.
> >
> > The connections to the database are created at startup and remain
> > connected until a re-start. The server hands over the connections on
> > demand to application objects which return the connection on
> > completion of their task.
> > Thus the connection is re-used without the overhead of connection
> > time.
> >
> > Problem is when we use procedures. When a procedure is re-compiled the
> > first call to it fails with an invalid procedure error from oracle,
> > the second call usually works since the procedure is re-validated for
> > the session after the first error.
> >
> > I would either have to re-code my application to always make the
> > second call on an error OR I can incoporate a check to see if any
> > procedures have been re-compiled since the connection to the db was
> > established, if yes then disconnect and re-connect.
> >
> > The SQL was intended for this check.
> >
> > We did consider using the SIGNATURE mode since the TIMESTAMP mode is
> > causing the error but I am at a loss as to how to determine what mode
> > a session is in and the ALTER SESSION command doesn't seem to have an
> > effect or doesn't work since I still get the same error on calling
> > re-compiled procedure.
> >
> > Thanks,
> > Anil
> >
> > Thomas Kyte <tkyte_at_us.oracle.com> wrote in message
> news:<9utgaf015ag_at_drn.newsguy.com>...
> > > In article <d24b10ea.0112071529.50cc84a0_at_posting.google.com>,
> > > anilasher_at_hotmail.com says...
> > > >
> > > >Please help me resolve an on going debate about how much load the
> > > >following SQL will cause if it were to called prior to every SQL call
> > > >
> > > >select * from dba_objects where OWNER = 'SOMEONE'
> > > >AND OBJECT_TYPE IN ('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY')
> > > >AND STATUS = 'VALID'
> > > >AND to_date(TIMESTAMP,'YYYY-MM-DD:HH24:MI:SS') >
> > > >(select logon_time FROM v$sessio where audsid = userenv('SESSIONID'))
> > > >
> > > >The purpose of the SQL is to determine if any Functions, Packages or
> > > >Procedures have been re-compiled after this connection was
> > > >established.
> > > >
> > >
> > > why? the server will detect that automatically and tell you about it
> with an
> > > error if you need to know (if you don't need to know, it won't tell
> you).
> > >
> > > why the heck would you want to do this?
> > >
> > > Yes, it'll definitely slow things down.
> > >
> > >
> > >
> > > >Also, if anyone knows of an environment variable (or equally efficient
> > > >object) which could provide us with the last compile time, please let
> > > >me know.
> > > >
> > > >Thanks in advacne for your help.
> > > >Anil
Received on Mon Dec 10 2001 - 08:20:04 CST