When PeopleSoft runs sql queries and it runs longer then the service timeout for the service operation, the service will terminate its connection to that query, however, it may stay running on the database. This can result in long running queries on the database draining valuable CPU/memory from the database server. Oracle has the ability to check for “dead” connections and terminate them.

Adding the parameter sqlnet.expire_timeout to the sqlnet.ora file that is housed on your database server ($ORACLE_HOME/network/admin directory), will send a probe to verify that connections are active versus dead due to some client termination. If a connection is determined to be terminated or no longer in use, an error is returned which will cause the server process to exit. The parameter is specified in a time interval of minutes, and PeopleSoft recommends a 10 minute interval.

sqlnet.expire_timeout=10

Some considerations to this are you must be using TPC/IPC connections for this to work, so if you are using the parameter UseLocalOracleDB=1 in your configurations you are not using TPC/IPC. Most client use TPC/IPC so this should not be an issue. This will also put some additional overhead on the server to execute the probes, however, if you have large/long running sql queries running against the database, the performance hit should be offset by the gains in killing the unnecessary (dead) connection.