Oracle Connection Pooling through the LTM

The way Oracle's sqlnet.expire_time works is to send a DCD (Dead Client Detection) packet at the specified time interval which tests the connection. If the test fails, the application server "should" establish a new session to the database server. In effect, however, the DCD packet will reset idle timeout counters on the F5's and most network firewalls.

 

 

Generally, a good rule of thumb is to use the formula, 3n+1, for the number of seconds to set idle timeouts to on network equipment. This allows for the possibility of a couple of dropped test packets without requiring reestablishment of the session. With a 300 second TCP timeout on the F5's default tcp profile, we can work backward to establish what n should be to set the sqlnet.expire_time on the application servers without creating a new F5 profile. 3n+1=300sec means that n in this case should be 99 seconds. Due to the fact that sqlnet.expire_time represents whole integer minutes only, the Oracle application server sqlnet.expire_time should be set to 1 minute.

 

 

Since it seems that all Oracle documentation makes reference to setting sqlnet.expire_time to 10 minutes, perhaps a better solution would be to create a F5 tcp profile using 3n+1 seconds as the idle timeout where n would be 600 seconds in this case. Were we to do this, then the F5 idle timeout should be set to 1801 seconds.

 

 

In practice, setting sqlnet.expire_time to 45 and the F5 timeout to 1 hour wouldn't be a very good solution since it could take up to 45 minutes for the application server to database connection to be down before the system realized it and tried to recover. Depending on the application, somewhere between 1 and 10 minutes feels like it would make more sense.