[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
.
.
--//注:文档这里标识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，然后是块数。
[20181022]lob字段的lobid来之那里.txt
-
lfree
2018-10-22 21:21:57 -
Oracle
- 原创