Datatypes - Expert Oracle Database Architecture - page 568

As we can see, the update of the out-of-line LOB consumed measurably more resources. It spent some amount
of time doing direct path writes (physical I/O) and performed many more current mode gets. These were in response
to the fact that the LOBINDEX and LOBSEGMENT had to be maintained in addition to the table itself. The INSERT
activity shows the same disparity:

INSERT INTO T (ID, IN_ROW) VALUES ( S.NEXTVAL, 'Hello World' )

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100 0.00 0.00 0 4 317 100
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 101 0.00 0.00 0 4 317 100
********************************************************************************
INSERT INTO T (ID,OUT_ROW) VALUES ( S.NEXTVAL, 'Hello World' )

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100 0.02 0.61 0 4 440 100
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 101 0.02 0.61 0 4 440 100
...
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
direct path write 100 0.01 0.60

Note the increased I/O usage, both on the read and writes. All in all, this shows that if you use a CLOB , and many
of the strings are expected to fit in the row (i.e., will be less than 4,000 bytes), then using the default of ENABLE STORAGE
IN ROW is a good idea.

CHUNK Clause
LOBs are stored in chunks; the index that points to the LOB data points to individual chunks of data. Chunks are logically
contiguous sets of blocks and are the smallest unit of allocation for LOBs, whereas normally a block is the smallest unit
of allocation. The CHUNK size must be an integer multiple of your Oracle blocksize—this is the only valid value.

■ the CHUNK clause only applies to BasicFiles. the CHUNK clause appears in the syntax clause for secureFiles for
backward compatibility purposes only.

Note

You must take care to choose a CHUNK size from two perspectives. First, each LOB instance (each LOB value stored
out of line) will consume at least one CHUNK . A single CHUNK is used by a single LOB value. If a table has 100 rows and
each row has a LOB with 7KB of data in it, you can be sure that there will be 100 chunks allocated. If you set the
CHUNK size to 32KB, you will have 100 32KB chunks allocated. If you set the CHUNK size to 8KB, you will have (probably)
100 8KB chunks allocated. The point is, a chunk is used by only one LOB entry (two LOBs will not use the same