Oracle 11. Updating BLOB field. Db file sequential read inappropriately slow?

Question

I've and Oracle (11 Enterprise) schema with a table

 CREATE TABLE USER.WSP_BUNDLE ( 
 NODE_ID RAW(16) NOT NULL,
 BUNDLE_DATA BLOB NOT NULL 
 );
CREATE UNIQUE INDEX USER.WSP_BUNDLE_IDX ON USER.WSP_BUNDLE(NODE_ID);

and 3'rd party library (Java 6, JDBC - latest jdbc driver) that manipulate it.

Oracle profiler (tkprof) shows that about 50% of overall time the lib execute such statement:

update WSP_BUNDLE set BUNDLE_DATA = :1 where NODE_ID = :2

TKProf data

Plan Hash: 4085453680
update WSP_BUNDLE set BUNDLE_DATA = :1 where NODE_ID = :2 
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 264 0.00 0.00 0 0 0 0
Execute 400 30.59 382.88 141451 1623163 3233827 400
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 664 30.59 382.88 141451 1623163 3233827 400
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 87 
Rows Row Source Operation
------- ---------------------------------------------------
 0 UPDATE WSP_BUNDLE (cr=8753 pr=707 pw=706 time=0 us)
 1 INDEX UNIQUE SCAN WSP_BUNDLE_IDX (cr=3 pr=0 pw=0 time=0 us cost=2 size=104 card=1)(object id 75730)
Elapsed times include waiting on following events:
 Event waited on Times Max. Wait Total Waited
 ---------------------------------------- Waited ---------- ------------
 db file sequential read 141251 5.53 328.04
 direct path write 402 0.09 0.43
 SQL*Net more data from client 142158 1.04 11.89
 direct path read 200 0.03 0.07
 Disk file operations I/O 1 0.00 0.00
 SQL*Net message to client 400 0.00 0.00
 SQL*Net message from client 400 0.29 0.50
 log file switch (private strand flush incomplete)
 5 0.05 0.23
 asynch descriptor resize 139723 7.46 8.57
 buffer busy waits 2 0.00 0.00
 log file switch (checkpoint incomplete) 3 0.18 0.27
 log file sync 2 0.00 0.00

Could anybody explain/hint me what's going on? Why the update is so slow?

The table WSP_BUNDLE contains about 200+k rows. At the same time I've other tables in the same schema with blobs (CLOB to be more concrete) that contains 600+k rows where similar updates work correctly.

Answer 1

Already answered at a parallel thread on serverfault: https://serverfault.com/questions/345253/oracle-11-updating-blob-field-db-file-sequential-read-inappropriately-slow/345588#345588

In Oracle, LOB (including BLOB) is stored as:

• in-the-table LOB - if the LOB is smaller than 3900 bytes it can be stored inside the table row; by default this is enabled, unless you specify DISABLE STORAGE IN ROW
• normal LOB - stored in a separate segment, outside of table, you may even put it in another tablespace; for these:
  • a minimum of CHUNK bytes are allocated and entirely redo-logged (even if LOB has only 1 byte)
  • there is an internal intermediate index behind a LOB column, which gets contentious on updates and may practically serialize them
  • access is multi-level and thus relatively slower
  • with NOCACHE option, the waiters are "direct path read" - the default
  • with CACHE option, the waiters are "db file sequential read"
    • where CACHE_SIZE_THRESHOLD is not taken into account, so a large LOB can waste your cache

Therefore, if your LOBs are larger than 4 kB they will get relatively slow, and this may simply be your case. I would examine the sizes.

I would examine USER_LOBS (or DBA_LOBS) to see how the "good" and "slow" LOB columns differ in their definitions.

The Metalink note ID 66431.1 describes this and may be of interest to you, if you have access there.

UPDATE - see link above, I've had some thoughts.

Answer 2

You should be using LOB SecureFile -- Basic will generate loads of sequential reads -- with or without prior DELETE.