0

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?

1
  • Enabling cache on the log means you don’t have to do direct path writes to it, allowing Oracle to do the write in its own time = faster. I wouldn’t change in row storage without a lot of testing, you want to check performance and space usage. You should also look at how you are using RAC here, all your inserts ought to use the same instance if you haven’t partitioned the table, this should help your GC waits Oct 14, 2022 at 12:33

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.