Subject: [Question] JDBC Connector + Oracle ojdbc8: batch_size not working for large column tables due to 2MB internal threshold

["怼烎@" <72...@qq.com>]

Hi all,


I've been investigating an issue where SeaTunnel's JDBC batch_size
configuration does not take effect when syncing Oracle tables with many
large VARCHAR2 columns. I've traced the root cause to the Oracle JDBC
driver itself and would like to ask the community for help.


==========
Environment
==========
- SeaTunnel: 2.3.12
- Connector-JDBC: 2.3.12 (bundled)
- Oracle JDBC driver: ojdbc8-21.9.0.0 (Thin driver)
- Deploy mode: local (-m local)
- parallelism: 1 (both source and sink)


==========
Issue
==========
With batch_size = 2000 configured, the behavior differs drastically
between two test cases:


Test 1: Small table (4 columns, ~500 bytes/row)
  -> Oracle V$SQL shows: EXECUTIONS=10, ROWS=20000, AVG_PER_EXEC=2000
  -> batch_size = 2000 works PERFECTLY


Test 2: Large table (210 columns with VARCHAR2(1000)~(4000), ~25KB/row)
  -> Oracle V$SQL shows: EXECUTIONS=63, ROWS=5000, AVG_PER_EXEC=79.4
  -> batch_size = 2000 is completely IGNORED


==========
SeaTunnel Config (sanitized)
==========
env {
  parallelism = 1
  job.mode = "BATCH"
}


source {
  Jdbc {
&nbsp; &nbsp; url = "jdbc:oracle:thin:@//<SRC_HOST&gt;:1521/<SRC_SERVICE&gt;"
&nbsp; &nbsp; driver = "oracle.jdbc.OracleDriver"
&nbsp; &nbsp; username = "<SRC_USER&gt;"
&nbsp; &nbsp; password = "<SRC_PASSWORD&gt;"
&nbsp; &nbsp; query = "SELECT * FROM <SCHEMA&gt;.<SOURCE_TABLE&gt;"
&nbsp; &nbsp; fetch_size = 5000
&nbsp; }
}


sink {
&nbsp; Jdbc {
&nbsp; &nbsp; url = "jdbc:oracle:thin:@//<DST_HOST&gt;:1521/<DST_SERVICE&gt;"
&nbsp; &nbsp; driver = "oracle.jdbc.OracleDriver"
&nbsp; &nbsp; username = "<DST_USER&gt;"
&nbsp; &nbsp; password = "<DST_PASSWORD&gt;"
&nbsp; &nbsp; generate_sink_sql = false
&nbsp; &nbsp; query = "INSERT INTO <SCHEMA&gt;.<TARGET_TABLE&gt; (col1, ..., col210) VALUES (?, ..., ?)"
&nbsp; &nbsp; batch_size = 2000
&nbsp; &nbsp; auto_commit = true
&nbsp; &nbsp; batch_interval_ms = 0
&nbsp; }
}


==========
What I've verified so far
==========


(1) SeaTunnel layer: NOT the problem
&nbsp; &nbsp; - parallelism=1 confirmed from DEBUG logs
&nbsp; &nbsp; - Source reads only 1 split, 1 chunk
&nbsp; &nbsp; - Connection pool maximumPoolSize=1, only 1 JDBC connection total
&nbsp; &nbsp; - Only 1 writer thread (st-multi-table-sink-writer-1)
&nbsp; &nbsp; - SeaTunnel DOES call executeBatch() after accumulating 2000 rows
&nbsp; &nbsp; - No multi-threading, no multi-connection, no parallel writes


(2) Root cause: ojdbc8 Thin driver hardcoded 2MB threshold
&nbsp; &nbsp; I decompiled ojdbc8-21.9.0.0.jar and found:


&nbsp; &nbsp; &nbsp; // OraclePreparedStatement (internal)
&nbsp; &nbsp; &nbsp; private static final int BATCH_SIZE_THRESHOLD = 2097152; // 2MB


&nbsp; &nbsp; The full picture is:


&nbsp; &nbsp; &nbsp; SeaTunnel adds 2000 rows via addBatch(), then calls executeBatch()
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; v
&nbsp; &nbsp; &nbsp; ojdbc8 accumulates bind parameter data in an internal buffer
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; v
&nbsp; &nbsp; &nbsp; 2000 rows x ~25KB/row = ~50MB total data
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; v
&nbsp; &nbsp; &nbsp; 50MB &gt;&gt; 2MB threshold -&gt; ojdbc8 auto-splits into ~25 sub-batches
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; v
&nbsp; &nbsp; &nbsp; executeBatch() sends each sub-batch as a separate network call
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; v
&nbsp; &nbsp; &nbsp; Oracle V$SQL sees 25 separate INSERT executions instead of 1


&nbsp; &nbsp; For the small table: 2000 rows x ~500B = ~1MB < 2MB -&gt; no split -&gt; works


(3) Other driver findings:
&nbsp; &nbsp; - ojdbc6: no 2MB threshold, but ojdbc6's Oracle-style batching
&nbsp; &nbsp; &nbsp; (defaultExecuteBatch &gt; 1) is mutually exclusive with standard JDBC
&nbsp; &nbsp; &nbsp; addBatch() API, which is what SeaTunnel uses internally
&nbsp; &nbsp; - ojdbc8: defaultExecuteBatch connection property is a NO-OP
&nbsp; &nbsp; &nbsp; (hardcoded to return 1, cannot be changed)
&nbsp; &nbsp; - I also patched ojdbc8 by changing 2MB to Integer.MAX_VALUE,
&nbsp; &nbsp; &nbsp; which improved AVG_PER_EXEC from ~80 to ~1500 (but still not 2000)


==========
Questions for the community
==========


1. Is there any way in SeaTunnel's JDBC Connector to work around or
&nbsp; &nbsp;mitigate this ojdbc8 2MB threshold issue?


2. Has the community considered special handling for Oracle large-column
&nbsp; &nbsp;tables in Connector-JDBC?


3. Is there a specific Oracle JDBC driver version recommended that
&nbsp; &nbsp;doesn't have this 2MB hardcoded limitation?


4. Does Oracle JDBC have any hidden connection property to adjust
&nbsp; &nbsp;this internal threshold? I couldn't find any official documentation
&nbsp; &nbsp;about this.


5. Any other suggestions for improving batch insert performance with
&nbsp; &nbsp;Oracle large-column tables?


==========
Verification SQL (Oracle side)
==========
SELECT
&nbsp; SUBSTR(SQL_TEXT, 1, 80) AS SQL_TEXT,
&nbsp; EXECUTIONS,
&nbsp; ROWS_PROCESSED,
&nbsp; ROUND(ROWS_PROCESSED / EXECUTIONS, 1) AS AVG_PER_EXEC
FROM V$SQL
WHERE SQL_TEXT LIKE '%INSERT%'
&nbsp; AND SQL_TEXT LIKE '%<TARGET_TABLE&gt;%'
ORDER BY LAST_ACTIVE_TIME DESC;


Looking forward to your feedback. Thanks!


Best regards,
hongyingjie


怼烎@
727662995@qq.com