LOB Buffering Subsystem

Oracle8i and Oracle9i provide a LOB buffering subsystem (LBS) for advanced OCI based applications such as Data Cartridges, Web servers, and other client-based applications that need to buffer the contents of one or more LOBs in the client's address space. The client-side memory requirement for the buffering subsystem during its maximum usage is 512KBytes. It is also the maximum amount that you can specify for a single read or write operation on a LOB that has been enabled for buffered access.

Advantages of LOB Buffering

The advantages of buffering, especially for client applications that perform a series of small reads and writes (often repeatedly) to specific regions of the LOB, are:

• Buffering enables deferred writes to the server. You can buffer up several writes in the LOB's buffer in the client's address space and eventually flush the buffer to the server. This reduces the number of network roundtrips from your client application to the server, and hence, makes for better overall performance for LOB updates.

• Buffering reduces the overall number of LOB updates on the server, thereby reducing the number of LOB versions and amount of logging. This results in better overall LOB performance and disk space usage.

Guidelines for Using LOB Buffering

The following caveats apply to buffered LOB operations:

• Explicitly flush LOB buffer contents. Oracle8i and Oracle9i provide a simple buffering subsystem, and not a cache. To be specific, Oracle does not guarantee that the contents of a LOB's buffer are always in sync with the LOB value in the server. Unless you explicitly flush the contents of a LOB's buffer, you will not see the results of your buffered writes reflected in the actual LOB on the server.

• Error recovery for buffered LOB operations is your responsibility. Owing to the deferred nature of the actual LOB update, error reporting for a particular buffered read or write operation is deferred until the next access to the server based LOB.

• LOB Buffering is Single User, Single Threaded. Transactions involving buffered LOB operations cannot migrate across user sessions -- the LBS is a single user, single threaded system.

• Maintain logical savepoints to rollback to. Oracle does not guarantee transactional support for buffered LOB operations. To ensure transactional semantics for buffered LOB updates, you must maintain logical savepoints in your application to rollback all the changes made to the buffered LOB in the event of an error. You should always wrap your buffered LOB updates within a logical savepoint (see "OCI Example of LOB Buffering").

• Ensure LOB is not updated by another bypassing transaction. In any given transaction, once you have begun updating a LOB using buffered writes, it is your responsibility to ensure that the same LOB is not updated through any other operation within the scope of the same transaction that bypasses the buffering subsystem.

  You could potentially do this by using an SQL statement to update the server-based LOB. Oracle cannot distinguish, and hence prevent, such an operation. This will seriously affect the correctness and integrity of your application.

• Updating buffer-enabled LOB locators. Buffered operations on a LOB are done through its locator, just as in the conventional case. A locator that is enabled for buffering will provide a consistent read version of the LOB, until you perform a write operation on the LOB through that locator.

  See Also: "Read Consistent Locators".

  Once the locator becomes an updated locator by virtue of its being used for a buffered write, it will always provide access to the most up-to-date version of the LOB as seen through the buffering subsystem. Buffering also imposes an additional significance to this updated locator -- all further buffered writes to the LOB can be done only through this updated locator. Oracle will return an error if you attempt to write to the LOB through other locators enabled for buffering.

  See Also: "Updating LOBs Via Updated Locators".

Passing a buffer-enabled LOB locator an IN OUT or OUT parameter. You can pass an updated locator that was enabled for buffering as an IN parameter to a PL/SQL procedure. However, passing an IN OUT or an OUT parameter will produce an error, as will an attempt to return an updated locator.

You cannot assign an updated locator that was enabled for buffering to another locator. There are a number of different ways that assignment of locators may occur -- through OCILobAssign(), through assignment of PL/SQL variables, through OCIObjectCopy() where the object contains the LOB attribute, and so on. Assigning a consistent read locator that was enabled for buffering to a locator that did not have buffering enabled, turns buffering on for the target locator. By the same token, assigning a locator that was not enabled for buffering to a locator that did have buffering enabled, turns buffering off for the target locator.

Similarly, if you SELECT into a locator for which buffering was originally enabled, the locator becomes overwritten with the new locator value, thereby turning buffering off.

When two or more locators point to the same LOB do not enable both for buffering. If two or more different locators point to the same LOB, it is your responsibility to make sure that you do not enable both the locators for buffering. Otherwise Oracle does not guarantee the contents of the LOB.

Buffer-enable LOBs do not support appends that create zero-byte fillers or spaces. Appending to the LOB value using buffered write(s) is allowed, but only if the starting offset of these write(s) is exactly one byte (or character) past the end of the BLOB (or CLOB/NCLOB). In other words, the buffering subsystem does not support appends that involve creation of zero-byte fillers or spaces in the server based LOB.

For CLOBs, Oracle requires the client side character set form for the locator bind variable be the same as that of the LOB in the server. This is usually the case in most OCI LOB programs. The exception is when the locator is SELECTed from a remote database, which may have a different character set form from the database which is currently being accessed by the OCI program. In such a case, an error is returned. If there is no character set form input by the user, then we assume it is SQLCS_IMPLICIT.

LOB Buffering Usage Notes

LOB Buffer Physical Structure

Each user session has the following structure:

• Fixed page pool of 16 pages, shared by all LOBs accessed in buffering mode from that session.

• Each page has a fixed size of up to 32K bytes (not characters) where page size = n x CHUNKSIZE ~= 32K.

A LOB's buffer consists of one or more of these pages, up to a maximum of 16 per session. The maximum amount that you ought to specify for any given buffered read or write operation is 512K bytes, remembering that under different circumstances the maximum amount you may read/write could be smaller.

Example of Using the LOB Buffering System (LBS)

Consider that a LOB is divided into fixed-size, logical regions. Each page is mapped to one of these fixed size regions, and is in essence, their in-memory copy. Depending on the input offset and amount specified for a read or write operation, Oracle8i and Oracle9i allocate one or more of the free pages in the page pool to the LOB's buffer. A free page is one that has not been read or written by a buffered read or write operation.

For example, assuming a page size of 32KBytes:

• For an input offset of 1000 and a specified read/write amount of 30000, Oracle reads the first 32K byte region of the LOB into a page in the LOB's buffer.

• For an input offset of 33000 and a read/write amount of 30000, the second 32K region of the LOB is read into a page.

• For an input offset of 1000, and a read/write amount of 35000, the LOB's buffer will contain two pages -- the first mapped to the region 1 -- 32K, and the second to the region 32K+1 -- 64K of the LOB.

This mapping between a page and the LOB region is temporary until Oracle maps another region to the page. When you attempt to access a region of the LOB that is not already available in full in the LOB's buffer, Oracle allocates any available free page(s) from the page pool to the LOB's buffer. If there are no free pages available in the page pool, Oracle reallocates the pages as follows. It ages out the least recently used page among the unmodified pages in the LOB's buffer and reallocates it for the current operation.

If no such page is available in the LOB's buffer, it ages out the least recently used page among the unmodified pages of other buffered LOBs in the same session. Again, if no such page is available, then it implies that all the pages in the page pool are modified, and either the currently accessed LOB, or one of the other LOBs, need to be flushed. Oracle notifies this condition to the user as an error. Oracle never flushes and reallocates a modified page implicitly. You can either flush them explicitly, or discard them by disabling buffering on the LOB.

To illustrate the above discussion, consider two LOBs being accessed in buffered mode -- L1 and L2, each with buffers of size 8 pages. Assume that 6 of the 8 pages in L1's buffer are dirty, with the remaining 2 containing unmodified data read in from the server. Assume similar conditions in L2's buffer. Now, for the next buffered operation on L1, Oracle will reallocate the least recently used page from the two unmodified pages in L1's buffer. Once all the 8 pages in L1's buffer are used up for LOB writes, Oracle can service two more operations on L1 by allocating the two unmodified pages from L2's buffer using the least recently used policy. But for any further buffered operations on L1 or L2, Oracle returns an error.

If all the buffers are dirty and you attempt another read from or write to a buffered LOB, you will receive the following error:

 Error 22280: no more buffers available for operation

There are two possible causes:

1. All buffers in the buffer pool have been used up by previous operations.

   In this case, flush the LOB(s) through the locator that is being used to update the LOB.

2. You are trying to flush a LOB without any previous buffered update operations.

   In this case, write to the LOB through a locator enabled for buffering before attempting to flush buffers.

Flushing the LOB Buffer

The term flush refers to a set of processes. Writing data to the LOB in the buffer through the locator transforms the locator into an updated locator. Once you have updated the LOB data in the buffer through the updated locator, a flush call will

• Write the dirty pages in the LOB's buffer to the server-based LOB, thereby updating the LOB value,

• Reset the updated locator to be a read consistent locator, and

• Free the flushed buffers or turn the status of the buffer pages back from dirty to unmodified.

After the flush, the locator becomes a read consistent locator and can be assigned to another locator (L2 := L1).

For instance, suppose you have two locators, L1 and L2. Let us say that they are both read consistent locators and consistent with the state of the LOB data in the server. If you then update the LOB by writing to the buffer, L1 becomes an updated locator. L1 and L2 now refer to different versions of the LOB value. If you wish to update the LOB in the server, you must use L1 to retain the read consistent state captured in L2. The flush operation writes a new snapshot environment into the locator used for the flush. The important point to remember is that you must use the updated locator (L1), when you flush the LOB buffer. Trying to flush a read consistent locator will generate an error.

This raises the question: What happens to the data in the LOB buffer? There are two possibilities. In the default mode, the flush operation retains the data in the pages that were modified. In this case, when you read or write to the same range of bytes no roundtrip to the server is necessary. Note that flush in this context does not clear the data in the buffer. It also does not return the memory occupied by the flushed buffer to the client address space.

Note: Unmodified pages may now be aged out if necessary.

In the second case, you set the flag parameter in OCILobFlushBuffer() to OCI_LOB_BUFFER_FREE to free the buffer pages, and so return the memory to the client address space. Note that flush in this context updates the LOB value on the server, returns a read consistent locator, and frees the buffer pages.

Flushing the Updated LOB

It is very important to note that you must flush a LOB that has been updated through the LBS in the following situations:

• Before committing the transaction,

• Before migrating from the current transaction to another,

• Before disabling buffering operations on a LOB

• Before returning from an external callout execution into the calling function/procedure/method in PL/SQL.

Note: When the external callout is called from a PL/SQL block and the locator is passed as a parameter, all buffering operations, including the enable call, should be made within the callout itself. In other words, adhere to the following sequence:

• Call the external callout,

• Enable the locator for buffering,

• Read/write using the locator,

• Flush the LOB,

• Disable the locator for buffering

• Return to the calling function/procedure/method in PL/SQL

Remember that Oracle never implicitly flushes the LOB.

Using Buffer-Enabled Locators

Note that there are several cases in which you can use buffer-enabled locators and others in which you cannot.

• When it is OK to Use Buffer-Enabled Locators:
  • OCI -- A locator that is enabled for buffering can only be used with the following OCI APIs:

    OCILobRead(), OCILobWrite(), OCILobAssign(), OCILobIsEqual(), OCILobLocatorIsInit(), OCILobCharSetId(), OCILobCharSetForm().

• When it is Not OK to Use Buffer-Enabled Locators: The following OCI APIs will return errors if used with a locator enabled for buffering:
  • OCI -- OCILobCopy(), OCILobAppend(), OCILobErase(), OCILobGetLength(), OCILobTrim(), OCILobWriteAppend().

    These APIs will also return errors when used with a locator which has not been enabled for buffering, but the LOB that the locator represents is already being accessed in buffered mode through some other locator.

  • PL/SQL (DBMS_LOB) -- An error is returned from DBMS_LOB APIs if the input lob locator has buffering enabled.

  • As in the case of all other locators, buffer-enabled locators cannot span transactions.

Saving Locator State to Avoid a Reselect

Suppose you want to save the current state of the LOB before further writing to the LOB buffer. In performing updates while using LOB buffering, writing to an existing buffer does not make a round-trip to the server, and so does not refresh the snapshot environment in the locator. This would not be the case if you were updating the LOB directly without using LOB buffering. In that case, every update would involve a round-trip to the server, and so would refresh the snapshot in the locator.

Therefore to save the state of a LOB that has been written through the LOB buffer, follow these steps:

1. Flush the LOB, thereby updating the LOB and the snapshot environment in the locator (L1). At this point, the state of the locator (L1) and the LOB are the same.

2. Assign the locator (L1) used for flushing and updating to another locator (L2). At this point, the states of the two locators (L1 and L2), as well as the LOB are all identical.

L2 now becomes a read consistent locator with which you are able to access the changes made through L1 up until the time of the flush, but not after! This assignment avoids incurring a roundtrip to the server to reselect the locator into L2.

OCI Example of LOB Buffering

The following pseudocode for an OCI program based on the Multimedia_tab schema illustrates the issues described above.

OCI_BLOB_buffering_program()
{
   int            amount;
   int            offset;
   OCILobLocator  lbs_loc1, lbs_loc2, lbs_loc3;
   void          *buffer;
   int            bufl;

   -- Standard OCI initialization operations - logging on to
   -- server, creating and initializing bind variables etc.
  
   init_OCI();

   -- Establish a savepoint before start of LBS operations 
   exec_statement("savepoint lbs_savepoint");
  
   -- Initialize bind variable to BLOB columns from buffered 
   -- access: 
   exec_statement("select frame into lbs_loc1 from Multimedia_tab
       where clip_id = 12");
   exec_statement("select frame into lbs_loc2 from Multimedia_tab
       where clip_id = 12 for update");
   exec_statement("select frame into lbs_loc2 from Multimedia_tab
       where clip_id = 12 for update");
      
   -- Enable locators for buffered mode access to LOB:
   OCILobEnableBuffering(lbs_loc1);
   OCILobEnableBuffering(lbs_loc2);
   OCILobEnableBuffering(lbs_loc3);
  
   -- Read 4K bytes through lbs_loc1 starting from offset 1:
   amount = 4096; offset = 1; bufl = 4096;
   OCILobRead(.., lbs_loc1, offset, &amount, buffer, bufl,   
      ..);
      if (exception)
          goto exception_handler;
          -- This will read the first 32K bytes of the LOB from 
          -- the server into a page (call it page_A) in the LOB's
          -- client-side buffer.
          -- lbs_loc1 is a read consistent locator.
         
          -- Write 4K of the LOB throgh lbs_loc2 starting from 
          -- offset 1:      
          amount = 4096; offset = 1; bufl = 4096;
          buffer = populate_buffer(4096);
          OCILobWrite(.., lbs_loc2, offset, amount, buffer, 
              bufl, ..);
      
      if (exception)
          goto exception_handler;
          -- This will read the first 32K bytes of the LOB from
          -- the server into a new page (call it page_B) in the
          -- LOB's buffer, and modify the contents of this page 
          -- with input buffer contents.
          -- lbs_loc2 is an updated locator.
      
          -- Read 20K bytes through lbs_loc1 starting from  
          -- offset 10K      
          amount = 20480; offset = 10240;
          OCILobRead(.., lbs_loc1, offset, &amount, buffer, 
              bufl, ..);
 
      if (exception)
        goto exception_handler;
          -- Read directly from page_A into the user buffer. 
          -- There is no round-trip to the server because the
          -- data is already in the client-side buffer.

          -- Write 20K bytes through lbs_loc2 starting from offset 
          -- 10K
          amount = 20480; offset = 10240; bufl = 20480;
          buffer = populate_buffer(20480);
          OCILobWrite(.., lbs_loc2, offset, amount, buffer, 
               bufl, ..);
      
      if (exception)
          goto exception_handler;
          -- The contents of the user buffer will now be written
          -- into page_B without involving a round-trip to the 
          -- server.  This avoids making a new LOB version on the
          -- server and writing redo to the log.  
                    
          -- The following write through lbs_loc3 will also  
          -- result in an error: 
          amount = 20000; offset = 1000; bufl = 20000;
          buffer = populate_buffer(20000);
          OCILobWrite(.., lbs_loc3, offset, amount, buffer, 
               bufl, ..);

      if (exception)
          goto exception_handler;
          -- No two locators can be used to update a buffered LOB 
          -- through the buffering subsystem
     
      -- The following update through lbs_loc3 will also           
      -- result in an error
      OCILobFileCopy(.., lbs_loc3, lbs_loc2, ..);

      if (exception)
          goto exception_handler;
          -- Locators enabled for buffering cannot be used with 
          -- operations like Append, Copy, Trim etc.
          -- When done, flush LOB's buffer to the server: 
      OCILobFlushBuffer(.., lbs_loc2, OCI_LOB_BUFFER_NOFREE);
  
      if (exception)
         goto exception_handler;
         -- This flushes all the modified pages in the LOB's buffer, 
         -- and resets lbs_loc2 from updated to read consistent 
         -- locator. The modified pages remain in the buffer 
         -- without freeing memory.  These pages can be aged 
         -- out if necessary.
      
      -- Disable locators for buffered mode access to LOB */
      OCILobDisableBuffering(lbs_loc1);
      OCILobDisableBuffering(lbs_loc2);
      OCILobDisableBuffering(lbs_loc3);

      if (exception)
         goto exception_handler;
         -- This disables the three locators for buffered access, 
         -- and frees up the LOB's buffer resources.
        exception_handler:
      handle_exception_reporting();
      exec_statement("rollback to savepoint lbs_savepoint");
}