Oracle 行迁移和行链接

最新推荐文章于 2024-08-17 10:26:03 发布
最新推荐文章于 2024-08-17 10:26:03 发布
阅读量325 收藏
点赞数
原文链接：https://www.cnblogs.com/Richardzhu/p/3449243.html
版权

一、行迁移

1.1、行迁移概念

当一个行上的更新操作(原来的数据存在且没有减少)导致当前的数据不能在容纳在当前块，我们需要进行行迁移。一个行迁移意味着整行数据将会移动，仅仅保留的是一个转移地址。因此整行数据都被移动，原始的数据块上仅仅保留的是指向新块的一个地址信息。

成因：当行被update时，如果update更新的行大于数据块的pctfree值，就需要申请第2个块，从而形成迁移。

后果：导致应用需要访问更多的数据块，性能下降。

预防：1.将数据块的pctfree调大；

        2.针对表空间扩大数据块的大小。

检查：analyze table 表名 validate structure cascade into chained_rows;

2.1、实例：

实验说明：

(以EMPLOYEES表为例，如果涉及到该表有主键，并且有别的表的外键REFERENCE关联到本表，必须要执行步骤2和步骤7，否则不必执行)；

1．  执行$ORACLE_HOME/rdbms/admin目录下的utlchain.sql脚本创建chained_rows表。

2.   禁用所有其它表上关联到此表上的所有限制(假想EMPLOYEES表有主键PK_EMPLOYEES_ID，假想test表有外键f_employees_id关联reference到employees表）。      

select index_name,index_type,table_name from user_indexes where table_name='EMPLOYEES';  

select  CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from USER_CONSTRAINTS where R_CONSTRAINT_NAME='PK_EMPLOYEES_ID';  

alter table test disable constraint f_employees_id;

3．  将存在有行迁移的表（用table_name代替）中的产生行迁移的行的rowid放入到chained_rows表中。

4．  将表中的行迁移的row id放入临时表中保存。

5．  删除原来表中存在的行迁移的记录行。

6．  从临时表中取出并重新插入那些被删除了的数据到原来的表中，并删除临时表。

7．  启用所有其它表上关联到此表上的所有限制。     

alter table test enable constraint f_employees_id;

此外还可以采用move和exp/imp的方式（特别注意move会导致索引失效，需要重建索引）。

----创建实验表----
SQL> DROP TABLE EMPLOYEES PURGE;
DROP TABLE EMPLOYEES PURGE
           *
第 1 行出现错误:
ORA-00942: 表或视图不存在


SQL> CREATE TABLE EMPLOYEES AS SELECT * FROM HR.EMPLOYEES ;

表已创建。

SQL> desc EMPLOYEES;
 名称                       是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                        NUMBER(6)
 FIRST_NAME                        VARCHAR2(20)
 LAST_NAME                   NOT NULL VARCHAR2(25)
 EMAIL                       NOT NULL VARCHAR2(25)
 PHONE_NUMBER                        VARCHAR2(20)
 HIRE_DATE                   NOT NULL DATE
 JOB_ID                    NOT NULL VARCHAR2(10)
 SALARY                         NUMBER(8,2)
 COMMISSION_PCT                     NUMBER(2,2)
 MANAGER_ID                        NUMBER(6)
 DEPARTMENT_ID                        NUMBER(4)

SQL> create index idx_emp_id on employees(employee_id);

索引已创建。

---扩大字段----
SQL> alter table EMPLOYEES modify FIRST_NAME VARCHAR2(1000);

表已更改。

SQL> alter table EMPLOYEES modify LAST_NAME  VARCHAR2(1000);

表已更改。

SQL> alter table EMPLOYEES modify EMAIL VARCHAR2(1000);

表已更改。

SQL> alter table EMPLOYEES modify PHONE_NUMBER  VARCHAR2(1000);

表已更改。

SQL> desc employees;
 名称                       是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                        NUMBER(6)
 FIRST_NAME                        VARCHAR2(1000)
 LAST_NAME                   NOT NULL VARCHAR2(1000)
 EMAIL                       NOT NULL VARCHAR2(1000)
 PHONE_NUMBER                        VARCHAR2(1000)
 HIRE_DATE                   NOT NULL DATE
 JOB_ID                    NOT NULL VARCHAR2(10)
 SALARY                         NUMBER(8,2)
 COMMISSION_PCT                     NUMBER(2,2)
 MANAGER_ID                        NUMBER(6)
 DEPARTMENT_ID                        NUMBER(4)

----更新表----
SQL> UPDATE EMPLOYEES
  2    SET FIRST_NAME = LPAD('1', 1000, '*'), LAST_NAME = LPAD('1', 1000, '*'), EMAIL = LPAD('1', 1000, '*'),
  3    PHONE_NUMBER = LPAD('1', 1000, '*');

已更新107行。

SQL> commit;

提交完成。

----行迁移优化前，先看看该语句逻辑读情况(执行计划及代价都一样，没必要展现了，就展现statistics即可）----
SQL> set autotrace traceonly stat
SQL> set linesize 1000
SQL> select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES  where employee_id>0;

已选择107行。


统计信息
----------------------------------------------------------
    152  recursive calls
      0  db block gets
    310  consistent gets
      0  physical reads
      0  redo size
     437664  bytes sent via SQL*Net to client
    492  bytes received via SQL*Net from client
      9  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
    107  rows processed

SQL> select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES  where employee_id>0;

已选择107行。


统计信息
----------------------------------------------------------
      0  recursive calls
      0  db block gets
    219  consistent gets
      0  physical reads
      0  redo size
     437664  bytes sent via SQL*Net to client
    492  bytes received via SQL*Net from client
      9  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
    107  rows processed

SQL> set autotrace off

----- 发现存在行迁移的方法
--首先建chaind_rows相关表，这是必需的步骤
SQL> drop table chained_rows purge;
drop table chained_rows purge
           *
第 1 行出现错误:
ORA-00942: 表或视图不存在


SQL> @?/rdbms/admin/utlchain.sql

表已创建。
----以下命令针对EMPLOYEES表和EMPLOYEES_BK做分析，将产生行迁移的记录插入到chained_rows表中

SQL> analyze table EMPLOYEES list chained rows into chained_rows;

表已分析。

SQL> select count(*)  from chained_rows where table_name='EMPLOYEES';

  COUNT(*)
----------
       105
---以下方法可以去除行迁移
SQL> drop table EMPLOYEES_TMP;
drop table EMPLOYEES_TMP
           *
第 1 行出现错误:
ORA-00942: 表或视图不存在


SQL> create table EMPLOYEES_TMP as select * from EMPLOYEES where rowid in (select head_rowid from chained_rows);

表已创建。

SQL> Delete from EMPLOYEES where rowid in (select head_rowid from chained_rows);

已删除105行。

SQL> Insert into EMPLOYEES select * from EMPLOYEES_TMP;

已创建105行。

SQL> delete from chained_rows ;

已删除105行。

SQL> commit;

提交完成。

SQL> analyze table EMPLOYEES list chained rows into chained_rows;

表已分析。

SQL> select count(*)  from chained_rows where table_name='EMPLOYEES';

  COUNT(*)
----------
     0

--这时的取值一定为0，用这种方法做行迁移消除，肯定是没问题的！

---行迁移优化后，先看看该语句逻辑读情况(执行计划及代价都一样，没必要展现了，就展现statistics即可）
SET AUTOTRACE traceonly statistics
SQL> set linesize 1000
SQL> select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES  where employee_id>0;

已选择107行。


统计信息
----------------------------------------------------------
      0  recursive calls
      0  db block gets
    116  consistent gets
      0  physical reads
      0  redo size
     437034  bytes sent via SQL*Net to client
    492  bytes received via SQL*Net from client
      9  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
    107  rows processed

二、行链接

2.1、行链接概念

当一行数据太大而不能在一个单数据块容纳时，行链接由此产生。举例来说，当你使用了4kb的Oracle数据块大小，而你需要插入一行数据是8k，Oracle则需要使用3个数据块分成片来存储。因此，引起行链接的情形通常是，表上行记录的大小超出了数据库Oracle块的大小。

产生原因：当一行数据大于一个数据块，ORACLE会同时分配两个数据块，并在第一个块上登记第二个块的地址，从而形成行链接。

预防方法：针对表空间扩大数据块大小。

检查：analyze table 表名 validate structure cascade into chained_rows;

----建表----
SQL> DROP TABLE EMPLOYEES PURGE;

表已删除。

SQL> CREATE TABLE EMPLOYEES AS SELECT * FROM HR.EMPLOYEES ;

表已创建。

SQL> set linesize 80;
SQL> desc EMPLOYEES;
 名称                       是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                        NUMBER(6)
 FIRST_NAME                        VARCHAR2(20)
 LAST_NAME                   NOT NULL VARCHAR2(25)
 EMAIL                       NOT NULL VARCHAR2(25)
 PHONE_NUMBER                        VARCHAR2(20)
 HIRE_DATE                   NOT NULL DATE
 JOB_ID                    NOT NULL VARCHAR2(10)
 SALARY                         NUMBER(8,2)
 COMMISSION_PCT                     NUMBER(2,2)
 MANAGER_ID                        NUMBER(6)
 DEPARTMENT_ID                        NUMBER(4)

SQL> create index idx_emp_id on employees(employee_id);

索引已创建。
----扩大字段----
SQL> alter table EMPLOYEES modify FIRST_NAME VARCHAR2(2000);

表已更改。

SQL> alter table EMPLOYEES modify LAST_NAME  VARCHAR2(2000);

表已更改。

SQL> alter table EMPLOYEES modify EMAIL VARCHAR2(2000);

表已更改。

SQL> alter table EMPLOYEES modify PHONE_NUMBER  VARCHAR2(2000);

表已更改。
----更新表----
UPDATE EMPLOYEES
  SET FIRST_NAME = LPAD('1', 2000, '*'), LAST_NAME = LPAD('1', 2000, '*'), EMAIL = LPAD('1', 2000, '*'),
  PHONE_NUMBER = LPAD('1', 2000, '*');
COMMIT;

已更新107行。

SQL>
提交完成。

-----行链接移优化前，先看看该语句逻辑读情况
SET AUTOTRACE traceonly
SQL> set linesize 1000
SQL> select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES  where employee_id>0;
统计信息
----------------------------------------------------------
    153  recursive calls
      1  db block gets
    415  consistent gets
      0  physical reads
    176  redo size
     868529  bytes sent via SQL*Net to client
    492  bytes received via SQL*Net from client
      9  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
    107  rows processed
SQL> select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES  where employee_id>0;

已选择107行。


统计信息
----------------------------------------------------------
      7  recursive calls
      0  db block gets
    397  consistent gets
      0  physical reads
      0  redo size
     868529  bytes sent via SQL*Net to client
    492  bytes received via SQL*Net from client
      9  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
    107  rows processed

SQL> set autotrace off
--------- 发现存在行链接的方法
--首先建chaind_rows相关表，这是必需的步骤
SQL> drop table chained_rows purge;

表已删除。

SQL> @?/rdbms/admin/utlchain.sql

表已创建。

----以下命令针对EMPLOYEES表和EMPLOYEES_BK做分析，将产生行迁移的记录插入到chained_rows表中

SQL> analyze table EMPLOYEES list chained rows into chained_rows;

表已分析。

SQL> select count(*)  from chained_rows where table_name='EMPLOYEES';

  COUNT(*)
----------
       107

---用消除行迁移的方法根本无法消除行链接!!!

SQL> drop table EMPLOYEES_TMP;

表已删除。

SQL> create table EMPLOYEES_TMP as select * from EMPLOYEES where rowid in (select head_rowid from chained_rows);

表已创建。

SQL> Delete from EMPLOYEES where rowid in (select head_rowid from chained_rows);

已删除107行。

SQL> Insert into EMPLOYEES select * from EMPLOYEES_TMP;

已创建107行。

SQL> delete from chained_rows ;

已删除107行。

SQL> commit;

提交完成。
--发现用消除行迁移的方法根本无法消除行链接!
SQL> analyze table EMPLOYEES list chained rows into chained_rows;

表已分析。

SQL> select count(*)  from chained_rows where table_name='EMPLOYEES';

  COUNT(*)
----------
       107

SQL> SET AUTOTRACE traceonly stat
SQL> select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES  where employee_id>0;

已选择107行。


统计信息
----------------------------------------------------------
      0  recursive calls
      0  db block gets
    223  consistent gets
      0  physical reads
      0  redo size
     867923  bytes sent via SQL*Net to client
    492  bytes received via SQL*Net from client
      9  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
    107  rows processed

---启动大小为32K的块新建表空间(WINDOWS下只能使用2K,4K，8K和16K)
--行链接只有通过加大BLOCK块的方式才可以避免，如下：
create tablespace TBS_JACK_16k
     blocksize 16k
   datafile '/u01/app/oracle/oradata/orcl/TBS_JACK_32K_01.dbf' size 100m
     autoextend on
  extent management local
  6      segment space management auto;
create tablespace TBS_JACK_16k
*
第 1 行出现错误:
ORA-29339: 表空间块大小 16384 与配置的块大小不匹配
------------------ORA-29339报错解决办法！

----解决问题后再次创建表空间----
SQL> /

表空间已创建。

SQL> DROP TABLE EMPLOYEES_BK PURGE;
DROP TABLE EMPLOYEES_BK PURGE
           *
第 1 行出现错误:
ORA-00942: 表或视图不存在
SQL> CREATE TABLE EMPLOYEES_BK TABLESPACE TBS_JACK_16K AS SELECT * FROM EMPLOYEES;

表已创建。

SQL> delete from chained_rows ;

已删除107行。
SQL> analyze table EMPLOYEES_BK list chained rows into chained_rows;

表已分析。

SQL> select count(*)  from chained_rows where table_name='EMPLOYEES_BK';

  COUNT(*)
----------
     0
动动口不如动动手
关注
Oracle行迁移和行链接
宁静致远
10-16 1万+
行迁移和行链接都会导致Oracle性能下降，这篇文章将介绍什么是行迁移和行链接，它们带来的问题，如何来判断它们，并提供了解决它们的办法。 什么是行迁移和行链接 行迁移 Oracle的数据块会保留部分空间供以后更新使用，通常的数据块结构如下： PCTFREE定义一个块保留的空间百分比，默认是10，表示当数据块的可用空间低于10%后，就不可以被insert了，只能被update（具体看下面
oracle行迁移和行链接优化
gumengkai的博客
10-29 1680
一. 概述、产生原因及影响 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 entie row into a new block and leave a pointer from the orgin
行链接和行迁移的概念：
qq_36066390的博客
07-29 801
行迁移概念： 当一个行上的更新操作(原来的数据存在且没有减少)导致当前的数据不能在容纳在当前块，我们需要进行行迁移。 一个行迁移意味着整行数据将会移动，仅仅保留的是一个转移地址。因此整行数据都被移动， 原始的数据块上仅仅保留的是指向新块的一个地址信息。 成因：当行被update时，如果update更新的行大于数据块的pctfree值，就需要申请第2个块，从而形成迁移。 后果：导致应用需要访...
Oracle（67）什么是行迁移和行链接？
最新发布
qq_43012298的博客
08-17 483
行迁移（Row Migration）和行链接（Row Chaining）都是与数据库存储管理相关的重要概念，它们可能会影响数据库的性能。行迁移发生在行更新后变大，但当前块没有足够空间时；而行链接发生在行的大小超过单个数据块时。可以通过增加PCTFREE参数、重建表或使用分区等方法来解决这些问题。
oracle 行迁移和行链接
一名数据库爱好者的专栏
10-10 205
https://www.cnblogs.com/Richardzhu/p/3449243.html 这篇 blog 讲的还是很清楚，转载一下。 行迁移 当一个行上的更新操作(原来的数据存在且没有减少)导致当前的数据不能在容纳在当前块，我们需要进行行迁移。一个行迁移意味着整行数据将会移动，仅仅保留的是一个转移地址。因此整行数据都被移动，原始的数据块上仅仅保留的是指向新块的一个地址信息。 成因：当行被update时，如果update更新的行大于数据块的pctfree值，就需要申请第2个块，从而形成迁移。 后
Oracle，行链接，行迁移
m0_72787342的博客
09-23 309
行链接是指表中的行太大了，超出了一个块的容量，导致一个行片段要在多个数据块中存储。行迁移是指更新（update）后，原有块不能提供长度增加后的新的行片段的空间要求，此时数据库会将整个行的数据迁移到新数据块中，原有行片段的空间保留并指向包含迁移行的新块。迁移行的ROWID不变。当行链接或迁移后，访问这些行时必须要扫描多个数据块才能检索到该块的信息，因此与此行关联的I/O性能会降低。
关于Oracle数据库中行迁移/行链接的问题(转)
ctf7893的博客
07-15 158
一、行迁移/行链接的介绍 在实际的工作中我们经常会碰到一些Oracle数据库性能较低的问题，当然，引起Oracle数据库性能较低的原因是多方面的，我们能够通过一些正确的设计和诊断来尽量的避免一些Oracle...
Oracle 行迁移与行链接的实验详解
11-19
行迁移与行链接是Oracle数据库中常见的问题，通过合理的配置调整和表结构调整可以有效减少这些问题的发生，从而提升数据库的整体性能。对于已经发生的行迁移或行链接，可以采取上述解决措施进行优化。
oracle_行链接和行迁移
07-25
### Oracle行链接和行迁移详解 #### 一、概述 Oracle数据库中，行链接和行迁移是两种可能影响数据库性能的现象。了解这两种现象的发生原因及其处理方法对于优化Oracle数据库至关重要。 #### 二、Oracle块 Oracle...
行链接和行迁移的秘密
01-31
如果你的Oracle数据库性能低下，行链接和行迁移可能是其中的原因之一。我们能够通过合理的设计或调整数据库来阻止这个现象。行链接和行迁移是能够被避免的两个潜在性问题。我们可以通过合理的调整来提高数据库性能。...
行链接和行迁移
m0_67751585的博客
07-14 439
行链接是指表中的行太大，超出了一个块的容量，导致一个行片段要在多个数据块中存储。行迁移是指在更新后，原有块不能提供长度增加后的新的行片段的空间要求，此时数据库更新后的行保存在相邻的数据块中，并在原有为之段保存新的行片段地址。 当行链接或迁移后，访问这些行时必须要扫描多个数据块才能检索到行的信息，因此与此行关联的 I/O性能会降低。 ...
Oracle 行链接（Row chaining）与行迁移（Row Migration）2022.9.13
ZSDLZ37的博客
09-13 365
Oracle 会使用该块链接的一块或者多块数据块来容纳改行的数据，行链接进厂插入比较大的数据时才会产生，经常发生行链接的数据类型：long，long row，lob 等数据类型。
Oracle 行链接 和 行迁移 详解
m0_37253968的博客
09-23 302
文章目录 1 概述2 详解2.1 行链接 row chaining2.2 行迁移 row migrating 3 扩展3.1 迁移查询3.2 pfctree 和 pctused3.3 block 1 概述 1. 出现场景 当一行的 '数据过长' 而不能存储在单个 '数据块' 中时，就可能出现两种情况: 行链接（Row Chaining） 或 行迁移（Row Migration） 2. 区别 (1) 行链接：发生在 insert 阶段 (2) 行迁移：发生在 u..
Oracle 行链接(Row chaining) 与行迁移(Row Migration)
weixin_30399055的博客
12-16 105
场景：如果VarChar和VarChar2更经常修改，且修改的数据长度每次都不一样，这会引起“行迁移”现象 概念： 行链接(Row chaining) 与行迁移(Row Migration)当一行的数据过长而不能插入一个单个数据块中时，可能发生两种事情：行链接(row chaining)或行迁移(row migration)。 行链接当第一次插入行时，由于行太长而不能容纳在一个数据块中时，就...
Oracle行迁移和行链接详解
iteye_19543的博客
02-14 287
[img]http://dl2.iteye.com/upload/attachment/0093/8382/96f89af0-67e8-3993-b7a7-6a57d30b9129.jpg[/img] 定义： 行迁移：因为update、将行变长、原来的块已经放不下了、行被挪进新块、 行链接：行一开始就太大、比如你插入了1w个字节的行、必须要分两个块存放、 由此、行变长的时间...
Oracle 行迁移详解
QianXun
09-22 8891
行迁移 英文的意思是 Row movement  默认情况下,oracle数据块中的一行其生命周期内是不会发生移动的,即块的rowid不会发生改变. 但是在某些情景下，我们希望行的rowid可以发生变化，这时候我们就需要启动表的row movement特性。 启用row movement特性，使用如下语句： Alter table table_name enable row movement
优化Oracle性能：行迁移与行链接的识别与规避策略
"行链接和行迁移是Oracle数据库性能优化的重要课题，这两个概念可能导致性能下降，尤其是在OLTP系统中。本文主要探讨了以下几个关键知识点： 1. 定义与影响： - 行迁移：当数据库执行索引读取时，如果需要访问的行...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值