Apply Servers Waiting on RO Enqueue

(Doc ID 787672.1)

Last updated on SEPTEMBER 07, 2015

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.4 to 11.2.0.3 [Release 10.2 to 11.2]
Information in this document applies to any platform.

Symptoms

Checking DBA_APPLY_PROGRESS for APPLY Latency
One of the Apply server will be waiting for RO Enqueue.

This could also be confirmed from systemstate dump

COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A17
COLUMN 'Latency in Seconds' FORMAT 999999
COLUMN 'Message Creation' FORMAT A17
COLUMN 'Apply Time' FORMAT A17
COLUMN APPLIED_MESSAGE_NUMBER HEADING 'Applied|Message|Number' FORMAT 999999

SELECT APPLY_NAME,
(APPLY_TIME-APPLIED_MESSAGE_CREATE_TIME)*86400 "Latency in Seconds",
TO_CHAR(APPLIED_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY')
"Message Creation",
TO_CHAR(APPLY_TIME,'HH24:MI:SS MM/DD/YY') "Apply Time",
APPLIED_MESSAGE_NUMBER
FROM DBA_APPLY_PROGRESS;

Offending SQL statement will be:

alter table sys.streams$_apply_spill_msgs_part truncate partition p2

The holder process will be the CKPT process .CKPT process will be just waiting for 'rdbms ipc message'

Find the list of sessions holding/waiting for RO Enqueue:

SQL> select * from v$lock where type='RO';

 Sessions holding RO Enqueue and blocking:

SQL> select * from v$lock where type='RO' and BLOCK=1;

Find the SQL's being executed by these sessions:

select tx.sql_text from v$sqltext tx, v$session s
where s.sql_address = tx.address
and s.sql_hash_value = tx.hash_value
and s.sid = <SID> /* Provide the SID acquired from previous Queries */
order by tx.piece;

The waiting Session should return:

alter table sys.streams$_apply_spill_msgs_part truncate partition <partition name>

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