Query on V$LOGMNR_CONTENTS not filtering on tables

[Michele Di Lella]

Hello everyone, I open this post to notify an issue that we are having with our installation of Kafka Connect with Oracle Debezium connector (Debezium v2.4 and Kafka v3.6.0).
Basically we noticed that the query that the connector does on the Oracle REDOLOG table retrieves a lot more data that actually concerns the table that we are listening on.
We extracted the query from the database logs:

SELECT SCN, SQL_REDO, OPERATION_CODE, TIMESTAMP, XID, CSF, TABLE_NAME, SEG_OWNER, OPERATION, USERNAME, ROW_ID, ROLLBACK, RS_ID, STATUS, INFO, SSN, THREAD# FROM V$LOGMNR_CONTENTS WHERE SCN > :1 AND SCN <= :2 AND (OPERATION_CODE IN (1,2,3,6,7,34,36,255) OR (OPERATION_CODE = 5 AND INFO NOT LIKE 'INTERNAL DDL%')) AND (SEG_OWNER IS NULL OR SEG_OWNER NOT IN ('APPQOSSYS','AUDSYS','CTXSYS','DVSYS','DBSFWUSER','DBSNMP','GSMADMIN_INTERNAL','LBACSYS','MDSYS','OJVMSYS','OLAPSYS','ORDDATA','ORDSYS','OUTLN','SYS','SYSTEM','WMSYS','XDB'));

And noticed that there is no filter applied regarding the table name. So we did some research on your documentation and we found this connector property: log.mining.query.filter.mode
It's default value is "none", which results in no filtering, so we valued instead with "in".
After doing so, the above query changed like this:

SELECT SCN, SQL_REDO, OPERATION_CODE, TIMESTAMP, XID, CSF, TABLE_NAME, SEG_OWNER, OPERATION, USERNAME, ROW_ID, ROLLBACK, RS_ID, STATUS, INFO, SSN, THREAD# FROM V$LOGMNR_CONTENTS WHERE SCN > :1 AND SCN <= :2 AND (OPERATION_CODE IN (1,2,3,6,7,34,36,255) OR (OPERATION_CODE = 5 AND INFO NOT LIKE 'INTERNAL DDL%')) AND (SEG_OWNER IS NULL OR UPPER(SEG_OWNER) IN ('UNKNOWN','DBAOBT')) AND (TABLE_NAME IS NULL OR TABLE_NAME LIKE 'OBJ#%' OR UPPER(SEG_OWNER || '.' || TABLE_NAME) IN ('DBAOBT.OBT_VEHICLE_ORDERS'))

Where "DBAOBT.OBT_VEHICLE_ORDERS" is the name of the table we are monitoring.
This query unfortunately has 2 issues that make it useless:

1. The column "TABLE_NAME" of the V$LOGMNR_CONTENTS does not contain the names of the tables, it contains instead a codification of the table in the form "OBJ# number". Because of this, the IN clause at the end of the above query will never be true. We found out that the actual name of the table can be decodified using the "DBA_OBJECTS" table in JOIN with the V$LOGMNR_CONTENTS view.
2. In the last AND condition of the query (last line above), before the "UPPER(SEG_OWNER || '.' || TABLE_NAME) IN ('DBAOBT.OBT_VEHICLE_ORDERS')" part, there are other 2 conditions in OR, among which there is "TABLE_NAME LIKE 'OBJ#%' ". This makes completely useless any following filter on the tablename, as the condition will be always true, since any table is identified on the V$LOGMNR_CONTENTS view with TABLENAME LIKE "OBJ# number".

Because of this, even with the log.mining.query.filter.mode set to "in", the query is downloading a lot of useless data from the REDOLOGS.
We did some test to put an actual number on it, and on our database the query is downloading around 3 billion records where actually the records related to our monitored table are only in the order of the hundreds.
This caused a big load on our infrastructure in terms of network usage and DB CPU usage.

We even went as far as talking to Confluent support about this, and they conveyed this looks like a bug on Debezium side.

Can you please give us a feedback on your side about this? Because in this state, the Debezium connector is unusable on our usecase.

Thank you!

[Chris Cranford]

Hi Michele,

As I have already reported here [1], lets keep the conversation on Zulip.

Thanks,
Chris

[1]: https://debezium.zulipchat.com/#narrow/stream/348250-community-oracle/topic/Query.20on.20V.24LOGMNR_CONTENTS.20not.20filtering.20on.20tables/near/422645839

--
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 on the web visit https://groups.google.com/d/msgid/debezium/48d6a10a-5b4a-4b25-92a0-390de15aadbbn%40googlegroups.com.