update 原理：

一說起update很多人第一感覺就是delete+insert來實現的包括我也是，後續看了些資料也做了相關實驗在此整理下

行 update(更新)操作時， server process會將整個行遷移到 free space或相鄰的block，編號還是源編號，查詢的還是源塊，原始資料塊僅僅是一個轉移地址來指向新塊的一個地址資訊且原update剩餘空間不再被資料庫使用(這個過程稱為行遷移)

oracle中block/ extent 分配/擴充最小單位：

oracle每次擴充的block要是連續的，extent包含塊，extent可以不連續，不允許跨檔案，而多個extent，segment可以跨檔案（快取最小單位block快取; 擴充的最小單位是extent ,表和索引都是以一個segment）。

row migration：when a row is to be updated and it cannot find the necessary free space in its block, the Oracle will move the entire row into a new block and leave a pointer from the orginal block to the new location. This process is called row migration.

row chain：When a row is too large to fit into any block, row chaining occurs. In this case, the Oracle devide the row into smaller chunks. each chunk is stored in a block along with the necessary poiters to retrive and assemble the entire row.

行連結：當一行資料太大而不能在一個單資料塊容納也就是必須要分兩個塊存放時，行連結由此產生。（insert操作）包含long, long row, lob等型別的資料時容易產生行連結。其次表上多於255列時Oracle會將這些過寬的表分片而產生行連結

-----&gt 當一行的資料過長而不能插入一個單個資料塊中時，可能發生兩種事情：行連結(row chaining)或行遷移(row migration)。

當修改不是行連結的行時，當修改後的行長度大於修改前的行長度，並且該資料塊中的空閒空間已經比較小而不能完全容納該行的資料時都會發生行遷移

update剩餘空間稱之為空洞也是產生表碎片的主要原因，表碎片基本上也是不可避免的，但是我們可以將其降到一個我們可以接受的程度。

----&gt 行連結和行遷移引起資料庫效能下降的原因：

由於引起多餘的I/O造成的。當透過索引訪問已有行遷移現象的行時，資料庫必須掃描一個以上的資料塊才能檢索到改行的資料。這主要有一下兩種表現形式：

1) row migration 或row chaining 導致 INSERT 或 UPDATE語句的效能比較差，因為它們需要執行額外的處理

2) 利用索引查詢已經連結或遷移的行的select語句效能比較差，因為它們要執行額外的I/O

遷移行對索引讀產生額外的I/O，對全表掃描沒什麼影響(當對一個表進行全表掃描時，我們實際上忽略行遷移中各個指向其它行的指標，因為我們知道，全表掃描會遍歷全表（表下對應的所有塊），最終會讀到發生行遷移的行的行資料，在此時才會處理這些行資料。因此，在全表掃描中，行遷移不會引發其它額外的工作

當透過索引讀一個表的資料時，被遷移的行會引起額外的I/O操作。這是因為從所引中我們會讀到資料行的rowid，它告訴資料庫到指定檔案的指定資料塊的指定slot上可以找到需要的資料，但是因為發生了行遷移，此處只存放一個指向資料的指標，而不是真正的資料，所以資料庫又需要根據該指標(類似rowid)到指定檔案的指定資料塊的指定slot上去找真正的資料，重複上面的過程，知道找到真正的資料。我們可以看出，這會引入額外的I/O操作。）

行連結則影響索引讀和全表掃描

讀一行要讀兩個塊，也就是要兩次邏輯讀。

① 先驗證update 是否是delete+insert

1）update和delete+insert哪個操作產生的redo量大

2）update、delete+insert的rowid改變情況

② 判斷行是否產生了行連結/行遷移的幾種方式

1）v$sysstat檢視. 'table fetch continued row'

2）CHAINED ROWS;

3) dump trace檔案的nrid:關鍵字

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

1）透過比較update產生的redo量和delete+insert 產生的redo量來判斷update機制：

SQL> create table t as select * from dba_tables;

表已建立。

SQL> select a.VALUE from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.name='re

do size';

VALUE

----------

6712984

SQL> delete from t where t.owner='SYS';

已刪除967行。

SQL> commit;

提交完成。

SQL> select a.VALUE from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.name='re

do size';

VALUE

----------

7320028

SQL> select 7320028- 6712984 from dual;

7320028-6712984

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

607044 ----&gt delete產生的redo

SQL> create table v as select * from dba_tables;

表已建立。

SQL> select a.VALUE from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.name='re

do size';

VALUE

----------

8473596

SQL> update v set wner='yss' where wner='SYS';

已更新967行。

SQL> select a.VALUE from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.name='re

do size';

VALUE

----------

8564236

SQL> select 8564236-8473596 from dual;

8564236-8473596

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

90640 ----&gt update產生的redo

SQL>可以看出update語句產生的redo遠遠小於delete的語句更何況是delete+insert2個操作

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

2） update、delete+insert的rowid改變情況：

ROWID：記錄物理位置ID的唯一標誌

1. 資料物件編號：每個資料物件（如表或索引）在建立時都分配有此編號，並且此編號在資料庫中是唯一的

2. 相關檔案編號：此編號對於表空間中的每個檔案是唯一的

3. 塊編號：表示包含此行的塊在檔案中的位置

4. 行編號：標識塊頭中行目錄位置的位置

SQL> conn sc1/sc1

已連線。

SQL> create table test1 (id number(10));

表已建立。

SQL> insert into test1 values(1);

已建立 1 行。

SQL> select test1.*,rowid from test1;

ID ROWID

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

1 AAAbB2AAFAAAAC9AAA

SQL> update test1 set id=2;

已更新 1 行。

SQL> select test1.*,rowid from test1;

ID ROWID

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

2 AAAbB2AAFAAAAC9AAA

SQL> delete from test1;

已刪除 1 行。

SQL> select test1.*,rowid from test1;

未選定行

SQL> insert into test1 values(1);

已建立 1 行。

SQL> select test1.*,rowid from test1;

ID ROWID

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

1 AAAbB2AAFAAAAC9AAB

SQL>

可以看出update時rowid在並沒有改變，delete時rowid發生了改變，-----&gt 行遷移

發生了行遷移的行的rowid還是不會變化，這也是行遷移會引起資料庫I/O效能降低的原因。

其實行遷移是行連結的一種特殊形式，但是它的起因與行為跟行連結有很大不同，所以一般把它從行連結中獨立出來，單獨進行處理。

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

3）判斷行會不會產生了行連結或者行遷移 nrid值表現出來

先檢視ALLAN這個表空間的資料檔案號，為了便於測試，我只建立了一個資料檔案。

SQL> select file_id from dba_data_files where tablespace_name='ALLAN';

FILE_ID

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

23

建立一個測試表test：

SQL> create table test ( x int primary key, a char(2000), b char(2000), c char(2000), d char(2000), e char(2000) ) tablespace allan;

Table created.

因為我的資料庫的db_block_size是8K，所以我建立的表有五個欄位，每個佔2000個位元組，這樣一行記錄大約10K,就能超過一個block的大小了。

然後插入一行記錄，只有一個欄位的：

SQL> insert into test(x) values (1);

1 row created.

SQL> commit;

Commit complete.

查詢這行記錄所在的block，並dump出來：

SQL> select dbms_rowid.rowid_block_number(rowid) from test;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)

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

34

SQL> alter system dump datafile 23 block 34;

System altered.

在udump目錄下檢視trace檔案的內容如下：

Start dump data blocks tsn: 34 file#: 23 minblk 34 maxblk 34

buffer tsn: 34 rdba: 0x05c00022 (23/34)

scn: 0x0000.013943f3 seq: 0x01 flg: 0x02 tail: 0x43f30601

frmt: 0x02 chkval: 0x0000 type: 0x06=trans data

Block header dump: 0x05c00022

Object id on Block? Y

seg/obj: 0x3ccd csc: 0x00.13943ef itc: 2 flg: O typ: 1 - DATA

fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x000a.02e.00000ad7 0x00800036.03de.18 --U- 1 fsc 0x0000.013943f3

0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

data_block_dump,data header at 0xadb505c

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

tsiz: 0x1fa0

hsiz: 0x14

pbl: 0x0adb505c

bdba: 0x05c00022

76543210

flag=--------

ntab=1

nrow=1

frre=-1

fsbo=0x14

fseo=0x1f9a

avsp=0x1f83

tosp=0x1f83

0xeti[0] nrow=1 ffs=0

0x12ri[0] ffs=0x1f9a

block_row_dump:

tab 0, row 0, @0x1f9a

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

col 0: [ 2] c1 02

end_of_block_dump

End dump data blocks tsn: 34 file#: 23 minblk 34 maxblk 34

對其中的一些資訊做一些解釋：

Fb:H是指行記錄的頭，L是指行記錄的最後一列，F是指行記錄的第一列。

Cc：列的數量

Nrid：對於行連結或者行遷移來說的下一個row id的值

由上面的dump資訊我們可以看出來當前表test是沒有行連結或者行遷移的。

然後更新test表,並重新dump出來：

SQL> update test set a='test',b='test',c='test',d='test',e='test' where x=1;

1 row updated.

SQL> commit;

Commit complete.

此時應該會產生行遷移了。

SQL> alter system dump datafile 23 block 34;

System altered.

在udump目錄下檢視trace檔案的內容如下：

Start dump data blocks tsn: 34 file#: 23 minblk 34 maxblk 34

buffer tsn: 34 rdba: 0x05c00022 (23/34)

scn: 0x0000.0139442b seq: 0x01 flg: 0x02 tail: 0x442b0601

frmt: 0x02 chkval: 0x0000 type: 0x06=trans data

Block header dump: 0x05c00022

Object id on Block? Y

seg/obj: 0x3ccd csc: 0x00.1394429 itc: 2 flg: - typ: 1 - DATA

fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x000a.02e.00000ad7 0x00800036.03de.18 C--- 0 scn 0x0000.013943f3

0x02 0x0004.002.00000ae0 0x0080003b.0441.11 --U- 1 fsc 0x0000.0139442b

data_block_dump,data header at 0xadb505c

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

tsiz: 0x1fa0

hsiz: 0x14

pbl: 0x0adb505c

bdba: 0x05c00022

76543210

flag=--------

ntab=1

nrow=1

frre=-1

fsbo=0x14

fseo=0x178a

avsp=0x177c

tosp=0x177c

0xeti[0] nrow=1 ffs=0

0x12ri[0] ffs=0x178a

block_row_dump:

tab 0, row 0, @0x178a

tl: 2064 fb: --H-F--N lb: 0x2 cc: 3

nrid: 0x05c00023.0[/COLOR]

col 0: [ 2] c1 02

col 1: [2000]

74 65 73 74 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

…………

col 2: [48]

74 65 73 74 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

end_of_block_dump

End dump data blocks tsn: 34 file#: 23 minblk 34 maxblk 34

nrid指向了下一個row id,證明剛剛的update操作使這行記錄產生了行連結或者行遷移了。

行遷移主要是由於設定的PCTFREE引數過小

（Pctfree：塊中保留用於UPDATE操作的空間百分比，當資料佔用的空間達到此上限時，新的資料將不能插入此塊中；Pctused：制定塊中資料使用空間的最低百分比）、（pctfree 控制留給更新,pctused 控制再次插入的）

導致沒有給update操作留下足夠的空閒空間引起。為了避免行遷移，所有被修改的表應該設定合適的PCTFREE 值，以便在每個資料塊內為資料修改保留足夠的空間。可以透過增加PCTFREE值的辦法來避免行遷移，但這種解決辦法是以犧牲更多的空間為代價的，這也就是我們通常所說的以空間換效率。而且透過增加PCTFREE值的辦法只能緩解行遷移現象，而不能完全解決行遷移，所以較好的辦法是在設定了合適的PCTFREE值的後，在發現行遷移現象比較嚴重時，對錶的資料進行重組。

更詳細的行遷移/行連結(原理/測試)資料

1）v$sysstat檢視. 'table fetch continued row'

2）CHAINED ROW

http://blog.csdn.net/robinson_0612/article/details/7266719

簡單瞭解 oracle update 原理（測試）、行遷移/行連結基本認識

相關文章

簡單瞭解 oracle update 原理（測試）、 行遷移/行連結基本認識

相關文章

簡單瞭解 oracle update 原理（測試）、行遷移/行連結基本認識