SecureFiles RETENTION-Datatypes
SecureFiles use RETENTION to control read consistency (just like BasicFiles). In the CREATE TABLE output of DBMS_METADATA for the SecureFiles LOB, there is no RETENTION clause. This is because the default RETENTION is set to AUTO, which instructs Oracle to retain undo long enough for read-consistent purposes.
If you want to alter the default RETENTION behavior, you can adjust it via the following parameters:
•\ Use MAX to indicate that the undo should be retained until the LOB segment has reached the MAXSIZE specified in the storage clause (therefore, MAX must be used in conjunction with the MAXSIZE clause in the storage clause).
•\ Set MIN N if the Flashback Database is enabled to limit the undoduration for the LOB to N seconds.
•\ Set NONE if undo is not required for consistent reads or flashback operations.
If you don’t set the RETENTION parameter for SecureFiles, or specify RETENTION with no parameters, then it is set to DEFAULT (which is the equivalent of AUTO).
CACHE Clause
The CREATE TABLE statement returned from DBMS_METADATA previously included the following for both SecureFiles and BasicFiles:
LOB (“TXT”) STORE AS … (… NOCACHE … )
The alternative to NOCACHE is CACHE or CACHE READS. This clause controls whether or not the LOBSEGMENT data is stored in the buffer cache.
The default NOCACHE implies that every access will be a direct read from disk, and every write/modification will likewise be a direct read from disk. CACHE READS allows LOB data that is read from disk to be buffered, but writes of LOB data will be done directly to disk. CACHE permits the caching of LOB data during both reads and writes.
In many cases, the default might not be what you want. If you have small- to medium-sized LOBS (e.g., you are using them to store descriptive fields of just a couple of kilobytes), caching them makes perfect sense.
If they are not cached, when the user updates the description field the user must also wait for the I/O to write the data to disk (an I/O the size of a CHUNK will be performed, and the user will wait for this I/O to complete). If you are performing a large load of many LOBs, you will have to wait for the I/O to complete on each row as they are loaded. It makes sense to enable caching on these LOBs. You may turn caching on and off easily:
ALTER TABLE tabname MODIFY LOB (lobname) ( CACHE ); ALTER TABLE tabname MODIFY LOB (lobname) ( NOCACHE );to see the effect this may have on you.
For a large initial load, it would make sense to enable caching of the LOBs and allow DBWR to write the LOB data out to disk in the background while your client application keeps loading more.
For small- to medium-sized LOBs that are frequently accessed or modified, caching makes sense so the end user doesn’t have to wait for physical I/O to complete in real time.
For a LOB that is 50MB in size, however, it probably does not make sense to have that in the cache.
Tip Bear in mind that you can make excellent use of the keep or recycle pools (discussed in Chapter 4) here. Instead of caching the LOBSEGMENT data in the default cache with all of the regular data, you can use the keep or recycle pools to separate it out. In that fashion, you can achieve the goal of caching LOB data without affecting the caching of existing data in your system.