Oracle multithreaded JDBC bulk select high-performance data export

Question

The goal of our solution is to export as fast as possible CLOB data from an events table. The concept is to logically partition the data with a partition_id column such that every thread from the external application (e.g. Spring Boot) does not interfere with the other threads. Approaches like CDC with Streaming APIs have been discussed and cannot be used in our case. Our goal is to use "basic" Oracle 12c/19c features that were highly optimized like the cost-based optimizer. There is a solution in place with advance queueing which is by far too slow and for internal reasons cannot be followed up.

We have discussed the following options.

Option 1, 'Mark events as processed in the events table'

The external thread of the application (e.g. Spring Boot) ...

1. registers via the partitions table for a dedicated partition (e.g. partition_id=10),
2. bulk selects a certain amount of data (e.g. rownum <= 100 ) of the events table of the partition (eg. partition_id=10) and processed the events externally,
3. bulk updates the processed ids in the events table (status -> ack),
4. de-registers the execution of the thread in the partitions table for another thread.

create table events(
    id               number(15),
    status_id        number(2), -- whether the events was successfully uploaded
    insert_timestamp timestamp,
    partition_id     number(9), --> partitions
    event            clob       -- the events paylod to export
);

create table partitions
(
    id               number(15),
    is_running       varchar2(1),  -- when a thread of the external application reserved a partition for an execution, a flag is set e.g. '+'
    last_execution   timestamp     -- when an execution of an export was done, the current timestamp is set
);

create index msg_idx on events (partition_id, status_id, id);

Question Option 1: As there will be multiple threads inserting new data, may be 10-50 threads bulk selecting data, and as many threads bulk updating the same table - however, always different rows!! - is there any risk that Oracle cannot handle the processing efficiently and maybe e.g. locks the whole table etc.? What would be important to consider using this approach?

Option 2, 'Save the highest processed commit sequence id ORA_ROWSCN in a partitions table as offset'

This option would make use of the ROWDEPENDENCIES feature. As the insert/commit sequence is known as number it can be used as "last_processed offset". The processing is similar for the registration of thread. The selection on the events table would start based on the last offset ORA_ROWSCN (like Apache Kafka consumers).

create table events
(
    id               number(15),
    insert_timestamp timestamp,
    partition_id     number(9), --> partitions
    event            clob       -- the event paylod to export
) ROWDEPENDENCIES;

create table partitions
(
    id               number(15),
    is_running       varchar2(1),  -- when a thread of the external application reserved a partition for an execution, a flag is set e.g. '+'
    last_offset      number(15),   -- ORA_ROWSCN of the last execution
    last_execution   timestamp     -- when an execution of an export was done, the current timestamp is set
);

Question Option 2: The advantage of this option would be, that events are only inserted and selected, never updated. However, as ORA_ROWSCN cannot be indexed, we would create an appropriate table with a materialized view.

• How should be the refresh of the materialized view configured ideally for such cases?
• What kind of risks comes with these solutions in term of hard to reproduce production issues?

Option 3, 'Save all processed event ids in a separate table'

The partition registration is the same as with the other options. The difference to option 1 is, that the events that were processed are not updated in the events table with a status, but inserted in a dedicated table. The selection would join the events and processed_events table to detect the events to be processed either with 'join', 'not in' etc.

create table events
(
    id               number(15),
    insert_timestamp timestamp,
    partition_id     number(9), --> partitions
    event            clob       -- the event paylod to export
);

create table processed_events(
    id               number(15) -- the event that where processed
    ,...
)

create table partitions
(
    id               number(15),
    is_running       varchar2(1),  -- when a thread of the external application reserved a partition for an execution, a flag is set e.g. '+'
    last_execution   timestamp     -- when an execution of an export was done, the current timestamp is set
);

Question Option 3: Are there any known performance aspects that should be considered?

For sure we will try the variants. However, the behavior on test systems with few CPUs might be quite different than on an instance with more than 100 CPUs etc. Therefore, we are very grateful for feedback of any kind.

Thanks for reading :)

Answer 1

I have also searched answer for this question and we use similar to your option 1. We create table

CREATE TABLE "EVENT" 
   (    "GUID" VARCHAR2(40) NOT NULL ENABLE, 
    ....
    "BODY" CLOB NOT NULL ENABLE, 
    "DO_SEND" NUMBER(1,0) DEFAULT 1 NOT NULL ENABLE, 
  "MOMENT_CREATE" TIMESTAMP (6) DEFAULT systimestamp NOT NULL ENABLE, 
  "MOMENT_SEND" TIMESTAMP (6), 
  "HASH_NUMBER" NUMBER(2,0) NOT NULL ENABLE
   )PARTITION BY RANGE(moment_create)INTERVAL (NUMTOYMINTERVAL (1,'MONTH'))
SUBPARTITION BY LIST(hash_number)
SUBPARTITION TEMPLATE (SUBPARTITION sp_part1 VALUES (1),
SUBPARTITION sp_part2 VALUES (2),
SUBPARTITION sp_part3 VALUES (3),
SUBPARTITION sp_part4 VALUES (4),
SUBPARTITION sp_part5 VALUES (5),
SUBPARTITION sp_part6 VALUES (6),
SUBPARTITION sp_part7 VALUES (7),
SUBPARTITION sp_part8 VALUES (8)
) 
(partition p_start values less than(date'2021-12-01') segment creation deferred);

And index

CREATE INDEX "EVENT_SEND" ON "EVENT" (HASH_NUMBER, CASE "DO_SEND" WHEN 1 THEN 1 ELSE NULL END ) 
 LOCAL;


CREATE unique INDEX PK_EVENT ON EVENT(MOMENT_CREATE,HASH_NUMBER,GUID) LOCAL;

In example below we first partition by date to easily delete partition. Subpartition by hash_number -> means each thread reads his own partition. Using EVENT_SEND we identify first record to read. First time scanning each partition by date until find not read records then cache this partition in thread value.

To your question in Option 1-> Right answer is to test it. We don't face this issue. In worse condition your could manipulate where to store subpartitions(different disks)

Some notes: We use hash_number (1-8) as thread_index for using static sql in query. In Oracle when you specifying partition name in plsql you have to use dynamic sql.