[20140731]關於LOB欄位儲存特性4.txt
[20140731]關於LOB欄位儲存特性4.txt
--12c開始建立表如果包含lob，預設建立的是securefile，securefile支援一些新特性，比如： Deduplication, Compression and Encryption.
--在11g下測試securefile看看。
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
CREATE TABLE "SCOTT"."T1"
( "ID" NUMBER,
"IDX" NUMBER,
"COL1" CLOB,
"COL2" CLOB,
"COL3" CLOB
)
LOB ("COL1") STORE AS SECUREFILE
LOB ("COL2") STORE AS SECUREFILE
LOB ("COL3") STORE AS SECUREFILE (disable storage in row);
create unique index i_t1_id on t1(id);
insert into t1 values (1,1,lpad('a',100,'a'),lpad('b',4000,'b'),lpad('c',4000,'c'));
commit ;
execute dbms_stats.gather_table_stats(user,'t1',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('a',100,'a'),應該在塊內。
--col2=lpad('b',4000,'b'),長度超出3964，應該在塊外。
--col3=lpad('c',4000,'c'),使用DISABLE STORAGE IN ROW，無論如何都在塊外。
SCOTT@test> SELECT DBMS_METADATA.get_ddl ('TABLE','T1') from dual ;
DBMS_METADATA.GET_DDL('TABLE','T1')
----------------------------------------------------------------------------
CREATE TABLE "SCOTT"."T1"
( "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 SECUREFILE (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
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))
LOB ("COL2") STORE AS SECUREFILE (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
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))
LOB ("COL3") STORE AS SECUREFILE (
TABLESPACE "USERS" DISABLE STORAGE IN ROW CHUNK 8192
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))
SCOTT@test> set autot trace
SCOTT@test> select id,idx from t1 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 t1 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 t1 where id=1;一樣,僅僅2個邏輯讀.
SCOTT@test> select id,col2 from t1 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 t1 where id=1;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
2 physical reads
0 redo size
--對比發現，ENABLE/DISABLE STORAGE IN ROW兩者的邏輯讀一樣對於securefile，都是4個邏輯讀，而且每次都是2個物理讀。
--在塊內欄位col1外，執行的語句select id,idx from t1 where id=1 與select id,col1 from t1 where id=1，兩者的邏輯讀一樣。
先做一個轉儲：
SCOTT@test> select rowid,id,idx from t1 where id=1;
ROWID ID IDX
------------------ ---------- ----------
AABGjbAAEAAAAWmAAA 1 1
SCOTT@test> @lookup_rowid AABGjbAAEAAAAWmAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
288987 4 1446 0 4,1446 alter system dump datafile 4 block 1446
SCOTT@test> alter system dump datafile 4 block 1446;
System altered.
Block header dump: 0x010005a6
Object id on Block? Y
seg/obj: 0x468db csc: 0x02.a6127dc2 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x10005a0 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0005.000.00006a25 0x00c002fb.2321.1e --U- 1 fsc 0x0000.a6127ee0
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x010005a6
data_block_dump,data header at 0x2a9751e264
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x2a9751e264
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1e5a
avsp=0x1e46
tosp=0x1e46
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1e5a
block_row_dump:
tab 0, row 0, @0x1e5a
tl: 318 fb: --H-FL-- lb: 0x1 cc: 5
col 0: [ 2] c1 02
col 1: [ 2] c1 02
col 2: [230]
00 54 00 01 02 0c 80 80 00 02 00 00 00 01 00 00 02 35 33 cd 00 d2 48 90 00
cc 00 00 c8 01 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61
00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00
61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61
00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00
61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61
00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00
61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61
00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00
61 00 61 00 61
LOB
Locator:
Length: 84(230)
Version: 1
Byte Length: 2
LobID: 00.00.00.01.00.00.02.35.33.cd
Flags[ 0x02 0x0c 0x80 0x80 ]:
Type: CLOB
Storage: SecureFile
Characterset Format: IMPLICIT
Partitioned Table: No
Options: VaringWidthReadWrite
SecureFile Header:
Length: 210
Old Flag: 0x48 [ DataInRow SecureFile ]
Flag 0: 0x90 [ INODE Valid ]
Layers:
Lengths Array: INODE:204
INODE:
00 00 c8 01 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61
00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61
00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61
col 3: [38]
00 54 00 01 02 0c 80 80 00 02 00 00 00 01 00 00 02 35 33 ce 00 12 40 90 00
0c 21 00 1f 40 01 00 01 01 00 02 a3 01
LOB
Locator:
Length: 84(38)
Version: 1
Byte Length: 2
LobID: 00.00.00.01.00.00.02.35.33.ce
Flags[ 0x02 0x0c 0x80 0x80 ]:
Type: CLOB
Storage: SecureFile
Characterset Format: IMPLICIT
Partitioned Table: No
Options: VaringWidthReadWrite
SecureFile Header:
Length: 18
Old Flag: 0x40 [ SecureFile ]
Flag 0: 0x90 [ INODE Valid ]
Layers:
Lengths Array: INODE:12
INODE:
21 00 1f 40 01 00 01 01 00 02 a3 01
col 4: [38]
00 54 00 01 02 0c 80 80 00 02 00 00 00 01 00 00 02 35 33 cf 00 12 40 90 00
0c 21 00 1f 40 01 00 01 01 00 02 bb 01
LOB
Locator:
Length: 84(38)
Version: 1
Byte Length: 2
LobID: 00.00.00.01.00.00.02.35.33.cf
Flags[ 0x02 0x0c 0x80 0x80 ]:
Type: CLOB
Storage: SecureFile
Characterset Format: IMPLICIT
Partitioned Table: No
Options: VaringWidthReadWrite
SecureFile Header:
Length: 18
Old Flag: 0x40 [ SecureFile ]
Flag 0: 0x90 [ INODE Valid ]
Layers:
Lengths Array: INODE:12
INODE:
21 00 1f 40 01 00 01 01 00 02 bb 01
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 1446 maxblk 1446
-- 對比col 3: [38]與col 4: [38]，塊內的長度都是一樣的。
col 3: [38]
00 54 00 01 02 0c 80 80 00 02 00 00 00 01 00 00 02 35 33 ce 00 12 40 90 00
0c 21 00 1f 40 01 00 01 01 00 02 a3 01
col 4: [38]
00 54 00 01 02 0c 80 80 00 02 00 00 00 01 00 00 02 35 33 cf 00 12 40 90 00
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~lobid
0c 21 00 1f 40 01 00 01 01 00 02 bb 01
~~~~~~~~~~~
如果參考URL
01 00 02 bb 表示 DBA of first block in extent
01 表示 # Blocks in extent
SCOTT@test> @dfb 010002a3
RFILE# BLOCK#
---------- ----------
4 675
TEXT
----------------------------------------
alter system dump datafile 4 block 675 ;
SCOTT@test> @dfb 010002bb
RFILE# BLOCK#
---------- ----------
4 699
TEXT
----------------------------------------
alter system dump datafile 4 block 699 ;
--轉儲對應的塊：
alter system dump datafile 4 block 675 ;
Block header dump: 0x010002a3
Object id on Block? Y
seg/obj: 0x468de csc: 0x02.a6127d14 itc: 1 flg: E typ: 5 - LOCAL LOBS
fsl: 0 fnx: 0xffffffff ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0005.000.00006a25 0x00000000.0000.00 -B-- 0 fsc 0x0000.00000000
========
bdba [0x010002a3]
kdlich [0x2a9751e24c 56]
flg0 0x20 [ver=0 typ=data lock=n]
flg1 0x00
scn 0x0002.a6127d14
lid 000000010000023533ce
rid 0x00000000.0000
kdlidh [0x2a9751e264 24]
flg2 0x00 [ver=0 lid=short-rowid hash=n cmap=n pfill=n]
flg3 0x00
pskip 0
sskip 0
hash 0000000000000000000000000000000000000000
hwm 8000
spr 0
data [0x2a9751e280 52 8060]
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 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
--- end of lob block dump ---
End dump data blocks tsn: 4 file#: 4 minblk 675 maxblk 675
alter system dump datafile 4 block 699 ;
Block header dump: 0x010002bb
Object id on Block? Y
seg/obj: 0x468e0 csc: 0x02.a6127dc0 itc: 1 flg: E typ: 5 - LOCAL LOBS
fsl: 0 fnx: 0xffffffff ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0005.000.00006a25 0x00000000.0000.00 -B-- 0 fsc 0x0000.00000000
========
bdba [0x010002bb]
kdlich [0x2a9751e24c 56]
flg0 0x20 [ver=0 typ=data lock=n]
flg1 0x00
scn 0x0002.a6127dc0
lid 000000010000023533cf
rid 0x00000000.0000
kdlidh [0x2a9751e264 24]
flg2 0x00 [ver=0 lid=short-rowid hash=n cmap=n pfill=n]
flg3 0x00
pskip 0
sskip 0
hash 0000000000000000000000000000000000000000
hwm 8000
spr 0
data [0x2a9751e280 52 8060]
00 63 00 63 00 63 00 63 00 63 00 63 00 63 00 63 00 63 00 63 00 63 00 63 00 63
00 63 00 63 00 63 00 63 00 63 00 63 00 63 00 63 00 63 00 63 00 63 00 63 00 63
...
00 63 00 63 00 63 00 63 00 63 00 63 00 63 00 63 00 63 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
--- end of lob block dump ---
End dump data blocks tsn: 4 file#: 4 minblk 699 maxblk 699
--從這些可以看出對於使用securefile，無論使用ENABLE/DISABLE STORAGE IN ROW,在塊內儲存有chunk的資訊,不需要透過lob index來定位,能獲得很好的效能。
--不知道什麼情況下透過lib index來定位????
SCOTT@test> alter table t1 modify lob (col2) (CACHE) modify lob (col3) (cache);
Table altered.
SCOTT@test> select id,col2 from t1 where id=1;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
SCOTT@test> select id,col3 from t1 where id=1;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
--可以發現設定cache後，多次執行physical reads=0.
--看來以後設定lob為securefile比較好一些。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1244298/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ORACLE LOB大欄位維護Oracle
- [20181020]lob欄位的索引段.txt索引
- [20181031]lob欄位與布隆過濾.txt
- [20210208]lob欄位與查詢的問題.txt
- [20181022]lob欄位的lobid來之那裡.txt
- LOB欄位相關概念(自動建立LOB索引段和重建索引方法)索引
- 主表子表動態欄位儲存實現方式總結
- SAP WM中階儲存型別裡的Full stk rmvl 欄位和Return Storage type欄位型別
- 儲存容量及相關計算單位
- 【ORA-01555】Oracle LOB欄位匯出 報錯 ORA-01555Oracle
- mssql sqlserver 可以儲存二進位制資料的欄位型別詳解SQLServer型別
- 關於Entity Freamwork 儲存過程操作儲存過程
- Oracle資料庫高水位釋放——LOB欄位空間釋放Oracle資料庫
- [重慶思莊每日技術分享]-重建LOB欄位上的IndexIndex
- 如何在Spring Data MongoDB 中儲存和查詢動態欄位SpringMongoDB
- [20181124]關於降序索引問題4.txt索引
- 關於日期及時間欄位的查詢
- 關於Thinkphp 使用AdvModel來讀取Blob欄位PHP
- SAP QM 主檢驗特性主資料關鍵欄位解釋
- 關於LaravelAdmin和Dcat在$form->saving修改了表單提交的欄位值，但儲存時卻不生效的解決方法LaravelORM
- dbms_lob儲存過程導致臨時表空間100%儲存過程
- 物件儲存服務的Lambda特性物件
- 物件儲存服務的加密特性物件加密
- 關於Mysql資料儲存，你瞭解多少？MySql
- 關於儲存及CSS的一些技巧CSS
- SAP CRM銷售訂單UI上的欄位對應的資料庫表儲存欄位：requested start date和end dateUI資料庫
- MySQL8.0 新特性：Partial Update of LOB ColumnMySql
- [BUG反饋]模型中的欄位型別為日期是隻儲存了年份模型型別
- Spark儲存Parquet資料到Hive，對map、array、struct欄位型別的處理SparkHiveStruct型別
- 物件儲存服務的壓縮特性物件
- 物件儲存服務的事件通知特性物件事件
- Python3中關於cookie的建立與儲存PythonCookie
- Oracle12C新特性_不可見欄位(二)Oracle
- ORACLE 資料匯出LOB欄位報錯ORA-31693,ORA-02354,ORA-22924Oracle
- 【Django drf】 序列化類常用欄位類和欄位引數 定製序列化欄位的兩種方式 關係表外來鍵欄位的反序列化儲存 序列化類繼承ModelSerializer 反序列化資料校驗原始碼分析Django繼承原始碼
- [20210524]分析library cache轉儲 4.txt
- 物件儲存服務的影像處理特性物件
- 阿里雲物件儲存OSS支援版本管理特性阿里物件
- 關於InnoDB表資料和索引資料的儲存索引