Batch Update Jobs on Table with Text Index Locking on $R, the Rowid Mapping Table (Doc ID 803464.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Text - Version: 10.2.0.1 to 10.2.0.5 - Release: 10.2 to 10.2
Information in this document applies to any platform.
***Checked for relevance on 18-Oct-2010***

Symptoms

Each job which is updating a table with Text context index is holding/waiting on a lock on the $R table so only one batch can process at a time. There is currently 80 Oracle sessions running this batch job. This batch job is taking several hours for some of the jobs to finish.

Tracing a session we can see 99% of the time was spent waiting on this query:

select data
from
"PRDMSTR"."DR$RTI_NAMES_1_CTX$R" where row_no = :row_no for update

call     count      cpu    elapsed       disk      query    current       rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse      163     0.02       0.00          0          0          0          0
Execute    328     2.86    5851.61          9      20183      13331          0
Fetch      328     0.08       0.06          0       6892          0        328
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total      819     2.96    5851.68          9      27075      13331        328

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: 65 (PRDMSTR) (recursive depth: 2)

Rows    Execution Plan
------- ---------------------------------------------------
      0 SELECT STATEMENT MODE: CHOOSE
      0  FOR UPDATE
      0   TABLE ACCESS (FULL) OF 'DR$RTI_NAMES_1_CTX$R' (TABLE)

*******************************************************************************

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms