Re: How much load will this SQL cause

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 Sun Dec 09 2001 - 08:58:24 CST