We had DB performance issue while inserting data into blob columns. Blob column data would be normally 10-12 MB size and will be inserting this data into a table for every 4 seconds. Inserts became very slow.
Similarly, for another table also, we are inserting data into blob and clob columns. Blob column data would be normally 10KB and clob column value would be normally 3-4 KB. For this table, insertion will be happening at a rate of 250 records/sec.
Inserts were too slow during the above 2 scenarios.
We investigated and found that below changes to resolve the issue that gave better performance.
1) db_block_size = to be changed from 8K to 16K
2) intial and next extent size to be changed to 100M
3) segment_space_management to be changed from Auto to Manual
But,our Customer DBA however is not agreeing for point 3. He says MANUAL segment space mgmt is not recommended as they will require downtime for defragmentation of manually managed segment space.
Need help in this regard to make BLOB inserts faster.
Database is 11gR2.
Inserting data from Java Program through prepared statement.
Sample code of Java program :
orclRawTrapsInsertStmt.setBytes(5, rts.trapObject); This is the line setting Java Object (as byte array) as Blob column value
Out of curiosity, when you made the 3 changes you listed did you make them one at a time and test each change individually? In other words, was there a continued improvement as you made each changes or did one of the changes give you the biggest improvement?