Re: Solution for DBD::Oracle 1.06 'make test' failure at repetitive connect/open/close/disconnect

Great detective work. Many thanks Alexi.

The underlying problem is related to the way perl collects signals.
But this is an effective workaround. I'll add it to the docs.

Tim.

On Tue, Feb 20, 2001 at 10:56:51AM +0800, Alexi S. Lookin wrote:
> 
> Hello, Tim!
> 
> Recently I stucked the same problem like many users in  dbi-dbd mailing list . It was the same
> DBD::Oracle 'make test' problem
> when connection to Oracle hungs after repetitive  connect/open/close/disconnect. Probably I found a
> workaround,
> at least it helped for me on three slightly different systems. I did not found any mention of this
> in dbi-users archives scattered around internet.
> Probably that helpful, sorry if not.
> 
> My test system is:
> 
> DBD::Oracle 1.06, DBI 1.14, Perl 5.005_03, Debian 2.2, Oracle 8.1.6 EE, Athlon 700/128MB RAM
> 
> Solution:
> =======
>      In short,  this problem was solved after addition of parameter
>      BEQUEATH_DETACH=YES in SQLNET.ORA and restarting Oracle instance.
> 
> Approach:
> ========
> 
> After continuous repetitive failures of 'make test' I decided to modify test2() routine of test.pl,
>   I had to add some print() calls to check the routine
> flow. I noticed that routine hungs when executing ora_logoff() at second iteration. When killing
> frozen perl process, I found two defunct processes
> (zombies).
> 
> This is modified procedure. All changes are quite harmless:
> 
> sub test2 {
>      print "logging...";
>     local($l) = &ora_login($dbname, $dbuser, '')
>                || die "ora_login: $ora_errno: $ora_errstr\n";
>      print "opening...";
>     local($c) = &ora_open($l, "select 42,42,42,42,42,42,42 from dual")
>                || die "ora_open: $ora_errno: $ora_errstr\n";
>     local(@row);
>      print "fetching...";
>     @row = &ora_fetch($c);
>      print "closing...";
>     &ora_close($c)  || warn "ora_close($c):  $ora_errno: $ora_errstr\n";
>      print "logging off...";
>     &ora_logoff($l) || warn "ora_logoff($l): $ora_errno: $ora_errstr\n";
>      print "done...\n";
> }
> 
> 
> 
> I supposed that there may be disconnection problem because of luck of time between two consequtive
> repetitions but I was not sure where it happened --
> at DBD side or Oracle, and who was an initiator? I added the sleep() call in test2() also.
> 
>      print "sl5.";
>      sleep 5;
>     &ora_logoff($l) || warn "ora_logoff($l): $ora_errno: $ora_errstr\n";
>      print "sl5.";
>      sleep 5;
> 
> ora_logoff() completed OK, but use of such delays was too sloooow to work in real life.  Attempt to
> reduce sleep() argument caused to hung again. Since
> connection worked over Bequeath protocol, I had to browse Net8 doc (it has length of 666 pages
> (A67440-01 Net8 Admin Guide  for Oracle 8.1.5, Feb.1999) , does
> this number occasional ? :)))), and found some mention of inadequate bequeath behaviour when
> disconnecting bequeath session, and some
> solution for this problem at page 10-15 (may vary at any other release) :
> 
> "p.10-15
> 
> Child Process Termination
> 
> Since the client application spawns a server process internally through the Bequeath
> protocol as a child process, the client application becomes responsible for cleaning
> up the child process when it completes. When the server process completes its
> connection responsibilities, it becomes a defunct process. Signal handlers are
> responsible for cleaning up these defunct processes. Alternatively, you may
> configure your client SQLNET.ORA file to pass this process to the UNIX init process
> by disabling signal handlers.
> 
> Use the Net8 Assistant to configure a client to disable the UNIX signal handler. The
> SQLNET.ORA parameter set to disable is as follows:
> 
> bequeath_detach=yes
> 
> This parameter causes all child processes to be passed over to the UNIX init process
> (pid = 1). The init process automatically checks for "defunct" child processes and
> terminates them.
> 
> Bequeath automatically chooses to use a signal handler in tracking child process
> status changes. If your application does not use any signal handling, then this
> default does not affect you."
> 
> 
> I tried it, it worked perfectly, even after I raised $opt_n value to 500.
> 
> I tried this solution on three different systems:
> 
> 1.Celeron 333/192MB RAM/Debian 2.2/DBI 1.14/DBD::Oracle 1.06/Oracle 8.1.6
> (this is evaluation site, it's quite slow :)), where original test hungs after
> second repetition. That helped.
> 
> 2.Athlon 700/128MB RAM/Debian  2.2/DBI 1.02/DBD::Oracle 1.06/Oracle 8.1.6
> (dev.site1), where original test hunged after fourth or five repetition. That helped.
> 
> 3.PIII-850/256MB RAM/Debian 2.2/DBI 1.14/DBD::Oracle 1.06/Oracle 8.1.5 (dev.site2), where
> original test with five repetitions hunged only once from five consequental runnings (quite fast
> computer). That helped also.
> 
> As you may see that problem depends on CPU speed (and probably on amount of RAM --- better caching -
>  better performance?) and
> I believe somewhere there are  systems that did not hung at all for this reason :).
> 
> That may rarely happen in real life, since it requires use of DBD on heavy load
> of very short length transactional operations ("select 'X' from dual" :)))), that
> is not often, but has a chance to happen. Oracle states that is responsibility
> of process using bequeath protocol to kill it's own childs.
> 
> Any comments?
> 
> Bye, Alexi Lookin,
> 
> Lead Programmer of branch "Irkutskiy", Alfa-Bank, Russia
> Eastern Siberia, Irkutsk.
> 
> 

• Re: Solution for DBD::Oracle 1.06 'make test' failure at repetitive connect/open/close/disconnect by Tim Bunce