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

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: Fascinated by the seemingly unexplainable wild amount of "db file sequential read", I did a little bit of searching, and found out that strange things can happen with the lob index after mass DELETEs. Just a guess, but looks very similar to your case. If this is it, I would rebuild the lob column entirely. (Moving a lob column might also rebuild the lob index - I'm not sure).