OBJECT_LOCK Table Requires Index. (Doc ID 871742.1)

Last updated on SEPTEMBER 17, 2013

Applies to:

Oracle Transportation Management - Version 5.5.05.02 to 5.5.05.04 [Release 5.5]
Information in this document applies to any platform.
***Checked for relevance on 17-Sep-2013***


Symptoms

-- Problem Statement:
Under active scalability mode, an Object_Lock table update query has been identified to be very ineffective compared to other object_lock update queries, because it does not use the primary key for object_lock in the where clause thus this query has the largest elapsed_time of any SQLcausing performance slowdowns in agent processing.

- The query out of V$sql is:
update object_lock set owner_machine_gid=null, owned_since=null, activity=null,
log_process_id=null, owner_sequence=null where owner_sequence=:1

- The other two update queries identified using v$sql table use primary key in where clause and
are listed below:

update object_lock set owner_machine_gid=:1, owned_since=sys_extract_utc(current_timestamp),
activity=:2, log_process_id=:3, owner_sequence=:4 where lock_type=:5 and object_id=:6

update object_lock set owner_machine_gid=:1, owned_since=sys_extract_utc(current_timestamp),
activity=:2, log_process_id=:3, owner_sequence=:4 where lock_type=:5 and object_id=:6

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