行链接和行迁移

发布时间：2020-08-09 12:00:39 作者：张冲andy
来源：ITPUB博客阅读：166

一、概述：
  如果你的Oracle数据库性能低下，行链接和行迁移可能是其中的原因之一。我们能够通过合理的设计或调整数据库来阻止这个现象。

  行链接和行迁移是能够被避免的两个潜在性问题。我们可以通过合理的调整来提高数据库性能。本文主要描述的是：
    什么是行迁移与行链接
    如何判断行迁移与行链接
    如何避免行迁移与行链接

  当使用索引读取单行时，行迁移影响OLTP系统。最糟糕的情形是，对所有读取操作而言，增加了额外的I/O。行链接则影响索引读和全表扫描。

  注：在翻译行(row)时使用记录来描述(便于理解)，如第一行，使用第一条记录。

二、Oralce 块
    操作系统块的大小是操作系统读写的最小操作单元，也是操作系统文件的属性之一。当创建一个数据库时，选择一个基于操作系统块的
  整数倍大小作为Oracle数据库块的大小。Oracle数据库读写操作则是以Oracle块为最小单位，而非操作系统块。一旦设置了Oracle数据块的大小，
  则在整个数据库生命期间不能被更改(除 Oracle 9i之外)。因此为Oracle数据库定制合理的Oralce块大小，象预期数据库总大小以及并发用户数这些
  因素应当予以考虑。
  数据库块由下列逻辑结构(在整个数据库结构下)

SELECT x,d,e FROM row_mig_chain_demo WHERE x = 3;
SELECT a.name, b.value
FROM v$statname a, v$mystat b
WHERE a.statistic# = b.statistic#
AND lower(a.name) = 'table fetch continued row';
NAME VALUE
---------------------------------------------------------------- ----------
table fetch continued row 2
--现在当我们通过主键索引扫描从记录3的尾部提取数据时，这将增加table fetch continued row的值。因为需要从行的头部和尾部获取数据来组合。
--现在来看看全表扫描是否也有相同的影响。
SELECT * FROM row_mig_chain_demo;
X
----------
3
2
1
SELECT a.name, b.value
FROM v$statname a, v$mystat b
WHERE a.statistic# = b.statistic#
AND lower(a.name) = 'table fetch continued row';
NAME VALUE
---------------------------------------------------------------- ----------
table fetch continued row 3
--此时table fetch continued row的值被增加，因为不得不对记录3的尾部进行融合。而记录1和2即便是存在迁移现象，但由于是全表扫描，
--因此不会增加table fetch continued row的值。
SELECT x,a FROM row_mig_chain_demo;
X
----------
3
2
1
SELECT a.name, b.value
FROM v$statname a, v$mystat b
WHERE a.statistic# = b.statistic#
AND lower(a.name) = 'table fetch continued row';
NAME VALUE
---------------------------------------------------------------- ----------
table fetch continued row 3
--当需要提取的数据是整个表上的头两列的时候，此时table fetch continued row也不会增加。因为不需要对记录3进行数据融合。
SELECT x,e FROM row_mig_chain_demo;
X
----------
3
2
1
SELECT a.name, b.value
FROM v$statname a, v$mystat b
WHERE a.statistic# = b.statistic#
AND lower(a.name) = 'table fetch continued row';
NAME VALUE
---------------------------------------------------------------- ----------
table fetch continued row 4
--但是当提取列d和e的时候，table fetch continued row的值被增加。通常查询时容易产生行迁移即使是真正存在行链接，因为我们的查询
--所需的列通常位于表的前几列。

八、如何鉴别行链接和行迁移

--聚合统计所创建的表，这也将使得重构整行而发生table fetch continued row
SELECT count(e) FROM row_mig_chain_demo;
COUNT(E)
----------
1
SELECT a.name, b.value
FROM v$statname a, v$mystat b
WHERE a.statistic# = b.statistic#
AND lower(a.name) = 'table fetch continued row';
NAME VALUE
---------------------------------------------------------------- ----------
table fetch continued row 5
--通过analyze table来校验表上的链接数
ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;
SELECT chain_cnt
FROM user_tables
WHERE table_name = 'ROW_MIG_CHAIN_DEMO';
CHAIN_CNT
----------
3
--3条记录是链接的。显然，他们中的两条记录是迁移(记录1，记录2)和一记录是链接(记录3).
--实例启动后的table fetch continued row的总数
--视图v$mystat告诉我们自从实例启动后，所有的表上共有多少次为table fetch continued row.
sqlplus system/<password>
SELECT 'Chained or Migrated Rows = '||value
FROM v$sysstat
WHERE name = 'table fetch continued row';
Chained or Migrated Rows = 31637
--上面的查询结果表明，可能有1个表上存在行链接被fetch了31637次，也可能有31637个表，每个表上有一个行链接，每次fetch一次。也有
--可能是上述情况的组合。
--31637次也许是好的，也许是坏的，仅仅是一个值而已。
--这取决于
--数据库启动了多久？
--这个值占总提取数据百分比的多少行？
--假如它占据了你从表上fetch的0.001%，则无关紧要。
--因此，比较table fetch continued row与总提取的记录数是有必要的
SELECT name,value FROM v$sysstat WHERE name like '%table%';
NAME VALUE
---------------------------------------------------------------- ----------
table scans (short tables) 124338
table scans (long tables) 1485
table scans (rowid ranges) 0
table scans (cache partitions) 10
table scans (direct read) 0
table scan rows gotten 20164484
table scan blocks gotten 1658293
table fetch by rowid 1883112
table fetch continued row 31637
table lookup prefetch client count 0

九、一个表上链接的行是多少？

--通过对表analyze后(未analyze是空值),可以从数据字典user_tales获得链接的记录数。
ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;
SELECT chain_cnt,
round(chain_cnt/num_rows*100,2) pct_chained,
avg_row_len, pct_free , pct_used
FROM user_tables
WHERE table_name = 'ROW_MIG_CHAIN_DEMO';
CHAIN_CNT PCT_CHAINED AVG_ROW_LEN PCT_FREE PCT_USED
---------- ----------- ----------- ---------- ----------
3 100 3691 10 40
--PCT_CHAINED 为100%,表明所有的行都是链接的或迁移的。

十、列出链接行

  当使用analyze table中的list chained rows子句能够列出一个表上的链接行。该命令的结果是将所有的链接上存储到一个由list chained rows子句
  显示指定的表中。这些结构有助于决定是否将来有足够的空间实现行更新。

  创建CHAINED_ROWS 表
  创建一个用于存储analyze ... list chained rows命令结果的表，可以执行位于$ORACLE_HOME/rdbms/admin目录下的UTLCHAIN.SQL或UTLCHN1.SQL 脚本。
  这个脚本会在当前schema下创建一个名为chained_rows的表
    create table CHAINED_ROWS (
      owner_name         varchar2(30),
      table_name         varchar2(30),
      cluster_name       varchar2(30),
      partition_name     varchar2(30),
      subpartition_name varchar2(30),
      head_rowid         rowid,
      analyze_timestamp date
    );

当chained_rows表创建后，可以使用analyze table命令来指向该表作为输出。

十一、如何避免行链接和行迁移
      增加pctfree能够帮助避免行链接。如果我们为块留下更多的可用空间，则行上有空间满足将来的增长。也可以对那些有较高删除率的表采用重新组织
  或重建表索引来避免行链接与行迁移。如果表上有些行被频繁的删除，则数据块上会有更多的空闲空间。当被插入的行后续扩展，则被插入的行可能会
  分布到那些被删除的行上而仍然没有更多空间来用于扩展。重新组织表则确保主要的空闲空间是完整的空块。

      ALTER TABLE ... MOVE 命令允许对一个未分区或分区的表上的数据进行重新分配到一个新的段。也可以分配到一个有配额的不同的表空间。该命令也允许
  你在不能使用alter table的情形下来修改表或分区上的一些存储属性。也可以使用ALTER TABLE ... MOVE 命令中的compress关键字在存储到新段时使用压缩选项。

[sql] view plain copy

<code class="language-sql">1. ALTER TABLE MOVE
使用alter table move 之前首先统计每个块上的行数.
SELECT dbms_rowid.rowid_block_number(rowid) "Block-Nr", count(*) "Rows"
FROM row_mig_chain_demo
GROUP BY dbms_rowid.rowid_block_number(rowid) order by 1;
Block-Nr Rows
---------- ----------
2066 3
--现在消除表上的行链接，使用alter table move来重建row_mig_chain_demo表到一个新段，指定一些新的存储选项。
ALTER TABLE row_mig_chain_demo MOVE
PCTFREE 20
PCTUSED 40
STORAGE (INITIAL 20K
NEXT 40K
MINEXTENTS 2
MAXEXTENTS 20
PCTINCREASE 0);
Table altered.
--在alter table move之后再次统计每一块上的行数
SELECT dbms_rowid.rowid_block_number(rowid) "Block-Nr", count(*) "Rows"
FROM row_mig_chain_demo
GROUP BY dbms_rowid.rowid_block_number(rowid) order by 1;
Block-Nr Rows
---------- ----------
2322 1
2324 1
2325 1
2. 重建表上的索引
--移动一个表将使得表上记录的rowid发生变化。这将引起表上的索引被置为unusable状态。基于该表使用索引的DML语句将收到ORA-01502 错误。
--因此表上的索引必须被删除或重建。同样地，表上的统计信息也会变得无效。因此统计信息在表移动之后也应当重新收集。
ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;
ERROR at line 1:
ORA-01502: index 'SCOTT.SYS_C003228' or partition of such index is in unusable state
--表上的主键必须被重建
ALTER INDEX SYS_C003228 REBUILD;
Index altered.
ANALYZE TABLE row_mig_chain_demo COMPUTE STATISTICS;
Table analyzed.
SELECT chain_cnt,
round(chain_cnt/num_rows*100,2) pct_chained,
avg_row_len, pct_free , pct_used
FROM user_tables
WHERE table_name = 'ROW_MIG_CHAIN_DEMO';
CHAIN_CNT PCT_CHAINED AVG_ROW_LEN PCT_FREE PCT_USED
---------- ----------- ----------- ---------- ----------
1 33.33 3687 20 40
--如果表包含LOB 列，用户可以指定该命令连同LOB数据段以及LOB索引段(同该表相关)一起移动(move)。
--当未指定时，则LOB数据段以及LOB索引段不参与移动。
</code>

十二、检测所有表上的行连接与行迁移

可以通过CHAINED_ROWS 表获取所有表上的行链接与行迁移。

1.创建chained_rows表
cd $ORACLE_HOME/rdbms/admin
sqlplus scott/tiger
@utlchain.sql
2.ananlyze 所有表/或指定表
SELECT 'ANALYZE TABLE '||table_name||' LIST CHAINED ROWS INTO CHAINED_ROWS;'
FROM user_tables
/
ANALYZE TABLE ROW_MIG_CHAIN_DEMO LIST CHAINED ROWS INTO CHAINED_ROWS;
ANALYZE TABLE DEPT LIST CHAINED ROWS INTO CHAINED_ROWS;
ANALYZE TABLE EMP LIST CHAINED ROWS INTO CHAINED_ROWS;
ANALYZE TABLE BONUS LIST CHAINED ROWS INTO CHAINED_ROWS;
ANALYZE TABLE SALGRADE LIST CHAINED ROWS INTO CHAINED_ROWS;
ANALYZE TABLE DUMMY LIST CHAINED ROWS INTO CHAINED_ROWS;
Table analyzed.
3.查看行链接的rowid
SELECT owner_name,
table_name,
count(head_rowid) row_count
FROM chained_rows
GROUP BY owner_name,table_name
/
OWNER_NAME TABLE_NAME ROW_COUNT
------------------------------ ------------------------------ ----------
SCOTT ROW_MIG_CHAIN_DEMO 1

通过该方式可以快速的定位一个表上有多少行链接问题的。如果行链接或行迁移较多，则应当基于该表增加pctfree的值或重建该表。

十三、结论：
   行迁移影响OLTP系统使用索引读取单行。最糟糕的情形所对所有的读都增加额外的I/O。而行链接则影响索引读和全表扫描。
   行迁移通常由update操作引起
   行链接通常有insert操作引起
   基于行链接或行迁移的查询或创建(如索引)由于需要更多的I/O将降低数据库的性能
   调试行链接或行迁移使用analyze 命令，查询v$sysdate视图
   移出行链接或行迁移使用更大的pctfree参数或使用alter table move命令

十四、关于作者

原文链接： The Secrets of Oracle Row Chaining and Migration

Martin Zahn, Akadia AG, Information Technology, CH-3672 Oberdiessbach
EMail: martin dot zahn at akadia dot ch

12.09.2007: Updated for Oracle 10.2

行链接和行迁移

相关阅读