integrated replicat is slow due to query on sys."_DBA_APPLY_CDR_INFO" (Doc ID 2242352.1)

Last updated on MARCH 09, 2017

Applies to:

Oracle GoldenGate - Version 12.1.3.0.0 and later
Information in this document applies to any platform.

Symptoms

sys."_DBA_APPLY_CDR_INFO" -- Query is running with more cost even there are no records in the table.

There are several IRs, with CDR and exception table setup to store the offending records.

One IR (RPOG071) abended after processing millions of collision records, and then a new IR was created to replace it.
The abended IR was left abended. Later it was found that the running IRs are all slow, due to following query:

select count(*)
from sys."_DBA_APPLY_CDR_INFO"
where spare1 = :apnum and resolution_status = 1;

PLAN:

SQL_ID cxf2ztw8kfzj8
--------------------
select count(*) from sys."_DBA_APPLY_CDR_INFO" where spare1 = :apnum
and resolution_status = 1

Plan hash value: 1033867489

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 63134 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
| 2 | TABLE ACCESS FULL| APPLY$_CDR_INFO | 1 | 6 | 63134 (1)| 00:00:03 |
--------------------------------------------------------------------------------------



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