模拟并消除行迁移(Row Migration)

最新推荐文章于 2022-06-30 22:24:41 发布

blockheading

最新推荐文章于 2022-06-30 22:24:41 发布

阅读量301

点赞数

分类专栏： Oracle Performance 文章标签：迁移优化

本文链接：https://blog.csdn.net/u013169075/article/details/72297557

版权

Oracle Performance 专栏收录该内容

30 篇文章 0 订阅

订阅专栏

--什么是行迁移？
A situation in which Oracle Database moves a row from one data block to another data block because the row grows too large to fit in the original block.

注：尽管行迁移与行链接是两个不同的事情，但是在oracle内部，它们被当作一回事。所以当你检测行迁移与行链接时，你应该仔细的分析当前你正在处理的是行迁移还是行链接。

--模拟实验
EODA@PROD1> create table t(x int, y varchar2(50));

Table created.

EODA@PROD1> begin
  2  for i in 1..1000 loop
  3    insert into t values (i,'A');
  4  end loop;
  5  commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

EODA@PROD1> analyze table t compute statistics;

Table analyzed.

--chain_cnt计算了存在行迁移和行链接的行数
EODA@PROD1> select table_name,num_rows,chain_cnt from user_tables where table_name='T';

TABLE_NAME			 NUM_ROWS  CHAIN_CNT
------------------------------ ---------- ----------
T				     1000	   0

EODA@PROD1> update t set y=rpad('A',50,'B');

1000 rows updated.

EODA@PROD1> commit;

Commit complete.

EODA@PROD1> analyze table t compute statistics;

Table analyzed.

EODA@PROD1> select table_name,num_rows,chain_cnt from user_tables where table_name='T';

TABLE_NAME			 NUM_ROWS  CHAIN_CNT
------------------------------ ---------- ----------
T				     1000	 967

--使用脚本创建CHAINED_ROWS表
EODA@PROD1> @?/rdbms/admin/utlchain

Table created.

EODA@PROD1> desc chained_rows
 Name						       Null?	Type
 ----------------------------------------------------- -------- ------------------------------------
 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

EODA@PROD1> analyze table t list chained rows into chained_rows;  --自动分析并存入CHAINED_ROWS表

Table analyzed.

EODA@PROD1> select count(*) from chained_rows;

  COUNT(*)
----------
       967

--通过重新插入解决行迁移
EODA@PROD1> create table tmp as select * from t where rowid in (select head_rowid from chained_rows);

Table created.

EODA@PROD1> delete t where rowid in (select head_rowid from chained_rows);

967 rows deleted.

EODA@PROD1> insert into t select * from tmp;

967 rows created.

EODA@PROD1> commit;

Commit complete.

EODA@PROD1> analyze table t compute statistics;

Table analyzed.

EODA@PROD1> select table_name,num_rows,chain_cnt from user_tables where table_name='T';

TABLE_NAME			 NUM_ROWS  CHAIN_CNT
------------------------------ ---------- ----------
T				     1000	   0


EODA@PROD1> drop table tmp purge;

Table dropped.