How to Automatically Resolve Blocking Locks Using Dead Connection Detection ...

In a recent load test of the Oracle Applications Release 11.5.10.2 Oracle Time and Labor module, we simulated time card entry and approvals for 400 simultaneous users. The generated wait events and subsequent wait times indicated the top three potential performance improvements were:

1. Running Statistics
2. Purging Workflow
3. Resolving Blocking Locks

The first two performance techniques are fairly well understood. These issues can be resolved by periodically running two concurrent programs: Analyze something something and Purge Obsolete Workflow Runtime Data (though Workflow may require additional scripts to deal with data that isn’t in the correct format for the purge program).

The blocking locks are primarily caused by abnormally terminated processes that refuse to release database locks when the process terminates. The typical process to remove blocking locks is performed manually by the DBA at the command line by querying the session and serial number of the process and then killing the specific database session. This manual approach takes time for the user to recognize there is a problem and more time for the DBA to find the session information and kill the session.

The use of Dead Connection Detection will automatically remove any dead database connections for any application that can recognize a dead process. The problem is the database considers the SQL*Net connection to be active, and by default, the Transparent Network Substrate can’t determine the status of the database connection .By setting parameters in the sqlnet.ora and tnsnames.ora files, dead connection detection can easily be enabled.

A common problem in Oracle Applications illustrates this problem. The Human Resources module uses a table named PER_ASSIGNMENT_ALL. Users access forms that lock rows in the table PER_ASSIGNMENT_ALL. When two users, or many times the same user with two forms open querying the same row, access that same row with the intention of updating the row, a blocking lock is created. This can happen when the first form is closed abnormally and the lock is not released. One lock is blocking and the other lock is waiting, but the blocking lock process has terminated and the database doesn’t realize the blocking lock is no longer valid, because DCD has not been enabled.

Oracle Network Basics

TCP/IP is a connection-oriented protocol, and provides packet timeout and retransmission in order to guarantee the safe and sequenced order of data packets. If a timely acknowledgement is not received in response to the probe packet, the TCP/IP stack will retransmit the packet some number of times before timing out. After TCP/IP gives up, then SQL*Net receives notification that the probe failed.

1. Dead Connection Detection

Dead Connection Detection (DCD) is a feature of SQL*Net 2.1 and later, including Oracle Net8. DCD detects when a partner in a SQL*Net V2 client/server or server/server connection has terminated unexpectedly, and releases the resources associated with it.

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 the sqlnet.ora file.

When the timer expires, SQL*Net on the server sends a "probe" packet to the client. The probe is an empty SQL*Net packet and does not represent any form of SQL*Net level data, but it creates data traffic on the underlying protocol.

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.

The client may be running any supported SQL*Net V2 release. DCD is more resource-intensive than similar mechanisms at the protocol level.

With DCD enabled, if the connection is idle for the duration of the time interval specified in minutes by the SQLNET.EXPIRE_TIME parameter, the Server-side process sends a small 10-byte packet to the client. This packet is sent using TCP/IP.

To Configure Dead Connection Detection (DCD)

Implement by:

changing SQLNET.EXPIRE_TIME = 1 (Minutes) to the sqlnet.ora file

With DCD enabled, if the connection is idle for the duration of the time interval specified in minutes by the SQLNET.EXPIRE_TIME parameter, the Server-side process sends a small 10-byte packet to the client. This packet is sent using TCP/IP.

If a timely acknowledgement is not received in response to the probe packet, the TCP/IP stack will retransmit the packet some number of times before timing out. After TCP/IP gives up, then SQL*Net receives notification that the probe failed.

If the client side connection is still connected and responsive, the client sends a response packet back to the database server, resetting the timer, and another packet will be sent when the next interval expires , assuming no other activity on the connection

If the client fails to respond to the DCD probe packet:
• The Server side process is marked as a dead connection and
• The database Process Monitor (PMON) performs the clean-up of the database processes / resources

Dead Connection Detection:
• DCD initiates clean up of OS and database processes that have disconnected / terminated abnormally
• DCD will not initiate clean-up for sessions that are still connected


2. tnsnames.ora

Client side SQL*Net connections do not enable keepalive for TCP connections by default. However, it is possible to enable this by adding the ENABLE=BROKEN parameter to the SQL*Net connect string in the tnsnames.ora file on the server..

Sample TNS alias to enable keepalive (notice the ENABLE=BROKEN clause):

VIS_BALANCE =
(DESCRIPTION =
(ENABLE=BROKEN)
(ADDRESS_LIST =
(LOAD_BALANCE = ON)
(FAILOVER = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = rh8)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rh6)(PORT = 1521)))

For more details, check out Mike's paper Parallel Concurrent Processing Failover and Load Balancing of E-Business Suite Release 11i and Release 12