We need Oracle LogMiner to provide Debezium transaction markers so that we know its safe to dispatch or discard transactions based on whether the transaction is committed or rolled back.

But there are some situations where the database may contain far more transactions that never mutate captured tables than those that do. This can lead to situations where the volume of changes sent over the network wire for START and COMMIT can be exceedingly high compared to the changes.

For example, one log provided for a 5hr period consisted of:

• 40M unique transactions
• Only 193k of those transactions had relevant changes for Debezium

In this log, the connector was already configured to skip the START events, but we still needed to process just under 40 million COMMIT events alone.

I propose introducing a new internal configuration option: log.mining.use.cte.query.

By default, this option is false, but enabling it allows Debezium to utilize a CTE-based query to filter out all non-relevant transactions that have no DML changes for captured tables at the database level before streaming the changes back to Debezium.

There are some caveats:

• The CTE and the non-CTE query both need to reference V$LOGMNR_CONTENTS and for each reference, Oracle will re-read the logs.
• Due to the CTE, this could lead to PGA_AGGREGATE_LIMIT issues if there are lots of relevant transactions within the batch window.

In such cases, users should reduce the log.mining.batch.size.* settings so that the chunk-based processing used by the CTE query does not require nearly as much memory to hash and store the relevant transaction identifiers.