How to Reclaim Space from LOBs: A Practical Case Study

Hi everyone, hope you all are doing well!

At our company, we have an internal distribution list where we discuss technical issues, challenges, etc.

A question came in this week:

I have a client with a very large lob segment, 15t. The total db size is 20t with only about 3t available. They want to archive the data and need to recover the lob segment space. What is the best method to do this without a reorg? Does the following work?

ALTER TABLE yourLobTable MODIFY LOB (yourLobColumn) (SHRINK SPACE);

And before resizing the data file you need to do this?

ALTER TABLE table DEALLOCATE UNUSED KEEP integer;

ALTER INDEX index DEALLOCATE UNUSED KEEP integer;

ALTER CLUSTER cluster DEALLOCATE UNUSED KEEP integer;

This is actually a good question, and after discussing it with this colleague, I had the idea to create this blog post as a case study.

Let’s first understand LOBs. The info below is available in the official Oracle documentation:

What are LOBs?

Large Objects (LOBs) are a set of data types that are designed to hold large amounts of data.

The maximum size for a single LOB can range from 8 terabytes to 128 terabytes depending on how your database is configured. Storing data in LOBs enables you to access and manipulate the data efficiently in your application.

Why Use Large Objects?

Large objects allow you to store large amounts of data in several types of structures.

Large objects are suitable for semistructured and unstructured data.

Large object features allow you to store these kinds of data in the database and in operating system files that are accessed from the database.

Semistructured data

Semistructured data has a logical structure that is not typically interpreted by the database, for example, an XML document that your application or an external service processes. Oracle Database provides features such as Oracle XML DB, Oracle Multimedia, and Oracle Spatial and Graph to help your application work with semistructured data.

Unstructured data

Unstructured data is easily not broken down into smaller logical structures and is not typically interpreted by the database or your application, such as a photographic image stored as a binary file.

With the growth of the Internet and content-rich applications, it has become imperative for Oracle Database to provide LOB support that:

Can store unstructured and semistructured data in an efficient manner
Is optimized for large amounts of data
Provides a uniform way of accessing data stored within the database or outside the database

LOBs Used for Semistructured Data:

Semistructured data include document files such as XML documents or word processor files, which contain data in a logical structure that is processed or interpreted by an application, and is not broken down into smaller logical units when stored in the database.

The data types used for Semistructured Data are:

CLOB
NCLOB

LOBs Used for Unstructured Data

Unstructured data is data that cannot be decomposed into standard components.

Unstructured data, such as a photograph, consists of a long stream of 1s and 0s. These bits are used to switch pixels on or off so that you can see the picture on a display, but the bits are not broken down into any standard components for database storage.

Also, unstructured data such as text, graphic images, still video clips, full motion video, and sound waveforms tends to be large in size. A typical employee record may be a few hundred bytes, while even small amounts of multimedia data can be thousands of times larger.

The data types used for Unstructured Data are:

BLOB: stored inside the Oracle table, as a column.
BFILE: not stored inside the Oracle table; the file is available in the OS and accessible through a directory object.

LOBs data types were introduced in Oracle 8i. In that time, the LOB architecture was called BasicFiles.

On 11g Release 1, Oracle introduced the SecureFiles, a new LOB architecture called SecureFiles.

Below there is a table explaining a little bit the LOB architectures:

Aspect	BasicFiles LOB	SecureFiles LOB
Tablespace Requirement	Requires tablespaces not managed with ASSM	Requires tablespaces managed with ASSM
Performance	Legacy storage with lower performance and scalability	Designed for high performance and scalability
Compression	Not supported	Supported via Advanced LOB Compression (requires Advanced Compression Option license)
Deduplication	Not supported	Supported via Advanced LOB Deduplication (requires Advanced Compression Option license)
Encryption	Not supported	Supported via TDE-based SecureFiles Encryption (requires Oracle Advanced Security Option license)
Network File System Performance	Not optimized	Designed to meet/exceed NFS performance

For my study case, I’ve built two tables:

BFILE_DEMO: with only one column named B_FILE, the data type is BFILE
LOB_DEMO: with two columns, ID as NUMBER and PHOTO as BLOB.

In the BFILE_DEMO table, we have only one record, pointing to a PNG file named lob.png, inside an object directory called PICS. Basically, when you have a BFILE data type, your table is pointing to a file outside the DB (inside an OS directory):

insert into bfile_demo values ( bfilename('PICS','lob.png'));

In the LOB_DEMO table, we’ll insert 12,500 records. IDs will be unique, and PHOTO will always point to the same file. We’ll use BFILE_DEMO as the source to insert the records into our LOB_DEMO. Rather than pointing to a file outside the DB, we’ll insert the content of the file lob.png into the column. To make our case simpler, all 12,500 records will have the same photo.

The picture below explain about this:

OK, let’s start our case study.

Case Study 1: Creating the table using a LOB SecureFile

CREATE TABLE lob_demo ( ID NUMBER, PHOTO BLOB) LOB (PHOTO) STORE AS SECUREFILE;

Checking the size for the table and for the LOB:

select segment_name, sum(bytes)/1024/1024 MBYTES, count(*) EXTENTS
from user_extents
where segment_name in ('LOB_DEMO')
or segment_name in (select segment_name from user_lobs where table_name='LOB_DEMO')
group by segment_name;

no rows selected

Interesting, isn’t?

Well, this happens because the parameter deferred_segment_creation is set to TRUE, so no segment/extent is created until we insert the first record into the table:

select name,value from v$parameter where name='deferred_segment_creation';

NAME                                                         VALUE
------------------------------------------------------------ ----------------------------------------
deferred_segment_creation                                    TRUE

Inserting the 12,500 records into the table:

declare
    tmp_blob blob default EMPTY_BLOB();
    tmp_bfile bfile:=null;
    dest_offset integer:=1;
    src_offset integer:=1;
begin
    select b_file into tmp_bfile from bfile_demo;
    DBMS_LOB.OPEN (tmp_bfile, DBMS_LOB.FILE_READONLY);
    dbms_lob.createtemporary(tmp_blob, TRUE);
    DBMS_LOB.LOADBLOBFROMFILE(tmp_blob,tmp_bfile,DBMS_LOB.LOBMAXSIZE,dest_offset,src_offset);
    for i in 1..12500 loop
        insert into lob_demo values(i,tmp_blob);
        commit;
    end loop;
    DBMS_LOB.CLOSE(tmp_bfile);
    end;
/

Checking the size again:

select segment_name, sum(bytes)/1024/1024 MBYTES, count(*) EXTENTS
from user_extents
where segment_name in ('LOB_DEMO')
or segment_name in (select segment_name from user_lobs where table_name='LOB_DEMO')
group by segment_name;

SEGMENT_NAME                             MBYTES        EXTENTS
------------------------------ ---------------- --------------
SYS_LOB0000075884C00002$$            19.010,19             413
LOB_DEMO                                   ,75              12

OK, so our LOB is basically 19GB in size.

Now, I will delete 50% of the records:

delete from lob_demo where (id/2) = trunc(id/2);

6250 rows deleted.

commit;

Checking the size again:

select segment_name, sum(bytes)/1024/1024 MBYTES, count(*) EXTENTS
from user_extents
where segment_name in ('LOB_DEMO')
or segment_name in (select segment_name from user_lobs where table_name='LOB_DEMO')
group by segment_name;

SEGMENT_NAME                             MBYTES        EXTENTS
------------------------------ ---------------- --------------
SYS_LOB0000075884C00002$$            19.010,19             413
LOB_DEMO                                   ,75              12

(No changes in size, even with 50% less data.)

Now, let’s perform some operations to shrink the space in the table. First, we need to enable row movement, as this is a requirement and then perform the other steps:

alter table lob_demo enable row movement;

alter table lob_demo shrink space cascade;

alter table lob_demo deallocate unused;

Checking the size again:

select segment_name, sum(bytes)/1024/1024 MBYTES, count(*) EXTENTS
from user_extents
where segment_name in ('LOB_DEMO')
or segment_name in (select segment_name from user_lobs where table_name='LOB_DEMO')
group by segment_name;

SEGMENT_NAME                             MBYTES        EXTENTS
------------------------------ ---------------- --------------
SYS_LOB0000075884C00002$$            19.010,19             413
LOB_DEMO                                   ,38               6

OK, the table size was reduced by 50%, but the LOB size slightly reduced.

Let’s try to shrink the LOB:

alter table lob_demo modify lob (photo) (shrink space);

*
ERROR at line 1:
ORA-10635: Invalid segment or tablespace type

Oops, we got this error!

According to the Oracle Documentation for 19c, shrink operations on SecureFile LOBs are not supported.

The good news is this is supported starting in 21c.

So, the only way to “reclaim” the space is by moving the LOB (and the table, if needed) to another tablespace:

alter table lob_demo move online tablespace users2;

select segment_name, sum(bytes)/1024/1024 MBYTES, count(*) EXTENTS
from user_extents
where segment_name in ('LOB_DEMO')
or segment_name in (select segment_name from user_lobs where table_name='LOB_DEMO')
group by segment_name;

SEGMENT_NAME                             MBYTES        EXTENTS
------------------------------ ---------------- --------------
SYS_LOB0000075884C00002$$             9.290,13            1143
LOB_DEMO                                   ,44               7

Great! We can see that the LOB size has been reduced by 50%.

Case Study 2: Creating the table using a LOB BasicFile

CREATE TABLE lob_demo ( ID NUMBER, PHOTO BLOB) LOB (PHOTO) STORE AS BASICFILE;

Checking the size for the table and for the LOB:

select segment_name, sum(bytes)/1024/1024 MBYTES, count(*) EXTENTS
from user_extents
where segment_name in ('LOB_DEMO')
or segment_name in (select segment_name from user_lobs where table_name='LOB_DEMO')
group by segment_name;

no rows selected

Inserting the 12,500 records into the table:

declare
    tmp_blob blob default EMPTY_BLOB();
    tmp_bfile bfile:=null;
    dest_offset integer:=1;
    src_offset integer:=1;
begin
    select b_file into tmp_bfile from bfile_demo;
    DBMS_LOB.OPEN (tmp_bfile, DBMS_LOB.FILE_READONLY);
    dbms_lob.createtemporary(tmp_blob, TRUE);
    DBMS_LOB.LOADBLOBFROMFILE(tmp_blob,tmp_bfile,DBMS_LOB.LOBMAXSIZE,dest_offset,src_offset);
    for i in 1..12500 loop
        insert into lob_demo values(i,tmp_blob);
        commit;
    end loop;
    DBMS_LOB.CLOSE(tmp_bfile);
    end;
/

Checking the size again:

select segment_name, sum(bytes)/1024/1024 MBYTES, count(*) EXTENTS
from user_extents
where segment_name in ('LOB_DEMO')
or segment_name in (select segment_name from user_lobs where table_name='LOB_DEMO')
group by segment_name;

SEGMENT_NAME                             MBYTES        EXTENTS
------------------------------ ---------------- --------------
LOB_DEMO                                  2,00              17
SYS_LOB0000075895C00002$$            18.496,00             472

The size for BasicFile is a little bit small comparing with the SecureFile.

OK, so, our LOB basically has 18.5G of size.

Now, I will delete 50% of the records:

delete from lob_demo where (id/2) = trunc(id/2);

6250 rows deleted.

commit;

Checking the size again:

select segment_name, sum(bytes)/1024/1024 MBYTES, count(*) EXTENTS
from user_extents
where segment_name in ('LOB_DEMO')
or segment_name in (select segment_name from user_lobs where table_name='LOB_DEMO')
group by segment_name;

SEGMENT_NAME                             MBYTES        EXTENTS
------------------------------ ---------------- --------------
LOB_DEMO                                  2,00              17
SYS_LOB0000075895C00002$$            18.496,00             472

(No changes in size, even with 50% less data.)

Now, let’s perform some operations to shrink the space in the table. First, we need to enable row movement, as this is a requirement and then the other steps:

alter table lob_demo enable row movement;

alter table lob_demo shrink space cascade;

Checking the size again:

select segment_name, sum(bytes)/1024/1024 MBYTES, count(*) EXTENTS
from user_extents
where segment_name in ('LOB_DEMO')
or segment_name in (select segment_name from user_lobs where table_name='LOB_DEMO')
group by segment_name;

SEGMENT_NAME                             MBYTES        EXTENTS
------------------------------ ---------------- --------------
LOB_DEMO                                   ,75              12
SYS_LOB0000075895C00002$$            16.927,13             448

OK, the table size was reduced by 50%, but the LOB size slightly reduced.

Let’s try to shrink the LOB:

alter table lob_demo modify lob (photo) (shrink space);

alter table lob_demo deallocate unused;

select segment_name, sum(bytes)/1024/1024 MBYTES, count(*) EXTENTS
from user_extents
where segment_name in ('LOB_DEMO')
or segment_name in (select segment_name from user_lobs where table_name='LOB_DEMO')
group by segment_name;

SEGMENT_NAME                             MBYTES        EXTENTS
------------------------------ ---------------- --------------
LOB_DEMO                                   ,75              12
SYS_LOB0000075895C00002$$            16.927,06             448

The LOB size reduced just a little bit. Why? LOBs are not stored using blocks, they are stored using CHUNKS.

Let’s try to move the table:

alter table lob_demo move online tablespace users2;

select segment_name, sum(bytes)/1024/1024 MBYTES, count(*) EXTENTS
from user_extents
where segment_name in ('LOB_DEMO')
or segment_name in (select segment_name from user_lobs where table_name='LOB_DEMO')
group by segment_name;

SEGMENT_NAME                             MBYTES        EXTENTS
------------------------------ ---------------- --------------
LOB_DEMO                                   ,75              12
SYS_LOB0000075895C00002$$             9.243,00             330

Great! We can see that the LOB size has been reduced by 50%!

Case Study 3: Creating the table using a LOB SecureFile enabling the deduplication

CREATE TABLE lob_demo ( ID NUMBER, PHOTO BLOB) LOB (PHOTO) STORE AS SECUREFILE(deduplicate);

Checking the size for the table and for the LOB:

select segment_name, sum(bytes)/1024/1024 MBYTES, count(*) EXTENTS
from user_extents
where segment_name in ('LOB_DEMO')
or segment_name in (select segment_name from user_lobs where table_name='LOB_DEMO')
group by segment_name;

no rows selected

Inserting the 12,500 records into the table:

declare
    tmp_blob blob default EMPTY_BLOB();
    tmp_bfile bfile:=null;
    dest_offset integer:=1;
    src_offset integer:=1;
begin
    select b_file into tmp_bfile from bfile_demo;
    DBMS_LOB.OPEN (tmp_bfile, DBMS_LOB.FILE_READONLY);
    dbms_lob.createtemporary(tmp_blob, TRUE);
    DBMS_LOB.LOADBLOBFROMFILE(tmp_blob,tmp_bfile,DBMS_LOB.LOBMAXSIZE,dest_offset,src_offset);
    for i in 1..12500 loop
        insert into lob_demo values(i,tmp_blob);
        commit;
    end loop;
    DBMS_LOB.CLOSE(tmp_bfile);
    end;
/

Checking the size again:

select segment_name, sum(bytes)/1024/1024 MBYTES, count(*) EXTENTS
from user_extents
where segment_name in ('LOB_DEMO')
or segment_name in (select segment_name from user_lobs where table_name='LOB_DEMO')
group by segment_name;

SEGMENT_NAME                             MBYTES        EXTENTS
------------------------------ ---------------- --------------
LOB_DEMO                                  2,00              17
SYS_LOB0000075906C00002$$                 3,13               4

Amazing, isn’t it? The deduplication on the LOB detects that the value in the PHOTO column is always the same (the same picture), so the space used by the LOB is really small. Please notice that I did not removed any record from the table.

The main point is: what are the odds that the client will always have the same data in the LOB column? I’d say the chances are small.

OK, let’s conclude here about the three case studies:

Case Study 1:
SecureFile LOB Size Savings in percentage after Shrink Space / Deallocate Unused: 0.0%
SecureFile LOB Size Savings in percentage after MOVE: 52.14%

Case Study 2:
BasicFile LOB Size Savings in percentage after Shrink Space / Deallocate Unused: 8.48%
BasicFile LOB Size Savings in percentage after MOVE: 50.03%

Case Study 3:
SecureFile Deduplicate LOB Initial Size in percentage comparing with SecureFile LOB Initial Size: 99.98%
SecureFile Deduplicate LOB Initial Size in percentage comparing with BasicFile LOB Initial Size: 99.98%

I even did not test the COMPRESSION, which can help us saving more space.

Now, back to my colleague’s main question:

ALTER TABLE yourLobTable MODIFY LOB (yourLobColumn) (SHRINK SPACE);

And before resizing the data file you need to do this?

ALTER TABLE table DEALLOCATE UNUSED KEEP integer;

ALTER INDEX index DEALLOCATE UNUSED KEEP integer;

ALTER CLUSTER cluster DEALLOCATE UNUSED KEEP integer;

Some Important Considerations:

We don’t know yet whether the LOB is BasicFile or SecureFile.
We don’t know yet the DB version, if supports shrink for SecureFile
We don’t know yet how the client will archive the data, especially given the limited 3TB of free space.
We don’t know yet the percentage of data the client plans to purge.
Even if it’s a BasicFile LOB, the chances of saving significant space using SHRINK SPACE are low.
SHRINK SPACE will lock the table, causing an outage.
We don’t know yet the allowed downtime for this operation.
We don’t know yet if there are referential constraints tied to this table.

If the client doesn’t have space for a reorg (like a MOVE), the only feasible solution I can see is:

Perform the LOB data cleanup
Export the table
Drop the table
Import the table

If any column in this table is used as a foreign key, that increases the complexity.

So, as you can see, there’s no clear conclusion. The best answer in the IT world is: it depends!

Hope this case study was helpful for you!

Peace!

Vinicius

How to Reclaim Space from LOBs: A Practical Case Study

ByVinicius

Related Post

AutoUpgrade-Composer: Installing a New Oracle Home with Create Home Mode

AutoUpgrade-Composer: Downloading Patches with Download Mode

Introducing: The AutoUpgrade-Composer Tool [STABLE]

Leave a Reply Cancel reply

You missed

AutoUpgrade-Composer: Installing a New Oracle Home with Create Home Mode

AutoUpgrade-Composer: Downloading Patches with Download Mode

How to Reclaim Space from LOBs: A Practical Case Study

How to Automate the Restore of /u01 on OCI Using Shell Script and oci-cli