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 :
- Blobs are written to this SYS_LOB0000040689C00004$$ segment which has a very high IO time
- The same segment has the highest Global Cache Buffer Busy
The table created uses all default values e.g.
DBA is suggesting :
- 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?
- 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?