When to buffer Large Objects (LOB)s in Oracle 19c

In Oracle, LOBs (Large Objects) can be stored with different caching or buffering options (e.g., `CACHE` vs. `NOCACHE` vs. `CACHE READS` in the `CREATE TABLE` or `ALTER TABLE` statement for LOB columns). The decision about whether to buffer (cache) LOB data depends primarily on data access patterns and memory considerations. Below are the main points you should evaluate:
1. Assess Data Access Patterns

1. Frequency of Reads and Writes
   • High-Frequency Reads: If the LOB data is read frequently (e.g., images, documents, or XML that is accessed repeatedly by applications), enabling caching can improve performance by reducing physical I/O.
   • Infrequent Access: If the LOB data is rarely accessed, caching might not provide a significant benefit—and may instead consume memory that could be used for other operations.
2. Sequential vs. Random Access
   • Sequential Reads: Large documents that are typically read from start to finish (e.g., streaming video) may benefit less from caching.
   • Random Access: If the application frequently accesses parts of the LOB at various offsets (e.g., partial updates or partial reads of a file), caching can help.
3. Data Volatility
   • Mostly Read-Only: If the LOB data changes infrequently, caching might help speed up repeated reads.
   • Frequent Writes: If the LOB is updated often, caching can help with partial updates, but keep in mind the overhead of writing dirty blocks back to disk.

2. Evaluate Memory and Storage Constraints

1. System Global Area (SGA) and Buffer Cache Size
   • Caching LOBs will consume buffer cache space. If your buffer cache is already heavily used by normal table data and indexes, enabling LOB caching could push out other frequently accessed data, hurting overall performance.
   • Ensure you have sufficient memory to handle the extra load.
2. LOB Size
   • Very large LOBs (hundreds of MBs or more) can rapidly consume cache. If you expect very large LOBs but only read them occasionally, NOCACHE is often more appropriate.
   • Smaller LOBs or small segments of larger LOBs (e.g., text documents under a few megabytes) are often good candidates for caching, especially if frequently accessed.
3. DB_BLOCK_SIZE and I/O
   • If LOB data is cached, Oracle must manage LOB blocks in the buffer cache. When large LOB data is brought into the cache, it can generate more I/O. Evaluate whether your I/O subsystem and block size settings can handle this efficiently.

3. Oracle Configuration Options
When creating or altering a LOB column in Oracle, you can specify caching behavior:

CREATE TABLE my_table (
    id          NUMBER,
    my_lob_col  CLOB
)
LOB (my_lob_col) STORE AS SECUREFILE my_lob_seg (
    CACHE
    -- or NOCACHE
    -- or CACHE READS
    -- or other options
);

1. CACHE
   • LOB data is placed in the buffer cache for both reads and writes.
   • Good for frequently accessed, small-to-medium-sized LOBs.
2. NOCACHE
   • LOB data is not cached in the buffer cache (though some minimal read/write buffering may still happen internally).
   • Best for large or rarely accessed LOBs.
3. CACHE READS
   • LOB data is cached only for read operations. Writes bypass the cache.
   • Useful if you have frequent reads but want to avoid overhead on writes.

4. Performance Testing
Ultimately, the best way to decide is to test both options (`CACHE` vs. `NOCACHE`) under realistic load and measure:

• Response times for LOB reads/writes
• Buffer cache hit ratios and memory usage
• Overall system throughput

Gathering these metrics in a pre-production environment will help you pinpoint the impact of caching on your specific workload.
5. Practical Guidelines

• Start with NOCACHE for very large LOBs that are infrequently accessed, especially if memory is constrained.
• Use CACHE for smaller or medium-sized LOBs that are read often and benefit from reduced I/O.
• Consider CACHE READS if you need to accelerate read access but want to minimize overhead for writes.
• Monitor SGA usage and watch for performance degradation in other parts of the system.

Summary: Deciding when to buffer (cache) LOBs in Oracle 19c (or any modern Oracle version) is a balancing act between performance gains for frequently accessed data and the potential memory overhead that caching large objects can impose on the buffer cache. Always align your caching strategy with actual access patterns and available system resources, and confirm through performance testing.

In Oracle, you can use a technique known as buffering LOBs. You buffer LOBs on the client side of a connection rather than the server side. This practice means that once you fetch a LOB, it is stored locally on the client machine. The process of using client-side buffering is illustrated in the following series of images.

In Oracle 19c, LOB buffering has evolved significantly, especially with SecureFiles LOBs^[1], which offer improved performance and storage efficiency over the older BasicFiles LOBs. Below is the updated version of your statements to reflect Oracle 19c enhancements:
Updated Statement for Oracle 19c
When you use LOB buffering, you can keep a buffer on the client, which is managed dynamically based on available memory and session settings. The buffer is used for all buffered SecureFiles LOBs on the client.
The LOB buffering API (`DBMS_LOB.BUFFER`) provides improved performance for read and write operations by reducing round-trips between the client and server. SecureFiles LOBs also introduce advanced caching mechanisms, deduplication, compression, and encryption, further optimizing LOB handling in Oracle 19c. Buffering LOBs has advantages and disadvantages depending on access patterns, available memory, and the type of LOB storage (`CACHE`, `NOCACHE`, `CACHE READS`). SecureFiles LOBs are recommended over BasicFiles LOBs for enhanced performance and efficient LOB management.
Key Updates in Oracle 19c

1. SecureFiles LOBs (default for new LOBs)
   • Introduces inline deduplication, compression, and encryption.
   • Performs significantly better than BasicFiles LOBs.
   • Supports LOB caching and buffering efficiently.
2. Dynamic Buffer Management
   • Oracle dynamically adjusts buffering size based on session and available memory.
   • Unlike Oracle 8i’s fixed 512 KB buffer, SecureFiles can utilize optimized memory management.
3. Enhanced LOB Caching and Buffering
   • CACHE, CACHE READS, and NOCACHE settings help control how LOBs are buffered.
   • SecureFiles LOBs in CACHE mode benefit from better memory and performance optimization.
4. LOB Buffering API Enhancements
   • DBMS_LOB.BUFFER improves client-side buffering.
   • Less frequent round-trips between the client and database.
   • Better concurrency handling for multi-session environments.

When you use LOB buffering, you can keep a buffer of up to 512 KB on the client. This buffer is used for all buffered LOBs on the client. Buffering LOBs has advantages and disadvantages.

• Advantages of buffering LOBs: When you buffer a LOB, a client application can work on the LOB, updating it as many times as it wants, without forcing a roundtrip to the server to store the changes. The reduction in roundtrips to the server can improve performance, especially because the update of a LOB can involve the transfer of a large amount of data. In addition to the reduction in network requirements, buffering LOBs can reduce the logging overhead of your Oracle server, because fewer changes to a large object require less logging of those changes.
• Disadvantages of buffering LOBs: There are many disadvantages to using LOB buffering. The basic problem comes from the fact that once you move a LOB to a client-side buffer, your Oracle server is out of the picture, as far as data management goes, until you update the LOB back to the server by flushing the buffer. Because of this, you have to create you own solutions for a number of data management issues:
  
  1. Because your Oracle server doesn't know what is happening on the client, you have to manage concurrency explicitly by locking the buffered LOB yourself.
  2. You have to handle transaction control and any potential errors yourself.
  3. Client-side buffering can be implemented only through the Oracle Call Interface (OCI), a fairly low-level C code application program interface (API).
  4. You cannot use the DBMS_LOB procedures with a buffered large object, which can further increase your coding burden.

As you can tell, a significant amount of coding is needed to use a client-side buffer for large objects. You will probably want to take advantage of this technique only when a particular circumstance dictates that the implementation effort is justified– a situation where, without buffering, you would be frequently updating the same LOB. For instance, if you were working on a particular image in an image editor, you might want to buffer it on the client until all your changes were completed. At the same time, you should make sure that the same situation does not call for shared use of the buffered LOB, because the lack of concurrency control, error handling, and transaction control can present insurmountable obstacles when buffering large objects that are shared in real time. The next lesson is the conclusion of the module.

Click the Quiz link below to test your knowledge of using LOBs.
LOB Storage - Quiz