[20140729]關於LOB欄位儲存特性1.txt
[20140729]關於LOB欄位儲存特性1.txt
CLOB/BLOB欄位的儲存可以使用引數ENABLE/DISABLE STORAGE IN ROW,預設是ENABLE STORAGE IN ROW,這種情況下,如果
儲存長度小於等於3964(主要前面佔用36位元組),實際上總和是4000位元組.
參考:
http://blog.itpub.net/267265/viewspace-755269/
[20130301]clob欄位的empty_clob與NULL.txt
在儲存的clob資訊有兩個選項,預設ENABLE STORAGE IN ROW (default),在這種情況下:
1.如果儲存的長度小於等於3964,lob的資訊儲存在塊內.
2.如果儲存的長度大於3964,lob的資訊儲存在塊外的lob segment內.
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 DBMS_METADATA.get_ddl ('TABLE','T') from dual ;
DBMS_METADATA.GET_DDL('TABLE','T')
------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."T"
( "ID" NUMBER,
"IDX" NUMBER,
"COL1" CLOB,
"COL2" CLOB,
"COL3" CLOB
) 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 ("COL1") STORE AS BASICFILE (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE 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))
LOB ("COL2") STORE AS BASICFILE (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE 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))
LOB ("COL3") STORE AS BASICFILE (
TABLESPACE "USERS" DISABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE 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))
--可以發現11g下預設使用的屬性STORE AS BASICFILE，不是securefile。
SCOTT@test> set autot trace
SCOTT@test> select id,idx from t where id=1;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
--注意如果出現recursive calls<>0,可以執行多次。可以發現僅僅2個邏輯讀。
SCOTT@test> select id,col1 from t where id=1;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
--col1的資訊長度<=3964,儲存在塊內,可以發現邏輯讀與select id,idx from t where id=1;一樣,僅僅2個邏輯讀.
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
--如果col2是ENABLE STORAGE IN ROW,由於長度大於3964,儲存在塊外,導致 consistent gets增加到4,同時存在
--2個physical reads,無論執行多少次都一樣.
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個物理讀。
--在塊內欄位col1，執行的語句select id,idx from t where id=1 與select id,col1 from t where id=1，兩者的邏輯讀一樣。
--為什麼讀取col2，col3 欄位，兩者的邏輯讀相差這麼大呢？
先做一個轉儲：
SCOTT@test> select rowid,id,idx from t where id=1;
ROWID ID IDX
------------------ ---------- ----------
AABGgGAAEAAAACmAAA 1 1
SCOTT@test> @lookup_rowid AABGgGAAEAAAACmAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
288774 4 166 0 4,166 alter system dump datafile 4 block 166 ;
SCOTT@test> alter system checkpoint ;
System altered.
SCOTT@test> alter system dump datafile 4 block 166 ;
System altered.
Block header dump: 0x010000a6
Object id on Block? Y
seg/obj: 0x46806 csc: 0x02.a60dfbd5 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x10000a0 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0005.020.00006957 0x00c01a26.22e7.1a --U- 1 fsc 0x0000.a60dfbdd
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x010000a6
data_block_dump,data header at 0x2a97255a64
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x2a97255a64
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1e64
avsp=0x1e50
tosp=0x1e50
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1e64
block_row_dump:
tab 0, row 0, @0x1e64
tl: 308 fb: --H-FL-- lb: 0x1 cc: 5
col 0: [ 2] c1 02
col 1: [ 2] c1 02
col 2: [236]
00 54 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 02 33 ad 91 00 d8 09 00 00
00 00 00 00 c8 00 00 00 00 00 01 00 62 00 62 00 62 00 62 00 62 00 62 00 62
00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00
62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62
00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00
62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62
00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00
62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62
00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00
62 00 62 00 62 00 62 00 62 00 62
LOB
Locator:
Length: 84(236)
Version: 1
Byte Length: 2
LobID: 00.00.00.01.00.00.02.33.ad.91
Flags[ 0x02 0x0c 0x80 0x00 ]:
Type: CLOB
Storage: BasicFile
Enable Storage in Row
Characterset Format: IMPLICIT
Partitioned Table: No
Options: VaringWidthReadWrite
Inode:
Size: 216
Flag: 0x09 [ Valid DataInRow ]
Future: 0x00 (should be '0x00')
Blocks: 0
Bytes: 200
Version: 00000.0000000001
Inline data[200]
Dump of memory from 0x0000002A972578F6 to 0x0000002A972579BE
2A972578F0 62000100 62006200 62006200 [...b.b.b.b.b]
2A97257900 62006200 62006200 62006200 62006200 [.b.b.b.b.b.b.b.b]
Repeat 10 times
2A972579B0 62006200 62006200 62006200 00286200 [.b.b.b.b.b.b.b(.]
--可以發現'b'儲存00 62(16進位制),佔用2個位元組.
col 3: [40]
00 54 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 02 33 ad 92 00 14 05 00 00
00 00 00 1f 40 00 00 00 00 00 02 01 00 02 76
LOB
Locator:
Length: 84(40)
Version: 1
Byte Length: 2
LobID: 00.00.00.01.00.00.02.33.ad.92
Flags[ 0x02 0x0c 0x80 0x00 ]:
Type: CLOB
Storage: BasicFile
Enable Storage in Row
Characterset Format: IMPLICIT
Partitioned Table: No
Options: VaringWidthReadWrite
Inode:
Size: 20
Flag: 0x05 [ Valid InodeInRow(ESIR) ]
Future: 0x00 (should be '0x00')
Blocks: 0
Bytes: 8000
Version: 00000.0000000002
DBA Array[1]:
0x01000276
col 4: [20] 00 54 00 01 02 08 80 00 00 02 00 00 00 01 00 00 02 33 ad 93
LOB
Locator:
Length: 84(20)
Version: 1
Byte Length: 2
LobID: 00.00.00.01.00.00.02.33.ad.93
Flags[ 0x02 0x08 0x80 0x00 ]:
Type: CLOB
Storage: BasicFile
Disable Storage in Row
Characterset Format: IMPLICIT
Partitioned Table: No
Options: VaringWidthReadWrite
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 166 maxblk 166
--注意看col 3: [40]以及col 4: [20]部分，對應的欄位col3與col3，前者長度40，後者長度僅僅20.
--col 3:
DBA Array[1]:
0x01000276
與
col 3: [40]
00 54 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 02 33 ad 92 00 14 05 00 00
00 00 00 1f 40 00 00 00 00 00 02 01 00 02 76
後面的部分相對應。
SCOTT@test> @dfb 01000276
RFILE# BLOCK#
---------- ----------
4 630
TEXT
----------------------------------------
alter system dump datafile 4 block 630 ;
--如果參考URL STORAGE IN ROW的情況下，僅僅12chunk記錄在塊內。
--LOB Index is used for 13th chunk. 這樣如果lob長度小於chunk size *12 = 8192*12 =98304 bytes下，並不透過lob index來定位。
--而看col 4: [20] ，後面記錄的LobID。這樣要透過lob index索引來定位資訊。
col 4: [20] 00 54 00 01 02 08 80 00 00 02 00 00 00 01 00 00 02 33 ad 93
LobID: 00.00.00.01.00.00.02.33.ad.93
--再做一個10046跟蹤看看。
SCOTT@test> alter system flush buffer_cache;
System altered.
SCOTT@test> @10046on 12
Session altered.
SCOTT@test> select id,col3 from t where id=1;
....
SCOTT@test> @10046off
Session altered.
=====================
PARSING IN CURSOR #182927045352 len=32 dep=0 uid=84 oct=3 lid=84 tim=1406709303754031 hv=3667476909 ad='b1392ef0' sqlid='4jcthq7d9khdd'
select id,col3 from t where id=1
END OF STMT
PARSE #182927045352:c=0,e=144,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1149237570,tim=1406709303754029
EXEC #182927045352:c=0,e=74,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1149237570,tim=1406709303754210
WAIT #182927045352: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1406709303754290
WAIT #182927045352: nam='SQL*Net message from client' ela= 24218 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1406709303778561
WAIT #182927045352: nam='Disk file operations I/O' ela= 290 FileOperation=2 fileno=4 filetype=2 obj#=288781 tim=1406709303779219
WAIT #182927045352: nam='db file scattered read' ela= 108 file#=4 block#=560 blocks=8 obj#=288781 tim=1406709303779359
WAIT #182927045352: nam='db file scattered read' ela= 75 file#=4 block#=160 blocks=8 obj#=288774 tim=1406709303779650
WAIT #182927045352: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=288774 tim=1406709303779797
FETCH #182927045352:c=1999,e=1211,p=16,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=1149237570,tim=1406709303779848
STAT #182927045352 id=1 cnt=1 pid=0 pos=1 obj=288774 op='TABLE ACCESS BY INDEX ROWID T (cr=2 pr=16 pw=0 time=1149 us cost=1 size=90 card=1)'
STAT #182927045352 id=2 cnt=1 pid=1 pos=1 obj=288781 op='INDEX UNIQUE SCAN I_T_ID (cr=1 pr=8 pw=0 time=776 us cost=0 size=0 card=1)'
WAIT #182927045352: nam='SQL*Net message from client' ela= 144 driver id=1650815232 #bytes=1 p3=0 obj#=288774 tim=1406709303780254
WAIT #0: nam='db file scattered read' ela= 168 file#=4 block#=1432 blocks=8 obj#=288780 tim=1406709303780934
WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=288779 tim=1406709303781254
LOBREAD: c=1000,e=998,p=9,cr=3,cu=0,tim=1406709303781308
WAIT #0: nam='SQL*Net more data to client' ela= 33 driver id=1650815232 #bytes=8265 p3=0 obj#=288779 tim=1406709303781382
WAIT #0: nam='SQL*Net message from client' ela= 33639 driver id=1650815232 #bytes=1 p3=0 obj#=288779 tim=1406709303815056
LOBREAD: c=0,e=149,p=1,cr=6,cu=0,tim=1406709303815300
WAIT #0: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=288779 tim=1406709303815343
WAIT #0: nam='SQL*Net message from client' ela= 5877 driver id=1650815232 #bytes=1 p3=0 obj#=288779 tim=1406709303821246
FETCH #182927045352:c=0,e=4,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=1149237570,tim=1406709303821320
WAIT #182927045352: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=288779 tim=1406709303821366
*** 2014-07-30 16:35:06.610
WAIT #182927045352: nam='SQL*Net message from client' ela= 2788695 driver id=1650815232 #bytes=1 p3=0 obj#=288779 tim=1406709306610093
CLOSE #182927045352:c=0,e=19,dep=0,type=1,tim=1406709306610236
=====================
PARSING IN CURSOR #182927041672 len=55 dep=0 uid=84 oct=42 lid=84 tim=1406709306610405 hv=2217940283 ad='0' sqlid='06nvwn223659v'
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.
SYS_LOB0000288774C00005$$ 的OBJECT_ID=288779,對應上面的跟蹤可以發現。
$ egrep "obj#=288779|obj#=288780" /u01/app/oracle11g/diag/rdbms/test/test/trace/test_ora_10999_127_0_0_1.trc
WAIT #0: nam='db file scattered read' ela= 168 file#=4 block#=1432 blocks=8 obj#=288780 tim=1406709303780934
WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=288779 tim=1406709303781254
WAIT #0: nam='SQL*Net more data to client' ela= 33 driver id=1650815232 #bytes=8265 p3=0 obj#=288779 tim=1406709303781382
WAIT #0: nam='SQL*Net message from client' ela= 33639 driver id=1650815232 #bytes=1 p3=0 obj#=288779 tim=1406709303815056
WAIT #0: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=288779 tim=1406709303815343
WAIT #0: nam='SQL*Net message from client' ela= 5877 driver id=1650815232 #bytes=1 p3=0 obj#=288779 tim=1406709303821246
WAIT #182927045352: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=288779 tim=1406709303821366
WAIT #182927045352: nam='SQL*Net message from client' ela= 2788695 driver id=1650815232 #bytes=1 p3=0 obj#=288779 tim=1406709306610093
--從這些可以看出一般使用ENABLE STORAGE IN ROW,在lob欄位長度很小的情況下(chunk size *12 = 8192*12 =98304)，chunk的資訊記錄在塊內,不需要透過lob index來定位,能獲得很好的效能。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1244288/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20140729]關於LOB欄位儲存特性2.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欄位的常用字元函式字元函式