简单了解 oracle update 原理（测试）、 行迁移/行链接基本认识

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28602568/viewspace-761340/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28602568/viewspace-761340/