[20140729]关于LOB字段存储特性1.txt

  1. 云栖社区>
  2. 博客>
  3. 正文

[20140729]关于LOB字段存储特性1.txt

lfreeali 2014-08-02 17:13:21 浏览759
展开阅读全文

[20140729]关于LOB字段存储特性1.txt

CLOB/BLOB字段的存储可以使用参数ENABLE/DISABLE STORAGE IN ROW,默认是ENABLE STORAGE IN ROW,这种情况下,如果
保存长度小于等于3964(主要前面占用36字节),实际上总和是4000字节.

参考:
http://blog.itpub.net/267265/viewspace-755269/
[20130301]clob字段的empty_clob与NULL.txt

在保存的clob信息有两个选项,缺省ENABLE STORAGE IN ROW (default),在这种情况下:
1.如果保存的长度小于等于3964,lob的信息保存在块内.
2.如果保存的长度大于3964,lob的信息保存在块外的lob segment内.

SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

create table t (id number,idx number,col1 clob,col2 clob, col3 clob) lob (col3) store as (disable storage in row);
create unique index i_t_id on t(id);
insert into t values (1,1,lpad('b',100,'b'),lpad('a',4000,'a'),lpad('a',4000,'a'));
commit ;
execute dbms_stats.gather_table_stats(user,'t',cascade=>true,method_opt=>'for all columns size 1',no_invalidate=>false);

--安装的语言选择NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK.这样即使1个英文字符也占用2个字节.
--这样都是插入8000字节,lob的信息保存在块外(我的数据库是8k的).
--题外话题:最好使用blob类型,这样可以原样保存,特别保存的文本是英文的情况下,空间浪费比较严重,对于中文字符集.
--col1=lpad('b',100,'b'),应该在块内。
--col2=lpad('a',4000,'a'),长度超出3964,应该在块外。
--col3=lpad('b',4000,'b'),使用ENABLE STORAGE IN ROW,无论如何都在块外。

SCOTT@test> SELECT DBMS_METADATA.get_ddl ('TABLE','T') from dual ;

DBMS_METADATA.GET_DDL('TABLE','T')
------------------------------------------------------------------------------
  CREATE TABLE "SCOTT"."T"
   (    "ID" NUMBER,
        "IDX" NUMBER,
        "COL1" CLOB,
        "COL2" CLOB,
        "COL3" CLOB
   ) SEGMENT CREATION IMMEDIATE
  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 ("COL1") STORE AS BASICFILE (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 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))
LOB ("COL2") STORE AS BASICFILE (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 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))
LOB ("COL3") STORE AS BASICFILE (
  TABLESPACE "USERS" DISABLE STORAGE IN ROW CHUNK 8192 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))

--可以发现11g下缺省使用的属性STORE AS BASICFILE,不是securefile。

SCOTT@test> set autot trace
SCOTT@test> select id,idx from t where id=1;
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
--注意如果出现recursive calls0,可以执行多次。可以发现仅仅2个逻辑读。

SCOTT@test> select id,col1 from t where id=1;
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
--col1的信息长度

SCOTT@test> select id,col2 from t where id=1;
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          2  physical reads
          0  redo size
--如果col2是ENABLE STORAGE IN ROW,由于长度大于3964,保存在块外,导致 consistent gets增加到4,同时存在
--2个physical reads,无论执行多少次都一样.

SCOTT@test> select id,col3 from t where id=1;         
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          2  physical reads
          0  redo size

--对比发现,ENABLE/DISABLE STORAGE IN ROW两者的逻辑读差异很大,前者仅仅4个逻辑读,而后者仅仅11个逻辑读,而且每次都是2个物理读。
--在块内字段col1,执行的语句select id,idx from t where id=1 与select id,col1 from t where id=1,两者的逻辑读一样。

--为什么读取col2,col3 字段,两者的逻辑读相差这么大呢?

先做一个转储:

SCOTT@test> select rowid,id,idx from t where id=1;

ROWID                      ID        IDX
------------------ ---------- ----------
AABGgGAAEAAAACmAAA          1          1

SCOTT@test> @lookup_rowid   AABGgGAAEAAAACmAAA
    OBJECT       FILE      BLOCK        ROW DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
    288774          4        166          0 4,166                alter system dump datafile 4 block 166 ;

SCOTT@test> alter system checkpoint ;
System altered.

SCOTT@test>  alter system dump datafile 4 block 166 ;
System altered.

Block header dump:  0x010000a6
Object id on Block? Y
seg/obj: 0x46806  csc: 0x02.a60dfbd5  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x10000a0 ver: 0x01 opc: 0
     inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0005.020.00006957  0x00c01a26.22e7.1a  --U-    1  fsc 0x0000.a60dfbdd
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x010000a6
data_block_dump,data header at 0x2a97255a64
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x2a97255a64
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1e64
avsp=0x1e50
tosp=0x1e50
0xe:pti[0]  nrow=1  offs=0
0x12:pri[0] offs=0x1e64
block_row_dump:
tab 0, row 0, @0x1e64
tl: 308 fb: --H-FL-- lb: 0x1  cc: 5
col  0: [ 2]  c1 02
col  1: [ 2]  c1 02
col  2: [236]
00 54 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 02 33 ad 91 00 d8 09 00 00
00 00 00 00 c8 00 00 00 00 00 01 00 62 00 62 00 62 00 62 00 62 00 62 00 62
00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00
62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62
00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00
62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62
00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00
62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62
00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00
62 00 62 00 62 00 62 00 62 00 62
LOB
Locator:
  Length:        84(236)
  Version:        1
  Byte Length:    2
  LobID: 00.00.00.01.00.00.02.33.ad.91
  Flags[ 0x02 0x0c 0x80 0x00 ]:
    Type: CLOB
    Storage: BasicFile
    Enable Storage in Row
    Characterset Format: IMPLICIT
    Partitioned Table: No
    Options: VaringWidthReadWrite
  Inode:
    Size:     216
    Flag:     0x09 [ Valid DataInRow ]
    Future:   0x00 (should be '0x00')
    Blocks:   0
    Bytes:    200
    Version:  00000.0000000001
    Inline data[200]
Dump of memory from 0x0000002A972578F6 to 0x0000002A972579BE
2A972578F0          62000100 62006200 62006200      [...b.b.b.b.b]
2A97257900 62006200 62006200 62006200 62006200  [.b.b.b.b.b.b.b.b]
        Repeat 10 times
2A972579B0 62006200 62006200 62006200 00286200  [.b.b.b.b.b.b.b(.]
--可以发现'b'保存00 62(16进制),占用2个字节.

col  3: [40]
00 54 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 02 33 ad 92 00 14 05 00 00
00 00 00 1f 40 00 00 00 00 00 02 01 00 02 76
LOB
Locator:
  Length:        84(40)
  Version:        1
  Byte Length:    2
  LobID: 00.00.00.01.00.00.02.33.ad.92
  Flags[ 0x02 0x0c 0x80 0x00 ]:
    Type: CLOB
    Storage: BasicFile
    Enable Storage in Row
    Characterset Format: IMPLICIT
    Partitioned Table: No
    Options: VaringWidthReadWrite
  Inode:
    Size:     20
    Flag:     0x05 [ Valid InodeInRow(ESIR) ]
    Future:   0x00 (should be '0x00')
    Blocks:   0
    Bytes:    8000
    Version:  00000.0000000002
    DBA Array[1]:
      0x01000276

col  4: [20]  00 54 00 01 02 08 80 00 00 02 00 00 00 01 00 00 02 33 ad 93
LOB
Locator:
  Length:        84(20)
  Version:        1
  Byte Length:    2
  LobID: 00.00.00.01.00.00.02.33.ad.93
  Flags[ 0x02 0x08 0x80 0x00 ]:
    Type: CLOB
    Storage: BasicFile
    Disable Storage in Row
    Characterset Format: IMPLICIT
    Partitioned Table: No
    Options: VaringWidthReadWrite
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 166 maxblk 166


--注意看col  3: [40]以及col  4: [20]部分,对应的字段col3与col3,前者长度40,后者长度仅仅20.
--col 3:
    DBA Array[1]:
      0x01000276

col  3: [40]
00 54 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 02 33 ad 92 00 14 05 00 00
00 00 00 1f 40 00 00 00 00 00 02 01 00 02 76

后面的部分相对应。

SCOTT@test> @dfb 01000276
    RFILE#     BLOCK#
---------- ----------
         4        630

TEXT
----------------------------------------
alter system dump datafile 4 block 630 ;


--如果参考URL http://juliandyke.com/Presentations/LOBInternals.ppt,可以发现ENABLE STORAGE IN ROW的情况下,仅仅12chunk记录在块内。
--LOB Index is used for 13th chunk. 这样如果lob长度小于chunk size *12 = 8192*12 =98304 bytes下,并不通过lob index来定位。

--而看col  4: [20] ,后面记录的LobID。这样要通过lob index索引来定位信息。
col  4: [20]  00 54 00 01 02 08 80 00 00 02 00 00 00 01 00 00 02 33 ad 93
  LobID: 00.00.00.01.00.00.02.33.ad.93

--再做一个10046跟踪看看。
SCOTT@test> alter system flush buffer_cache;
System altered.

SCOTT@test> @10046on 12
Session altered.

SCOTT@test> select id,col3 from t where id=1;
....

SCOTT@test> @10046off
Session altered.

=====================
PARSING IN CURSOR #182927045352 len=32 dep=0 uid=84 oct=3 lid=84 tim=1406709303754031 hv=3667476909 ad='b1392ef0' sqlid='4jcthq7d9khdd'
select id,col3 from t where id=1
END OF STMT
PARSE #182927045352:c=0,e=144,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1149237570,tim=1406709303754029
EXEC #182927045352:c=0,e=74,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1149237570,tim=1406709303754210
WAIT #182927045352: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1406709303754290
WAIT #182927045352: nam='SQL*Net message from client' ela= 24218 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1406709303778561
WAIT #182927045352: nam='Disk file operations I/O' ela= 290 FileOperation=2 fileno=4 filetype=2 obj#=288781 tim=1406709303779219
WAIT #182927045352: nam='db file scattered read' ela= 108 file#=4 block#=560 blocks=8 obj#=288781 tim=1406709303779359
WAIT #182927045352: nam='db file scattered read' ela= 75 file#=4 block#=160 blocks=8 obj#=288774 tim=1406709303779650
WAIT #182927045352: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=288774 tim=1406709303779797
FETCH #182927045352:c=1999,e=1211,p=16,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=1149237570,tim=1406709303779848
STAT #182927045352 id=1 cnt=1 pid=0 pos=1 obj=288774 op='TABLE ACCESS BY INDEX ROWID T (cr=2 pr=16 pw=0 time=1149 us cost=1 size=90 card=1)'
STAT #182927045352 id=2 cnt=1 pid=1 pos=1 obj=288781 op='INDEX UNIQUE SCAN I_T_ID (cr=1 pr=8 pw=0 time=776 us cost=0 size=0 card=1)'
WAIT #182927045352: nam='SQL*Net message from client' ela= 144 driver id=1650815232 #bytes=1 p3=0 obj#=288774 tim=1406709303780254
WAIT #0: nam='db file scattered read' ela= 168 file#=4 block#=1432 blocks=8 obj#=288780 tim=1406709303780934
WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=288779 tim=1406709303781254
LOBREAD: c=1000,e=998,p=9,cr=3,cu=0,tim=1406709303781308
WAIT #0: nam='SQL*Net more data to client' ela= 33 driver id=1650815232 #bytes=8265 p3=0 obj#=288779 tim=1406709303781382
WAIT #0: nam='SQL*Net message from client' ela= 33639 driver id=1650815232 #bytes=1 p3=0 obj#=288779 tim=1406709303815056
LOBREAD: c=0,e=149,p=1,cr=6,cu=0,tim=1406709303815300
WAIT #0: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=288779 tim=1406709303815343
WAIT #0: nam='SQL*Net message from client' ela= 5877 driver id=1650815232 #bytes=1 p3=0 obj#=288779 tim=1406709303821246
FETCH #182927045352:c=0,e=4,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=1149237570,tim=1406709303821320
WAIT #182927045352: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=288779 tim=1406709303821366

*** 2014-07-30 16:35:06.610
WAIT #182927045352: nam='SQL*Net message from client' ela= 2788695 driver id=1650815232 #bytes=1 p3=0 obj#=288779 tim=1406709306610093
CLOSE #182927045352:c=0,e=19,dep=0,type=1,tim=1406709306610236
=====================
PARSING IN CURSOR #182927041672 len=55 dep=0 uid=84 oct=42 lid=84 tim=1406709306610405 hv=2217940283 ad='0' sqlid='06nvwn223659v'


SCOTT@test> column OBJECT_NAME format a30
SCOTT@test> select * from user_objects where
object_name in (
select segment_name a from user_lobs where table_name='T'
union all
select index_name a from user_lobs where table_name='T'
)
or object_name in ('T','I_T_ID');
OBJECT_NAME                    SUBOBJECT_  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED             LAST_DDL_TIME       TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME
------------------------------ ---------- ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------
SYS_IL0000288774C00005$$                      288780         288780 INDEX               2014-07-30 15:40:38 2014-07-30 15:40:38 2014-07-30:15:40:38 VALID   N Y N          4
SYS_LOB0000288774C00005$$                     288779         288779 LOB                 2014-07-30 15:40:38 2014-07-30 15:40:38 2014-07-30:15:40:38 VALID   N Y N          8
SYS_IL0000288774C00004$$                      288778         288778 INDEX               2014-07-30 15:40:38 2014-07-30 15:40:38 2014-07-30:15:40:38 VALID   N Y N          4
SYS_LOB0000288774C00004$$                     288777         288777 LOB                 2014-07-30 15:40:38 2014-07-30 15:40:38 2014-07-30:15:40:38 VALID   N Y N          8
SYS_IL0000288774C00003$$                      288776         288776 INDEX               2014-07-30 15:40:38 2014-07-30 15:40:38 2014-07-30:15:40:38 VALID   N Y N          4
SYS_LOB0000288774C00003$$                     288775         288775 LOB                 2014-07-30 15:40:38 2014-07-30 15:40:38 2014-07-30:15:40:38 VALID   N Y N          8
T                                             288774         288774 TABLE               2014-07-30 15:40:38 2014-07-30 15:43:03 2014-07-30:15:40:38 VALID   N N N          1
I_T_ID                                        288781         288781 INDEX               2014-07-30 15:43:03 2014-07-30 15:43:03 2014-07-30:15:43:03 VALID   N N N          4
8 rows selected.

SYS_LOB0000288774C00005$$ 的OBJECT_ID=288779,对应上面的跟踪可以发现。

$ egrep "obj#=288779|obj#=288780" /u01/app/oracle11g/diag/rdbms/test/test/trace/test_ora_10999_127_0_0_1.trc
WAIT #0: nam='db file scattered read' ela= 168 file#=4 block#=1432 blocks=8 obj#=288780 tim=1406709303780934
WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=288779 tim=1406709303781254
WAIT #0: nam='SQL*Net more data to client' ela= 33 driver id=1650815232 #bytes=8265 p3=0 obj#=288779 tim=1406709303781382
WAIT #0: nam='SQL*Net message from client' ela= 33639 driver id=1650815232 #bytes=1 p3=0 obj#=288779 tim=1406709303815056
WAIT #0: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=288779 tim=1406709303815343
WAIT #0: nam='SQL*Net message from client' ela= 5877 driver id=1650815232 #bytes=1 p3=0 obj#=288779 tim=1406709303821246
WAIT #182927045352: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=288779 tim=1406709303821366
WAIT #182927045352: nam='SQL*Net message from client' ela= 2788695 driver id=1650815232 #bytes=1 p3=0 obj#=288779 tim=1406709306610093

--从这些可以看出一般使用ENABLE STORAGE IN ROW,在lob字段长度很小的情况下(chunk size *12 = 8192*12 =98304),chunk的信息记录在块内,不需要通过lob index来定位,能获得很好的性能。

网友评论

登录后评论
0/500
评论
lfreeali
+ 关注