This discussion is archived
5 Replies Latest reply: Jan 8, 2013 12:49 PM by Another_user RSS

BLOB inserts performance slow when segment space management is Auto

SreekeshavaS Newbie
Currently Being Moderated
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.setLong(1, rts.id);
orclRawTrapsInsertStmt.setString(2, rts.source_ip);
orclRawTrapsInsertStmt.setString(3, rts.oid);
orclRawTrapsInsertStmt.setString(4, rts.varbinds);
orclRawTrapsInsertStmt.setBytes(5, rts.trapObject); This is the line setting Java Object (as byte array) as Blob column value
orclRawTrapsInsertStmt.setString(6, rts.receivedTime);
orclRawTrapsInsertStmt.setInt(7, rts.trapVersion);
orclRawTrapsInsertStmt.executeUpdate();

Thanks in advance/Sreekeshava S

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points