ANBOB™

前段时间一客户的Oracle数据库使用datapump做了迁移，发现相同数据LOB段迁移前后占用空间有原来的45G增长到了103GB, 朋友在墨天轮社区记录了这个问题click here, 主要原因是因为使用了不同的Lob Chunk Size，导致的空间浪费。这里简单的记录一下这个问题。

LOB Storage

This section summarizes LOB storage characteristics to consider when designing tables with LOB column types.

CHUNK

A chunk is one or more Oracle blocks. You can specify the chunk size for the LOB when creating the table that contains the LOB. This corresponds to the data size used by Oracle Database when accessing or modifying the LOB value. Part of the chunk is used to store system-related information and the rest stores the LOB value. The API you are using has a function that returns the amount of space used in the LOB chunk to store the LOB value. In PL/SQL use DBMS_LOB.GETCHUNKSIZE.

If the tablespace block size is the same as the database block size, then CHUNK is also a multiple of the database block size. The default CHUNK size is equal to the size of one tablespace block, and the maximum value is 32K.

Choosing the Value of CHUNK

Once the value of CHUNK is chosen (when the LOB column is created), it cannot be changed. Hence, it is important that you choose a value which optimizes your storage and performance requirements.

DEMO
— test oracle 19.3 on linux ,block size 8k

create table anbob.tab_lob(
  id           number,
  blob_8k       blob,
  blob_32k      blob
)
lob (blob_8k) store as basicfile (
  enable       storage in row
  chunk        8192)
lob (blob_32k) store as basicfile (
  enable       storage in row
  chunk        32768);
  
insert into anbob.tab_lob values (1, empty_blob(), empty_blob());

commit;


select id, 
      dbms_lob.getchunksize(blob_8k)  blob_8k, 
      dbms_lob.getchunksize(blob_32k) blob_32k
from anbob.tab_lob;  

        ID    BLOB_8K   BLOB_32K
---------- ---------- ----------
         1       8132      32528

SQL> truncate table anbob.tab_lob;

Table truncated.

SQL> declare
l_lob blob;
len number:=0;
BEGIN
dbms_lob.createtemporary(lob_loc => l_lob, cache => true);
while len<5000 loop 
DBMS_LOB.WRITEAPPEND(l_lob,1000,hextoraw(rpad('01',2000,'01'))); 
select dbms_lob.getlength(l_lob) into len from dual ; 
end loop; 
for i in 1..1000 loop 
INSERT INTO anbob.tab_lob VALUES (i,l_lob,l_lob); 
COMMIT; 
end loop; 
END; 
PL/SQL procedure successfully completed. 

SQL> select dbms_lob.getlength(blob_8k),dbms_lob.getlength(blob_32k) 
from anbob.tab_lob 
where rownum<=10; 
DBMS_LOB.GETLENGTH(BLOB_8K) DBMS_LOB.GETLENGTH(BLOB_32K) 
--------------------------- ---------------------------- 
5000 5000 
5000 5000 
5000 5000 
5000 5000 
5000 5000 
5000 5000 
5000 5000 
5000 5000 
5000 5000 
5000 5000 
10 rows selected. 

SQL> @lob anbob.%

OWNER       TABLE_NAME     COLUMN_NAME     SEGMENT_NAME                   TABLESPACE_NAME                INDEX_NAME                           CHUNK PCTVERSION  RETENTION  FREEPOOLS CACHE      LOGGING ENCR COMPRE DEDUPLICATION   IN_ FORMAT          PAR SEC SEG RETENTI RETENTION_VALUE
--------- - -------------- --------------- ------------------------------ ------------------------------ ------------------------------- ---------- ---------- ---------- ---------- ---------- ------- ---- ------ --------------- --- --------------- --- --- --- ------- ---------------
ANBOB       TAB_LOB        BLOB_8K         SYS_LOB0000079303C00002$$      USERS                          SYS_IL0000079303C00002$$              8192                   900            NO         YES     NONE NONE   NONE            YES NOT APPLICABLE  NO  NO  YES YES
ANBOB       TAB_LOB        BLOB_32K        SYS_LOB0000079303C00003$$      USERS                          SYS_IL0000079303C00003$$             32768                   900            NO         YES     NONE NONE   NONE            YES NOT APPLICABLE  NO  NO  YES YES

SQL> @seg anbob.sys_

    SEG_MB OWNER      SEGMENT_NAME                   SEG_PART_NAME     SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK
---------- ---------- ------------------------------ --------------- - -------------------- ------------------------------ ---------- ---------- ----------
         0 ANBOB      SYS_IL0000079303C00002$$                         LOBINDEX             USERS                                   8         12      27234
         0 ANBOB      SYS_IL0000079303C00003$$                         LOBINDEX             USERS                                   8         12      27250
         9 ANBOB      SYS_LOB0000079303C00002$$                        LOBSEGMENT           USERS                                1152         12      27226
        33 ANBOB      SYS_LOB0000079303C00003$$                        LOBSEGMENT           USERS                                4224         12      27242

SQL> SELECT SUM(BYTES) FROM DBA_EXTENTS WHERE SEGMENT_NAME = 'SYS_LOB0000079303C00003$$';

SUM(BYTES)
----------
  34603008

SQL> SELECT SUM(BYTES) FROM DBA_EXTENTS WHERE SEGMENT_NAME = 'SYS_LOB0000079303C00002$$';

SUM(BYTES)
----------
   9437184

SQL> SELECT SUM(DBMS_LOB.GETLENGTH(blob_8k)) "LOB DATA" FROM anbob.tab_lob;

  LOB DATA
----------
   5000000

SQL> SELECT SUM(DBMS_LOB.GETLENGTH(blob_32k)) "LOB DATA" FROM anbob.tab_lob;

  LOB DATA
----------
   5000000

Note:
可见对于平均长度为5000bytes的LOB段, 32K的lob chunk浪费了更多的存储空间。

打赏

微信扫一扫，打赏作者吧～