[20140729]關於LOB欄位儲存特性2.txt
[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
--其他不好猜測了。還是不明白邏輯讀為什麼這麼多？放棄！
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1244291/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20140729]關於LOB欄位儲存特性1.txt
- [20140729]關於LOB欄位儲存特性3.txt
- [20140731]關於LOB欄位儲存特性4.txt
- 小議lob欄位結構和儲存
- oracle中lob欄位Oracle
- 行連結與行遷移, LOB欄位的儲存及效能影響
- LOB儲存筆記筆記
- 【LOB】使用資料泵時 LOB 欄位存放位置
- LOB欄位EMPTY_LOB和NULL的區別Null
- ORACLE LOB大欄位維護Oracle
- LOB欄位資料清理 - 更新為null後move lobNull
- [轉]LOB儲存筆記筆記
- lob欄位表空間遷移
- Oracle LOB儲存知識(zt)Oracle
- Oracle 建表時LOB欄位語法Oracle
- 帶有LOB欄位的表遷移
- Oracle Lob型別儲存淺析Oracle型別
- 儲存大容量欄位出現的問題
- 釋放大資料量的lob欄位空間大資料
- Oracle 帶LOB欄位的表的遷移Oracle
- varchar or blob：欄位型別的儲存和溢位條件型別
- 在SQL Server中修改欄位型別和欄位名稱的儲存過程SQLServer型別儲存過程
- Oracle lob載入bfile資料到blob欄位中Oracle
- lob欄位的ora-1555處理方案
- [20130106]關於不同字符集下clob欄位的儲存問題.txt
- 主表子表動態欄位儲存實現方式總結
- varchar型別的欄位儲存純數字的排序型別排序
- MySQL 如何儲存長度很長的資料欄位MySql
- 更新大表中某個欄位的儲存過程儲存過程
- SAP WM中階儲存型別裡的Full stk rmvl 欄位和Return Storage type欄位型別
- DatabaseLink不支援merge和lob欄位查詢Database
- lob欄位型別轉換ora-22858型別
- 一次PLSQL處理LOB欄位的優化SQL優化
- 儲存容量及相關計算單位
- 關於jdon儲存，修改問題
- 密碼欄位，使用md5加密，欄位儲存用raw，避免字符集的問題密碼加密
- MySQL - 一個欄位儲存多個ID時的查詢MySql
- 可以處理LOB欄位的常用字元函式字元函式