Oracle LOB Improvement Ideas

[Chris Cranford]

Hi all -

The Oracle connector added support for capturing changes for CLOB / BLOB columns in Debezium 1.6.  While this support is incubating, we've been looking at some ways to improve and mature this feature.  In DBZ-4366 [1], some gaps have been identified in how the connector currently attempts to reconcile a transaction that operates on LOB columns and how we merge the synthetic LOB events with the logical DML events performed on the same row.  This reconciliation step's goal is to emit a single logical change event that consists of 1 or many redo entries mined from LogMiner for a single row when LOB columns are involved.

Unfortunately, one of the shortcomings identified in DBZ-4366 is that the reconcile step makes some assumptions about how the LOB data is manipulated.  In particular, it does not take into account situations where the DBMS_LOB package is used to manipulate the LOB-based columns using WRITEAPPEND, WRITE, or ERASE in certain scenarios which leads to the LOB data being inconsistently represented in the change event.  We'd like to solve this problem with your help / feedback.

So far we've identified 4 ideas and we'd like to know if perhaps we've overlooked some other obvious choices.

    (a) Emit LOB changes from the redo logs to a separate topic from DML events
    (b) Emit LOB changes from the redo logs to the same topic as DML events but as different event types
    (c) Provide an SMT that allows the re-selection of a row out of bands if a specific indicator is detected on the emitted event
    (d) Implement (c) baked directly into the Oracle connector, no SMT required.   

Option (a) is really the least ideal of them all.  This puts the ownership of combining multiple topics and guaranteeing the chronological order of events across those topics on the consumer.  While this certainly makes it easy for us, we certainly prefer a more suitable approach because we know we can do better; and we should :).

Option (b) is an improved version of (a).  By using the same topic and emitting separate events (insert, update, delete, lob-delta), the chronological order of events is maintained automatically and consumption of these events become significantly easier to manage in the consumer pipeline.

In either (a) or (b), the emitted event's value might consist of something like:

{
  "op": "ld",
  "action": "w",
  "offset": 10,
  "length": 3,
  "bytes": "..."
}

The op-code "ld" stands for LOB delta.  The action would consist of "w" for WRITE and "e" for ERASE.  The offset/length represent the position/size in the LOB column that is being changed with the bytes simply being the raw character/bytes for the delta.

Option (c) takes a slightly different approach to the reconciliation and instead relies on using some marker indicator on the incoming change event to determine whether we need to re-select the row or allow the incoming event to be passed through as is.  For changes to tables that don't have LOB columns, the events would simply be passed through without modification.  For changes to tables that do have LOB columns, instead of mining the LOB data from the transaction logs we would instead simply mark the emitted event to be re-selected and the SMT would issue an out of bands query on the row, building a new read event that would be emitted in its place.  The benefit is that the Oracle connector wouldn't need to buffer any LOB data, keeping its memory footprint lower and would allow for users to use JDBC or the DBMS_LOB package to manipulate the LOB columns in any desired way; while emitting consistent events for those rows.  Such an SMT could have cross-connector benefits by even allowing a PostgreSQL user to re-select toasted columns in the same way we would LOB columns for Oracle.

Option (d) builds on top of (c) by implementing the SMT logic directly in the connector.  The idea is that there could be a "lob.handling.mode" connector option that has a value-set of {"merged", "reselect", ...}.  The "merged" option would tell the connector to use its existing functionality.  We would still need to address any potential limitations of this mode either by fixing them (if possible) or documenting them.  The "reselect" option would the baking of the SMT option (c) idea directly into the connector.  The benefit to baking this directly into the connector is that we could make some assumptions about how we mine redo entries, most importantly we could skip doing the re-mining entirely.  We basically would not care about the LOB changes as they're materialized by Oracle LogMiner and we would rely on our relational model to know if a table has a LOB column and if so, trigger the re-selection of the row like we do for a snapshot.

Do any of you spot or think there are some other alternatives we may have missed, feel strongly about one approach, or have questions about any of these?

We'd love to hear your feedback!

Thanks,
Chris

[1]: https://issues.redhat.com/browse/DBZ-4366

[Dominique Chanet]

On Wednesday, December 1, 2021 at 9:20:28 AM UTC+1 Chris Cranford wrote:

Hi all -

The Oracle connector added support for capturing changes for CLOB / BLOB columns in Debezium 1.6.  While this support is incubating, we've been looking at some ways to improve and mature this feature.  In DBZ-4366 [1], some gaps have been identified in how the connector currently attempts to reconcile a transaction that operates on LOB columns and how we merge the synthetic LOB events with the logical DML events performed on the same row.  This reconciliation step's goal is to emit a single logical change event that consists of 1 or many redo entries mined from LogMiner for a single row when LOB columns are involved.

Unfortunately, one of the shortcomings identified in DBZ-4366 is that the reconcile step makes some assumptions about how the LOB data is manipulated.  In particular, it does not take into account situations where the DBMS_LOB package is used to manipulate the LOB-based columns using WRITEAPPEND, WRITE, or ERASE in certain scenarios which leads to the LOB data being inconsistently represented in the change event.  We'd like to solve this problem with your help / feedback.

So far we've identified 4 ideas and we'd like to know if perhaps we've overlooked some other obvious choices.

    (a) Emit LOB changes from the redo logs to a separate topic from DML events
    (b) Emit LOB changes from the redo logs to the same topic as DML events but as different event types
    (c) Provide an SMT that allows the re-selection of a row out of bands if a specific indicator is detected on the emitted event
    (d) Implement (c) baked directly into the Oracle connector, no SMT required.    

Option (a) is really the least ideal of them all.  This puts the ownership of combining multiple topics and guaranteeing the chronological order of events across those topics on the consumer.  While this certainly makes it easy for us, we certainly prefer a more suitable approach because we know we can do better; and we should :).

Agreed.

 

Option (b) is an improved version of (a).  By using the same topic and emitting separate events (insert, update, delete, lob-delta), the chronological order of events is maintained automatically and consumption of these events become significantly easier to manage in the consumer pipeline.

In either (a) or (b), the emitted event's value might consist of something like:

{
  "op": "ld",
  "action": "w",
  "offset": 10,
  "length": 3,
  "bytes": "..."
}

The op-code "ld" stands for LOB delta.  The action would consist of "w" for WRITE and "e" for ERASE.  The offset/length represent the position/size in the LOB column that is being changed with the bytes simply being the raw character/bytes for the delta.

One advantage of going with options (a) or (b) is that the emitted messages are always on the small(ish) side - which means you won't run into too much trouble posting them on a Kafka topic. LOBs can (at least in theory) run into the hundreds of megabytes, which puts you at risk of running the connector out of memory, and which forces you to use alternative means of serialization because you can't comfortably post a 500MB message on a Kafka topic. You might still have trouble emitting the initial snapshot of a large LOB value, but you could probably replace that with a set of smaller, synthetic LOB deltas to work around it.

This option works well for DB replication use cases, where you don't really care how many messages it takes for your LOB to reach the target; as long as you can reconstruct it in the end, all is well. It's less ideal, though, for streaming processing of the change records. There, you'd probably want to see the entire row in one go, without the need for a stateful cache of the LOB contents. OTOH, I can't imagine many use cases where you'd want to do streaming processing on (large) LOB values.

 

Option (c) takes a slightly different approach to the reconciliation and instead relies on using some marker indicator on the incoming change event to determine whether we need to re-select the row or allow the incoming event to be passed through as is.  For changes to tables that don't have LOB columns, the events would simply be passed through without modification.  For changes to tables that do have LOB columns, instead of mining the LOB data from the transaction logs we would instead simply mark the emitted event to be re-selected and the SMT would issue an out of bands query on the row, building a new read event that would be emitted in its place.  The benefit is that the Oracle connector wouldn't need to buffer any LOB data, keeping its memory footprint lower and would allow for users to use JDBC or the DBMS_LOB package to manipulate the LOB columns in any desired way; while emitting consistent events for those rows.  Such an SMT could have cross-connector benefits by even allowing a PostgreSQL user to re-select toasted columns in the same way we would LOB columns for Oracle.

Option (d) builds on top of (c) by implementing the SMT logic directly in the connector.  The idea is that there could be a "lob.handling.mode" connector option that has a value-set of {"merged", "reselect", ...}.  The "merged" option would tell the connector to use its existing functionality.  We would still need to address any potential limitations of this mode either by fixing them (if possible) or documenting them.  The "reselect" option would the baking of the SMT option (c) idea directly into the connector.  The benefit to baking this directly into the connector is that we could make some assumptions about how we mine redo entries, most importantly we could skip doing the re-mining entirely.  We basically would not care about the LOB changes as they're materialized by Oracle LogMiner and we would rely on our relational model to know if a table has a LOB column and if so, trigger the re-selection of the row like we do for a snapshot.

I'm still plugging away on a PR that should make the current logic a bit more robust at least; I hope to be able to submit that in the coming days.

In the ideal world, we'd introduce `lob.handling.mode` and give it three options: "merged" (an improvement on current behavior), "reselect" (option c or d as discussed above) or "chunked" (option b as discussed above). I think each of the approaches has their own merits and drawbacks, depending on the use case you're shooting for.

The downside is, of course, triple the amount of work and triple the amount of code to maintain. I'm not sure if that's worth it...

[Chris Cranford]

Hi Dominique!

I look at these LOB handling mode options in the same way that we look the various decoders for PostgreSQL.  There is a certain level of maintainability that comes with having such fleixbility but I think as long as we have the proper test coverage the 3 modes are fine.  I'm not opposed to focusing on flexibility right now because I think users will use the connector for a variety of reasons as you indicated.  We can always circle back in the future and re-evaluate if all modes are still necessary and if one is not as popular or useful we can certainly trim back.

Gunnar / Jiri; either of you have a particular stance on this that differs from my own you'd like to share and persuade me otherwise? :)

Chris

--
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/d29c4db3-8c7d-4c3a-88ef-af63b9ffbdbfn%40googlegroups.com.