Oracle 21c New Feature - Automatic SecureFiles Defragmentation

This blog post explores a new feature in Oracle 21c related to SecureFiles LOB objects.  

As you know, Oracle provides the BLOB and CLOB data types to store large objects inside the database, and the BFILE data type to reference large objects stored outside the database.  Oracle's BLOB and CLOB data types support two formats: BasicFiles and SecureFiles.  Each format also has its own driver within the Oracle stack.  The primary API for working with either BasicFiles or SecureFiles LOBs is DBMS_LOB.

The BasicFiles format was first introduced in Oracle 8.0 and is still supported today but is not the preferred format.  The SecureFiles format was introduced in Oracle 11g and is the preferred format today because it is more efficient, more performant, and offers high-end features like compression, deduplication, and encryption. 

Starting with Oracle 12c R1, all new BLOB and CLOB columns are created as SecureFiles unless you specifically request the BasicFiles format.  This behavior is controlled by parameter DB_SECUREFILE.

SecureFiles have always supported compression, deduplication, and encryption.  These are generally considered to be extra-cost features since compression and deduplication of LOBs requires a license for the Oracle Advanced Compression Option, and encryption of LOBs requires a license for the Oracle Advanced Security Option.  Using SecureFiles LOBs is otherwise free.

LOB data can get fragmented over time.  Gaps open between fragments of the data, and fragments can also be stored out-of-order.  This can have a serious negative impact on LOB performance.

Defragmenting a LOB prior to Oracle 21c required moving the LOB.  A complete re-write of the LOB in a new location ensured it would be defragmented.  However, that was an expensive operation in terms of time and I/O, and the LOB was offline to users during the entire operation.

Starting with Oracle 21c, Automatic SecureFiles Defragmentation provides online defragmentation of allocated and freed space in SecureFiles segments.  The work occurs automatically using a background process that is transparent to users.  The only thing you should notice when it is running is the allocation of temp segments, and these temp segments are released once the operation completes.

Automatic SecureFiles Defragmentation uses the Oracle Segment Advisor to determine when a LOB segment needs to be defragmented.

You may also run the defrag manually.  Oracle 21c offers two newly modified SQL statements:

  • To defragment a specific LOB:

ALTER TABLE <TABLE_NAME> MODIFY LOB (<LOB_COLUMN_NAME>) (SHRINK SPACE);

  • To defragment all LOBs of a given table:

ALTER TABLE <TABLE_NAME> SHRINK SPACE CASCADE;

Automatic SecureFiles Defragmentation works with all SecureFiles LOBs including those using Oracle Partitioning, compression, de-duplication, and encryption

To monitor defrag operations, just query the view V$SECUREFILE_SHRINK.  Each defrag operation adds a row to this view.  The row is periodically updated as the defrag operation runs.

Comments

Post a Comment

Popular posts from this blog

Using DBGen to Generate TPC-H Test Data

Image
In this post I describe the Data Base Generator (DBGen) utility in terms of what it is, how to install it, and how to use it.   This post assumes a Linux operating system.   There are different build procedures for DBGen on Windows, but the concepts carry over. DBGen is used to create a TPC-H schema (i.e., it provides the CREATE TABLE statements) and to generate the data.   DBGen is used in official TPC-H benchmarking, but it can also be used in informal TPC-H-like benchmarking.   The schema and data is compatible with most 3 rd party benchmarking tools like HammerDB, so even if you plan to run a TPC-H-like test using HammerDB you may still use DBGen to create the schema and data. The  CREATE TABLE   statements are ANSI standard SQL and can be run in any DBMS without editing.   There are no SQL statements for indexes or foreign key constraints provided by DBGen since those types of objects are not required.   The data is created as delimited...
Read more

Oracle 21c Caching Solutions

This blog post reviews all of the caching solutions available in Oracle Database 21c.  I dive into several of the caching solutions, but I do try to keep things relatively short-and-sweet here.  I have other blogs dedicated to each caching solution. I don't mean the Oracle SGA or PGA.   I mean optional features you can enable to enhance performance. The content of this blog post is applicable to Oracle 12g, 18c, 19c, and 21c.   Some of the caching solutions were available in Oracle 11g, but I did not regression test my examples back that far. There have been no new caching solutions in Oracle 12g R2 through 21c.   One solution in particular, the In-Memory Column Store, has received some major enhancements.   I briefly describe it here, but the deep dive is in another blog post. Summary of Oracle Caching Solutions Oracle 21c includes six caching features as listed in my table below.   I've listed them in order from newest on top to oldest at t...
Read more

TPC-like Database Benchmarking Tools

Image
  This post describes five popular tools for benchmarking relational databases: HammerDB, Quest Benchmark Factory, SwingBench, pgbench, and Oracle RAT.  Previously, I described TPC and TPC-like benchmarks for relational databases.   Recall a benchmark is "like" a TPC benchmark if it follows some of the rules but not all of them.   The tools described in this post follow some of the TPC's rules so we call them "TPC-like".   Publishing results is generally not allowed by commercial DBMS vendors, and use of the benchmark trademark is also restricted. Ok then, let's look at the most commonly used benchmarking tools for relational databases. HammerDB is my #1 choice for benchmarking relational databases.   HammerDB supports every major DBMS on Linux and Windows including bare metal, VM, and cloud.   This means you can use one tool for all environments.   You can even compare performance across platforms or DBMS, such as comparing Oracle on Linux...
Read more