[20140729]关于LOB字段存储特性2.txt
--前面提到查询字段是DISABLE STORAGE IN ROW的读会增加，从4->11. 多扫描lob index 来定位信息，但是增加也太多。
--有必要看看lob index 的上数据结构。
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 id,col2 from t where id=1;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
2 physical reads
0 redo size
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个物理读。
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.
SCOTT@test> alter session set events 'immediate trace name treedump level 288780';
----- begin tree dump
leaf: 0x100059b 16778651 (0: nrow: 1 rrow: 1)
----- end tree dump
SCOTT@test> set verify off
SCOTT@test> @dfb 100059b
RFILE# BLOCK#
---------- ----------
4 1435
TEXT
------------------------------------------
alter system dump datafile 4 block 1435 ;
SCOTT@test> exec print_table('select * from dba_segments where owner=user and segment_name=''SYS_IL0000288774C00005$$''');
OWNER : SCOTT
SEGMENT_NAME : SYS_IL0000288774C00005$$
PARTITION_NAME :
SEGMENT_TYPE : LOBINDEX
SEGMENT_SUBTYPE : ASSM
TABLESPACE_NAME : USERS
HEADER_FILE : 4
HEADER_BLOCK : 1434
BYTES : 65536
BLOCKS : 8
EXTENTS : 1
INITIAL_EXTENT : 65536
NEXT_EXTENT : 1048576
MIN_EXTENTS : 1
MAX_EXTENTS : 2147483645
MAX_SIZE : 2147483645
RETENTION :
MINRETENTION :
PCT_INCREASE :
FREELISTS :
FREELIST_GROUPS :
RELATIVE_FNO : 4
BUFFER_POOL : DEFAULT
FLASH_CACHE : DEFAULT
CELL_FLASH_CACHE : DEFAULT
-----------------
PL/SQL procedure successfully completed.
SCOTT@test> column SEGMENT_NAME format a30
SCOTT@test> SELECT segment_name, extent_id, file_id, block_id, bytes, blocks, relative_fno FROM dba_extents WHERE segment_name = 'SYS_IL0000288774C00005$$';
SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
SYS_IL0000288774C00005$$ 0 4 1432 65536 8 4
--从跟踪可以知道索引块在file#=4 block#=1432这个位置.
...
WAIT #0: nam='db file scattered read' ela= 168 file#=4 block#=1432 blocks=8 obj#=288780 tim=1406709303780934
...
--file#=4 mblock#=1435才是索引的root节点。
SCOTT@test> alter system dump datafile 4 block 1435 ;
System altered.
Block header dump: 0x0100059b
Object id on Block? Y
seg/obj: 0x4680c csc: 0x02.a60dfbc2 itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1000598 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x0005.020.00006957 0x00c01a26.22e7.19 --U- 1 fsc 0x0000.a60dfbdd
Leaf block dump
===============
header address 182924434020=0x2a97255a64
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 1
kdxcofbo 38=0x26
kdxcofeo 7982=0x1f2e
kdxcoavs 7944
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 32
kdxlebksz 8032
row#0[7982] flag: ------, lock: 2, len=50, data:(32):
00 20 03 00 00 00 00 00 1f 40 00 00 00 00 00 01 01 00 05 8e 00 00 00 00 00
00 00 00 00 00 00 00
col 0; len 10; (10): 00 00 00 01 00 00 02 33 ad 93
col 1; len 4; (4): 00 00 00 00
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 1435 maxblk 1435
--col 0 = LobID.
--搞不懂data部分是表示什么，应该是lob的信息。乱猜一下：
1f 40 ＝ 8000 (十进制字串长度）
01 00 05 8e ＝ lob 的块信息。
SCOTT@test> @dfb 0100058e
RFILE# BLOCK#
---------- ----------
4 1422
TEXT
-----------------------------------------
alter system dump datafile 4 block 1422 ;
SCOTT@test> alter system dump datafile 4 block 1422 ;
System altered.
*** 2014-07-31 09:32:06.566
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4 rdba=16778638
Block dump from disk:
buffer tsn: 4 rdba: 0x0100058e (4/1422)
scn: 0x0002.a60dfbd3 seq: 0x02 flg: 0x04 tail: 0xfbd32802
frmt: 0x02 chkval: 0xe181 type: 0x28=PAGETABLE MANAGED LOB BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000002A97255A00 to 0x0000002A97257A00
2A97255A00 0000A228 0100058E A60DFBD3 04020002 [(...............]
2A97255A10 0000E181 0004680B 01000000 33020000 [.....h.........3]
2A97255A20 000093AD 00000000 00000000 00000000 [................]
2A97255A30 01000588 00000000 62006200 62006200 [.........b.b.b.b]
2A97255A40 62006200 62006200 62006200 62006200 [.b.b.b.b.b.b.b.b]
Repeat 498 times
2A97257970 62006200 62006200 20002000 20002000 [.b.b.b.b. . . . ]
2A97257980 20002000 20002000 20002000 20002000 [. . . . . . . . ]
Repeat 6 times
2A972579F0 20002000 20002000 20002000 FBD32802 [. . . . . . .(..]
Long field block dump:
Object Id 288779
LobId: 000100233AD93 PageNo 0
Version: 0x0000.00000000 pdba: 16778632
--其他不好猜测了。还是不明白逻辑读为什么这么多？放弃！