New Utility to Migrate BasicFiles LOBS to SecureFiles LOBS
In previous versions it was challenging to decide which BasicFiles LOBs to migrate to SecureFiles LOBs, and whether or not to compress the LOBs, especially considering that organizations often have multiple databases, with a large numbers of schemas, tables, and segments.
You can now use the SecureFiles Migration Utility to simplify the migration, and compression, of BasicFiles LOB segments to SecureFiles LOB segments. The SecureFiles Migration Utility automates several steps that were earlier performed manually. It also generates reports that help you decide which BasicFiles LOBs you want to migrate and compress.
Migration Utility Advantages
– No need to take the table or partition offline
– Perform the migration at the database, schema, table, or LOB segment level
– After migrating the data, you can also use the SecureFiles Migration Utility to compress the SecureFiles LOBs (SecureFiles Compression is a feature of Oracle Advanced Compression)
Migration Utility Disadvantages
– Additional storage, equal to the entire table or partition, is required and all LOB segments must be available
– Global indexes must be rebuilt
This new utility is the recommended method to migrate BasicFiles LOB data to SecureFiles storage. This utility encapsulates all the functionality offered by Online Redefinition and saves you the time, and effort, involved in manually running a series of API calls.
Please note that the SecureFiles Migration Utility consists of various scripts used to configure a LOB migration. As such, there is nothing to be downloaded. Please see the SecureFiles Migration Utility documentation for usage details and examples.
Using Online Redefinition for Migration
As in previous releases, you can still use Online Redefinition to migrate LONG, or BasicFiles LOB data, to SecureFiles storage by running several API calls. While online redefinition for LONG to LOB migration must be performed at the table level, BasicFiles LOB to SecureFiles LOB migration can be performed at the table or partition level.
Online Redefinition Advantages
– No need to take the table or partition offline
– Can be performed in parallel
Online Redefinition Disadvantages
– Additional storage, equal to the entire table or partition, is required and all LOB segments must be available
– Global indexes must be rebuilt
For more information about the SecureFiles Migration Utility, using Online Redefinition for SecureFiles migration, and other migration methods, please see the Oracle documentation [here].
Rename LOB Segment Enhancement
In previous releases, to rename an existing LOB segment users performed an operation such as ALTER TABLE ... MOVE, which could perform slowly since the operation physically moves the LOB data as part of the renaming.
This enhancement improves the performance of renaming a LOB segment, at the table, partition and subpartition level by eliminating the physical movement of the LOB data.
Enhancement to SecureFiles LOBS Compression Advisor (DBMS_COMPRESSION)
Use Compression Advisor to estimate the storage space that you can save by enabling the compression feature for an existing SecureFiles LOB. It analyzes the compression ratio of a table, or an index, and provides information about the compressibility of the object. You can provide various parameters to selectively analyze different compression types. A new parameter, byte_comp_ratio, was added in this release and provides the ratio of bytes of uncompressed data to the bytes of compressed data for LOBS. (see the documentation link below for a complete list of parameters).
In Oracle Database 23ai, the compression advisor for LOBs procedure has been enhanced to estimate the compression ratio faster for both inline and out-of-line LOBs while using less space. Now you can also estimate the compression ratio for BasicFiles LOBs. This helps you decide upfront whether you want to compress the resultant SecureFiles LOB, before migrating BasicFiles LOBs to SecureFiles LOBs. You can also estimate the compression ratio at the LOB byte level, and the time taken in hours, to compress the LOB data in the table.
For example:
Consider that you have inserted 1000 LOBs, of 3K each, in seperate tables where one table stored the LOBs inline and the other table stored the LOBs out-of-line (overriding the default). By default, if the LOB is less than 4K, then data is stored in the table segment (inline), otherwise, it is stored in a seperate LOB segment (out-of-line). Using Compression Advisor, we will estimate the storage savings for both tables, the results of running compression advisor are as follows:
Sample output of compression ratio for table with inline LOBs:
Estimated block compression ratio: 1
Estimated byte compression ratio: 57.6
Space used (in bytes): 0
Space used (in blocks): 0
Sample output of compression ratio for table with out-of-line LOBs:
Estimated block compression ratio: 1
Estimated byte compression ratio: 56.1
Space used (in bytes): 8MB
Space used (in blocks): 1000
In this example, even though the estimated byte and block compression ratios are almost the same for inline and out-of-line LOBs, the space that is used is different. In the case of the inline table, a LOB segment is not used so the space used is zero. In both cases, the data is approximately 3KB, which is small. Therefore, the data before and after compression uses the same number of blocks (one block), so the block compression ratio is one. However, the byte level compression ratio, byte_comp_ratio, which compares the actual number of bytes used by the LOBs before, and after compresion, is 57.6 or 56.1.
Disclaimer: The compression ratio is an approximate value, which is calculated based on the sampled rows in the LOB column. The actual space that you save, when you enable compression for the complete table, may be different.
For more information about Compression Advisor, and usage examples, please see the Oracle documentation [here].
Enhancements to Estimate Space Saving before using Advanced LOB Deduplication
Advanced LOB Deduplication enables Oracle Database to automatically detect duplicate LOB data, within a LOB column or partition, and conserve space by storing only one copy of the data. Note that Advanced LOB Deduplication is a feature of Advanced Compression. You can now estimate the space, that you can save, before enabling Advanced LOB Deduplication. This allows you to make an informed decision whether or not to enable LOB deduplication as well as decide whether you want to deduplicate the resultant SecureFiles LOB, before migrating BasicFiles LOB to SecureFiles LOB.
The GET_LOB_DEDUPLICATION_RATIO function estimates the storage space that you can save by enabling the deduplication feature, for existing SecureFiles LOBs, and returns the deduplication ratio.
Syntax
DBMS_LOB.GET_LOB_DEDUPLICATION_RATIO (
tablespacename IN VARCHAR2,
tabowner IN VARCHAR2,
loccolumnname IN VARCHAR2,
partname IN VARCHAR2,
dedup_ratio IN NUMBER,
subset_numrows IN NUMBER DEFAULT
DEDUP_RATIO_LOB_MAXROWS
)
The deduplication ratio (dedup_ratio) is estimated for the number of rows in the LOB column that you specify, the syntax above uses DEDUP_RATIO_LOB_MAXROWS to specify all rows be included in the estimate. The LOB storage savings depends upon the deduplication ratio achieved. For example, say that the deduplication advisor estimated a deduplication ratio of 2.33x, this means that generally, the amount of space consumed by the LOBs, without deduplication, will be 2.33 times larger.
Note: The maximum number of LOBs that this function can process is 100000 or 1% of the total number of rows in the table, whichever is lesser.
Disclaimer: The deduplication ratio is an approximate value, which is calculated based on the sampled rows in the LOB column. The actual space that you save, when you enable deduplication for the complete table, may be different.
For more information about the GET_LOB_DEDUPLICATION_RATIO function, and an usage example, please see the Oracle documentation [here].