From djones1688@hotmail.com Thu, 15 Mar 2001 13:55:45 -0800 From: "David Jones" Date: Thu, 15 Mar 2001 13:55:45 -0800 Subject: Perl DBI/Oracle connectivity help Message-ID: MIME-Version: 1.0 Content-Type: text/plain Fellow DBAs, I am working on a problem for classmates.com. Here is the problem. There are several web servers connecting to the oracle database (8.1.6.2.0). Each webserver uses apache/perl-dbi and establishes 30 persistent connections to the database. These connections are established when the apache server is started/bounced/restarted. When the maxrequestsperprocess (parameter in perl dbi) reaches 20000 for any process then that process is killed (kill -9) and a new process is established. Here process refers to the perl process which connects to the database. There are 2 scenarios under which a connection is established - 1. When the web server is restarted/started 2. When the process has processed 20000 requests So given the above each web server cannot have more than 30 connections at any point in time. However it has been found that sometimes they have more than 30 connections per web server. The extra connections are most likely dead connections. I say most likely because when this problem occurs they don't call us, they bounce the database. They say they can't wait because it is a production database. I have verified and been told by my client that only perl dbi connects occur from the web server. There are no parallel queries or other connections to the datbase from the web servers besides the perl dbi connects. I have suggested 2 things 1. Use sqlnet.expire_time = 5 or 10. This is a workaround for the dead connections. 2. Instead of killing the perl process (see above) do a disconnect (graceful exit from the db) since when a client (perl process) is killed it can possibly leave dead connections in the database. This could be the solution. I have looked at the listener logs and done sqlnet trace on the server and don't find anything abnormal. I have also monitored the current connections to the database. They will be trying both of my above suggestions. What makes me queasy is that they claim that this did not happen in their previous co-lo. They claim the network architecture, database version and patch levels, apache webserver/dbi version and patch levels, os and everything else is identical. Ofcourse here we have the f5 load alancers which is before or above the web servers. I have searched metalink and haven't come across anything like this except for my solution 2 above. Have you all seen anything like this? Any light u can throw will help. Regards --dj _________________________________________________________________ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David Jones INET: djones1688@hotmail.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).