Re: orphan processes
Date: Thu, 27 Sep 2012 11:48:17 -0700 (PDT)
Message-ID: <1348771697.42334.YahooMailNeo_at_web83607.mail.sp1.yahoo.com>
Hi Max,
It has been many moons since I dealt with a situation similar to this. The fix at the time was to set SQLNET.EXPIRE_TME to a non-zero value. Although, at that time in theory (per documentation), it needed to be set ONLY on the server's sqlnet.ora, I distinctly remember having to set it on both the client and the server's sqlnet.ora files and bounce the listener, to get it to work. Don't ask why, let's just call it an undocumented feature :)
Also, am I understanding you correctly that "each query/SQL request" opens a NEW connection, runs the query and then closes it? Is there any reason why connection pooling is not enabled on WebLogic? OS Process creation and deletion on the database server is an expensive operation, not to mention - adds additional elapsed time for your query's execution. Just curious why it is not enabled!
Cheers,
Gaja
Gaja Krishna Vaidyanatha,
CEO & Founder, DBPerfMan LLC
http://www.dbperfman.com
http://www.dbcloudman.com
Phone - +1-650-743-6060
LinkedIn - http://www.linkedin.com/in/gajakrishnavaidyanatha
Co-author: Oracle Insights:Tales of the Oak Table - http://www.apress.com/9781590593875 Primary Author: Oracle Performance Tuning 101 - http://www.amzn.com/0072131454 Enabling Cloud Deployment & Management for Oracle & Big Data
From: "Pakhutkin, Maxim (Max)" <maxim.pakhutkin_at_windstream.com> To: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> Sent: Thursday, September 27, 2012 8:50 AM Subject: orphan processes
On our 10.2.0.3 database (Solaris 10, client is weblogic/jdbc on Linux on Sun) we've started experiencing orphan process generation at a rate of about a hundred a minute. We've raised the processes limit from about 400 to 5000 and after a couple of hours of running the orphan processes reached about 4800 and started falling off. The session count stays at about 350 at all times. No core dumps on the client and the application that is responsible for the orphans does not registers any errors (except when we reach the process limit), so network doesn't appear to be an issue. No expire_time set in sqlnet.ora. Setting it to 1 or 10, didn't make a difference either. The application connects once per query it needs to execute and then disconnects. The login/logoff trigger registers both logins and logoffs for each connection, so I'm thinking the orphan process is created after the login trigger fires, but before the final connection closure negotiation. No
changes in code or in the
database configuration have been identified. There were some changes on the Unix side, having to do with clustering (a new "package" was brought to the host). The orphan processes are cleaned up automatically (no need to kill -9 them) but not as fast as they are generated. The database has been restarted since the issue surfaced, but that didn't change anything.
We are working on setting up sql*net trace on the client to see what is happening and have an SR with oracle open. If I could patch and upgrade the database version now, I would, but I can't, so that's not an option.
I'm wondering if anyone experienced something similar and what they discovered. Thanks in advance.
--
Max Pakhutkin
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Sep 27 2012 - 13:48:17 CDT