Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle



  Oracle Tips by Burleson

Inter- and Intranet Connections

In recent years the internet and company intranets have virtually taken over the domain of the Oracle NET8 and Oracle Net processes. Actually, I should say they have added to the process stack. Let me explain. In an inter or intranet connection an Application Server (usually Apache since that is what is shipped with Oracle) is configured to listen for HTTP protocol connection requests (usually on port 8088) when on is received it is passed on as a connection to the database server (identified in a DAD configuration file). Once the database server receives the connection request it is processed identically as would be a client server connection request.      

The addition of the Application server adds another layer of complexity to the management of the Oracle connection process. If not properly setup and tuned the Apache server can be a performance nightmare for the Oracle DBA. Usually the Oracle DBA will be saddled with the care and feeding of the Apache server since it is supplied with the Oracle software.     

In dealing with several Apache server installations I have come across a couple of tips that I want to share in regards to Apache service performance. The techniques appear valid on both HP and Sun installs but I have not seen the problems discussed on Windows or Linux based platforms. In general the problems deal with the usage of the KeepAlive variables and how the clients, servers and application servers signal that a process is finished.      

Essentially, when the server closes a TCP connection, it sends a packet with the FIN bit set to the client, which then responds with a packet with the ACK bit set. The client then sends a packet with the FIN bit set to the server, which responds with an ACK and the connection is closed. The state that the connection is in during the period between when the server gets the ACK from the client and the server gets the FIN from the client is known as FIN_WAIT_2. At least on HP and Solaris this final FIN from the client doesn't seem to be being sent when the Apache server is used. I discovered this first on an HP client running Oracle Financials.    

The server was a HP9000 L2000 with 4 gig of memory and 4 processors. It was running and 11.5.2 of Apps on B11.0.0 U HPUX. When I got here they could run 10 processes against the Oracle DB and would peak out memory and then CPU at 4 hour intervals when they would have to bounce the webserver/forms server and do some zombie slaying to get back memory.

After a couple of false starts I did some web research and found out that Apache clients may have memory leaks (of course they blame it on the OS libraries) and JDBC may as well. The lack of the FIN_WAIT_2 processes from closing and the multiple connection attempts to the clients filled process slots and ate memory until reboot was required.       

I edited the httpd.conf file and adjusted MaxRequestsPerChild (which is actually MaxConnectionsPerChild) from its default of 0, which means UNLIMITED, to 2500 (some people have adjusted this down as far as 300). Each time a process connected the memory leak caused the child process to grow until finally all memory disappears (gee sounds like real kids...) This solved the memory leak problem, but what about the FIN_WAIT_2 processes?     

The killing of FIN_WAIT_2 depends on either the KeepAlive option in httpd.conf (which defaults to ON or the TCP server value for tcp_keepalive_interval which defaults usually to 7200000 milliseconds (2 hours) I set KeepAlive to OFF and the TCP setting to 45000 milliseconds. The problem was that the quasi-zombies where hanging around for 2 hours holding resources, but the rate at which they where being killed was slower than the buildup rate. By adjusting the timing of the kill process we balanced the create/destroy so that they didn't build up past a certain point.

See Code Depot


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.