Oracle performance issue

[Miroslav Hořejší]

Hi everyone,

We are facing performance issues with the Debezium Oracle connector. We’ve noticed a growing "behind source" lag, which eventually leads to an exception stating that the requested SCN is no longer available because the redo logs have been deleted. Please note that our redo log retention is set to at least 24 hours.
It seems that Debezium registers an open transaction and holds it, but never receives the "end of transaction" signal. Consequently, Debezium reports a long-running transaction even though our DBA has confirmed there are no such transactions active in the database.

Our Environment:
Deployment: Debezium running within a Spring Boot application.
Redo Log Size: 2GB, multiple groups.
Traffic: Average of 300 log switches per day, with peaks of up to 35 switches per hour.

Observed behaviour across versions:
3.2.2.Final: No major issues recorded (only occasional warnings).
3.3.1.Final: Issues appeared after several months of stable run.
3.3.2.Final / 3.4.0.Final / 3.4.1.Final: Issues appear within a few days.

What are the recommended settings for this kind of load and to prevent these "ghost" long-running transactions from stalling the connector?

Thanks in advance for any advice!

[Chris Cranford]

Hi -

Ideally we need to identify what the transaction looks like. One way to do this is to enable DEBUG logging for io.debezium.connector.oracle.logminer.buffered.BufferedLogMinerStreamingChangeEventSource. At the end of each mining step, we will log the active transaction identifiers and their starting SCN number. You could have the DBA locate the transaction manually using LogMiner and then extract the information about the transaction.

Now if the transaction is rolled back, you could be looking at this issue [1], so upgrading to 3.4.2.Final would help if it is related. 

If it isn't a roll back transaction, then sharing the details of the extracted changes would be helpful to see if there are any edge cases that may be happening in your system that Debezium isn't accounting for.

Thanks,
-cc

[1]: https://github.com/debezium/dbz/issues/1145

--
You received this message because you are subscribed to the Google Groups "debezium" group.
To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/debezium/f50fe8b6-fdb9-45ce-b603-0b6f07b89138n%40googlegroups.com.