Re: Downside to ora_envhp=>0 to fix OCIHandleAlloc(OCI_HTYPE_ERROR) problem

On 25/01/2011 8:21 PM, William Ward gmail wrote:
> In regards to this thread:
> http://www.mail-archive.com/[email protected]/msg32115.html
> (found via google)
>
> I'm having the same problem at $JOB and before we throw ora_envhp=>0
> into our connect attribute, we were wondering if this will cause
> performance issues?  Is there a downside to ora_envhp that isn't
> described in the thread?
Yes that will case some performance slow downs as it will have to 
connect and recreate the environment(connection) for each dbi->connect().
Normally it will reuse the environment(connection) and just create a new 
session for each dbi->connect().
>
> Or, is there some way to verify the OCI environment and reset it when
> it is found to be unusable - in other words, trap the
> "OCIHandleAlloc(OCI_HTYPE_ERROR)" error and reconnect?
>

Well normally an environment will not die unless you loose connectivity. 
  If you have lost connectivity then there would not be much point in 
trying to reconnect automatically your network or DB is Down??

Who knows how long it will take to come back up?

I have seen people use Ping to keep the session alive which does test 
the connection each time (does only a single a round trip, in later 
DBD::ORacle versions) if this fails then their programs tries to create 
a new environment(connection) x number of times and eventually fails.


I think there way too many variables here for DBD::Oracle to take care 
of me thinks, there are so many ways to disconnect/loose a session or 
loose connectivity to get them all covered.

Just off the top of my head there are 3 ways to loose a session and at 
least that many for a connection that can be started on the server end 
of things. At this point in time there is no way in DBD::Oracle to tell 
how you lost connectivity, connection or session


What I am working on now for 1.29 (March) is User callback and TAF 
(http://download.oracle.com/docs/cd/B19306_01/java.102/b14355/ocitaf.htm) which 
should address some of these issues

Though TAF is rather obsolete now so I do not see many people using it.

Once that is in there you will at least be able to know how you lost 
connectivity and then program for that.

cheers John