My Oracle Support Banner

High Number Of "enq: TX - Row Lock Contention" On FND_USER_PREFERENCES (Doc ID 2578726.1)

Last updated on OCTOBER 02, 2020

Applies to:

Oracle E-Business Suite Performance - Version 12.2.7 to 12.2.7 [Release 12.2]
Information in this document applies to any platform.

Symptoms

High number of "enq: TX - row lock contention" on FND_USER_PREFERENCES table

Running E-Business Suite 12.2.7 and recently upgraded from earlier version,experiencing lots of wait events on the FND_USER_PREFERENCES table.
This contention appears at anytime.

AWR Report below SQL

SQLID:3c4awu4jgqy2u

UPDATE FND_USER_PREFERENCES SET PREFERENCE_VALUE = :B4
WHERE
    USER_NAME = :B3
    AND
    MODULE_NAME = :B2
    AND
    PREFERENCE_NAME = :B1

Stuck Thread Report below PL/SQL

SQLID:a8hkdp9wa360n
DECLARE x_tab fnd_preference.prefs_tab_type;i NUMBER; BEGIN
x_tab(1).name := :1;
x_tab(1).value := :2;
x_tab(1).action := :3;
fnd_preference.save_changes(:4,:5,x_tab);


ERROR

Blocker
  The "Current Wait Event" of the blocking session is always "SQL*Net message from client"

Blocked
  The "Current Wait Event" of waiting sessions is "enq: TX - row lock contention" on FND_USER_PREFERENCES



This Contention can also cause oacore managed servers goes into Warning status and following call stack is reported in stuck thread incident reports :

 

java.net.SocketInputStream.socketRead0(Native Method)
java.net.SocketInputStream.read(SocketInputStream.java:153)
java.net.SocketInputStream.read(SocketInputStream.java:122)
oracle.net.nt.MetricsEnabledInputStream.read(TcpNTAdapter.java:730)
oracle.net.ns.Packet.receive(Packet.java:310)
oracle.net.ns.DataPacket.receive(DataPacket.java:108)
oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:325)
oracle.net.ns.NetInputStream.read(NetInputStream.java:269)
oracle.net.ns.NetInputStream.read(NetInputStream.java:191)
oracle.net.ns.NetInputStream.read(NetInputStream.java:108)
oracle.jdbc.driver.T4CSocketInputStreamWrapper.readNextPacket(T4CSocketInputStreamWrapper.java:126)
oracle.jdbc.driver.T4CSocketInputStreamWrapper.read(T4CSocketInputStreamWrapper.java:82)
oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1178)
oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:314)
oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)
oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)
oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:213)
oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1111)
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1488)
oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3769)
oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3954)
oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:9353)
oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1539)
weblogic.jdbc.wrapper.PreparedStatement.execute(PreparedStatement.java:99)
oracle.apps.fnd.common.DBPreferenceStore.save(DBPreferenceStore.java:485)
oracle.apps.fnd.login.lang.LanguageContext.recordLastSessionLanguage(LanguageContext.java:1043)
oracle.apps.fnd.sso.SessionMgr.createSession(SessionMgr.java:2463)
oracle.apps.fnd.sso.SessionMgr.createSession(SessionMgr.java:1091)
oracle.apps.fnd.sso.SessionMgr.createAppsSession(SessionMgr.java:3525)
oracle.apps.fnd.login.LoginHelper.doAuthenticate(LoginHelper.java:738)
oracle.apps.fnd.login.LoginHelper.callService(LoginHelper.java:1048)
oracle.apps.fnd.login.LoginHelper.service(LoginHelper.java:372)
_pages.__appslocallogin._jspService(__appslocallogin.java:194)

 

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.