Already answered at a parallel thread on serverfault: http://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.

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.

Already answered at a parallel thread on serverfault: http://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.

Already answered at a parallel thread on serverfault: http://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.