• 博客访问: 1588540
  • 博文数量: 117
  • 用 户 组: 普通用户
  • 注册时间: 2014-04-06 15:21
  • 认证徽章:
个人简介

不仅仅专注Oracle database技术, member of SHOUG

文章分类

全部博文(117)

文章存档

2017年(3)

2016年(20)

2015年(58)

2014年(36)

分类: Oracle

2014-10-15 17:39:26


SecureFiles LOBs相比于BasicFiles LOBs具有加密(encryption)、去重(deduplicaiton)、压缩(compression)等新功能,pctversionchunksize等参数也仅仅为了向后兼容而保留,因此SecureFiles LOBs的自适应能力更强,在管理上更为简化,成为了clobblob等大对象使用的首选,上面的这些功能描述可以参考官方文档来获得。

我们今天要讨论的是SecureFiles LOBs里与存储相关的知识,当你在使用SecureFiles LOBs的时候你是否了解它在磁盘上是如何存储的,在创建SecureFiles LOBs时对于存储有关的参数设定有何要求,lob如何管理自己的undo等问题,我们都将会通过实验来为大家解答这些问题。

 

?  Securefile LOBs字段所在数据块的存储结构

--建立测试用表为dump作准备

create table lobt1 (id number,c1 clob) lob(c1) store as securefile lobt1_c1(disable storage in row);

insert into lobt1 values(1,lpad('A',10,'A'));

insert into lobt1 values(2,lpad('B',10,'B'));

commit;

 

alter system flush buffer_cache;

 

 

select dbms_rowid.rowid_to_absolute_fno(rowid,'SCOTT','LOBT1') absfno,dbms_rowid.rowid_block_number(rowid) blkno,count(1) from LOBT1 group by dbms_rowid.rowid_to_absolute_fno(rowid,'SCOTT','LOBT1'),dbms_rowid.rowid_block_number(rowid);

   ABSFNO      BLKNO   COUNT(1)

---------- ---------- ----------

       131     723110          2

 

alter system dump datafile 131 block 723110;     

 

-- lobt1dump结果节选:

tab 0, row 0, @0x1f6c

tl: 44 fb: --H-FL-- lb: 0x1  cc: 2

col  0: [ 2]  c1 02

col  1: [37]

 00 54 00 01 02 0c 80 80 00 02 00 00 00 01 00 04 29 37 3d a5 00 11 40 90 00

 0b 20 00 14 01 00 00 20 cb 0f 2f 01

LOB

Locator:

  Length:        84(37)

  Version:        1

  Byte Length:    2

  LobID: 00.00.00.01.00.04.29.37.3d.a5

  Flags[ 0x02 0x0c 0x80 0x80 ]:

    Type: CLOB

    Storage: SecureFile

    Characterset Format: IMPLICIT

    Partitioned Table: No

    Options: VaringWidthReadWrite

  SecureFile Header:

    Length:   17

    Old Flag: 0x40 [ SecureFile ]

    Flag 0:   0x90 [ INODE Valid ]

    Layers:

      Lengths Array: INODE:11

      INODE:

        20 00 14 01 00 00 20 cb 0f 2f 01

 

02 0c 80 80:字段类型是clob,如果是01 0c 00 80则为blob

00 00 00 01 00 04 29 37 3d a5:每一行都有一个唯一的一个lobid

00 11securefile header的长度为17,从00 11开始到行尾红17 bytes

40:表示disable storage in row securefile,如果是48表示enable storage in row securefile

00 0binode长度

14lob字段中数据所占的字节数为,0x14代表20bytes,我们插入的10个字母占用20 bytes(使用AL16UTF16字符集)

20 cb 0f 2fchunk所在的data block address

01:以上述chunk地址为起始地址,所包含多少个连续的chunk

 

?  SecureFiles LOBs对表与表空间存储参数上的要求

////////////// 1Maxsize的最小值测试 //////////////

--创建一个表空间extent size设置为128K

create tablespace ts128k1 datafile '/oradata06/ts128k1.dbf' size 128M extent management local uniform size 128K segment space management auto;

 

--创建带有lob字段的表,并把lob segmentmaxsize设置为512k,结果报错

create table tlob33 (id number, t33col2 clob) lob(t33col2) store as securefile tlob_tmp (tablespace ts128k1 storage(maxsize 512K) disable storage in row retention max);

 

ERROR at line 1:

ORA-60014: invalid MAXSIZE storage option value

 

create table tlob33 (id number, t33col2 clob) lob(t33col2) store as securefile tlob_tmp (tablespace ts128k1 storage(maxsize 768K) disable storage in row retention max);

 

ERROR at line 1:

ORA-60014: invalid MAXSIZE storage option value

 

--逐级增加maxsize的大小,来测试create 语句能否成功,直到maxsize=1024k才成功

create table tlob33 (id number,t33col2 clob) lob(t33col2) store as securefile tlob_33 (tablespace ts128k1 storage(maxsize 1024K) disable storage in row retention max);

 

Table created.

 

--记录lob segment的大小等信息

select bytes,segment_name from dba_segments where segment_name='TLOB_33';

     BYTES SEGMENT_NAME

---------- ---------------------------------------------------------------------------------

    131072 TLOB_33

   

col segment_name format a20

col segment_type format a10

col tablespace_name format a15

set linesize 150

select segment_name,segment_type,tablespace_name,extent_id,file_id,relative_fno,block_id,blocks,bytes from dba_extents where segment_name='TLOB_33' and tablespace_name='TS128K1';

SEGMENT_NAME         SEGMENT_TY TABLESPACE_NAME  EXTENT_ID    FILE_ID RELATIVE_FNO   BLOCK_ID     BLOCKS      BYTES

-------------------- ---------- --------------- ---------- ---------- ------------ ---------- ---------- ----------

TLOB_33              LOBSEGMENT TS128K1                  0       1036           13        128         16     131072

 

////////////// 2、表空间extent_size的最小值测试 //////////////

--新创建一个表空间uniform size 缩小至64k,观察一下securefileextent size大小是否有要求

create tablespace ts128k2 datafile '/oradata06/ts128k2.dbf' size 128M extent management local uniform size 64K segment space management auto;

 

--下面的错误表明Secure file对于表空间的extent size要求至少为112k(14*8k),而实际Extent size只有64k(8*8k),创建不成功

create table tlob44 (id number,t44col2 clob) lob(t44col2) store as securefile tlob_44 (tablespace ts128k2 storage(maxsize 1024K) disable storage in row retention max);

ERROR at line 1:

ORA-60019: Creating initial extent of size 14 in tablespace of extent size 8

 

--新建一个表空间uniform size 设置为112k,观察表空间的extent_size最小设为多少

create tablespace ts128k3 datafile '/oradata06/ts128k3.dbf' size 128M extent management local uniform size 112K segment space management auto;

 

--这回虽然112K了,但报了个ORA-00600错误

create table tlob55 (id number,t55col2 clob) lob(t55col2) store as securefile tlob_55 (tablespace ts128k3 storage(maxsize 2128K) disable storage in row retention max);

 

SQL> create table tlob55 (id number,t55col2 clob) lob(t55col2) store as securefile tlob_55 (tablespace ts128k3 storage(maxsize 2128K) disable storage in row retention max);

create table tlob55 (id number,t55col2 clob) lob(t55col2) store as securefile tlob_55 (tablespace ts128k3 storage(maxsize 2128K) disable storage in row retention max)

*

ERROR at line 1:

ORA-00600: internal error code, arguments: [ktsladdfcb-bsz], [3], [], [], [],

[], [], [], [], [], [], []

 

--测试下来最小的Extent size必须是8k*14+1=114689,因为一定要是8k的整数倍,实际就是120K(8k*15)作为最小的extent size

drop tablespace ts128k3 including contents and datafiles;

create tablespace ts128k3 datafile '/oradata06/ts128k3.dbf' size 128M extent management local uniform size 114689 segment space management auto;

 

Tablespace created.

 

drop tablespace ts128k3 including contents and datafiles;

create tablespace ts128k3 datafile '/oradata06/ts128k3.dbf' size 128M extent management local uniform size 120k segment space management auto;

 

Tablespace created.

 

drop table tlob55;

create table tlob55 (id number,t55col2 clob) lob(t55col2) store as securefile tlob_55 (tablespace ts128k3 storage(maxsize 2128K) disable storage in row retention max);

 

Table created.

 

--下面来看一下为何oracle对存放lob segmentextent size大小有最低要求,以第一个测试中建立的TLOB_33这个segment为例,dump一下

alter system dump datafile 1036 block min 128 block max 143;

 

--dump出来的内容里过滤出每个block的用途发现其中16blocks中有11个是存放metadata的,我们知道lob是自己管理undo的所以这些都是存储上花费的开销,还有5个是存放数据用的

frmt: 0x02 chkval: 0x798b type: 0x45=NGLOB: Lob Extent Header

frmt: 0x02 chkval: 0xa7e5 type: 0x3f=NGLOB: Segment Header

frmt: 0x02 chkval: 0x798f type: 0x3d=NGLOB: Hash Bucket

frmt: 0x02 chkval: 0x798e type: 0x3d=NGLOB: Hash Bucket

frmt: 0x02 chkval: 0x798b type: 0x3d=NGLOB: Hash Bucket

frmt: 0x02 chkval: 0x07f8 type: 0x3d=NGLOB: Hash Bucket

frmt: 0x02 chkval: 0x798b type: 0x3d=NGLOB: Hash Bucket

frmt: 0x02 chkval: 0x798b type: 0x3d=NGLOB: Hash Bucket

frmt: 0x02 chkval: 0x7983 type: 0x3d=NGLOB: Hash Bucket

frmt: 0x02 chkval: 0x7983 type: 0x3d=NGLOB: Hash Bucket

frmt: 0x02 chkval: 0x7983 type: 0x3d=NGLOB: Hash Bucket

frmt: 0x02 chkval: 0x012d type: 0x00=unknown

frmt: 0x02 chkval: 0x012a type: 0x00=unknown

frmt: 0x02 chkval: 0x012b type: 0x00=unknown

frmt: 0x02 chkval: 0x0128 type: 0x00=unknown

frmt: 0x02 chkval: 0x0129 type: 0x00=unknown

 

结论1securefiles LOBs的最小大小必须>=1024k,其所在表空间的最小extent size15blocks,对于blocksize=8k来说, 1 extent size就是120k

 

?  SecureFiles LOBs在磁盘上的存储方式

Securefiles LOBs存储方式和BasicFiles LOBs一样有两种,一种是inline storage,另一种是out-of-line storage。我们知道对于BasicFiles LOBs来说如果lob字段长度<=3964个字节是和表存储在一起的,称为inline storage>3964字节时会迁移到lob segment里,即out-of-line storage。对于SecureFiles LOBs来说这个值是多少?我们下面测试一下。

在测试之前有个概念需要明确一下,对于CLOB类型的字段其存储时所用的字符集有可能与数据库本身的字符集不一致,对于使用US7ASCIIWE8ISO8859P1等定长字符集的数据库来说clob字段存储时使用的字符集和数据库字符集一致;对于使用ZHS16GBKUTF8等变长字符集的数据库clob字段存储时使用的字符集为UCS29i及以下版本)或者AL16UTF16(10g及以上版本)UCS2AL16UTF16都是定长的,长度为2bytes,举个例子对于字符A来说存储到varchar2字段占用1个字节,存储到clob字段时就会占用2个字节。以下测试数据库使用的是ZHS16GBK字符集

////////////// 3SecureFiles inline storage &out-of-line storage界限测试 //////////////

--创建测试表

create table tsec_lob (id number,secol2 clob) lob(secol2) store as securefile lob_tsec (retention);

 

--先插入1982个字符,每个字符两个字节,总共占用是3964 bytes

insert into tsec_lob values(1,lpad('D',1982,'D')); 

commit;   

 

--dump出数据块的内容

select table_name,segment_name,securefile from dba_lobs where table_name='TSEC_LOB';

TABLE_NAME                     SEGMENT_NAME                   SEC

------------------------------ ------------------------------ ---

TSEC_LOB                       LOB_TSEC                       YES

 

select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from tsec_lob;

 

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)

------------------------------------ ------------------------------------

4284                                  302

                               

select * from v$diag_info where name='Default Trace File';   --shzw1_ora_54592144.trc

 

alter system flush buffer_cache;

 

alter system dump datafile 302 block 4284;

   

    --dump节选,目前字段总长度为3995bytes,其中前面31bytesmetadata

block_row_dump:

tab 0, row 0, @0xff4

tl: 4004 fb: --H-FL-- lb: 0x1  cc: 2

col  0: [ 2]  c1 02

col  1: [3995]

00 54 00 01 02 0c 80 80 00 02 00 00 00 01 00 04 28 c9 b5 05 0f 87 48 90 0f

81 01 00 0f 7c 01 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00

 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44

 。。。。。。此处略去,一共有1982”00 44”

 

--根据上面的推断4000-31=3969,如果字段里真正数据占用的长度达到3969 bytes时就会迁移到lob segments上去,在原来基础上增加两个字符的长度,总长度达到3999 bytes,其中数据部分为3968 bytesByte Length: 2表示一个字符占据2 bytes

 

update tsec_lob set secol2=lpad('D',1984,'D');

commit;

 

alter system flush buffer_cache;

 

select * from v$diag_info where name='Default Trace File';   --shzw1_ora_24903952.trc

 

alter system dump datafile 302 block 4284;

 

--shzw1_ora_24903952.trc内容

data_block_dump,data header at 0x11085d264

===============

tsiz: 0x1f98

hsiz: 0x14

pbl: 0x11085d264

     76543210

flag=--------

ntab=1

nrow=1

frre=-1

fsbo=0x14

fseo=0x4c

avsp=0xfdc

tosp=0xfdc

0xe:pti[0]      nrow=1  offs=0

0x12:pri[0]     offs=0x4c

block_row_dump:

tab 0, row 0, @0x4c

tl: 4008 fb: --H-FL-- lb: 0x2  cc: 2

col  0: [ 2]  c1 02

col  1: [3999]

 00 54 00 01 02 0c 80 80 00 02 00 00 00 01 00 04 28 c9 b5 9b 0f 8b 48 90 0f

 85 01 00 0f 80 01 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00

 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44

 。。。省略

 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44

LOB

Locator:

  Length:        84(3999)

  Version:        1

  Byte Length:    2

  LobID: 00.00.00.01.00.04.28.c9.b5.9b

  Flags[ 0x02 0x0c 0x80 0x80 ]:

    Type: CLOB

    Storage: SecureFile

    Characterset Format: IMPLICIT

    Partitioned Table: No

    Options: VaringWidthReadWrite

  SecureFile Header:

    Length:   3979

    Old Flag: 0x48 [ DataInRow SecureFile ]

    Flag 0:   0x90 [ INODE Valid ]

    Layers:

      Lengths Array: INODE:3973

      INODE:

 

  --再增加一个字符的长度,发现字段会挪出table,放到数据dba:0x4b80b56f中,由此判断securefile字节数超过3969bytes时就会从in-line storage =>out-of-line storage

 

update tsec_lob set secol2=lpad('D',1985,'D');

commit;

 

alter system flush buffer_cache;

 

alter system dump datafile 302 block 4284;   --shzw1_ora_24903952.trc

 

--shzw1_ora_24903952.trc内容

tab 0, row 0, @0x1f

tl: 45 fb: --H-FL-- lb: 0x1  cc: 2

col  0: [ 2]  c1 02

col  1: [38]

 00 54 00 01 02 0c 80 80 00 02 00 00 00 01 00 04 28 c9 b5 9c 00 12 40 90 00

 0c 21 00 0f 82 01 00 01 4b 80 b5 6f 01

LOB

Locator:

  Length:        84(38)

  Version:        1

  Byte Length:    2

  LobID: 00.00.00.01.00.04.28.c9.b5.9c

  Flags[ 0x02 0x0c 0x80 0x80 ]:

    Type: CLOB

    Storage: SecureFile

    Characterset Format: IMPLICIT

    Partitioned Table: No

    Options: VaringWidthReadWrite

  SecureFile Header:

    Length:   18

    Old Flag: 0x40 [ SecureFile ]

    Flag 0:   0x90 [ INODE Valid ]

    Layers:

      Lengths Array: INODE:12

      INODE:

        21 00 0f 82 01 00 01 4b 80 b5 6f 01

 

--inode中的4b 80 b5 6f转换成rdba地址,dump数据块内容

select dbms_utility.data_block_address_File(to_number(replace('4b 80 b5 6f',' '),'xxxxxxxx')) rfno ,dbms_utility.data_block_address_block(to_number(replace('4b 80 b5 6f',' '),'xxxxxxxx')) blkno from dual;

     RFNO      BLKNO

----------   ----------

       302      46447

 

select * from v$diag_info where name='Default Trace File';   --shzw1_ora_3146348.trc

 

alter system flush buffer_cache;

 

alter system dump datafile 302 block 46447;    

 

--shzw1_ora_3146348.trc 内容,证明lob字段的内容已经迁移到lob segment

seg/obj: 0x51a682  csc: 0xb89.32fd27ef  itc: 1  flg: E  typ: 5 - LOCAL LOBS

     fsl: 0  fnx: 0xffffffff ver: 0x01

 

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0406.01d.0019e032  0x00000000.0000.00  -B--    0  fsc 0x0000.00000000

========

bdba    [0x4b80b56f]

kdlich  [11085d24c 56]

  flg0  0x20 [ver=0 typ=data lock=n]

  flg1  0x00

  scn   0x0b89.32fd27ef

  lid   00000001000428c9b59c

  rid   0x00000000.0000

kdlidh  [11085d264 24]

  flg2  0x00 [ver=0 lid=short-rowid hash=n cmap=n pfill=n]

  flg3  0x00

  pskip 0

  sskip 0

  hash  0000000000000000000000000000000000000000

  hwm   3970

  spr   0

  data  [11085d280 52 8060]

00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44

00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44

。。。省略 

00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 4f 00 47 00 56 00 41

 

结论2securefiles LOBs inline存储的字节数为<=3969 bytes>3969 bytes时会整体迁移到lobsegment里,注意对于CLOB而言这个字节数仅仅表示存储上所占用的空间,具体对应多少个字符,还要根据数据库所采用的字符集来确定,对于ZHS16GBK等变长字符集的数据库来说,1个字符在存储上要占用两个字节,所以字符数<=1984时为inline存储,>1984时为out-of-line存储,对于其它定长字符集的数据库则不存在这个转换关系,对于BLOB类型的字段由于存储的都是二进制数据所以也无需进行换算

 

?  如何通过chunk address找到SecureFiles LOBs

SecureFiles LOBs是以chunk为单位存储的,要找到chunk必须先有chunk addresschunk address的存放有直接和间接两种方式,直接方式是指chunk address就保存在表里,通过这个chunk address能直接找到包含数据的chunk,但当一个SecureFiles LOBs较大且占用的空间连续性不是很好的时候就会以间接方式存放,间接方式是指表里的block指向包含chunk address列表的另外一个block,由这另外一个block去指向包含数据的chunk。看下面的图就很清楚了。

 

 

////////////// 4、直接方式寻址  //////////////

--创建测试表,disable storage in row

create tablespace lobtest_out datafile '/oradata06/lobtest_out.dbf' size 128m extent management local uniform size 128k segment space management auto;

 

drop table tout_lob;

create table tout_lob (id number,outcol2 clob) lob(outcol2) store as securefile lob_out (tablespace lobtest_out disable storage in row retention);

 

--为了能占用较多的chunk,采用loadclobfromfile将文本文件内容导入的方式,impmd.log.load.lob文件大小为1571061字节,使用存储过程进行导入

declare

v_bfile bfile:=bfilename('HISDMP','impmd.log.load.lob');

v_clob clob;

ncycle integer:=1;

i integer:=1;

v_dest_offset integer:=1;

v_src_offset integer:=1;

v_lang_context integer:=0;

v_warning integer;

begin

dbms_lob.fileopen(v_bfile);

v_clob:=empty_clob();

dbms_lob.createtemporary(v_clob,FALSE,dbms_lob.session);

dbms_lob.loadclobfromfile(v_clob,v_bfile,amount=>dbms_lob.lobmaxsize,dest_offset=>v_dest_offset,src_offset=>v_src_offset,bfile_csid=>0,lang_context=>v_lang_context,warning=>v_warning);

while ( i <= ncycle)

loop

insert into tout_lob values(i,v_clob);

i:=i+1;

end loop;

dbms_lob.fileclose(v_bfile);

end;

/

 

--导入完成后对表进行dump

select * from v$diag_info where name='Default Trace File';   --shzw1_ora_53936650.trc

 

alter system flush buffer_cache;

 

select dbms_rowid.rowid_to_absolute_fno(rowid,'SCOTT','TOUT_LOB') absfno,dbms_rowid.rowid_relative_fno(rowid) rfno,dbms_rowid.rowid_block_number(rowid) blkno,count(1) from tout_lob group by dbms_rowid.rowid_to_absolute_fno(rowid,'SCOTT','TOUT_LOB'),dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid);

   ABSFNO       RFNO      BLKNO   COUNT(1)

---------- ---------- ---------- ----------

       115        115     441493          1

 

select id,dbms_lob.getlength(outcol2) from tout_lob;      

       ID DBMS_LOB.GETLENGTH(OUTCOL2)

---------- ---------------------------

         1                     1571061

 

alter system dump datafile 115 block 441493;

 

--shzw1_ora_53936650.trc dump内容,其中03 80 02 a1记录的是包含chunk地址列表的dba地址

tab 0, row 0, @0x1f6d

tl: 43 fb: --H-FL-- lb: 0x1  cc: 2

col  0: [ 2]  c1 02

col  1: [36]

 00 54 00 01 02 0c 80 80 00 02 00 00 00 01 00 04 28 c9 b8 ed 00 10 40 90 00

 0a 42 00 2f f1 ea 01 03 80 02 a1

LOB

Locator:

  Length:        84(36)

  Version:        1

  Byte Length:    2

  LobID: 00.00.00.01.00.04.28.c9.b8.ed

  Flags[ 0x02 0x0c 0x80 0x80 ]:

    Type: CLOB

    Storage: SecureFile

    Characterset Format: IMPLICIT

    Partitioned Table: No

    Options: VaringWidthReadWrite

  SecureFile Header:

    Length:   16

    Old Flag: 0x40 [ SecureFile ]

    Flag 0:   0x90 [ INODE Valid ]

    Layers:

      Lengths Array: INODE:10

      INODE:

        42 00 2f f1 ea 01 03 80 02 a1

 

--03 80 02 a1转换为rdba地址

select dbms_utility.data_block_address_File(to_number(replace('03 80 02 a1',' '),'xxxxxxxx')) rfno ,dbms_utility.data_block_address_block(to_number(replace('03 80 02 a1',' '),'xxxxxxxx')) blkno from dual;

      RFNO      BLKNO

---------- ----------

        14        673

 

select name,file#,rfile# from v$datafile where rfile#=14;

NAME                                          FILE#     RFILE#

---------------------------------------- ---------- ----------

/oradata02/undo/undo202.dbf                      14         14

/oradata06/lobtest_out.dbf                     1037         14

 

---证明1037/673位于lob segment
select segment_name,segment_type,tablespace_name,extent_id,file_id,relative_fno,block_id,blocks,bytes from dba_extents where segment_name='LOB_OUT' and block_id<=673 and block_id+blocks>=673;

SEGMENT_NAME         SEGMENT_TY TABLESPACE_NAME  EXTENT_ID    FILE_ID RELATIVE_FNO   BLOCK_ID     BLOCKS      BYTES

-------------------- ---------- --------------- ---------- ---------- ------------ ---------- ---------- ----------

LOB_OUT              LOBSEGMENT LOBTEST_OUT             33       1037           14        672         16     131072

 

--1037/673进行dump的结果显示一共使用了390block,从dba:0x038000a7开始

select * from v$diag_info where name='Default Trace File';   --shzw1_ora_8652048.trc

 

alter system dump datafile 1037 block 673; --shzw1_ora_8652048.trc

 

--shzw1_ora_8652048.trc dump内容节选

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0405.018.00165fd5  0x0105a321.128c.10  ----    1  fsc 0x0000.00000000

========

bdba    [0x038002a1]

kdlich  [11085d24c 56]

  flg0  0x18 [ver=0 typ=lhb lock=y]

  flg1  0x00

  scn   0x0b89.32fddadb

  lid   00000001000428c9b8ed

  rid   0x00000000.0000

kdlihh  [11085d264 24]

  flg2  0x00 [ver=0 lid=short-rowid hash=n it=n bt=n xfm=n ovr=n aux=n]

  flg3  0x80 [vll=y]

  flg4  0x00

  flg5  0x00

  hash  0000000000000000000000000000000000000000

  llen  0.3142122

  ver   0.1

  #ext  40

  asiz  40

  hwm   40

  ovr   0x00000000.0

  dba0  0x00000000

  dba1  0x00000000

  dba2  0x00000000

  dba3  0x00000000

  auxp  0x00000000

  ldba  0x03800251

  nblk  390

    [0] 0x00 0x00 9 0x038000a7

    [1] 0x00 0x00 3 0x038000bd

    [2] 0x00 0x00 2 0x038000a5

    [3] 0x00 0x00 15 0x038000c1

    [4] 0x00 0x00 9 0x038000d7

    [5] 0x00 0x00 6 0x038000d1

    [6] 0x00 0x00 9 0x038000e7

    [7] 0x00 0x00 6 0x038000e1

    [8] 0x00 0x00 9 0x038000f7

    [9] 0x00 0x00 6 0x038000f1

    [10] 0x00 0x00 15 0x03800101

    [11] 0x00 0x00 9 0x03800117

    [12] 0x00 0x00 6 0x03800111

    [13] 0x00 0x00 9 0x03800127

    [14] 0x00 0x00 6 0x03800121

    [15] 0x00 0x00 9 0x03800137

    [16] 0x00 0x00 6 0x03800131

    [17] 0x00 0x00 9 0x03800147

    [18] 0x00 0x00 6 0x03800141

    [19] 0x00 0x00 15 0x03800151

    [20] 0x00 0x00 15 0x03800161

    [21] 0x00 0x00 9 0x03800177

    [22] 0x00 0x00 6 0x03800171

    [23] 0x00 0x00 9 0x03800187

    [24] 0x00 0x00 6 0x03800181

    [25] 0x00 0x00 15 0x03800191

    [26] 0x00 0x00 9 0x038001a7

    [27] 0x00 0x00 6 0x038001a1

    [28] 0x00 0x00 15 0x038001b1

    [29] 0x00 0x00 9 0x038001c7

    [30] 0x00 0x00 6 0x038001c1

    [31] 0x00 0x00 15 0x038001d1

    [32] 0x00 0x00 15 0x038001e1

    [33] 0x00 0x00 15 0x03800201

    [34] 0x00 0x00 15 0x038001f1

    [35] 0x00 0x00 15 0x03800211

    [36] 0x00 0x00 15 0x03800221

    [37] 0x00 0x00 15 0x03800231

    [38] 0x00 0x00 15 0x03800241

    [39] 0x00 0x00 1 0x03800251

 

 

--dump一下0x038000a7的内容,包含的就是impmd.log.load.lob文件开头的4030个字符

alter system dump datafile 1037 block 167; --shzw1_ora_54067770.trc

 

--dump内容节选:

  hwm   8060

  spr   0

  data  [11085d280 52 8060]

00 0a 00 43 00 6f 00 6e 00 6e 00 65 00 63 00 74 00 65 00 64 00 20 00 74 00 6f

00 3a 00 20 00 4f 00 72 00 61 00 63 00 6c 00 65 00 20 00 44 00 61 00 74 00 61

 

////////////// 5、满足什么条件会采用间接方式寻址  //////////////

--新建测试表,准备好大小为82957 bytes的文本文件 impmd.log.load.12c1

drop table tout_lob;

 

create table tout_lob (id number,outcol2 clob) lob(outcol2) store as securefile lob_out (tablespace lobtest_out disable storage in row retention);

 

--执行存储过程用impmd.log.load.12c1文件填充lob字段

declare

v_bfile bfile:=bfilename('HISDMP','impmd.log.load.12c1');

v_clob clob;

ncycle integer:=1;

i integer:=1;

v_dest_offset integer:=1;

v_src_offset integer:=1;

v_lang_context integer:=0;

v_warning integer;

begin

dbms_lob.fileopen(v_bfile);

v_clob:=empty_clob();

dbms_lob.createtemporary(v_clob,FALSE,dbms_lob.session);

dbms_lob.loadclobfromfile(v_clob,v_bfile,amount=>dbms_lob.lobmaxsize,dest_offset=>v_dest_offset,src_offset=>v_src_offset,bfile_csid=>0,lang_context=>v_lang_context,warning=>v_warning);

while ( i <= ncycle)

loop

insert into tout_lob values(i,v_clob);

i:=i+1;

end loop;

dbms_lob.fileclose(v_bfile);

end;

/

 

--填充后dump tout_lob表内容

select * from v$diag_info where name='Default Trace File';   --shzw1_ora_11994174.trc

 

alter system flush buffer_cache;

 

select dbms_rowid.rowid_to_absolute_fno(rowid,'SCOTT','TOUT_LOB') absfno,dbms_rowid.rowid_relative_fno(rowid) rfno,dbms_rowid.rowid_block_number(rowid) blkno,count(1) from tout_lob group by dbms_rowid.rowid_to_absolute_fno(rowid,'SCOTT','TOUT_LOB'),dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid);

   ABSFNO       RFNO      BLKNO   COUNT(1)

---------- ---------- ---------- ----------

       441        441      17562          1

 

alter system dump datafile 441 block 17562;

 

--shzw1_ora_11994174.trc内容,一共使用了21blocks,分别是0x038000a9起始的7blocks0x038000bd起始的3blocks0x038000a5起始的4blocks0x038000ca起始的6block0x038000c1起始的1block,采用的是直接寻址的方式

tab 0, row 0, @0x1f52

tl: 70 fb: --H-FL-- lb: 0x1  cc: 2

col  0: [ 2]  c1 02

col  1: [63]

 00 54 00 01 02 0c 80 80 00 02 00 00 00 01 00 04 28 ca e5 51 00 2b 40 90 00

 25 22 00 02 88 1a 01 04 01 03 80 00 a9 07 01 03 80 00 bd 03 01 03 80 00 a5

 04 01 03 80 00 ca 06 01 03 80 00 c1 01

 

--为了模拟出间接寻址的情况,使用如下过程往lob字段里每次增加4000字符,每个字符占用两个字节,所以每追加一次最多只会占用一个block,便于我们观察效果

select id,dbms_lob.getlength(outcol2) from tout_lob;      

 

        ID DBMS_LOB.GETLENGTH(OUTCOL2)

---------- ---------------------------

1                                                                                 82957

 

--下面的过程执行6次,每次执行后都dump一下观察是否转为了间接寻址

declare

v_buffer varchar2(6000):=lpad('AB',4000,'AB');

v_clob clob;

i integer:=0;

ncycle integer:=1;

begin

select outcol2 into v_clob from tout_lob for update;

while (i < ncycle) loop

dbms_lob.writeappend(v_clob,4000,v_buffer);

i:=i+1;

end loop;

commit;

end;

/

 

--最新一次执行后的dump结果结果如下,blocks数量已经增加到了27个,表里存放的

chunk address数量已经达到了6个,使用的还是直接寻址:

select * from v$diag_info where name='Default Trace File'; --shzw1_ora_11994176.trc

 

alter system dump datafile 441 block 17562;

tab 0, row 0, @0x1f06

tl: 76 fb: --H-FL-- lb: 0x1  cc: 2

col  0: [ 2]  c1 02

col  1: [69]

 00 54 00 01 02 0c 80 80 00 02 00 00 00 01 00 04 28 ca e5 51 00 31 40 90 00

 2b 22 00 03 43 9a 07 05 01 03 80 00 a9 07 01 03 80 00 bd 03 01 03 80 00 a5

 04 01 03 80 00 ca 06 01 03 80 00 c1 02 01 03 80 00 c4 05

 

**最后再执行一次存储过程后,dump 441/17562的结果里终于呈现出间接寻址的迹象了

--再执行一次过程

declare

v_buffer varchar2(6000):=lpad('AB',4000,'AB');

v_clob clob;

i integer:=0;

ncycle integer:=1;

begin

select outcol2 into v_clob from tout_lob for update;

while (i < ncycle) loop

dbms_lob.writeappend(v_clob,4000,v_buffer);

i:=i+1;

end loop;

commit;

end;

/

 

--block 441/17562 dump结果,明显可以看出地址变短了,原来存放的6chunk address变成了0x038000e1这一个地址

tab 0, row 0, @0x1edb

tl: 43 fb: --H-FL-- lb: 0x2  cc: 2

col  0: [ 2]  c1 02

col  1: [36]

 00 54 00 01 02 0c 80 80 00 02 00 00 00 01 00 04 28 ca e5 51 00 10 40 90 00

 0a 42 00 03 df da 08 03 80 00 e1

LOB

Locator:

  Length:        84(36)

  Version:        1

  Byte Length:    2

  LobID: 00.00.00.01.00.04.28.ca.e5.51

  Flags[ 0x02 0x0c 0x80 0x80 ]:

    Type: CLOB

    Storage: SecureFile

    Characterset Format: IMPLICIT

    Partitioned Table: No

    Options: VaringWidthReadWrite

  SecureFile Header:

    Length:   16

    Old Flag: 0x40 [ SecureFile ]

    Flag 0:   0x90 [ INODE Valid ]

    Layers:

      Lengths Array: INODE:10

      INODE:

        42 00 03 df da 08 03 80 00 e1

 

-- dba:0x038000e1dump结果显示该块中包含了前面直接寻址时的chunk地址,

 Object id on Block? Y

 seg/obj: 0x51a69a  csc: 0xb89.32fe6c01  itc: 1  flg: E  typ: 5 - LOCAL LOBS

     fsl: 0  fnx: 0xffffffff ver: 0x01

 

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x03fb.012.001414b3  0x030693ac.fae5.07  ----    1  fsc 0x0000.00000000

========

bdba    [0x038000e1]

kdlich  [11085d24c 56]

  flg0  0x18 [ver=0 typ=lhb lock=y]

  flg1  0x00

  scn   0x0b89.32fe6c01

  lid   00000001000428cae551

  rid   0x00000000.0000

kdlihh  [11085d264 24]

  flg2  0x00 [ver=0 lid=short-rowid hash=n it=n bt=n xfm=n ovr=n aux=n]

  flg3  0x80 [vll=y]

  flg4  0x00

  flg5  0x00

  hash  0000000000000000000000000000000000000000

  llen  0.253914

  ver   0.8

  #ext  9

  asiz  9

  hwm   9

  ovr   0x00000000.0

  dba0  0x00000000

  dba1  0x00000000

  dba2  0x00000000

  dba3  0x00000000

  auxp  0x00000000

  ldba  0x038000c9

  nblk  32

    [0] 0x00 0x00 7 0x038000a9

    [1] 0x00 0x00 3 0x038000bd

    [2] 0x00 0x00 4 0x038000a5

    [3] 0x00 0x00 6 0x038000ca

    [4] 0x00 0x00 2 0x038000c1

    [5] 0x00 0x00 5 0x038000c4

    [6] 0x00 0x00 1 0x038000c3

    [7] 0x00 0x00 3 0x038000dd

[8] 0x00 0x00 1 0x038000c9

 

dba:0x038000e1这个起到地址索引效果的块位于lob segment里,并不在lob index里,oracle何时会使用lob index有待进一步考证

结论3:当表里直接寻址的chunk Address地址数大于6个时,会转换为间接寻址的模式

 

?  SecureFiles LOBsread consistency特性

lob字段和表中其它非lob字段分开存放的时候,lob字段的读一致性实现依赖于lob segment本身,所有的修改前镜像会保存在lob segment里,和undo表空间没有任何关系,这样设计也是为了避免lob字段过大时产生的before-image过大从而影响undo表空间中的其它非lob对象的读一致性。Lob segment自己的读一致性管理机制由LOB_retention_clause所定义的值决定,这个值在建表的时候指定,之后也可以使用alter table进行修改,retention有以下四种取值,

 MAX:在lob segment达到最大值之后,才开始覆盖before-image所占用的block,前提是在建lob segment时必须指定maxsize

 MIN:数据库工作在闪回模式下,限定特定的lob segment能够闪回到多久时间以前的状态

 AUTObefore-image的保留时间参照数据库的undo_retention参数

 NONE:不保存before-image,用于不需要读一致性的环境

 

 深入lob读一致性之前,我们先要了解一下一个lob segment中的数据块有哪些类型,MOS 1453350.1中提供了check_space_securefile存储过程,里面封装的是DBMS_SPACE.SPACE_USAGE,用来检测lob segment中数据块的使用情况。以ownerlobsegment名作为输入,例如:exec check_space_securefile('SCOTT','TLOB_AUTO'); 输出为:

 Segment Blocks/Bytes   = 64 / 524288

Unused Blocks/Bytes    = 34 / 278528

Used Blocks/Bytes      = 8 / 65536

Expired Blocks/Bytes   = 13 / 106496

Unexpired Blocks/Bytes = 9 / 73728

==========================================================================

NON Data Blocks/Bytes  = 56 / 458752

 

它将数据块分为unusedusedexpiredunexpired四种,

Unused block:这个命名有点误导,指的是起到存放metadatablock,诸如:Segment HeaderCFS Hash BucketCommitted Free SpaceUncommit Free SpacePersistent Undo,这些类型的的block都是lob segment所特有的,既然能有自己的读一致性机制这些控制信息必不可少。所以Unused block并非指空闲的数据块。

 

Used block:已经包含有用户数据的block

 

Expired block:空闲的数据库块(包括从未被使用的和曾经被使用过但按照现有的retention策略可以被覆盖使用的)

 

Unexpired blocks:存放修改前镜像,为满足读一致性需要暂时保留不能被覆盖的数据块

以上输出中的NON Data Blocks是将segment_blocks-used_blocks而得到

 

 我们分别体验一下MAXAUTONONE三个参数的作用

////////////// 6retention MAX  //////////////

###先测试一下在没有达到maxsize的情况下,修改前镜像会一直保留着

--建表,指定lob segment最大为1024k

drop table tlob44;

create table tlob44 (id number,t44col2 clob) lob(t44col2) store as securefile tlob_44 (tablespace ts128k3 storage(maxsize 1024K) disable storage in row retention max);

 

--插入若干条记录

declare

v_str varchar2(6000);

i number;

j number;

begin

i:=101;

j:=1;

while ( i < 123 ) loop

v_str:=lpad(chr(i),6000,chr(i));

insert into tlob44 values(j,v_str);

i:=i+1;

j:=j+1;

end loop;

commit;

end;

/

 

--观察lob segment的空间使用情况

exec check_space_securefile('SCOTT','TLOB_44');

Segment Blocks/Bytes   = 96 / 786432

Unused Blocks/Bytes    = 36 / 294912

Used Blocks/Bytes      = 45 / 368640

Expired Blocks/Bytes   = 15 / 122880

Unexpired Blocks/Bytes = 0 / 0

===========================================================================

NON Data Blocks/Bytes  = 51 / 417792

 

--删除其中id<109条记录,有18block变成了unexpired

delete tlob44 where id<10;

commit;

 

exec check_space_securefile('SCOTT','TLOB_44');

Segment Blocks/Bytes   = 96 / 786432

Unused Blocks/Bytes    = 36 / 294912

Used Blocks/Bytes      = 27 / 221184

Expired Blocks/Bytes   = 15 / 122880

Unexpired Blocks/Bytes = 18 / 147456

===========================================================================

NON Data Blocks/Bytes  = 69 / 565248

 

--再插入9条记录,由于没有达到maxsize所以unexpired blocks不会被重用

declare

v_str varchar2(6000);

i number;

j number;

begin

i:=98;

j:=1;

while ( i < 107 ) loop

v_str:=lpad(chr(i),6000,chr(i));

insert into tlob44 values(j,v_str);

i:=i+1;

j:=j+1;

end loop;

commit;

end;

/

 

---unexpired blocks依然保持18个,新进来的数据使用新分配的空间

exec check_space_securefile('SCOTT','TLOB_44');

Segment Blocks/Bytes   = 112 / 917504

Unused Blocks/Bytes    = 37 / 303104

Used Blocks/Bytes      = 45 / 368640

Expired Blocks/Bytes   = 12 / 98304

Unexpired Blocks/Bytes = 18 / 147456

===========================================================================

NON Data Blocks/Bytes  = 67 / 548864

 

###接着测试在达到maxsize的情况下,如果有新进数据会覆盖修改前镜像

drop table tlob33;

create table tlob33 (id number,t33col2 clob) lob(t33col2) store as securefile tlob_33 (tablespace ts128k3 storage(maxsize 1024K) disable storage in row retention max);

 

--插入44行记录,都是大小写英文字母,每行6000个字符,占据2blocks

declare

v_str varchar2(6000);

j number:=1;

i number;

begin

i:=101;

while ( i < 123 ) loop

v_str:=lpad(chr(i),6000,chr(i));

insert into tlob33 values(j,v_str);

i:=i+1;

j:=j+1;

end loop;

i:=65;

while ( i < 87 ) loop

v_str:=lpad(chr(i),6000,chr(i));

insert into tlob33 values(j,v_str);

i:=i+1;

j:=j+1;

end loop;

commit;

end;

/

 

--统计space usage,还剩1个空闲的block

exec check_space_securefile('SCOTT','TLOB_33'); 

Segment Blocks/Bytes   = 128 / 1048576

Unused Blocks/Bytes    = 38 / 311296

Used Blocks/Bytes      = 89 / 729088

Expired Blocks/Bytes   = 1 / 8192

Unexpired Blocks/Bytes = 0 / 0

===========================================================================

NON Data Blocks/Bytes  = 39 / 319488

 

--只能再插入一个block,插入第二个block时就报错了,因为已经达到1024k上限了,说明maxsize设置生效

declare

v_str45 varchar2(3000):=lpad('S',3000,'S');

begin

insert into tlob33 values(45,v_str45); 

commit;

end;

/

        

PL/SQL procedure successfully completed.

        

declare

v_str46 varchar2(3000):=lpad('T',3000,'T');

begin

insert into tlob33 values(46,v_str46); 

commit;

end;

/

 

ERROR at line 1:

ORA-60010: adding (144) blocks to LOB segment SCOTT.TLOB_33 with MAXSIZE (128)

ORA-06512: at line 4

 

--expired block=0 说明没有空闲空间来容纳新的记录

SQL> exec check_space_securefile('SCOTT','TLOB_33'); 

Segment Blocks/Bytes   = 128 / 1048576

Unused Blocks/Bytes    = 38 / 311296

Used Blocks/Bytes      = 90 / 737280

Expired Blocks/Bytes   = 0 / 0

Unexpired Blocks/Bytes = 0 / 0

===========================================================================

NON Data Blocks/Bytes  = 38 / 311296

 

select bytes,segment_name from dba_segments where segment_name='TLOB_33';

     BYTES SEGMENT_NAME

---------- --------------------

   1048576 TLOB_33

 

   -- session 2:为测试一致性读另开的

   set transaction read only;

 

--删除刚刚插入的id=45的记录,释放出一个block

delete tlob33 where id=45;

commit;

 

--删除的记录放在Unexpired Blocks

exec check_space_securefile('SCOTT','TLOB_33');

Segment Blocks/Bytes   = 128 / 1048576

Unused Blocks/Bytes    = 38 / 311296

Used Blocks/Bytes      = 89 / 729088

Expired Blocks/Bytes   = 0 / 0

Unexpired Blocks/Bytes = 1 / 8192

 

--session 2 能一致性读到id=45的记录,

select id,dbms_lob.getlength(t33col2) from tlob33 where id=45;

    ID DBMS_LOB.GETLENGTH(T33COL2)

---------- ---------------------------

45                        3000

 

--tlob33表中再次插入一条记录,unexpiredblock会被重用

declare

v_str47 varchar2(3000):=lpad('U',3000,'U');

begin

insert into tlob33 values(47,v_str47); 

commit;

end;

/   

 

--unexpired blocks变为0Used Blocks增加1

exec check_space_securefile('SCOTT','TLOB_33');

Segment Blocks/Bytes   = 128 / 1048576

Unused Blocks/Bytes    = 38 / 311296

Used Blocks/Bytes      = 90 / 737280

Expired Blocks/Bytes   = 0 / 0

Unexpired Blocks/Bytes = 0 / 0

===========================================================================

NON Data Blocks/Bytes  = 38 / 311296

 

--session 2,再次查询tlob33收到ORA-1555错误:

select * from tlob33 where id=45;

 

ERROR:

ORA-01555: snapshot too old: rollback segment number  with name "" too small

ORA-22924: snapshot too old

 

select count(*) from tlob33 where id<45;   

  COUNT(*)

----------

        44

 

--继续删除id<3的记录

delete tlob33 where id<3; 

 

2 rows deleted.

 

commit;

 

--空出来4blocks

exec check_space_securefile('SCOTT','TLOB_33');

Segment Blocks/Bytes   = 128 / 1048576

Unused Blocks/Bytes    = 38 / 311296

Used Blocks/Bytes      = 86 / 704512

Expired Blocks/Bytes   = 0 / 0

Unexpired Blocks/Bytes = 4 / 32768

===========================================================================

NON Data Blocks/Bytes  = 42 / 344064

 

--session 2中能一致性读到这两条记录

select count(*) from tlob33 where id<3;

  COUNT(*)

----------

         2

 

--tlob33表中再次插入一条记录,4expired blocks里有两个会被重用,根据先进先出的原则被重用的应该是先插入的id=1的记录所在的block被覆盖

declare

v_str48 varchar2(6000):=lpad('V',6000,'V');

begin

insert into tlob33 values(48,v_str48); 

commit;

end;

/   

 

--session 2,测试结果验证了上面的结论:id=1的记录不能读取,id=2的记录能读到

SQL>select count(*) from tlob33 where id<3;

ERROR:

ORA-01555: snapshot too old: rollback segment number  with name "" too small

ORA-22924: snapshot too old

 

SQL> select * from tlob33 where id=1;

ERROR:

ORA-01555: snapshot too old: rollback segment number  with name "" too small

ORA-22924: snapshot too old

 

SQL> select * from tlob33 where id=2;

 

        ID

----------

T33COL2

--------------------------------------------------------------------------------

         2

ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff

 

结论4retention=max时当lob segment大小增加到maxsize值时,会挪用unexpired blocks作为可用空间存放新入数据,挪用unexpired blocks时遵循先进先出的原则,即优先覆盖较早生成的block。在未触及maxsize的情况下修改前镜像会一直保留着。

 

////////////// 7retention AUTO  //////////////

--设置undo_retention=90retention auto时会参照undo_retention设置时间保留修改前镜像

alter system set undo_retention=90;

SQL> show parameter undo_retention

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_retention                       integer     90

 

--创建测试表

create table tlobauto (id number,autocol2 clob) lob(autocol2) store as securefile tlob_auto

(tablespace ts128k1 storage(maxsize 1024K) disable storage in row retention auto);

 

--检查空间使用情况

Set serveroutput on

exec check_space_securefile('SCOTT','TLOB_AUTO');

 

Segment Blocks/Bytes   = 16 / 131072

Unused Blocks/Bytes    = 11 / 90112

Used Blocks/Bytes      = 0 / 0

Expired Blocks/Bytes   = 5 / 40960

Unexpired Blocks/Bytes = 0 / 0

===========================================================================

NON Data Blocks/Bytes  = 16 / 131072

 

--填充16行,每行50个字符

declare

v_str_length integer:=50;   --指定随机字串的单位长度

v_nrows integer:=16;              --指定插入行数

v_conn_num integer:=1;       --指定随机字串的单元数

i integer:=0;

j integer:=0;

v_str varchar2(32767);

begin

while (i < v_nrows) loop

v_str:=dbms_random.string('U',v_str_length);

   while (j < v_conn_num-1) loop

   v_str:=v_str||dbms_random.string('U',v_str_length);

   j:=j+1;

   end loop;

dbms_output.put_line(length(v_str));

insert into tlobauto values(i,v_str);

commit;

i:=i+1;

j:=0;

end loop;

commit;

end;

/    

 

--17used block13expired blocks

SQL> exec check_space_securefile('SCOTT','TLOB_AUTO');

Segment Blocks/Bytes   = 64 / 524288

Unused Blocks/Bytes    = 34 / 278528

Used Blocks/Bytes      = 17 / 139264

Expired Blocks/Bytes   = 13 / 106496

Unexpired Blocks/Bytes = 0 / 0

===========================================================================

NON Data Blocks/Bytes  = 47 / 385024

 

--删除id<55行记录

Delete tlobauto where id<5;

Commit;

 

--5unexpired blocks,存放了被删除的5行记录

SQL> exec check_space_securefile('SCOTT','TLOB_AUTO');

Segment Blocks/Bytes   = 64 / 524288

Unused Blocks/Bytes    = 34 / 278528

Used Blocks/Bytes      = 12 / 98304

Expired Blocks/Bytes   = 13 / 106496

Unexpired Blocks/Bytes = 5 / 40960

===========================================================================

NON Data Blocks/Bytes  = 52 / 425984

 

--等待90秒后再检验,unexpired blocks05blocks都加到了expired blocks上面

SQL> exec check_space_securefile('SCOTT','TLOB_AUTO');

Segment Blocks/Bytes   = 64 / 524288

Unused Blocks/Bytes    = 34 / 278528

Used Blocks/Bytes      = 12 / 98304

Expired Blocks/Bytes   = 18 / 147456

Unexpired Blocks/Bytes = 0 / 0

===========================================================================

NON Data Blocks/Bytes  = 52 / 425984

 

--上面的结果看似验证了retention autobefore-imagelob segment里的保留时间跟随undo_retention的设置,但这也不是绝对的,比如下面的场景

 

--重新建立表tlobauto

Drop table tlobauto;

create table tlobauto (id number,autocol2 clob) lob(autocol2) store as securefile tlob_auto

(tablespace ts128k1 storage(maxsize 1024K) disable storage in row retention auto);

 

--初始空间使用情况

exec check_space_securefile('SCOTT','TLOB_AUTO');

 

Segment Blocks/Bytes   = 16 / 131072

Unused Blocks/Bytes    = 11 / 90112

Used Blocks/Bytes      = 0 / 0

Expired Blocks/Bytes   = 5 / 40960

Unexpired Blocks/Bytes = 0 / 0

===========================================================================

NON Data Blocks/Bytes  = 16 / 131072

 

--修改undo_retention7200

Alter system set undo_retention=7200 scope=memory;

 

SQL> show parameter undo_retention

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_retention                       integer     7200

 

--第一轮填充tlobauto表,16行,每行50个字符

set serveroutput on

declare

v_str_length integer:=50;   --指定随机字串的单位长度

v_nrows integer:=16;              --指定插入行数

v_conn_num integer:=1;       --指定随机字串的单元数

i integer:=0;

j integer:=0;

v_str varchar2(32767);

begin

while (i < v_nrows) loop

v_str:=dbms_random.string('U',v_str_length);

   while (j < v_conn_num-1) loop

   v_str:=v_str||dbms_random.string('U',v_str_length);

   j:=j+1;

   end loop;

dbms_output.put_line(length(v_str));

insert into tlobauto values(i,v_str);

commit;

i:=i+1;

j:=0;

end loop;

commit;

end;

/    

 

--check space usage

exec check_space_securefile('SCOTT','TLOB_AUTO');

Segment Blocks/Bytes   = 64 / 524288

Unused Blocks/Bytes    = 34 / 278528

Used Blocks/Bytes      = 17 / 139264

Expired Blocks/Bytes   = 13 / 106496

Unexpired Blocks/Bytes = 0 / 0

===========================================================================

NON Data Blocks/Bytes  = 47 / 385024

 

--删除 9行记录

delete tlobauto where id>6 and id<16;

commit;

 

--check space usage

exec check_space_securefile('SCOTT','TLOB_AUTO');

Segment Blocks/Bytes   = 64 / 524288

Unused Blocks/Bytes    = 34 / 278528

Used Blocks/Bytes      = 8 / 65536

Expired Blocks/Bytes   = 13 / 106496

Unexpired Blocks/Bytes = 9 / 73728

===========================================================================

NON Data Blocks/Bytes  = 56 / 458752

 

--第二轮继续填充tlobauto表,插入11行,每行50个字符

set serveroutput on

declare

v_str_length integer:=50;   --指定随机字串的单位长度

v_nrows integer:=18;              --指定插入行数

v_conn_num integer:=1;       --指定随机字串的单元数

i integer:=7;

j integer:=0;

v_str varchar2(32767);

begin

while (i < v_nrows) loop

v_str:=dbms_random.string('U',v_str_length);

   while (j < v_conn_num-1) loop

   v_str:=v_str||dbms_random.string('U',v_str_length);

   j:=j+1;

   end loop;

dbms_output.put_line(length(v_str));

insert into tlobauto values(i,v_str);

commit;

i:=i+1;

j:=0;

end loop;

commit;

end;

/

 

--check space usage发现used blocks8增加到了19expired blocks13下减到了11

exec check_space_securefile('SCOTT','TLOB_AUTO');

Segment Blocks/Bytes   = 64 / 524288

Unused Blocks/Bytes    = 34 / 278528

Used Blocks/Bytes      = 19 / 155648

Expired Blocks/Bytes   = 2 / 16384

Unexpired Blocks/Bytes = 9 / 73728

===========================================================================

NON Data Blocks/Bytes  = 45 / 368640

 

--第三轮又插入三行记录,每行50个字符

set serveroutput on

declare

v_str_length integer:=50;   --指定随机字串的单位长度

v_nrows integer:=21;              --指定插入行数

v_conn_num integer:=1;       --指定随机字串的单元数

i integer:=18;

j integer:=0;

v_str varchar2(32767);

begin

while (i < v_nrows) loop

v_str:=dbms_random.string('U',v_str_length);

   while (j < v_conn_num-1) loop

   v_str:=v_str||dbms_random.string('U',v_str_length);

   j:=j+1;

   end loop;

dbms_output.put_line(length(v_str));

insert into tlobauto values(i,v_str);

commit;

i:=i+1;

j:=0;

end loop;

commit;

end;

/

 

--最终检查空间使用情况发现虽然没有达到undo_retention所指定的7200秒,lob segment也没有达到其maxsize所定义的上限值1024k,但还是挪用了1unexpired blocksunexpired blocks9减少到了8,可见在retention auto的情况下并不是完全听从undo_retention的指挥,为何会出现这种情况还下不了结论,但至少证明了retention auto并不像文档中说的那么简单,还是有自己的一套算法

exec check_space_securefile('SCOTT','TLOB_AUTO');

Segment Blocks/Bytes   = 80 / 655360

Unused Blocks/Bytes    = 35 / 286720

Used Blocks/Bytes      = 22 / 180224

Expired Blocks/Bytes   = 15 / 122880

Unexpired Blocks/Bytes = 8 / 65536

===========================================================================

NON Data Blocks/Bytes  = 58 / 475136

 

结论5retention auto的情况下before-image的保留时间不完全遵循与undo_retention参数的设定值,可能会引起ORA-01555错误

 

////////////// 8retention none  //////////////

--retention none比较好理解就是永远不保存修改前的镜像,这种情况下无法实现read consistency

 

create table tlobnone (id number,nonecol2 clob) lob(nonecol2) store as securefile tlob_none (tablespace ts128k1 storage(maxsize 1024K) disable storage in row retention none);

 

insert into tlobnone values(1,'A');

 

commit;

 

--插入一行后空间使用情况

exec check_space_securefile('SCOTT','TLOB_NONE');

Segment Blocks/Bytes   = 48 / 393216

Unused Blocks/Bytes    = 33 / 270336

Used Blocks/Bytes      = 2 / 16384

Expired Blocks/Bytes   = 13 / 106496

Unexpired Blocks/Bytes = 0 / 0

===========================================================================

NON Data Blocks/Bytes  = 46 / 376832

 

--session 2:测试一致性读所开的session

Set transaction read only

Select * from tlobnone where id=1;

        ID

----------

NONECOL2

--------------------------------------------------------------------------------

         1

A

 

--删除这唯一的一行

Delete tlobnone where id=1;

Commit;

 

--unexpired blocks0,被删除的数据对应的block直接归到了空闲的block

SQL> exec check_space_securefile('SCOTT','TLOB_NONE');

Segment Blocks/Bytes   = 48 / 393216

Unused Blocks/Bytes    = 33 / 270336

Used Blocks/Bytes      = 1 / 8192

Expired Blocks/Bytes   = 14 / 114688

Unexpired Blocks/Bytes = 0 / 0

===========================================================================

NON Data Blocks/Bytes  = 47 / 385024

 

--session 2:还是能读到修改前的镜像,因为被删除的行所对应的block只是被标记为了空闲块的属性,block里的内容尚未被清理或者覆盖,所以session 2能够实现read consistency

Set transaction read only

Select * from tlobnone where id=1;

        ID

----------

NONECOL2

--------------------------------------------------------------------------------

         1

A

 

--执行下列语句15次,用尽14expired blocks

insert into tlobnone values(1,'B');

 

--再回到session 2执行时报了ORA-01555,因为数据块被真正覆盖了

select * from tlobnone;

*

ERROR:

ORA-01555: snapshot too old: rollback segment number  with name "" too small

ORA-22924: snapshot too old

 

结论6retention none的情况下无法实现read consistency,随时会导致ORA-01555

 

 

 

 

 

阅读(11081) | 评论(0) | 转发(1) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册