小議lob欄位結構和儲存

Lob欄位包含三個部分：Lob列、lob資料段、lob索引段。

Lob列由lob定位符也就是指向lob欄位實際存放位置的指標和lob值組成。

Lob資料段可以分為多個chunk，每個chunk用來存放lob資料。

Lob索引段存放lob索引，由多個entry組成，每個entry結構大致為lob index locator、chunk id和chunk location，一個entry指向一個lob塊。也就是記錄在lob索引段裡面的entry等於lob資料段內的chunk數目。

結構大體如下：

Lob列 LOB定位符 lob值

Lob資料段 chunk1 chunk2 chunk3 chunk4…

Log 索引段 entry entry entry entry…

SQL> create table tab_lob01(id number,cnt clob)

2 tablespace users

3 lob(cnt) store as seg_cnt01(tablespace netbaisdata

4 chunk 32K

5 pctversion 5

6 );

Table created

其中會建立一個表段，lob資料段和lob索引段，其中制定的seg_cnt01的lob資料段儲存在netbaisdata段內，而系統預定義的lob索引段也會儲存在netbaisdata表空間中。

其中的上述的chunk也就是分配給lob段的最小單元，由於lob欄位的讀取和寫入都是以chunk為單位，一般chunk為db_block_size的整數倍，oracle10g 版本最大值可以為2G，預設情況使用最小值。

Enable/disable storage in row

Enable storage in row模式表示小於4K的lob欄位儲存在表段內，大於4k的lob欄位儲存在lob資料段內。

Disable storage in row不管lob欄位的長度都儲存在lob資料段內，僅僅只是在表的行記憶體儲lob定位符。

Pctversion

Lob資料段不適用回滾機制，即當更新lob資料時，lob資料段內分配新chunk去插入一條新的記錄，不對前映像修改，那麼如果lob欄位更新較多，肯定會產生很多的新version，會浪費較多的空間，所以需要用pctversion來控制前映像空間在lob資料空間中所佔用的比例。大於此比例將會重用這些前映像，預設值是10%，最小設定為0，最大100%。

Cache/nocache/cache read

Cache表示將lob資料段放在快取中，nocache不放在快取中，cache read則是讀的時候會把lob儲存在快取中，預設的是nocache。

Loc的儲存位置：

根據lob欄位的儲存中的enable| disbale storage in row分析，當採用enable storage in row時，如果lob欄位小於4K，也就是in-line blob的會直接儲存在表段內；而當大於4K時（out-of-line）且lob欄位沒有超過12個的chunk，lob定位符直接指向lob資料段內的資料，不用lob索引段。Disable storage in row或者是二進位制資料大於4k，而且超過12chunk時，需要透過lob定位符—lob索引段—lob資料段，也就是每一個lob資料段內的每個chunk地址都存在lob索引中。

SQL> select avg(dbms_lob.getlength(in_flow_img)) from ql_inf;

AVG(DBMS_LOB.GETLENGTH(IN_FLOW

------------------------------

93100.3484848485

SQL> select segment_name,chunk,pctversion,cache,logging,in_row from user_lobs where column_name='IN_FLOW_IMG' and table_name='QL_INF';

SEGMENT_NAME CHUNK PCTVERSION CACHE LOGGING IN_ROW

------------------------------ ---------- ---------- ---------- ------- ------

SYS_LOB0000052854C00019$$ 8192 10 NO YES YES

Lob欄位in_flow_img的SYS_LOB0000052854C00019$$的預設chunk是8kb，而實際我們儲存的欄位大概有91kb的樣子，由於一個chunk只儲存一行資料，實際會佔用12個chunk，那麼會產生過多的行連線，這裡設定較大的chunk會更好，不夠在預設的db_block_size 8kb下只能設定32k的chunk，超過這個數在oracle10.2R下就會出現invalid lob option value的ora-22851錯誤。當然如果實際的lob欄位容量較小，比如4kb，而實際你的chunk預設的是8kb，由於chunk只儲存一行資料，同樣會佔用8kb的大小，其餘的空間也不會節省出來。

SQL> create table lob01(cnt1 clob,cnt2 clob) lob(cnt2) store as(disable storage in row);

Table created

SQL> insert into lob01 values(lpad('x',10000,'x'),rpad('x',10000,'x'))

;

2 /

1 row inserted

SQL> commit;

Commit complete

SQL> select avg(dbms_lob.getlength(cnt1)),avg(dbms_lob.getlength(cnt2)) from lob01;

AVG(DBMS_LOB.GETLENGTH(CNT1)) AVG(DBMS_LOB.GETLENGTH(CNT2))

----------------------------- -----------------------------

4000 4000

可以看出都應該在同一個chunk內，預設chunk是8kb。那麼disable和enable所走的IO將不同。

SQL> set autotrace traceonly statistics

SQL> select cnt1 from lob01;

統計資訊

----------------------------------------------------------

0 recursive calls

0 db block gets

11 consistent gets

4 physical reads

0 redo size

1023 bytes sent via SQL*Net to client

678 bytes received via SQL*Net from client

5 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> select cnt2 from lob01;

統計資訊

----------------------------------------------------------

0 recursive calls

0 db block gets

19 consistent gets

4 physical reads

0 redo size

963 bytes sent via SQL*Net to client

638 bytes received via SQL*Net from client

5 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

多執行幾次減小物理IO，可以看出真正的physical reads並不能絕對的減小到0，那是因為lob預設的是不會cache的。

SQL> alter table lob01 move tablespace netbaisdata

2 lob(cnt1) store as(cache)

3 lob(cnt2) store as(cache);

表已更改。

SQL> select cnt1 from lob01;

統計資訊

----------------------------------------------------------

62 recursive calls

0 db block gets

23 consistent gets

3 physical reads

72 redo size

1023 bytes sent via SQL*Net to client

678 bytes received via SQL*Net from client

5 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> select cnt1 from lob01;

統計資訊

----------------------------------------------------------

0 recursive calls

0 db block gets

7 consistent gets

0 physical reads

0 redo size

1023 bytes sent via SQL*Net to client

678 bytes received via SQL*Net from client

5 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> select cnt2 from lob01;

統計資訊

----------------------------------------------------------

1 recursive calls

0 db block gets

15 consistent gets

0 physical reads

0 redo size

963 bytes sent via SQL*Net to client

638 bytes received via SQL*Net from client

5 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

可以看出設定cache後，物理讀會慢慢減少到0，全部轉化為邏輯讀，但是由於cnt2是disable storage in row的，需要先根據lob定位器然後到lob索引段最後到lob資料段，需要訪問更多的IO，所有邏輯讀會大於cnt1.

接著看下面的事例：

SQL> select count(rowid) from jhqlnwhxdb.ql_inf;

COUNT(ROWID)

------------

16810

SQL> select count(rowid) from jhqlnwhxdb.ql_inf where dbms_lob.getlength(in_flow_img)>4096;

COUNT(ROWID)

------------

16803

SQL> select avg(dbms_lob.getlength(in_flow_img)) from jhqlnwhxdb.ql_inf where dbms_lob.getlength(in_flow_img)>4096;

AVG(DBMS_LOB.GETLENGTH(IN_FLOW

------------------------------

268087.183717193

可以看出in_flow_img的基本都大於4k,其大於4k的資料平均長度要達到160k的樣子，如果大部分lob欄位小於4k，則肯定是enable storage in row模式優先，有效的減少了行連結，而如果大於4k但是還是沒有超過12個chunk，其實也就是資料的長度沒有達到預設的12*8kb也就是96kb的值，也可以選擇enable模式。如果大部分大於4k且基本超過了12 chunk值，也就是out-of-line儲存的比例較高，應該選擇disable模式，這樣lob資料會儲存在外面的lob資料段，如果進行fts或者查詢非lob欄位，將大大加快儲存訪問的速度，這個是需要dba更多的關注的，而另外也可以設定較大的chunk，oracle每次的IO資料就會多一些，總體會減少相應的IO。

Lob資料段和lob索引段可以分開儲存到不同的tbs下，然後到不同的磁碟，減小磁碟的競爭，對於lob資料而言，只要一行資料超過oracle資料塊的大小必產生行連線，我們可以透過analyze後來分析表中的行連線，透過oracle concept中就清楚設計中要相應的減少了行連結和行遷移，因為一旦過多的IO越來越多就可能往往成為你係統瓶頸的最終原因，不過生產中暫時還沒有碰見這個成為瓶頸的案例。

SQL> analyze table jhqlnwhxdb.ql_inf compute statistics;

Table analyzed

SQL> select chain_cnt from dba_tables where table_Name='QL_INF' and

2 owner='JHQLNWHXDB';

CHAIN_CNT

----------

220

[@more@]