Row Migration和row chained

靜以致遠√團團發表於2014-07-29
AI

行遷移和行連線

OracleBlock的組成

資料塊頭：主要包含有資料塊地址的一些基本資訊和段的型別，以及表和包含有資料的實際行的地址。
自由空間：是指可以為以後的更新和插入操作分配的空間，大小由PCTFREE和PCTUSED兩個引數影響。 
實際資料：是指在行記憶體儲的實際資料。

當建立或者更改任何表和索引的時候，Oracle在空間控制方面使用兩個儲存引數：

 PCTFREE：為一個塊保留的空間百分比，表示資料塊在什麼情況下可以被insert，預設是10，表示當資料塊的可用空間低於10%後，就不可以被insert了，只能被用於update。當使用一個block時，在達到pctfree之前，該block是一直是空閒狀態，這個時候處在上升期。 一旦一個INSERT操作使得資料塊的90%被使用，這個資料塊就從空閒列表(free list)中移出。 
PCTUSED：是指當塊裡的資料低於多少百分比時，又可以重新被insert，一般預設是40,即40%，即：當資料低於40%時，又可以寫入新的資料，這個時候處在下降期。當記錄從資料表中刪除時，資料庫的資料塊就有空間接受新的記錄，但只有當填充的空間降到PCTUSED值以下時，該資料塊才被連線到空閒列表中，才可以往其中插入資料。 

當插入一條記錄的時候，Oracle會在free list中先去尋找一個自由的塊，並且將資料插入到這個自由塊中。而在free list中存在的自由的塊是由pctfree值決定的。初始的空塊都是在free list中的，直到塊中的自由空間達到pctfree的值，此塊就會從free list中移走，而當此塊中的使用空間低於pctused的時候，此塊又被重新放到free list中。

如圖：

假設PCTFREE=20 PCTUSED=40 新建立的表中，資料塊被不斷插入資料，當資料達到整個資料塊的80%，此時該資料塊被從free list移出，不再提供空間進行新的資料插入，預留出20%的空間提供給對該資料塊的update操作，而當隨著刪除該資料塊上的資料，只有當該資料塊資料比例達到40%以下，才可以插入新的資料。

 

行遷移和行連線介紹

行遷移（Row Migration） 

當對錶中資料進行update操作時，如果被更新的資料佔用的空間要比原來的資料佔用空間大到pctfree預留出的空間也不夠用，此時 Oracle會將整行的資料遷移到一個新的資料塊上，而將該行原先的空間只放一個指標，指向該行的新的位置，並且該行原先空間的剩餘空間不再被資料庫使用，這些剩餘的空間我們將其稱之為空洞，這就是產生表碎片的主要原因，表碎片基本上也是不可避免的，但是我們可以將其降到一個我們可以接受的程度。注意，即使發生了行遷移，發生了行遷移的行的rowid 還是不會變化，這也是行遷移會引起資料庫I/O效能降低的原因。

行連結（row chained ）

當使用insert 插入一條新的記錄時，一個block空間不夠容納一條記錄，這種情況下，Oracle將使用連結一個或者多個在這個段中保留的block儲存這一行記錄，行連結比較容易發生在比較大的行上，例如行上有LONG、LONG RAW、LOB等資料型別的欄位，這種時候行連結是不可避免的會產生的。

 

例子

建立一個測試表，因為db_block_size是8K，為了達到效果，測試表中的每行的欄位長度要超過8196。如test表中有五個char欄位，單行資料位10000kb超出8k，插入單行資料就將處罰行連結

 

SQL> create table test(

  2  id char(2000),

  3  value1 char(2000),

  4  value2 char(2000),

  5  value3 char(2000),

  6  value4 char(2000))

  7  pctfree 20

  8  pctused 50

  9  tablespace users

 10  /

Table created.

 

先插入一個欄位的資料，檢視一下資料塊，dump出該資料塊資訊

SQL> insert into test(id) values('aa');

1 row created.

 

SQL> commit;

Commit complete.

SQL> select 

  2  dbms_rowid.rowid_relative_fno(rowid)rel_fno,

  3  dbms_rowid.rowid_block_number(rowid)blockno,

  4  dbms_rowid.rowid_row_number(rowid)rowno

  5  from test

  6  /

 

   REL_FNO    BLOCKNO      ROWNO

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

         4       1092          0

 

SQL> alter system dump datafile 4 block 1092;

System altered.

 

SQL> oradebug setmypid;

Statement processed.

SQL> oradebug tracefile_name;

/u01/app/oracle/admin/orcl_dup/udump/orcl_dup_ora_4302.trc

 

data_block_dump,data header at 0xd96d664

===============

tsiz: 0x1f98

hsiz: 0x14

pbl: 0x0d96d664

bdba: 0x01000444

     76543210

flag=--------

ntab=1

nrow=1

frre=-1

fsbo=0x14

fseo=0x17c2

avsp=0x17ae

tosp=0x17ae

0xe:pti[0]      nrow=1  offs=0

0x12:pri[0]     offs=0x17c2

block_row_dump:

tab 0, row 0, @0x17c2

tl: 2006 fb: --H-FL-- lb: 0x1  cc: 1

col  0: [2000]

 61 61 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20

 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20

 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20

 

其中

fb：H是指行記錄的頭，L是指行記錄的最後一列，F是指行記錄的第一列。 
       cc：列的數量。 
       nrid：對於行連結或者行遷移來說的下一個row id的值。

 

更新一下該行資料

 

SQL>  update test set id='id',value1='value1',value2='value2',value3='value3',value4='value4' where id='aa';

1 row updated.

 

SQL> commit;

Commit complete.

 

再次dump出該塊

 

data_block_dump,data header at 0xdbe2664

===============

tsiz: 0x1f98

hsiz: 0x14

pbl: 0x0dbe2664

bdba: 0x01000444

     76543210

flag=--------

ntab=1

nrow=1

frre=-1

fsbo=0x14

fseo=0xfb1

avsp=0x1773

tosp=0x1773

0xe:pti[0]      nrow=1  offs=0

0x12:pri[0]     offs=0xfb1

block_row_dump:

tab 0, row 0, @0xfb1

tl: 2065 fb: --H-F--N lb: 0x2  cc: 2

nrid:  0x01000445.0

col  0: [2000]

 69 64 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20

 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20

 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20

 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20

注意到此處的fb上L轉換成N  N代表接下來的資料記錄在另外的資料塊上

根據nrid查出具體的資料塊

其中cc：2第二個列記錄的是行連結的地址

SQL> select

  2  to_number('1000445','xxxxxxxxxx') rowid_tonu,

  3  dbms_utility.data_block_address_file(to_number('1000445','xxxxxxxxxx')) file#,

  4  dbms_utility.data_block_address_block(to_number('1000445','xxxxxxxxxx')) blcok#

  5  from dual;

 

Dump該資料塊會發現，fb上p表示第一列的值來自上個資料塊的連線。cc的值是4也就是說1092資料塊中記錄的連結rowid指定的該塊記錄了剩下的四列的資料

ROWID_TONU      FILE#     BLCOK#

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

  16778309          4       1093

data_block_dump,data header at 0xdc1367c

===============

tsiz: 0x1f80

hsiz: 0x14

pbl: 0x0dc1367c

bdba: 0x01000445

     76543210

flag=--------

ntab=1

nrow=1

frre=-1

fsbo=0x14

fseo=0x65

avsp=0x51

tosp=0x51

0xe:pti[0]      nrow=1  offs=0

0x12:pri[0]     offs=0x65

block_row_dump:

tab 0, row 0, @0x65

tl: 7963 fb: -----LP- lb: 0x1  cc: 4

檢查行遷移和行連結

為了方便檢查行遷移和行連結資訊，oracle提供了一個指令碼：utlchain.sql，該指令碼在$ORACLE_HOME/rdbms/admin中，執行該指令碼生成chained_rows表，通過analyze table tname list chained rows into chained_rows可以逐個的分析表。

SQL> @?/rdbms/admin/utlchain.sql

Table created.

 

SQL> analyze table test list chained rows into chained_rows;

Table analyzed.

 

查詢表的行遷移或行連結的次數

SQL> select table_name,count(*) from chained_rows group by table_name;

TABLE_NAME        COUNT(*)

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

TEST                     1

 

通過v$sysstat檢視查詢行遷移和行連結的總數

SQL> select name,value from v$sysstat where name='table fetch continued row';

 

NAME                                VALUE

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

table fetch continued row            5044

清除行遷移和行連結

1、借用chained_rows表清除

/*如果不是第一次建立chained_rows為免以前的分析資料影響操作，記得對改變truncate該表*,上面演示用的test表因為每行的資料都超過了db_block_size的限制，所以每行資料都會肯定有行遷移，不適合做該測試/

 

  1  create table test2 (

  2  id char(2000),

  3  test2 char(2000))

  4  pctfree 30

  5  pctused 40

  6* tablespace users

SQL> /

Table created.

 

SQL> @?/rdbms/admin/utlchain.sql

Table created.

 

SQL> analyze table test list chained rows into chained_rows;

Table analyzed.

 

SQL> select table_name,count(*) from chained_rows group by table_name;

TABLE_NAME        COUNT(*)

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

TEST                    19

 

SQL> create table test_emp 

  2  as

  3  select * from test

  4  where rowid in(

  5  select head_rowid from chained_rows where table_name='TEST')

  6  /

Table created.

 

SQL> delete from test

  2  where rowid in(

  3  select head_rowid from chained_rows where table_name='TEST')

  4  /

8 rows deleted.

 

SQL> commit;

Commit complete.

 

SQL> insert into test select * from test_emp;

8 rows created.

 

SQL> commit;

Commit complete.

 

SQL> analyze table test list chained rows into chained_rows;

Table analyzed.

 

SQL> select table_name,count(*) from chained_rows group by table_name;

TABLE_NAME        COUNT(*)

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

TEST                    0

2、通過move移動表消除行遷移

SQL> alter table test2 move;

Table altered.

或者

SQL> alter table test2 move tablespace tts_test;

Table altered.

3、使用exp/imp或者expd/impd工具匯出匯入表

避免行遷移和行連結

1、適當的增大db_cache_size的值，即增大block的容量，這樣單個block容納更多的資料，可以有效減少行連線和行遷移數量，但是該引數最好在資料庫安裝時設定，並且要是自己系統的block的整數倍。

2、設定合適的pctfree和pctused能有效的減少行遷移和行連結的次數，比如如果對錶的update操作較為頻繁，而且每次變更的資料所佔空間相差較大，可以考慮將pctfree設定的大點，預留出更多的空間。pctused的設定更多的是考慮對塊的利用率上，如果delete和insert較為頻繁，並且系統所有空間有限，為了避免浪費空間，可以將pctused的值設定大點，反之，如果考慮到insert的效率，可以將該值設定小一些。給出的建議公式如下：

PCTFREE = 100 × upd / (average row length)

PCTUSED = 100 – PCTFREE – 100 × rows × (average row length) / blocksize

upd ： the average amount added by updates, in bytes。This is determined by subtracting the average row length of intercurrent average row length

average row length：在執行了analyize命令之後，這個值可以從dba_tables中的avg_row_len列中獲得。
    rows ： the number of rows to be deleted before free list maintenance occurs。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29320885/viewspace-1241573/，如需轉載，請註明出處，否則將追究法律責任。

相關文章