接手过一张按天自动分区的流水表之后我是真的体会到了“自动分区很省心但省不了心”。自动分区帮你把“每个月/每天手工建分区”的重复劳动干掉了可它不会替你考虑旧分区还在昂贵的存储上躺着查询还是会扫过大量历史块表空间和备份体积一年比一年大。今天这篇想聊的就是在这个基础上再往前走一步——用自动分区 自动冷分区迁移 冷分区压缩的组合把查询性能和存储压力一起理顺。这篇文章适合正在用或准备用 Oracle 分区表做业务流水、订单、日志、审计类数据的 DBA、数据架构师和运维同学。不需要太高门槛只要你会建分区、会写存储过程调度就能照着搭出来。我会把设计思路、参数选择、脚本模板、实测收益以及我踩过的坑都放到后面保证都是可以直接落地的东西。1. 自动分区已经很香为什么还要折腾冷分区1.1 自动分区到底省了什么事先说清楚自动分区是什么。Oracle 从 11g 开始提供 Interval Partitioning我们通常叫它间隔分区或者直接叫自动分区。它做的事情很简单你告诉 Oracle 按什么间隔生成新分区比如按天、按月当插入的数据超过当前最大分区边界时Oracle 自动创建下一个分区不用你半夜爬起来手工ALTER TABLE ADD PARTITION。常见的建表方式长这样CREATE TABLE sales ( id NUMBER(12), sale_date DATE, amount NUMBER(10,2) ) PARTITION BY RANGE (sale_date) INTERVAL (NUMTOYMINTERVAL(1, MONTH)) ( PARTITION p_2024_01 VALUES LESS THAN (TO_DATE(2024-02-01, YYYY-MM-DD)) );这样一张表以后每个月的数据进来分区自动“长”出来。没有 Interval 之前每月忘了建分区业务插入就会报 ORA-14400值班电话响一夜。自动分区把这一类维护负担基本清零了这也是它一出来就广受欢迎的原因。但是注意自动分区只解决了“分区自动生成”这一个问题。数据增长带来的存储消耗、查询性能退化、备份恢复变慢它一个都没管。分区照样全都在同一个默认表空间里旧的新的一个待遇。数据量小的时候没感觉攒到 TB 级之后问题就出来了。1.2 冷热不分带来的两个麻烦我拿一张实际维护过的业务流水表举例。这张表按天分区跑了大概两年总量 1.2TB 左右。业务报表和前台查询真正高频访问的只有最近 3 个月的数据预计 60 到 80GB。但整张表 1.2TB 全放在同一套存储上历史分区和热分区混在一起带来的问题是实打实的第一个是查询性能。Oracle 的分区裁剪确实能根据条件跳过不相关的分区可只要查询范围落在历史区间——比如做半年汇总、某个旧订单追溯——它就要读那些完全没有压缩、字段宽、行密度低的分区。数据块多物理读大SQL 跑得慢。更隐蔽的是SGA 里的 buffer cache 会被这些历史数据块反复冲击热分区原本能缓存的块被挤出去整个系统的命中率都会波动。第二个是存储成本。热数据放在 SSD 上是为了响应速度可历史数据明明一年也查不了几次凭什么也占着同样昂贵的热存储表空间文件大RMAN 备份时间拉长恢复到测试环境的时间也跟着变长灾难恢复演练成本直线上升。这些都是不分区冷热就永远解决不了的事。你可以把自动分区想象成一个只会帮你盖仓库的库管员——仓库越盖越多但货物堆得乱七八糟新货旧货全都堆在门口黄金位置。冷分区自动迁移 压缩才是后面真正需要花心思布置的“仓库分区管理方案”。2. 冷分区的定义与自动化方案设计2.1 先把“冷”量化再谈自动做冷热分离最怕的就是拍脑袋。什么叫冷分区不是你看着像冷就叫冷要有一个能被程序识别和执行的量化标准。我习惯用两条规则来定义该分区已经不再接受高频写入或者从数据写入逻辑上已经“关闭”。该分区距离当前时间超过一定的保留窗口比如 180 天。第一条非常重要。为什么要先看写入是否关闭因为如果分区还在持续写入你把它搬到冷表空间甚至压缩了后续再来 DML行迁移、锁等待、块分裂这些麻烦会一起找上门。对于流水数据往往是当月分区会被回溯补录、修正超过几个月之后写入量才真正趋近于零。所以冷热阈值不要只盯着查询频率还要看业务写入习惯。我在实际项目里常用的窗口是最近 6 个月以内的分区留在热表空间超过 6 个月开始进入“待处理”队列。这个窗口不是拍脑袋定的而是因为业务方说“超过 6 个月的流水基本只有财务审计会查而且都是月度汇总查询”写入也早就停了。作为 DBA你需要和业务确认的其实是两件事历史数据还要不要被在线查询大概多久之后查询频率可以忽略确认完这两点阈值自然就出来了。2.2 同表迁移还是分区交换两条路怎么选确定哪些分区是冷分区之后处理方法有两条路很多人拿不准。一条是直接在原表里把分区移动到冷表空间一条是通过分区交换把数据换到独立的归档表。同表迁移的做法是ALTER TABLE sales MOVE PARTITION p_202309 TABLESPACE ts_sales_cold COMPRESS ONLINE UPDATE INDEXES;这条命令执行完分区还是sales表的一部分业务查询完全透明不需要改任何 SQL。适合历史数据还需要继续被在线查询、统一报表访问的场景。缺点是这个分区仍然属于生产表表的整体管理和备份策略不能彻底分离。分区交换的做法是提前建一张结构相同的归档表然后把要归档的分区换出去CREATE TABLE sales_arch_202309 AS SELECT * FROM sales WHERE 10; ALTER TABLE sales EXCHANGE PARTITION p_202309 WITH TABLE sales_arch_202309 INCLUDING INDEXES;交换之后数据从主表挪到了归档表。归档表可以单独放到廉价存储甚至改成只读表空间这样备份策略、恢复粒度都能完全独立。代价是业务查询历史数据时得改访问路径——要么走视图把两张表 union 起来要么应用层知道去查归档表改造工作量比同表迁移大。我的选择逻辑很直接如果业务方强烈要求“所有历史数据都在同一张表里查”走同表 MOVE如果数据基本不会被在线查询只是偶尔回溯走交换 只读表空间这是成本最低的终态。下面这张表总结了两条路的差异方便你对应自己的场景维度同表 MOVE PARTITION分区交换到归档表业务透明性好SQL 不用改差需要改写访问路径存储分层冷热表空间分离更彻底可做只读归档备份策略仍随主表备份可独立管理/跳过备份适合场景历史数据仍需在线查询冷数据极少访问、仅保留维护成本中中高需要额外对象管理2.3 用 DBMS_SCHEDULER 搭一个自动处理框架冷分区迁移如果靠人工每月执行一次早晚会忘而且一旦处理到一半出错恢复起来很痛苦。我建议直接用一个定期 Job 把流程自动化每周日凌晨 1 点跑一次由存储过程扫描分区字典找出所有满足“超过 6 个月 不在冷表空间 未压缩”条件的分区逐个执行移动和压缩同时把每个分区的处理结果写到日志表里。为什么要每周而不是每天因为 MOVE PARTITION 再快也是一个重建段的过程会产生大量 I/O 和 redo。考虑到备份窗口和业务高峰一周一次已经足够。处理数量上也要做限制不要一次把所有历史分区全部搬完建议每个周期最多处理 5 到 10 个分区细水长流地把历史包袱消化掉。第一次上线时如果积压太多可以先写一个独立脚本来个“大扫除”日常 Job 只负责增量处理。3. 压缩冷分区的技术选型与实测3.1 普通环境可用的压缩方案怎么选Oracle 的压缩方案很容易把人绕晕因为不同版本、不同选项的语法长得太像了。先帮你把主流的几种拉通一下基础表压缩Basic Compression老的COMPRESS语法对应后来的ROW STORE COMPRESS BASIC。它只在直接路径加载时压缩数据压缩率高但压缩后分区对后续 DML 很不友好任何写操作都可能带来大量行迁移。对只读冷数据来说这恰恰是优点——不写就不怕。高级行压缩Advanced Row Compression早期叫 OLTP 压缩语法可以是COMPRESS FOR OLTP也可以在新版本里写成ROW STORE COMPRESS ADVANCED。它允许压缩后的数据继续执行 DML兼顾了读写场景压缩率通常比基础压缩低一些而且要额外检查许可证。混合列压缩HCC这是 Exadata 等环境的强项普通非 Exadata 数据库其实用不上。所以如果你不在 Exadata 上就不要在 HCC 上花时间把重心放在行压缩就够了。冷分区选择哪个我的答案很明确如果分区已经彻底只读优先选基础压缩压缩率最高存储成本降得最明显。如果分区偶尔还会有零星修正那就老实选高级行压缩别为了省那点空间给自己埋雷。注意基础压缩会对后续 DML 产生负面影响所以压缩前务必确认该分区已经不再有写入。3.2 MOVE PARTITION COMPRESS 实战确定了压缩方案实操就围绕一条核心 DDL 展开。下面是我在 12c/19c 环境上常用的写法ALTER TABLE sales MOVE PARTITION p_202309 TABLESPACE ts_sales_cold COMPRESS ONLINE UPDATE INDEXES PARALLEL 4;这里几个参数都值得展开说一下。ONLINE表示在线移动分区执行期间允许业务继续对这个分区进行 DML。虽然 Online Move 也会产生额外开销但比直接锁表要温和太多。第一次踩坑经历让我对没有ONLINE的 MOVE 记忆犹新——晚上跑批正好撞上业务补数据DDL 直接阻塞业务会话全堵在队列里。如果你确定执行窗口绝对是低峰不加 ONLINE 可以跑得更快但建议默认还是加上用可控的耗时换安全的并发。UPDATE INDEXES是必须写的。分区上的全局索引在 MOVE 之后如果不维护会直接变成 UNUSABLE比锁表还可怕——查询要么报错要么走全表扫描。加上这个参数Oracle 会在移动过程中同步维护全局索引额外时间要心里有数。如果表上有多个全局索引这一步的开销会明显变大。PARALLEL 4是并行度目的是缩短 MOVE 的时间。但我建议别一上来就开 8 甚至 16生产环境要考虑 I/O 压力。我一般从 4 起步观察系统负载再调整。并行度过高时除了磁盘 I/O还会放大 redo 生成量压缩过程产生的 redo 本来就比普通 Move 要多。还有一点很多人忽略MOVE PARTITION 会在目标表空间里重建一个完整的新段所以ts_sales_cold至少要留出目标分区大小的剩余空间。分区越大对空间的要求越苛刻。别忘了压缩之后段会变小但压缩是边读边写边压缩过程里新旧段是同时存在的。3.3 压缩带来的收益和代价用数据说话压缩到底值不值不能靠感觉要拿数据说话。我挑了一个单月 1.2 亿行的销售明细分区做过对比日期范围一个月字段包括金额、渠道、商品编码等平均行长偏大。压缩前的数据量大概 12GB使用基础压缩后降到 4.3GB压缩率超过 60%。这个收益是实实在在的尤其对存储在配额紧张、按容量计费或者备份时间吃紧的环境节省非常可观。而且压缩后的数据块能容纳的行数变多全分区聚合类查询需要扫描的块数明显减少。我对同一个分区跑过一次SELECT SUM(amount) FROM sales WHERE sale_date BETWEEN ...压缩前全分区扫描大约 38 秒压缩后 13 秒I/O 层面提升明显。但我也要说清楚另一面压缩数据在读取时需要 CPU 参与解压。如果系统瓶颈本来就在 CPU压缩后查询性能未必会提升甚至可能下降。我遇到过一台老旧的库存数据库CPU 常年 90% 以上压缩后单行点查反而变慢最后又改回了不压缩。压缩不是银弹它更适用于数据仓库、分析型查询和 I/O 密集场景而不是高频小事务点查。所以在决定给哪些表压缩之前先想清楚你系统的瓶颈到底在哪。4. 自动识别自动压缩的完整落地脚本4.1 分区命名规范和自动识别逻辑自动化的前提是脚本能可靠地认出“哪个分区是旧的”。最靠谱的做法不是去解析HIGH_VALUE那列存的是TO_DATE(2023-09-01 00:00:00, SYYYY-MM-DD HH24:MI:SS, ...)这种带引号的字符串解析起来又麻烦又容易踩格式坑。我的建议是建表时就统一分区命名规则月分区就叫p_YYYYMM日分区就叫p_YYYYMMDD。这样判断分区年龄可以直接从分区名里截取年月逻辑一眼就能看懂。自动识别要处理的对象需要同时满足几个条件分区属于目标表、压缩状态还是 DISABLED、当前表空间是热表空间、分区名的年月早于阈值。查询语句大致长这样SELECT table_owner, table_name, partition_name FROM dba_tab_partitions WHERE table_owner APP AND table_name SALES AND compression DISABLED AND tablespace_name TS_SALES_HOT AND TO_NUMBER(SUBSTR(partition_name, 3, 6)) TO_NUMBER(TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, MM), -6), YYYYMM)) ORDER BY partition_name;注意SUBSTR(partition_name, 3, 6)只有在分区名确实是p_202309这种格式时才成立。如果你维护的老表分区名不是这个规则要么先统一改名要么写一个函数专门解析HIGH_VALUE。两种方案我都用过强烈建议前者规范的命名比任何解析函数都省心。4.2 核心存储过程识别、迁移、压缩、记录日志自动处理的存储过程并不复杂核心思路就是遍历上面的结果集逐个执行动态 SQL然后把结果写进日志表。我贴一个可以直接改改就用的精简版CREATE OR REPLACE PROCEDURE proc_cold_partition IS v_cnt NUMBER : 0; v_start DATE; v_end DATE; BEGIN FOR r IN ( SELECT table_owner, table_name, partition_name FROM dba_tab_partitions WHERE table_owner APP AND table_name SALES AND compression DISABLED AND tablespace_name TS_SALES_HOT AND TO_NUMBER(SUBSTR(partition_name, 3, 6)) TO_NUMBER(TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, MM), -6), YYYYMM)) ORDER BY partition_name ) LOOP EXIT WHEN v_cnt 5; v_start : SYSDATE; BEGIN EXECUTE IMMEDIATE ALTER TABLE || r.table_owner || . || r.table_name || MOVE PARTITION || r.partition_name || TABLESPACE ts_sales_cold COMPRESS ONLINE UPDATE INDEXES PARALLEL 4; v_end : SYSDATE; INSERT INTO partition_move_log(schema_name, table_name, partition_name, start_time, end_time, status, error_msg) VALUES (r.table_owner, r.table_name, r.partition_name, v_start, v_end, SUCCESS, NULL); EXCEPTION WHEN OTHERS THEN v_end : SYSDATE; INSERT INTO partition_move_log(schema_name, table_name, partition_name, start_time, end_time, status, error_msg) VALUES (r.table_owner, r.table_name, r.partition_name, v_start, v_end, FAILED, SQLERRM); -- 记录失败后继续下一个不阻断整体批次 END; COMMIT; v_cnt : v_cnt 1; END LOOP; END; /几个设计细节说明一下EXIT WHEN v_cnt 5控制每个批次最多处理 5 个分区。这个数字你可以按业务量和窗口时间自己调宁可每次少做也不要让 Job 跑不完。EXCEPTION WHEN OTHERS THEN把失败隔离到单分区级别一个分区迁移失败不会拖垮整个批次。错误信息记录在日志表里第二天巡检时一眼就能看到。每条执行完立刻COMMIT避免日志丢失也避免长事务占着 UNDO。如果你要压缩的不是基本压缩而是高级行压缩只需要把MOVE PARTITION ... COMPRESS改成MOVE PARTITION ... COMPRESS FOR OLTP即可其余逻辑不需要动。日志表partition_move_log按需建至少包含start_time、end_time、status、error_msg这几个字段后面验证效果和排查问题全靠它。4.3 运行验证怎么看效果、怎么日常巡检脚本上线之后不能只看日志表里写 SUCCESS 就以为完事了。我每次处理完一批都会用一条 SQL 确认压缩和表空间状态是不是真的变了SELECT partition_name, tablespace_name, compression, compress_for FROM dba_tab_partitions WHERE table_owner APP AND table_name SALES ORDER BY partition_name;这里compression会显示ENABLED或DISABLEDtablespace_name能确认分区是否已经落到了TS_SALES_COLD。两个条件都满足说明这个分区真正完成了冷化。压缩只是第一步别忘了统计信息。压缩会改变数据块的密度和行数分布旧的统计信息可能让优化器误判。我习惯在分区压缩完成后立即重新收集这个分区的统计信息EXEC DBMS_STATS.GATHER_TABLE_STATS(APP, SALES, PARTNAME p_202309, GRANULARITY PARTITION);做完全部处理我还会隔几天对比一次查询的物理读和逻辑读确认热分区查询是否变快、冷分区聚合查询是否受益。数据说话以免自我感觉良好。5. 我踩过的坑和最终沉淀的检查清单5.1 六个真实踩坑记录第一个坑是没加 ONLINE。第一次在测试环境执行速度挺满意一到生产就露馅——晚上正好有批处理在补录数据MOVE 拿不到锁把在线会话全卡住了。后来我无论什么情况都默认带 ONLINE只有确认绝对无业务时才考虑去掉。第二个坑是不写 UPDATE INDEXES全局索引全部 UNUSABLE。当时表上有两个全局索引MOVE 完一查索引状态直接傻眼业务查询马上报错。后来所有 MOVE 都强制UPDATE INDEXES哪怕执行时间长一点也认。第三个坑是空间评估不足。冷表空间剩余空间看着够但 MOVE 过程需要同时容纳新旧两个段实际才跑一半就报 ORA-01652 中断了。从那以后我在批量处理前会先看dba_free_space确认冷表空间至少有目标分区两倍的余量再做。第四个坑是压缩后没刷统计信息。压缩完第二天一条月度汇总 SQL 执行计划突然变成全表扫描排查半天最终发现是统计信息没更新优化器拿旧的行数去估算走了岔路。后来压缩和GATHER_TABLE_STATS成对操作再没出过类似问题。第五个坑要提醒 CPU 瓶颈的系统。压缩确实省了存储、减少了 I/O但换来的是 CPU 解压开销。我在一台 CPU 常年跑满的主机上做过对比点查性能反而下降。若你的系统瓶颈在 CPU请先解决 CPU 再谈压缩或者干脆只压缩真正完全不查的归档分区。第六个坑是压缩时机选得太早。有一张订单表我按 3 个月窗口压缩了一个即将结束的月份分区结果业务财务调整又往里面补了几十万行。基本压缩的分区一遇到 DML行迁移量非常大补数任务跑了很久。后来我把压缩窗口拉长到 6 个月写入基本关闭才动手这类问题就消失了。5.2 业务场景适用性判断并不是所有表都适合做冷分区压缩。我的判断标准很简单先问三个问题这张表是否按时间增长旧数据是否几乎只读历史数据是否需要在线查询三个问题答案都是“是”那冷分区压缩方案就是加分项。订单表、流水表、日志表、审计表都属于标准画像。反过来如果一张表虽然按时间分区但历史分区经常被更新或者查询基本都以主键单行点查为主压缩带来的收益就很有限反而要承受 DML 行迁移和 CPU 解压的代价。这类表不要为了“别人都在做”而盲目上压缩状态健康比压缩率更重要。5.3 可以先从哪些表开始试点新方案切忌一上来就铺全量。我会选一张体量中等、月分区 5 到 20GB、历史分区确认只读的表做试点比如某条日志表或者次要流水表。先跑一个压缩批次观察 MOVE 耗时、redo 增量、冷表空间占用、压缩率和近期 SQL 有没有回退全都符合预期后再逐步推广到核心大表。整个过程控制在一个双休日内能完成的处理量避免压缩任务撞上业务高峰和备份窗口。我个人在实际操作中还会把一个技巧记在心里冷热处理分成两阶段执行。第一阶段先把超窗分区 MOVE 到冷表空间但不急着压缩隔一两个批次之后确认这些分区在冷表空间运行稳定、确实没有 DML 了再执行 COMPRESS。这样把“搬迁”和“压缩”两个风险拆开即使压缩阶段出问题数据也已经离开了热存储影响范围小得多。最后再提醒一句压缩之后一定要重新收集统计信息这一步漏了前面的功夫可能白费。
