[20181022]lob字段的lobid来之那里.txt

[20181022]lob字段的lobid来之那里.txt --//这阵子探究lob字段,遇到一个问题就是lob中的lobid来之那里, --//按照文档http://www.juliandyke.com/Presentations/Presentations.php#LOBInternals的介绍, --//lobid来自SYS.IDGEN1$ ,我的测试遇到一些问题,专门研究看看. 1.环境: SCOTT@test01p> @ver1 PORT_STRING          VERSION    BANNER                                                                       CON_ID -------------------- ---------- ---------------------------------------------------------------------------- ------ IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production      0 --//按照链接http://www.juliandyke.com/Presentations/Presentations.php#LOBInternals,摘要如下 Basic Files LOB ID . LOB ID is a 10 byte number identifying individual instance of a LOB . Allocated when LOB value is created including EMPTY_CLOB() etc . Format is <X><Y> where . <X> is a currently unknown 4-byte number (always 1) . <Y> is a 6-byte number generated from sequence SYS.IDGEN1$ --//注:文档这里标识SYS.IDGEN$ ,而PPT下面显示的是IDGEN1$ ,我估计作者笔误.而且我在12c没有SYS.IDGEN$,找到SYS.IDGEN1$. SELECT sequence_owner       ,sequence_name       ,increment_by       ,cache_size       ,last_number   FROM DBA_SEQUENCES  WHERE SEQUENCE_NAME = 'IDGEN1$' AND sequence_owner = 'SYS'; SEQUENCE_OWNER       SEQUENCE_NAME        INCREMENT_BY CACHE_SIZE LAST_NUMBER -------------------- -------------------- ------------ ---------- ----------- SYS                  IDGEN1$                        50       1000     3950401 2.测试: --//session 1: CREATE TABLE T ( ID NUMBER,   IMAGE BLOB ) LOB (IMAGE) STORE AS  securefile  (ENABLE STORAGE IN ROW CHUNK 32768 RETENTION NOCACHE) ; --//注:我建立CHUNK 32768,数据块大小是8192.类型是securefile. SCOTT@test01p> @ ddl scott.t C100 ------------------------------------------------------------------------------------   CREATE TABLE "SCOTT"."T"    (    "ID" NUMBER,         "IMAGE" BLOB    ) 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 ("IMAGE") STORE AS SECUREFILE (   TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 32768   NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES   STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645   PCTINCREASE 0   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ; --//session 2.顺便取一个看看: SYS@test01p> select /*+ aaaaa */ sys.IDGEN1$.nextval from dual;    NEXTVAL ----------    3950401 --//session 1: CREATE OR REPLACE DIRECTORY TMP_EXPDP AS 'D:\tmp\expdp\'; GRANT EXECUTE, READ, WRITE ON DIRECTORY TMP_EXPDP TO SCOTT WITH GRANT OPTION; D:\tmp\expdp>ls -l 1.txt -rw-rw-rw-   1 user     group      418209 Oct 20 20:08 1.txt $ cat c1.txt declare b_file bfile; b_lob blob; begin insert into t values(1,empty_blob()) return image into b_lob;      b_file:=bfilename('TMP_EXPDP','1.txt');      dbms_lob.open(b_file,dbms_lob.file_readonly);      dbms_lob.loadfromfile(b_lob,b_file,dbms_lob.getlength(b_file));      dbms_lob.close(b_file); commit; end; / @ 10046on 12 insert into t values(2,'aa') ; commit ; @ 10046off SCOTT@test01p> @ c1.txt PL/SQL procedure successfully completed. SCOTT@test01p> select rowid,id from t; ROWID                      ID ------------------ ---------- AAAFlCAALAAAAC0AAA          2 AAAFlCAALAAAAC0AAB          1 SCOTT@test01p> @ rowid AAAFlCAALAAAAC0AAB     OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT ---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------      22850         11        180          1  0x2C000B4           11,180               alter system dump datafile 11 block 180 3.探究: --//很奇怪跟踪文件并没有IDGEN1$字符串。查询nextval也没有对应字符串。 SYS@test01p> alter system checkpoint; System altered. SCOTT@test01p> alter system dump datafile 11 block 180; System altered. Block header dump:  0x02c000b4  Object id on Block? Y  seg/obj: 0x5942  csc:  0x000000000030988c  itc: 2  flg: E  typ: 1 - DATA      brn: 0  bdba: 0x2c000b0 ver: 0x01 opc: 0      inc: 0  exflg: 0    Itl           Xid                  Uba         Flag  Lck        Scn/Fsc 0x01   0x0006.008.00000218  0x01801b36.0050.10  --U-    1  fsc 0x0000.0030988d 0x02   0x0006.01e.00000215  0x01801b36.0050.15  --U-    1  fsc 0x0000.003098b4 bdba: 0x02c000b4 data_block_dump,data header at 0x1a81064 =============== tsiz: 0x1f98 hsiz: 0x16 pbl: 0x01a81064      76543210 flag=-------- ntab=1 nrow=2 frre=-1 fsbo=0x16 fseo=0x1f19 avsp=0x1f28 tosp=0x1f28 0xe:pti[0]    nrow=2    offs=0 0x12:pri[0]    offs=0x1f72 0x14:pri[1]    offs=0x1f19 block_row_dump: tab 0, row 0, @0x1f72 *** 2018-10-22T20:05:40.177470+08:00 (TEST01P(3)) tl: 38 fb: --H-FL-- lb: 0x1  cc: 2 col  0: [ 2]  c1 03 col  1: [31]  00 70 00 01 01 0c 00 80 00 01 00 00 00 01 00 00 00 3c 47 73 00 0b 48 90 00                                ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~  05 00 00 01 01 aa tab 0, row 1, @0x1f19 tl: 52 fb: --H-FL-- lb: 0x2  cc: 2 col  0: [ 2]  c1 02 col  1: [45]  00 70 00 01 01 0c 00 80 00 01 00 00 00 01 00 00 00 3c 47 74 00 19 40 90 00                                ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~  13 22 00 06 61 a1 01 01 01 02 c0 00 c3 05 02 02 c0 01 37 2f end_of_block_dump End dump data blocks tsn: 4 file#: 11 minblk 180 maxblk 180 --//注意看下划线就是lobid，可以发现是线性增长的。 SCOTT@test01p>  @ 16to10 3c4773 16 to 10 DEC ------------      3950451 --//0x3c4773=3950452. --//如果对比前面的select /*+ aaaaa */ sys.IDGEN1$.nextval from dual;的结果 --//3950401 加上 50 就是  3950451，正好与lobid的最后部分对上。 --//另外可以发现一个会话再插入lob字段时，并没有再从sys.IDGEN1$取顺序号。而是在原来基础上+1. --//我估计一个会哈用完50个，再从sys.IDGEN1$取。这个操作不验证了。 4.继续探究： --//在打开一个会话，session 3: SCOTT@test01p> insert into t values (3,'bb'); 1 row created. SCOTT@test01p> commit ; Commit complete. SCOTT@test01p> select rowid,id from t where id=3; ROWID                      ID ------------------ ---------- AAAFlCAALAAAAC2AAA          3 SCOTT@test01p> @ rowid AAAFlCAALAAAAC2AAA     OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT ---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------      22850         11        182          0  0x2C000B6           11,182               alter system dump datafile 11 block 182 SCOTT@test01p> alter system checkpoint; System altered. SCOTT@test01p> alter system dump datafile 11 block 182; System altered. --//检查转储： Block header dump:  0x02c000b6  Object id on Block? Y  seg/obj: 0x5942  csc:  0x000000000030988c  itc: 2  flg: E  typ: 1 - DATA      brn: 0  bdba: 0x2c000b0 ver: 0x01 opc: 0      inc: 0  exflg: 0    Itl           Xid                  Uba         Flag  Lck        Scn/Fsc 0x01   0x0006.00a.00000217  0x01801b36.0050.16  --U-    1  fsc 0x0000.00309ad2 0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000 bdba: 0x02c000b6 data_block_dump,data header at 0x23231064 =============== tsiz: 0x1f98 hsiz: 0x14 pbl: 0x23231064      76543210 flag=-------- ntab=1 nrow=1 frre=-1 fsbo=0x14 fseo=0x1f72 avsp=0x1f5e tosp=0x1f5e 0xe:pti[0]    nrow=1    offs=0 0x12:pri[0]    offs=0x1f72 block_row_dump: tab 0, row 0, @0x1f72 tl: 38 fb: --H-FL-- lb: 0x1  cc: 2 col  0: [ 2]  c1 04 col  1: [31]  00 70 00 01 01 0c 00 80 00 01 00 00 00 01 00 00 00 3d 0a 91 00 0b 48 90 00                                ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~  05 00 00 01 01 bb end_of_block_dump End dump data blocks tsn: 4 file#: 11 minblk 182 maxblk 182 SCOTT@test01p> @ 16to10 3d0a91 16 to 10 DEC ------------      4000401 --//session 2: SELECT sequence_owner       ,sequence_name       ,increment_by       ,cache_size       ,last_number   FROM DBA_SEQUENCES  WHERE SEQUENCE_NAME = 'IDGEN1$' AND sequence_owner = 'SYS'; SEQUENCE_OWNER       SEQUENCE_NAME        INCREMENT_BY CACHE_SIZE LAST_NUMBER -------------------- -------------------- ------------ ---------- ----------- SYS                  IDGEN1$                        50       1000     4050401 SYS@test01p> select /*+ aaaaa */ sys.IDGEN1$.nextval from dual;    NEXTVAL ----------    4000451 --//可以验证确实如此。 5.再来看看chunk =32K 的情况： tab 0, row 1, @0x1f19 tl: 52 fb: --H-FL-- lb: 0x2  cc: 2 col  0: [ 2]  c1 02 col  1: [45]  00 70 00 01 01 0c 00 80 00 01 00 00 00 01 00 00 00 3c 47 74 00 19 40 90 00  13 22 00 06 61 a1 01 01 01 02 c0 00 c3 05 02 02 c0 01 37 2f        ~~~~~~~~~~~--//文件大小 418209=0x661a1 --//418209/8060 = 51.88697270471464019851,占52块 --//securefile的格式basic存在很大的不同。 --//参考http://www.juliandyke.com/Presentations/Presentations.php#LOBInternals的介绍： 01 01 01 02 c0 00 c3 05 02 02 c0 01 37 2f SCOTT@test01p> @ dfb16 0x02c000c3     RFILE#     BLOCK# TEXT ---------- ---------- -----------------------------------------------         11        195 alter system dump datafile 11 block 195 ; SCOTT@test01p> @ dfb16 0x02c00137     RFILE#     BLOCK# TEXT ---------- ---------- ------------------------------------------         11        311 alter system dump datafile 11 block 311 ; --//相当于dba=11,195,0x05表示# Blocks in extent --//      dba=11,311,0x2f=47. --//这个chunk如何体会，不理解？ --//换systeminternals的Procmon.exe跟踪看看，直接跟踪tid。 "Time of Day","Process Name","PID","Operation","Path","Result","Detail","TID" "21:00:28.2079556","ORACLE.EXE","3324","ReadFile","D:\app\oracle\oradata\test\test01p\USERS01.DBF","SUCCESS","Offset: 1,597,440, Length: 40,960, I/O Flags: Non-cached, Priority: Normal","2956" "21:00:28.2079975","ORACLE.EXE","3324","ReadFile","D:\app\oracle\oradata\test\test01p\USERS01.DBF","SUCCESS","Offset: 2,547,712, Length: 385,024, I/O Flags: Non-cached, Priority: Normal","2956" --//第2次： "21:00:46.8923040","ORACLE.EXE","3324","ReadFile","D:\app\oracle\oradata\test\test01p\USERS01.DBF","SUCCESS","Offset: 1,597,440, Length: 40,960, I/O Flags: Non-cached, Priority: Normal","2956" "21:00:46.8924736","ORACLE.EXE","3324","ReadFile","D:\app\oracle\oradata\test\test01p\USERS01.DBF","SUCCESS","Offset: 2,547,712, Length: 385,024, I/O Flags: Non-cached, Priority: Normal","2956" --//1597440/8192 = 195 , 40960/8192 = 5 --//2547712/8192 = 311 , 385024/8192 = 47 --//都能对上。 6.总结： --//不过为什么跟踪看不到取sequence的信息有点奇怪。另外我扫描共享池也没有发现痕迹，不知道为什么... --//看不出chunk的作用，也许对于securefile无用。 7.补充测试： CREATE TABLE TX ( ID NUMBER,   IMAGE BLOB ) LOB (IMAGE) STORE AS  securefile  (ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE) ; --//修改前面c1.txt脚本 ，t => tx. SCOTT@test01p> @ D:\tools\git_study\test1.git\c1.txt PL/SQL procedure successfully completed. SCOTT@test01p> select rowid,id from tx ; ROWID                      ID ------------------ ---------- AAAFlFAALAAAADXAAA          1 SCOTT@test01p> @ rowid AAAFlFAALAAAADXAAA     OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT ---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------      22853         11        215          0  0x2C000D7           11,215               alter system dump datafile 11 block 215 SCOTT@test01p> alter system dump datafile 11 block 215 ; System altered. Block header dump:  0x02c000d7  Object id on Block? Y  seg/obj: 0x5945  csc:  0x000000000030ac4f  itc: 2  flg: E  typ: 1 - DATA      brn: 0  bdba: 0x2c000d0 ver: 0x01 opc: 0      inc: 0  exflg: 0    Itl           Xid                  Uba         Flag  Lck        Scn/Fsc 0x01   0x0006.011.00000218  0x01801b38.0050.0d  --U-    1  fsc 0x0000.0030ac5d 0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000 bdba: 0x02c000d7 data_block_dump,data header at 0x9481064 =============== tsiz: 0x1f98 hsiz: 0x14 pbl: 0x09481064      76543210 flag=-------- ntab=1 nrow=1 frre=-1 fsbo=0x14 fseo=0x1f3f avsp=0x1f50 tosp=0x1f50 0xe:pti[0]    nrow=1    offs=0 0x12:pri[0]    offs=0x1f3f block_row_dump: tab 0, row 0, @0x1f3f tl: 52 fb: --H-FL-- lb: 0x1  cc: 2 col  0: [ 2]  c1 02 col  1: [45]  00 70 00 01 01 0c 00 80 00 01 00 00 00 01 00 00 00 3e 91 32 00 19 40 90 00  13 22 00 06 61 a1 01 01 01 02 c0 00 e3 05 02 02 c0 01 b7 2f                          ~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~ end_of_block_dump End dump data blocks tsn: 4 file#: 11 minblk 215 maxblk 215 01 01 01 02 c0 00 e3 05 02 02 c0 01 b7 2f --//可以看出chunk对于securefile基本无用，而且可以发现securefile的读写效率更高。 --//它保存的是first chunk，然后是块数。