Row Lock Contention Updating Rows In WC_NAVIGATION_ACTIVITY (Doc ID 1954421.1)

Last updated on OCTOBER 09, 2023

Applies to:

Symptoms

During performance testing, users are experience very slow performance navigating the WebCenter Portal site with 50 concurrent users.
Pages are taking up to 50 seconds to load. WebLogic server is showing an increase in the Hogging Thread Count.

During this performance test, it was found that the table WC_NAVIGATION_ACTIVITY had a lot of "enq: TX - row lock contention" wait events.  After sometime, the database sessions all complete their update on the table WC_NAVIGATION_ACTIVITY.

Database Analysis of performance test:

• Sessions were running via ‘anonymous’ application user.
• The sessions were getting queued up one after the other and were constantly stuck on ‘enq: TX - row lock contention’ wait event. This was because all of them were trying to run the below update statement:
  
  UPDATE WC_NAVIGATION_ACTIVITY SET EXT_ATTR1 = :1 , EXT_ATTR2 = :2 WHERE (USER_ID = :3 )
  
  
• The SQL statement above will try to update all records for a specific user_id (in this case ‘anonymous’) in the WC_NAVIGATION_ACTIVITY table, causing Row Lock Contention for all the other sessions that are connected as ‘anonymous’ user.

Cause

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