[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

--其他不好猜測了。還是不明白邏輯讀為什麼這麼多？放棄！

[20140729]關於LOB欄位儲存特性2.txt

相關文章