| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: BLOB or BFILE ?
There are 4 types of LOB:
CLOB, BLOB, NCLOB stored internally to Oracle
BFILE stored externally
Attributes
A table containing LOBs (CLOB, NCLOB and BLOB) creates 2 additional disk segments per LOB column - a LOBINDEX and a LOBSEGMENT. These can be viewed, along with the LOB attributes, using the dictionary views:
DBA_LOBS, ALL_LOBS or USER_LOBS
Storage Parameters
Eg: Create table DemoLob ( A number, B clob )
LOB(b)
STORE AS lobsegname (
TABLESPACE lobsegts
STORAGE (lobsegment storage clause)
INDEX lobindexname (
TABLESPACE lobidxts
STORAGE ( lobindex storage clause )
)
)
TABLESPACE tables_ts
STORAGE( tables storage clause )
;
In ROW Versus Out of ROW
"STORE AS ( enable storage in row )"
Allows LOB data to be stored in the TABLE segment provided
it is less than about 4000 bytes.
The actual maximum in-line LOB is 3964 bytes.
If the lob value is greater than 3964 bytes then the LOB data is
stored in the LOB SEGMENT (ie: out of line). An out of line
LOB behaves as described under 'disable storage in row' except that
if its size shrinks to 3964 or less the LOB can again be stored
inline.
When a LOB is stored out-of-line in an 'enable storage in row'
LOB column between 36 and 84 bytes of control data remain in-line
in the row piece.
In-line LOBS are subject to normal chaining and row migration
rules within Oracle. Ie: If you store a 3900 byte LOB in a row
with a 2K block size then the row piece will be chained across
two or more blocks.
Both REDO and UNDO are written for in-line LOBS as they are part
of the normal row data.
"STORE AS ( disable storage in row )"
This option prevents any size of LOB from being stored in-line.
Instead a 20 byte LOB locator is stored in the ROW which gives
a unique identifier for a LOB in the LOB segment for this column.
The Lob Locator actually gives a key into the LOB INDEX which
contains a list of all blocks (or pages) that make up the LOB.
The minimum storage allocation for an out of line LOB is 1 Database
BLOCK per LOB ITEM and may be more if CHUNK is larger than a
single block.
UNDO is only written for the column locator and LOB INDEX changes.
No UNDO is generated for pages in the LOB SEGMENT.
Consistent Read is achieved by using page versions.
Ie: When you update a page of a LOB the OLD page remains and a
new page is created. This can appear to waste space but
old pages can be reclaimed and reused.
CHUNK size
Can ONLY be specified at creation time.
In 8.0 values of CHUNK are in bytes and are rounded to the next
highest multiple of DB_BLOCK_SIZE without erroring.
Eg: If you specify a CHUNK of 3000 with a block size of 2K then
CHUNK is set to 4096 bytes.
"bytes" / DB_BLOCK_SIZE determines the unit of allocation of
blocks to an 'out of line' LOB in the LOB segment.
Eg: if CHUNK is 32K and the LOB is 'disable storage in row'
then even if the LOB is only 10 bytes long 32K will be
allocated in the LOB SEGMENT.
CHUNK does NOT affect in-line LOBS.
PCTVERSION
PCTVERSION can be changed after creation using:
ALTER TABLE tabname MODIFY LOB (lobname) ( PCTVERSION n );
PCTVERSION affects the reclamation of old copies of LOB data.
This affects the ability to perform consistent read.
If a session is attempting to use an OLD version of a LOB
and that version gets overwritten (because PCTVERSION is too small)
then the user will typically see the errors:
ORA-01555: snapshot too old:
rollback segment number with name "" too
small
ORA-22924: snapshot too old
PCTVERSION can prevent OLD pages being used and force the segment
to extend instead.
Do not expect PCTVERSION to be an exact percentage of space as there
is an internal fudge factor applied.
CACHE
This option can be changed after creation using:
ALTER TABLE tabname MODIFY LOB (lobname) ( CACHE );
or
ALTER TABLE tabname MODIFY LOB (lobname) ( NOCACHE );
With NOCACHE set (the default) reads from and writes to the
LOB SEGMENT occur using direct reads and writes. This means that
the blocks are never cached in the buffer cache and the the Oracle
shadow process performs the reads/writes itself.
The reads / writes show up under the wait events "direct path read"
and "direct path write" and multiple blocks can be read/written at
a time (provided the caller is using a large enough buffer size).
When set the CACHE option causes the LOB SEGMENT blocks to
be read / written via the buffer cache . Reads show up as
"db file sequential read" but unlike a table scan the blocks are
placed at the most-recently-used end of the LRU chain.
The CACHE options for LOB columns is different to the CACHE
option for tables as CACHE_SIZE_THRESHOLD does not limit the
size of LOB read into the buffer cache. This means that extreme
caution is required otherwise the read of a long LOB can effectively
flush the cache.
In-line LOBS are not affected by the CACHE option as they reside
in the actual table block (which is typically accessed via the
buffer
cache any way).
The cache option can affect the amount of REDO generated for
out of line LOBS. With NOCACHE blocks are direct loaded and
so entire block images are written to the REDO stream. If CHUNK
is also set then enough blocks to cover CHUNK are written to REDO.
If CACHE is set then the block changes are written to REDO.
Eg: In the extreme case 'DISABLE STORAGE IN ROW NOCACHE CHUNK
32K'
would write redo for the whole 32K even if the LOB was only
5 characters long. CACHE would write a redo record describing
the
5 byte change (taking about 100-200 bytes).
LOGGING
This option can be changed after creation but the LOGGING / NOLOGGING
attribute must be prefixed by the NOCACHE option. The CACHE option
implicitly enables LOGGING.
The default for this option is LOGGING.
If a LOB is set to NOCACHE NOLOGGING then updates to the LOB SEGMENT
are not logged to the redo logs. However, updates to in-line LOBS
are still logged as normal. As NOCACHE operations use direct
block updates then all LOB segment operations are affected.
NOLOGGING of the LOB segment means that if you have to recover the
database then sections of the LOB segment will be marked as corrupt
during recovery.
Space required for updates
INSERT a large LOB LOB SEGMENT extends take the new pages
COMMIT;
DELETE the above LOB The LOB pages are not yet free as
they will be needed in case of
rollback.
INSERT a new LOB Hence this insert may require more
space in the LOB SEGMENT
COMMIT; Only after this point could the
deleted pages be used.
Performance Issues
If this is small (say 2K) then there can be a round trip to the database
for each 2K chunk of the LOB. To make the issue worse the server will
only fetch the blocks needed to satisfy the current request so may
perform single block reads against the LOB SEGMENT. If however a
larger
chunk size is used (say 32K) then the server can perform multiblock
operations and pass the data back in larger chunks.
There is a LOB buffering subsystem which can be used to help improve the transfer of LOBs between the client and server processes. See the documentation for details of this.
BFILEs
The performance implications of the buffer size are the same as for
internal
LOBS.
bee <julienpi_at_iii.org.tw> wrote in message
news:7kl1k1$2va$1_at_news.seed.net.tw...
> I am using Oracle 8i + OAS 4. Our multimedia data ( image and audio...)
are
> to be downloaded through web. Could a BLOB be downloaded like a file
through
> PL/SQL or any other interface? Or, in this situation, should I use BFILE > instead of BLOB to get better performance? > Need help! > > Thanks a lot. > >Received on Sun Jun 27 1999 - 08:44:50 CDT
 |
 |