数据迁徙的代价深度解析Oracle行迁移与行链接的原理、检测与优化方案一、开篇现象为什么查询一行数据需要两次I/O二、全景对比图行迁移与行链接的区别三、行迁移Row Migration深度解析1.行迁移的本质定义2.行迁移的完整发生过程3.行迁移对性能的影响四、行链接Row Chaining深度解析1.行链接的本质定义2.行链接的存储结构3.行链接对性能的影响五、行迁移与行链接的六大核心区别六、如何检测行迁移与行链接1.方法一ANALYZE TABLE最常用2.方法二V$SYSSTAT查看全库迁移统计3.方法三DBMS_ROWID分析ROWID4.方法四使用数据块DUMP确认七、消除行迁移的三种方法1.方法一ALTER TABLE MOVE最彻底2.方法二ALTER TABLE SHRINK在线操作3.方法三导出导入跨数据库迁移八、预防行迁移的五大设计策略1.策略一合理设置PCTFREE2.策略二避免对变长列的频繁大幅更新3.策略三选择合适的块大小4.策略四使用ASSM表空间5.策略五定期检测与清理九、总结记住这个“搬家”类比就够了The Begin点点关注收藏不迷路⬇ ⬇ 底部 ⬇ ⬇一、开篇现象为什么查询一行数据需要两次I/O某个银行系统的客户信息表customers每行约2KB。某天DBA发现一个简单的按主键查询语句每次执行都产生2次物理读。按理说索引唯一扫描应该只需要1次I/O——读取索引块1次I/O读取数据块但statistics显示实际是2次数据块读取。这就是Oracle中经典的行迁移Row Migration问题。行被UPDATE后变长原块空间不足整行被迫“搬家”到新块原块只留下一个“转发地址”。读取这行时需要先读原块找到转发地址再读新块获取数据——一次查询变成了两次I/O。今天这篇文章带你从原理到检测再到解决全面掌握行迁移与行链接的诊断与优化。二、全景对比图行迁移与行链接的区别先通过一张对比图直观理解行迁移和行链接的根本区别行链接-Row Chaining链接指针链接指针块1: 行数据片段1-前8KB块2: 行数据片段2-中间8KB块3: 行数据片段3-后8KB行迁移-Row Migration转发指针块1-原块: 只存转发地址块2-新块: 整行数据完整存储正常行块1: 整行数据完整存储在一个块中关键解读绿色正常行一行数据完整存储在一个块中1次I/O即可读取。橘色行迁移整行数据在新块中原块只有转发地址。读取需要2次I/O。红色行链接行数据本身大于块大小分散在多个块中。读取需要N次I/O。三、行迁移Row Migration深度解析1.行迁移的本质定义精确描述行迁移是指当一行数据被UPDATE后总长度变长而原数据块的空闲空间不足以容纳更新后的行时Oracle将整行数据移动到另一个有空闲空间的数据块中在原块中留下一个指向新块的转发地址Forwarding Address。行迁移的产生条件原行长度 更新增加的长度 原块的空闲空间 且 原行长度 更新增加的长度 ≤ 块的可用空间否则触发行链接2.行迁移的完整发生过程UNDO表空间ITL事务槽新数据块-Block2原数据块-Block1用户UPDATE操作UNDO表空间ITL事务槽新数据块-Block2原数据块-Block1用户UPDATE操作alt[空间不足][空间足够]之后读取该行时先读Block1→发现是转发地址→再读Block21.找到目标行2.返回行数据3.计算更新后的行长度4.检查Block1空闲空间5.寻找有空闲空间的Block26.记录旧值到UNDO7.删除原行数据8.在原位置写入转发地址指向Block29.在Block2中插入完整的更新后的行10.在两个块中各分配一个ITL槽直接在Block1中更新关键步骤详解步骤5-寻找新块Oracle在同一个段内寻找有空闲空间的块。优先选择同一区Extent内的块。如果同区内没有扩展到其他区。步骤8-转发地址原行位置保留一个短记录转发地址。转发地址只包含新块的RDBA4字节。这个转发地址是永久性的即使原块后来有了空间行也不会迁回。3.行迁移对性能的影响影响一增加I/O次数正常读取1次I/O → 读取数据块 行迁移读取2次I/O → 读取原块发现转发地址 读取新块影响二索引无法直接定位新位置ROWID指向的是原块的地址。索引扫描通过ROWID定位时总是先访问原块。即使行已经迁移到新块ROWID也不会更新除非MOVE/SHRINK。影响三影响表连接和排序性能Nested Loop连接、Sort Merge操作都会受到行迁移的影响。每次通过ROWID回表都可能产生额外的I/O。四、行链接Row Chaining深度解析1.行链接的本质定义精确描述行链接是指当一行数据的总长度超过数据库块的大小时Oracle将这一行数据拆分成多个片段存储在不同的数据块中通过链接指针Rowid Chain连接。行链接的产生条件行总长度 块的可用空间块大小 - 块头 - 行目录 - PCTFREE预留常见产生场景包含LOB列CLOB、BLOB且未使用DISABLE STORAGE IN ROW。包含大量VARCHAR2列且每列都接近4000字节。表的块大小设置过小如2KB块存8KB行。2.行链接的存储结构一行数据跨多个块的链接方式块1头部块 ├── 行头包含链接标志 ├── 列1~列M的数据 ├── 链接指针 → 指向块2的RDBA └── 行尾标记 块2中间块 ├── 链接指针 → 指向块3的RDBA ├── 列M1~列N的数据 └── 链接指针 → 指向块3 块3尾部块 ├── 链接指针 → NULL表示最后一块 ├── 列N1~列P的数据 └── 行尾标记3.行链接对性能的影响影响一成倍的I/O增加行在1个块中1次I/O 行在3个块中3次I/O 行在10个块中10次I/O影响二索引扫描的连锁反应索引唯一扫描通常只需1次表访问。行链接后1次索引扫描变成了N次表访问。影响三Buffer Cache的额外压力一行占用多个Buffer Cache槽位。热点行如果是链接行Buffer Cache命中率降低。五、行迁移与行链接的六大核心区别序号对比维度行迁移Row Migration行链接Row Chaining1产生原因UPDATE后行变长原块空间不足行总长度 块大小2原块内容转发地址指向新块行数据片段13新块内容完整的行数据行数据片段2、片段3…4I/O次数2次原块新块N次N行占用的块数5可消除性可通过MOVE/SHRINK消除无法消除只能增大块大小或拆分表6对ROWID的影响ROWID不变仍指向原块ROWID指向头部块六、如何检测行迁移与行链接1.方法一ANALYZE TABLE最常用-- 分析表统计行迁移和链接数量ANALYZETABLEempCOMPUTESTATISTICS;-- 查看统计结果SELECTtable_name,num_rows,chain_cnt,ROUND(chain_cnt/num_rows*100,2)ASchain_pct,avg_row_len,blocksFROMdba_tablesWHEREtable_nameEMP;判断标准chain_pct 1%正常。chain_pct 1% ~ 5%需要关注。chain_pct 5%严重问题必须优化。注意chain_cnt统计的是行迁移和行链接的总和无法区分。2.方法二V$SYSSTAT查看全库迁移统计-- 查看全库级别的行迁移统计SELECTname,valueFROMv$sysstatWHEREnameIN(table fetch continued row,-- 行链接导致的额外I/Otable fetch by rowid-- 通过ROWID访问的总次数);-- 计算行迁移的影响比例SELECTROUND((SELECTvalueFROMv$sysstatWHEREnametable fetch continued row)/(SELECTvalueFROMv$sysstatWHEREnametable fetch by rowid)*100,2)ASmigration_impact_pctFROMdual;解读table fetch continued row读取行迁移/行链接的额外I/O次数。这个值越大说明行迁移/链接的影响越严重。3.方法三DBMS_ROWID分析ROWID-- 通过ROWID分析行是否在原始块中SELECTemp_id,ROWID,DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)ASfile_id,DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)ASblock_id,DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)ASrow_numFROMempWHEREemp_id100;-- 如果ROWID指向的块中没有该行数据说明发生了行迁移4.方法四使用数据块DUMP确认-- 导出数据块内容ALTERSYSTEMDUMPDATAFILE4BLOCK128;-- 在trace文件中查找-- nrid: 0x01000082 → 转发地址nrid next rowid-- 如果行头中有nrid说明该行发生了行迁移Trace文件中的标志tab 0, row 1, 0x1f8a tl: 9 fb: --H-F--- → fb中的H标志表示这是行迁移的头部 nrid: 0x01000082 → 转发目标块地址七、消除行迁移的三种方法1.方法一ALTER TABLE MOVE最彻底适用场景可以接受短暂停机窗口。操作步骤-- 1. 记录当前chain_cntANALYZETABLEempCOMPUTESTATISTICS;SELECTchain_cntFROMdba_tablesWHEREtable_nameEMP;-- 假设chain_cnt 50000-- 2. 执行MOVE操作ALTERTABLEemp MOVE;-- 3. 重建索引ALTERINDEXidx_emp_name REBUILD;ALTERINDEXidx_emp_dept REBUILD;-- 4. 重新收集统计信息EXECDBMS_STATS.GATHER_TABLE_STATS(SCOTT,EMP);-- 5. 验证效果ANALYZETABLEempCOMPUTESTATISTICS;SELECTchain_cntFROMdba_tablesWHEREtable_nameEMP;-- chain_cnt应该变为0如果没有行链接的话MOVE消除行迁移的原理MOVE将表的数据逐行读出再逐行插入到新段中。插入时会根据PCTFREE重新分配空间。所有行都在新的块中完整存储无转发地址。2.方法二ALTER TABLE SHRINK在线操作适用场景不能接受长时间停机需要在线操作。操作步骤-- 1. 启用行移动ALTERTABLEempENABLEROWMOVEMENT;-- 2. 执行SHRINKALTERTABLEemp SHRINK SPACECASCADE;-- 3. 验证效果ANALYZETABLEempCOMPUTESTATISTICS;SELECTchain_cntFROMdba_tablesWHEREtable_nameEMP;SHRINK消除行迁移的原理将后面的行向前面的空闲空间移动。移动过程中重新组织行数据。移动后所有行都在新位置完整存储。SHRINK vs MOVE的选择场景推荐方法生产环境需要在线操作SHRINK维护窗口可以接受停机MOVE需要同时修改表空间MOVE TABLESPACE xxx表有大量索引SHRINK自动维护索引需要最快速度MOVE 并行重建索引3.方法三导出导入跨数据库迁移适用场景需要将表迁移到其他数据库或需要彻底重组。操作步骤-- 导出expdp scott/tiger DIRECTORYdpump_dirDUMPFILEemp.dmpTABLESemp-- 导入自动重建表结构行在新块中完整存储impdp scott/tiger DIRECTORYdpump_dirDUMPFILEemp.dmp TABLE_EXISTS_ACTIONREPLACE八、预防行迁移的五大设计策略1.策略一合理设置PCTFREE规则对于频繁UPDATE且行会变长的表增大PCTFREE。-- 如果表的某列经常从NULL更新为长字符串-- 例如订单状态从NEW更新为SHIPPED同时更新备注列ALTERTABLEorders PCTFREE30;-- 预留30%空间给更新扩展PCTFREE建议值表类型推荐PCTFREE原因只读表/日志表0~5无更新操作一般OLTP表10~20偶尔更新频繁更新变长列表20~40需要预留扩展空间极频繁更新变长列40~50极大减少行迁移2.策略二避免对变长列的频繁大幅更新错误做法-- 将remarks从NULL更新为2000字节的字符串UPDATEordersSETremarks很长很长的备注...WHEREorder_id100;优化方案-- 方案1将备注列移到独立表减少主表的行大小变化CREATETABLEorder_remarks(order_id NUMBERPRIMARYKEY,remarks CLOB);-- 方案2使用CLOB列自动处理行链接ALTERTABLEordersMODIFYremarks CLOB;3.策略三选择合适的块大小规则如果表的平均行长度较大使用更大的块大小。-- 创建32KB块大小的表空间CREATETABLESPACElarge_ts DATAFILE/u01/oradata/ORCL/large_ts01.dbfSIZE1G BLOCKSIZE32K;-- 将大行表移入该表空间ALTERTABLEbig_row_table MOVETABLESPACElarge_ts;块大小选择建议平均行长度推荐块大小 2KB8KB默认2KB ~ 8KB16KB8KB ~ 16KB32KB 16KB考虑使用CLOB或拆分表4.策略四使用ASSM表空间规则ASSM自动段空间管理比MSSM手动段空间管理更能有效减少行迁移。-- 创建ASSM表空间CREATETABLESPACEassm_ts DATAFILE/u01/oradata/ORCL/assm_ts01.dbfSIZE1G EXTENT MANAGEMENTLOCALAUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;-- ASSM的关键配置5.策略五定期检测与清理建立定期监控任务-- 创建监控脚本每月检查一次DECLAREv_chain_pct NUMBER;BEGINFORrecIN(SELECTtable_nameFROMdba_tablesWHEREownerSCOTT)LOOPEXECUTEIMMEDIATEANALYZE TABLE SCOTT.||rec.table_name|| COMPUTE STATISTICS;SELECTROUND(chain_cnt/DECODE(num_rows,0,1,num_rows)*100,2)INTOv_chain_pctFROMdba_tablesWHEREtable_namerec.table_name;IFv_chain_pct5THENDBMS_OUTPUT.PUT_LINE(WARNING: ||rec.table_name|| chain_pct ||v_chain_pct||%);ENDIF;ENDLOOP;END;/九、总结记住这个“搬家”类比就够了【行迁移是整体搬家行链接是把一个人拆成几块】行迁移Row Migration一个人原来住30平米的小房子后来东西太多了UPDATE变长搬到了50平米的大房子。但户口本ROWID上的地址还是老房子快递员Oracle送快递时先去老房子看到门上的纸条转发地址再送到新房子。行链接Row Chaining一个人有200平米的家具行大于块大小一个房间放不下只能分三个房间存放。快递员取东西时需要分别跑三个房间才能拿全。消除方法行迁移可以通过MOVE/SHRINK消除。行链接无法消除只能增大块大小或拆分表。理解行迁移和行链接的原理你就掌握了大表性能优化的一个重要方向。定期检查chain_cnt及时MOVE/SHRINK是DBA保持数据库高性能运行的基本功。你在日常运维中是否遇到过行迁移导致的性能问题是如何发现的用了哪种方法解决欢迎在评论区分享你的实战经验。The End点点关注收藏不迷路⬆ ⬆ 顶部 ⬆ ⬆