相同的数据，导入2套库为什么LOB占用的存储大小相差一倍？

今天一个同事咨询不同块大小的库（一套32K,一套8K），导入了相同的数据，LOB占用的空间大小差了1倍，32k的占了103g，8k的占了45g。

根据这个描述，首先想到的LOB存储是分CHUMK的，一个块中只能放一行LOB的数据，剩余空间也不能被其它行的LOB使用。根据这个猜想，接下来就是验证。

验证猜想

验证内容，创建一张带LOB列的表，INSERT 2行数据，每行的LOB为1000字节，DUMP数据块，如果这2行LOB放到了2个数据块，则能证明上面的猜测。

1、创建表

我的空是8K的块，这里指定了CHUNK 1024，但实际创建后 CHUNK为8192，不能小于块大小。
CREATE TABLE "TEST"."TESTLOB"
 (    "ID" NUMBER,
      "C2" BLOB
 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
 LOB ("C2") STORE AS BASICFILE (
  TABLESPACE "USERS" DISABLE STORAGE IN ROW CHUNK 1024 RETENTION
  NOCACHE LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;

复制

2、INSERT 2条数据，每行LOB 1000字节
执行2次，INSERT 2行。

DECLARE
c blob;
BEGIN
dbms_lob.createtemporary(lob_loc => c, cache => true, dur => dbms_lob.call);
DBMS_LOB.WRITEAPPEND(c,1000,hextoraw(rpad('01',2000,'01')));
INSERT INTO TEST.TESTLOB VALUES (2,c);
COMMIT;
END;

复制

3、查看数据大小

SQL> select dbms_lob.getlength(c2) from TEST.TESTLOB;

DBMS_LOB.GETLENGTH(C2)
----------------------
                  1000
                  1000

复制

4、DUMP数据块，可以看到2行数据放到了不同的BLOCK上

SQL> @lob TEST.TESTLOB

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
----------------- ----------------- ------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ------- ---- ------ --------------- --- --------------- --- --- --- ------- ---------------
TEST                TESTLOB          C2                             SYS_LOB0004292755C00002$$                                                                                                        USERS                          SYS_IL0004292755C00002$$                                                                                                               8192                   900            NO         YES     NONE NONE   NONE            NO  NOT APPLICABLE  NO  NO  YES YES

SQL> 

SQL> @seg TEST.SYS_LOB0004292755C00002$$

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
         0 TEST                   SYS_LOB0004292755C00002$$                                     LOBSEGMENT           USERS                                   8         27      72346

SQL> alter system dump datafile 27 block 72348;

System altered.

SQL> alter system dump datafile 27 block 72349;

System altered.


通过DUMP BLOCK可以看到虽然每个LOB1000字节，但还是分到2个块上存放,一个块上不能放多行LOB。

---BLCOK 27/72348
Block dump from disk:
buffer tsn: 5 rdba: 0x06c11a9c (27/72348)
scn: 0xfa14def230b seq: 0x02 flg: 0x04 tail: 0x230b2802
frmt: 0x02 chkval: 0xa14a type: 0x28=PAGETABLE MANAGED LOB BLOCK
Hex dump of block: st=0, typ_found=1

Long field block dump:
Object Id  4292756
LobId: 000100361FE8D PageNo        0
Version: 0x0000.00000000  pdba: 113318552
01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01
    01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01
    01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01
    01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01
    01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01


---BLCOK 27/72349
Block dump from cache:
Dump of buffer cache at level 3 for pdb=3 tsn=5 rdba=113318557
Block dump from disk:
buffer tsn: 5 rdba: 0x06c11a9d (27/72349)
scn: 0xfa14def2336 seq: 0x02 flg: 0x04 tail: 0x23362802
frmt: 0x02 chkval: 0xa24b type: 0x28=PAGETABLE MANAGED LOB BLOCK
Hex dump of block: st=0, typ_found=1
Long field block dump:
Object Id  4292756
LobId: 000100361FE8E PageNo        0
Version: 0x0000.00000000  pdba: 113318552
01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01
    01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01
    01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01
    01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01
    01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01
复制

实际问题分析

统计平均每行LOB大小为13576
with t(select dbms_lob.getlength() sz from table where lobcol is not null and rownum<10000)
select avg(sz),min(sz),max(sz) from t;

8k块空空间利用率约为80%
In [11]: 13576/(8192*2)
Out[11]: 0.82861328125

32k块空空间利用率约为40%
In [12]: 13576/32768
Out[12]: 0.414306640625

空间利用率，差了一倍，占用空间差距就有了。