Tuesday, March 17, 2009

Deduplication Using SecureFile for LOBS in 11g

In my previous blog, I had moved a lob from basicfile to securefile. I will use the same table for the dedupication example.
I had five rows in that table, 3 had similar lob data.

Storage Stats of the table:
SQL> select owner,table_name,COLUMN_NAME,SEGMENT_NAME from dba_lobs where table_name='BLOB_TEST';

OWNER TABLE_NAME COLUMN_NAME SEGMENT_NAME
---------- ---------- --------------- --------------------------------------------------
HR BLOB_TEST DATA SYS_LOB0000073688C00002$$

The below plsql block is courtesy oracle
declare
l_segment_size_blocks number;
l_segment_size_bytes number;
l_used_blocks number;
l_used_bytes number;
l_expired_blocks number;
l_expired_bytes number;
l_unexpired_blocks number;
l_unexpired_bytes number;

begin
dbms_space.space_usage(
segment_owner => 'HR',
segment_name => 'SYS_LOB0000073688C00002$$',
segment_type => 'LOB',
partition_name => NULL,
segment_size_blocks => l_segment_size_blocks,
segment_size_bytes => l_segment_size_bytes,
used_blocks => l_used_blocks,
used_bytes => l_used_bytes,
expired_blocks => l_expired_blocks,
expired_bytes => l_expired_bytes,
unexpired_blocks => l_unexpired_blocks,
unexpired_bytes => l_unexpired_bytes
);
dbms_output.put_line('Segment Size in Blocks => '|| l_segment_size_blocks);
dbms_output.put_line('Segment Size in Bytes => '|| l_segment_size_bytes);
dbms_output.put_line('Used Blocks => '|| l_used_blocks);
dbms_output.put_line('Used Bytes => '|| l_used_bytes);
dbms_output.put_line('Expired Blocks => '|| l_expired_blocks);
dbms_output.put_line('Expired Bytes => '|| l_expired_bytes);
dbms_output.put_line('Unexpired Blocks => '|| l_unexpired_blocks);
dbms_output.put_line('Unexpired Bytes => '|| l_unexpired_bytes);
end;
/
============================================
Segment Size in Blocks => 189848
Segment Size in Bytes => 1555234816
Used Blocks => 167392
Used Bytes => 1371275264 (1307.75 MB)
Expired Blocks => 22246
Expired Bytes => 182239232
Unexpired Blocks => 0
Unexpired Bytes => 0
============================================
SQL> alter table blob_test modify lob(data) (deduplicate);
Table altered.

This command takes some time to run.
Storage Stats after the deduplication.
============================================
Segment Size in Blocks => 416408
Segment Size in Bytes => 3411214336
Used Blocks => 55799
Used Bytes => 457105408 (435.93 MB)
Expired Blocks => 80882
Expired Bytes => 662585344
Unexpired Blocks => 279292
Unexpired Bytes => 2287960064
============================================

You can see that the Used Bytes column has come down visibly. As I had mentioned that there were 3 LOBs of the same size, which shows that the size has come down to 1/3rd.
The blob stored was:
a.dmp, b.dmp and c.dmp the same ones with size 428.88

0 Comments: