你的位置:首页 > 数据库

[数据库]Oracle行内链接不会引起USER_TABLES中CHAIN_CNT值变化


    前几天和群里网友讨论一个关于行内链接(intra-block chaining)的问题,问题非常有意思,恰好今天有空,顺便整理了一下这些知识点。

 

    问题描述:下面SQL,创建一个超过255列的表(实际为256列),然后插入几条数据,然后对表做ANALYZE分析过后,但是发现user_tables的CHAIN_CNT字段值为0,chained_rows表中没有记录,为什么会这样

declare
v_sql varchar2(32767) ;
begin
v_sql := 'create table t_chain1 ( ' ;
for i in 1..256 loop
v_sql := v_sql || 'id'||i||' number,' ;
end loop ;
v_sql := rtrim(v_sql, ',') || ')';
execute immediate v_sql;
end ;
/
 
insert into t_chain1(id256) values(1);
insert into t_chain1(id256) values(2);
insert into t_chain1(id256) values(3);
commit;
 
 
 
analyze table t_chain1 list chained rows;
analyze table t_chain1 compute statistics;
 
 
 
SQL> select table_name, num_rows, chain_cnt, avg_row_len from user_tables
  2  where table_name='T_CHAIN1';
 
TABLE_NAME                       NUM_ROWS  CHAIN_CNT AVG_ROW_LEN
------------------------------ ---------- ---------- -----------
T_CHAIN1                                3          0         267
 
SQL> select * from chained_rows;
 
no rows selected


clip_image001

 

在分析这个问题前,我们要先了解一下Oracle数据库当中的Row Migration (行迁移) & Row Chaining (行链接)概念:

    当表中一行的数据不能在一个数据block中放入的时候,这个时候就会发生两种情况,一种是行链接(Row Chaining),另外一种就是行迁移(Row Migration)了。

   行链接产生在第一次插入数据的时候如果一个block不能存放一行记录的情况下。这种情况下,Oracle将使用链接一个或者多个在这个段中保留的block存储这一行记录,行链接比较容易发生在比较大的行上,例如行上有LONG、LONG RAW、LOB等数据类型的字段,这种时候行链接是不可避免的会产生的。

   当一行记录初始插入的时候事可以存储在一个block中的,由于更新操作导致行长增加了,而block的自由空间已经完全满了,这个时候就产生了行迁移。在这种情况下,Oracle将会迁移整行数据到一个新的block中(假设一个block中可以存储下整行数据),Oracle会保留被迁移行的原始指针指向新的存放行数据的block,这就意味着被迁移行的ROW ID是不会改变的。

 

当发生了行迁移或者行链接,对这行数据操作的性能就会降低,因为Oracle必须要扫描更多的block来获得这行的信息

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.

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.

 

那么现在回到这个问题,我们先来看看表t_chain1的rowid,以及对应的文件号等信息:

select dbms_rowid.rowid_object(rowid)       obj#  ,
       dbms_rowid.rowid_relative_fno(rowid) rfile#,
       dbms_rowid.rowid_block_number(rowid) block#,
       dbms_rowid.rowid_row_number(rowid)   row#
from t_chain1 ;


clip_image002

 

我们看到这三条记录对应的行数据在BLOCK中的相对位置为1,3,5,那么说明当表的字段个数超过255时,是发生了行内链接的,关于这个,我们继续回顾一下行片段(row pieces)和行内链接(intra-block chaining)等概念

Row Format and Size

Oracle stores each row of a database table containing data for less than 256 columns as one or more row pieces. If an entire row can be inserted into a single data block, then Oracle stores the row as one row piece. However, if all of a row's data cannot be inserted into a single data block or if an update to an existing row causes the row to outgrow its data block, then Oracle stores the row using multiple row pieces. A data block usually contains only one row piece for each row. When Oracle must store a row in more than one row piece, it is chained across multiple blocks.

When a table has more than 255 columns, rows that have data after the 255th column are likely to be chained within the same block. This is called intra-block chaining. A chained row's pieces are chained together using the rowids of the pieces. With intra-block chaining, users receive all the data in the same block. If the row fits in the block, users do not see an effect in I/O performance, because no extra I/O operation is required to retrieve the rest of the row.

Each row piece, chained or unchained, contains a row header and data for all or some of the row's columns. Individual columns can also span row pieces and, consequently, data blocks. Figure 5-3 shows the format of a row piece:

clip_image003

 

这里面介绍了行内链接(intra-block chaining)概念,当一个表的列超过255列,ORACLE会把行记录分成两个或多个行片段(row piece),一个row piece包含255个字段,如果表中有312个字段,那么就会有三个行片段(row piece), 行内链接(intra-block chaining)只是多个行片段(row piece)通过rowid串联起来,这也是上面测试案例,你看到的对应rowid返回该行数据在BLOCK中的相对位置对应是1、3、5 ,而不是1、2、3的原因,因为行内链接(intra-block chaining)发生的同一个块内(block),所以它并不会产生额外的IO操作,也就是说不影响IO(当然这个要看你如何理解)。那么我使用alter system dump 来看看行在块里面的信息吧

clip_image004

 

去$ORACLE_BASE下面的udmp找到对应的trc文件,我实验中生成的文件为scm2_ora_20850.trc

clip_image005

clip_image006

cc:表示列数,fb:H是指行记录的头,L是指行记录的最后一列,F是指行记录的第一列. 实验结果跟理论是一致的。到这里似乎一直没有回到我们的问题来,那么我们先来看看官方文档对AVG_ROW_LEN的解释:

 

Number of rows in the table that are chained from one data block to another, or which have migrated to a new block, requiring a link to preserve the old ROWID

注意我标记为红色的部分,显然AVG_ROW_LEN记录的是发生了行链接或行迁移的行数,要么是数据从一个block迁移到另外一个block,要么是数据从一个block链接到另外一个block。而行内链接(intra-block chaining)是发生在同一个block内的,所以这里实验产生的行内链接并不会记录到AVG_ROW_LEN里面,所以这就解释了AVG_ROW_LEN为0,chained_rows没有记录的原因。

下面我们来构造一个行链接的案例,如下所示,新建表t_chain,使其一行的记录无法插入到一个block里面,那么当插入的时候,就会产生行链接,此时对表做ANALYZE分析过后,但是发现user_tables的CHAIN_CNT字段值不为0了,chained_rows表中也会有相关记录

declare
v_sql varchar2(32767) ;
begin
v_sql := 'create table t_chain ( ' ;
for i in 1..256 loop
v_sql := v_sql || 'id'||i||' char(36),' ;
end loop ;
v_sql := rtrim(v_sql, ',') || ')';
execute immediate v_sql;
end ;
/
 
declare
v_sql varchar2(32767) ;
begin
v_sql := 'insert into t_chain select ' ;
for i in 1..255 loop
v_sql := v_sql || '''it is only test'',' ;
end loop ;
v_sql := v_sql || '''it is only test'' from dual; commit;';
dbms_output.put_line( v_sql); --将生成的脚本执行2次
 
end ;
/
 
 
SQL> analyze table t_chain list chained rows;
 
Table analyzed.
 
SQL> analyze table t_chain compute statistics;
 
Table analyzed.
 
SQL>  select table_name, num_rows, chain_cnt, avg_row_len from user_tables
  2  where table_name='T_CHAIN' ;
 
TABLE_NAME                       NUM_ROWS  CHAIN_CNT AVG_ROW_LEN
------------------------------ ---------- ---------- -----------
T_CHAIN                                 2          2        9481
 
SQL> select count(1) from chained_rows;
 
  COUNT(1)
----------
         2
 
SQL> select * from chained_rows;
 
OWNER_NAME   TABLE_NAME   CLUSTER_NAME   PARTITION_NAME  SUBPARTITION_NAME   HEAD_ROWID      ANALYZE_T
----------- ------------ --------------- -------------- ----------------- ------------------ ---------
SYS             T_CHAIN                                       N/A          ACOhqAABAAAVMLAAA 10-JUL-16
SYS             T_CHAIN                                       N/A          AACOhqAABAAAVMNAAA 10-JUL-16
 
SQL> 


clip_image007

 

 

参考资料:

https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2105.htm#REFRN20286

http://docs.oracle.com/cd/B19306_01/server.102/b14220/schema.htm#i4383

http://docs.oracle.com/cd/B28359_01/server.111/b28318/schema.htm#CNCPT1129