My Oracle Support Banner

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

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
  Cause
  Solution
  References

Platforms: 1-914CU;

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

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