Oracle CACHE and STORAGE IN ROW behaviour

Question

We have a Java Spring boot application that writes 20 million plus rows in a table with 2 blob columns in a batched manner into an Oracle 21c DB.

We are using JDBC batch insert for fast inserts.

However, we have observed that regardless of the batch size (500 or 3000 etc) the insertion of each batch takes around 10 seconds.

The AWR report of oracle suggests the following :

1. Blobs are written to this SYS_LOB0000040689C00004$$ segment which has a very high IO time
2. The same segment has the highest Global Cache Buffer Busy

The table created uses all default values e.g. orderdoc blob

DBA is suggesting :

1. Use CACHE instead of NOCACHE - I think this won't help as we are writing a lot to the DB. And CACHE will only help once we start reading. At the moment it's just a lot of write operations and no reads. Is my understanding correct?
2. DISABLE STORAGE IN ROW - I think this won't help as the blobs are not huge and are in KBs. Is my understanding correct?

On the Java side, we are using ByteArryInputStream to create the blob and new StringReader(string) to store Clobs.

Is there a more efficient way to write Blobs and Clobs on the java side?

Answer 1

FYI

"DISABLED STORAGE IN ROW" had a negative effect for me since 90% of the data was <4000 bytes (the cut off for in-row storage). This is because each CLOB needs a minimum of 1 block in the SYS_LOB0000040689C00004 table (an expensive process)

Another problem I've had was when the rows were created without the CLOB data then added CLOB data. The problem: adding the CLOB data caused row migration (ROWID changed) due to <4000 CLOBs.