Increased LogMiner query latency in CTE query despite returning 0 rows [DBZ-9447]

Migrated from [DBZ-9447](https://issues.redhat.com/browse/DBZ-9447)

In order to make your issue reports as actionable as possible, please provide the following information, depending on the issue type.
h1. Bug report

For bug reports, provide this information, please:
h2. What Debezium connector do you use and what version?

Oracle connector running on Debezium 3.2.1
h2. What is the connector configuration?

~140 tables in the include list, here's the LogMiner configuration. A heartbeat is running every minute on an externally scheduled Cron job:
{code:java}
"internal.log.mining.use.cte.query": "true",
"query.fetch.size": "50000",
"log.mining.batch.size.increment": "50000",
"log.mining.batch.size.max": "100000", {code}
h2. What is the captured database version and mode of deployment?

(E.g. on-premises, with a specific cloud provider, etc.)

On premise Oracle 11g
h2. What behavior do you expect?

LogMiner query times comparable to single-pass version of the query when there are 0 changes the connector is interested in in the batch
h2. What behavior do you see?

LogMiner query time increase despite queries returning 0 rows.

This is during a period of heavy batch job activity, made up of large transactions potentially producing millions of rows. Other connectors are deployed against the same Oracle instance and the LogMiner query times are significantly lower. Here are some stats across this period that might be useful, taken from:

[#community-oracle > Performance degradation after connector setup change @ 💬|https://debezium.zulipchat.com/#narrow/channel/348250-community-oracle/topic/Performance.20degradation.20after.20connector.20setup.20change/near/537832306]
h3. Max logs mined

Orange is the CTE connector
!image-2025-09-05-16-01-53-328.png!
h3. LogMiner query performance

Purple is LastDurationOfFetchQueryInMilliseconds, light blue is LogSwitchCount, magenta is MaximumMinedLogCount
!image-2025-09-05-16-01-38-211.png!
h3. OffsetScn over time

Orange is the CTE connector, other lines are not using the CTE
!image-2025-09-05-16-00-59-084.png!
h3. LagFromSourceInMilliseconds

Orange is the CTE connector, converted to seconds

!image-2025-09-05-16-02-52-256.png!
h3. TotalProcessedRows

Orange is the CTE connector

!image-2025-09-05-16-03-36-257.png!
h2. Do you see the same behaviour using the latest released Debezium version?

(Ideally, also verify with latest Alpha/Beta/CR version)

We've only tested 3.2.1 for this one
h2. Do you have the connector logs, ideally from start till finish?

(You might be asked later to provide [DEBUG/TRACE|https://debezium.io/documentation/reference/stable/operations/logging.html] level log)

Unfortunately not!
h2. How to reproduce the issue using our [tutorial|https://github.com/debezium/debezium-examples/tree/main/tutorial] deployment?

Potentially possible by enabling CTE queries in the Oracle connector, generating a lot of changes in tables outside of the include list and monitoring the impact on LastDurationOfFetchQueryInMilliseconds in two connectors - One using the CTE, one not using the CTE

Start with the Debezium Oracle connector's CTE LogMiner query path and compare its behavior with the single-pass query when no included-table changes are returned. Reproduce the workload described in the issue, using the tutorial setup if possible, and compare query duration and mined-log metrics between CTE and non-CTE connectors. Done means the CTE query no longer shows increased latency in the zero-row case or the cause is documented.

Written by the indexing model from the issue text.