Oracle行迁移实验

最新推荐文章于 2021-04-14 14:54:45 发布

转载最新推荐文章于 2021-04-14 14:54:45 发布 · 92 阅读

作为ORACLE DBA，您应该定期对数据做体检，今天给大家介绍如何解决Oracle 行迁移和行链接技术。
在什么情况下会出现行迁移，在对表进行频繁update时会产生行迁移。行链接出现一般是由于数据表
设计不合理造成的，
注：
行迁移：频繁update的表会产生，要注意！
行链接：表设计不合理会产生。
我们通过实验理解一下：
该实验的目的是详细理解表的存储。
行迁移的形成：由update造成的。
当行长增加的时候，本数据块没有足够的空闲空间。导致该行被迫存储到其它数据块，在原数据块保留访问的指针。
当数据库访问该行时，要进行二次io。导致数据库的性能下降。
-----------------------------------------实验------------------------------------------------------
SQL>conn scott/tiger
SQL>drop table MG_CHAIN purge;
SQL> create table MG_CHAIN(name varchar2(30));
SQL> alter table MG_CHAIN pctfree 0;--使表中的数据块都存放数据。不预留空间
SQL>begin-- 制造8000行数据。
   for i in 1 .. 8000 loop
      insert into MG_CHAIN values ('abcdef');
   end loop;
end;
/
SQL>commit;
SQL> ANALYZE TABLE t1 COMPUTE STATISTICS;--分析表可以得到表的行迁移信息，通过user_tables中的列CHAIN_CNT来获得。
SQL> select TABLE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN
    from user_tables where table_name='MG_CHAIN';
注意查看结果，CHAIN_CNT如果为0说明没有迁移的行。
SQL>update MG_CHAIN set name='qwertyuiopasdfghjklzxcvbnm26';--更新为26个字母。字段的长度变长了。
SQL> ANALYZE TABLE MG_CHAIN COMPUTE STATISTICS;
SQL>select TABLE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN
    from user_tables where table_name='MG_CHAIN';
注意查看结果，CHAIN_CNT应该有8000左右，说明行发生了迁移。BLOCKS的数量比上次增加了很多。
--------------------------------我们通过上面的方法制造了行迁移，并通过分析表的方式找到了行迁移-----------------------------
下面我们来消除行迁移。
1.移动表
2.给表做外科手术方式来消除迁移的行
3.Exp/imp通过导入导出的方式

重点讲第2种
1.移动表方式非常简单
SQL> alter table MG_CHAIN move tablespace users;
SQL> ANALYZE TABLE MG_CHAIN COMPUTE STATISTICS;
SQL>select TABLE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN
    from user_tables where table_name='MG_CHAIN';
我们消除了迁移的行,表BLOCKS块,下降了,AVG_ROW_LEN平均行长下降了.CHAIN_CNT变0了。
2.给表做外科手术方式来消除迁移的行
SQL>update MG_CHAIN set name='qwertyuiopasdfghjklzxcvbnm26';--在次修改更新为26个字母。制造行迁移。
SQL> ANALYZE TABLE MG_CHAIN COMPUTE STATISTICS;
SQL>select TABLE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN
    from user_tables where table_name='MG_CHAIN';--行迁移又产生了。
SQL> @%oracle_home%\rdbms\admin\utlchain.sql--我们执行一个脚本，来记录行迁移信息，这个脚本其实很简单，只是一个简单的表，大家可以
打开看看。该表的功能只是收集记录行迁移信息。
SQL> 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
SQL> ANALYZE TABLE MG_CHAIN list chained rows;--执行这条语句，就会把行迁移的信息记录到上面新建的表CHAINED_ROWS中。
SQL> select * from CHAINED_ROWS;

SQL> create table temp_chain as select * from MG_CHAIN where rowid in(select HEAD_ROWID from CHAINED_ROWS);--temp_chain 表中临时存储被迁移的行。
SQL> delete MG_CHAIN where rowid in(select HEAD_ROWID from CHAINED_ROWS);--删除所有迁移的行
SQL> insert into MG_CHAIN select * from temp_chain ;--再将被删除的行插入到原来的表中
SQL> commit;

SQL> ANALYZE TABLE MG_CHAIN COMPUTE STATISTICS;--再次分析下表

SQL>select TABLE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN
from user_tables where table_name='MG_CHAIN';
外科手术式的消除迁移的行,因为insert不产生迁移,update才会发生迁移。
3.Exp/imp通过导入导出的方式
使用EXP先把表导出，再使用IMP导入。
其实就实现了先把表数据删除了，再插入到表中。

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

转载于:http://blog.itpub.net/12798004/viewspace-1144874/