Oracle Terminated Connection Timeout
来源:互联网 发布:c#数组转字符串 编辑:程序博客网 时间:2024/05/14 17:47
I have recently come across situations on two different PeopleSoft sites where ad-hoc queries continue to run on the Oracle database server long after the Application Server process, which is the Oracle session client, has terminated. Often, queries perform poorly because they are poorly coded, but that is another story. To help guard against this situation Oracle has mechanism calledTerminated Connection Timeout (also known as Dead Connection Detection (DCD) when it was introduced in Net8).
Oracle Support Note 615782.1 explains the mechanism. "DCD is initiated on the server when a connection is established. At this time SQL*Net reads the SQL*Net parameter files and sets a timer to generate an alarm. The timer interval is set by providing a non-zero value in minutes for the SQLNET.EXPIRE_TIME parameter in thesqlnet.ora file on the database server side. When the timer expires, SQL*Net on the server sends a 'probe' packet, essentially an empty SQL*Net packet, to the client. If the client end of the connection is still active, the probe is discarded, and the timer mechanism is reset. If the client has terminated abnormally, the server will receive an error from the send call issued for the probe, and SQL*Net on the server will signal the operating system to release the connection's resources."
Thus, if a PeopleSoft operator initiates an ad-hoc query that runs on the PSQRYSRV server for longer than the ICQueryservice time-out (default 1200 seconds), then Tuxedo will terminate and restart the busy server process. However, the query will continue run on the database server until the current fetch operation returns. In the case of a query performing a large sort or hash operation, it might be a long time before the first row is returned. All the while, the query is continuing to consume resources on the database.
PeopleTools 8.44, also introduced the ability to kill a query via the Query Monitor that had reached a maximum run time. This is one of the functions of the PSMONITORSRV server process (see Oracle Support Note624339.1. The maximum run time is specified in a permission list (see Security Administration PeopleBookPermission List Query Profile) and then the ability to kill queries that have timed out can be enabled or disabled system-wide (see PeopleSoft Query PeopleBookQuery Administration. It will kill the application server process that submitted the query, but for the same reasons, the query may continue to run on the database.
Thus, setting Terminated Connection Timeout is not merely a good idea for a PeopleSoft system running on Oracle, it is effectively mandatory. Otherwise. some PeopleSoft functionality simply won't work as intended.
What is an appropriate value for SQLNET.EXPIRE_TIME?
The value for this parameter is the time between successive SQL*Net probes sent by the Oracle shadow server process to the client. Setting it is a balance between the maximum time that a query can be left to consume resources after a client process terminates, against the additional overhead of every client process sending a probe every few minutes.
The SQL*Net documents often talk about additional network traffic generated by DCD. This was a consideration in the past on client-server applications that ran across a wide area network. However, it is rarely a consideration in relatively modern systems such as PeopleTools 8, the database connections are made by Application Server and Process Scheduler, which are usually physically close to the database server.
The time-out can be set independently of any of the other time-outs for the Application Server and Web Server. Documents on Metalink often suggest 5 or 10 minutes, and I don't think that is unreasonable.
My thanks to Colin Kilpatrick who prompted me to look at this again.
Oracle Support Note 615782.1 explains the mechanism. "DCD is initiated on the server when a connection is established. At this time SQL*Net reads the SQL*Net parameter files and sets a timer to generate an alarm. The timer interval is set by providing a non-zero value in minutes for the SQLNET.EXPIRE_TIME parameter in thesqlnet.ora file on the database server side. When the timer expires, SQL*Net on the server sends a 'probe' packet, essentially an empty SQL*Net packet, to the client. If the client end of the connection is still active, the probe is discarded, and the timer mechanism is reset. If the client has terminated abnormally, the server will receive an error from the send call issued for the probe, and SQL*Net on the server will signal the operating system to release the connection's resources."
Thus, if a PeopleSoft operator initiates an ad-hoc query that runs on the PSQRYSRV server for longer than the ICQueryservice time-out (default 1200 seconds), then Tuxedo will terminate and restart the busy server process. However, the query will continue run on the database server until the current fetch operation returns. In the case of a query performing a large sort or hash operation, it might be a long time before the first row is returned. All the while, the query is continuing to consume resources on the database.
PeopleTools 8.44, also introduced the ability to kill a query via the Query Monitor that had reached a maximum run time. This is one of the functions of the PSMONITORSRV server process (see Oracle Support Note624339.1. The maximum run time is specified in a permission list (see Security Administration PeopleBookPermission List Query Profile) and then the ability to kill queries that have timed out can be enabled or disabled system-wide (see PeopleSoft Query PeopleBookQuery Administration. It will kill the application server process that submitted the query, but for the same reasons, the query may continue to run on the database.
Thus, setting Terminated Connection Timeout is not merely a good idea for a PeopleSoft system running on Oracle, it is effectively mandatory. Otherwise. some PeopleSoft functionality simply won't work as intended.
What is an appropriate value for SQLNET.EXPIRE_TIME?
The value for this parameter is the time between successive SQL*Net probes sent by the Oracle shadow server process to the client. Setting it is a balance between the maximum time that a query can be left to consume resources after a client process terminates, against the additional overhead of every client process sending a probe every few minutes.
The SQL*Net documents often talk about additional network traffic generated by DCD. This was a consideration in the past on client-server applications that ran across a wide area network. However, it is rarely a consideration in relatively modern systems such as PeopleTools 8, the database connections are made by Application Server and Process Scheduler, which are usually physically close to the database server.
The time-out can be set independently of any of the other time-outs for the Application Server and Web Server. Documents on Metalink often suggest 5 or 10 minutes, and I don't think that is unreasonable.
My thanks to Colin Kilpatrick who prompted me to look at this again.
- Oracle Terminated Connection Timeout
- Connection Timeout和Command Timeout
- restemplate设置connection timeout
- ssh出现connection timeout
- vagrant connection timeout. Retrying...
- Ldap connection timeout error:
- MongoDB connection timeout
- 不支持关键字: “connection timeout”
- AxisFault: Timeout waiting for connection
- AxisFault: Timeout waiting for connection
- AxisFault: Timeout waiting for connection
- ConnectionPoolTimeoutException: Timeout waiting for connection
- Connection Timeout Using LINQ DataContext
- BlToolKit database connection timeout exception
- IDbConnection的connection timeout 和 IDbcommand.commandTimeout
- Connection Pool 与 Connect Timeout=0
- Axis2 - Timeout waiting for connection 问题解决方法
- ZeroMQ timeout a REQ/REP connection
- 关于C#中Thread.Join()的一点理解
- Poor performance of PSPMSESSIONS_VW view affects Performance Monitor System Monitor Component
- Cassandra参考资料汇总
- 使用PowerDesigner生成HTML功能
- vs_setup.msi could not be opened (vs_setup.msi 未能打开)
- Oracle Terminated Connection Timeout
- 纯小数转换与定点补码
- [解锁越狱]诺基亚 Lumia 710 800 最详细的解锁越狱教程
- 3G网卡在开发板上使用(模式转换)
- 此号被黑
- 基于数论变换的大整数乘法的性能测试
- [Oracle]关于Oracle流水记
- java.lang包中类的总
- ZOJ 2478 Encoding