Oracle Database 23c: new DBMS_LOB function GET_LOB_DEDUPLICATION_RATIO

Leave a reply
Photo by Greg Shield on Unsplash

Oracle Database provides advanced features to optimize storage and enhance performance. Starting with Oracle Database 23c, it is now possible to “estimate the space that you can save by enabling advanced LOB deduplication for existing LOBS“, by using the new GET_LOB_DEDUPLICATION_RATIO function. Let’s quickly demonstrate how it works.

Let’s start with a table ALBUMS in schema MUSIC containing a LOB column ALBUM_JACKET :

SQL> select SEGMENT_NAME,SECUREFILE,DEDUPLICATION from DBA_LOBS 
where TABLE_NAME='ALBUMS' and OWNER='MUSIC' ;

SEGMENT_NAME                 SECUREFILE    DEDUPLICATION
____________________________ _____________ ________________
SYS_LOB0000071544C00004$$    YES           NO

SQL> select BYTES, BYTES/1024/1024/1024 GB from DBA_SEGMENTS 
where SEGMENT_NAME = 'SYS_LOB0000071544C00004$$' ;

        BYTES                GB
_____________ _________________
   2219048960    2.066650390625

This is a SecureFile LOB, it does not use deduplication, and the corresponding segment is approximately 2Gb.

Now let’s determine the potential deduplication ratio. The DBMS_LOB.GET_LOB_DEDUPLICATION_RATIO function provides a straightforward way to calculate this ratio:

SQL> 
set serveroutput on

DECLARE
  ratio number;
BEGIN
  ratio := DBMS_LOB.GET_LOB_DEDUPLICATION_RATIO(
   tablespacename        => 'USERS',
   tabowner              => 'MUSIC',
   tabname               => 'ALBUMS',
   lobcolumnname         => 'ALBUM_JACKET',
   partname              => '',
   dedup_ratio           => ratio,
   subset_numrows        => '-1'
);
dbms_output.put_line(ratio);
END;
/

116.986656132576419700638502027170345421

PL/SQL procedure successfully completed.

The estimation shows that we could save up to 116.9 times the current space if deduplication is used! Let’s verify this by enabling deduplication on this LOB:

SQL> alter table MUSIC.ALBUMS move lob(ALBUM_JACKET) store as securefile(deduplicate);

Table MUSIC.ALBUMS altered.

Let’s check again the properties of the LOB, and of course, its new size:

SQL> select SEGMENT_NAME,SECUREFILE,DEDUPLICATION from DBA_LOBS 
where TABLE_NAME='ALBUMS' and OWNER='MUSIC' ;

SEGMENT_NAME                 SECUREFILE    DEDUPLICATION
____________________________ _____________ ________________
SYS_LOB0000071544C00004$$    YES           LOB


SQL> select BYTES, BYTES/1024/1024/1024 GB from DBA_SEGMENTS 
where SEGMENT_NAME = 'SYS_LOB0000071544C00004$$' ;

      BYTES                GB
___________ _________________
   20185088    0.018798828125

SQL> select round(2219048960/20185088) ;

   ROUND(2219048960/20185088)
_____________________________
                          110

Now the actual size of the segment is only 19Mb, indicating a significant reduction in storage consumption. Considering the actual ratio of 110 that we got, I’d say the promise has been kept.

The DBMS_LOB.GET_LOB_DEDUPLICATION_RATIO function provides valuable insights into the potential benefits of LOB deduplication. This approach is of great help for decision-making and the implementation of storage optimization strategies. But please, test and validate these changes before applying them to a production database 🙂

Leave a comment