Apply Servers Waiting on RO Enqueue
(Doc ID 787672.1)
Last updated on AUGUST 04, 2018
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;
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;
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
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 |