仓库里某个大表更新变得奇慢查了AWR发现热块争论严重顺手看了下表结构发现还是默认的PCTFREE 10。改参数之前得先搞清楚这两个存储参数到底在管什么。很多做了几年开发的人对Oracle的pctfree和pctused还是模模糊糊面试被问到就说一个是给更新留空间的一个是控制插入的再往深里问就说不清了。这篇直接把这两兄弟从头到尾讲透看完你至少能回答为什么块满了还能更新为什么ASSM下pctused没用了怎么估算该设多少1. 为什么需要关心pctfree和pctused先回到最基础的物理结构Oracle的数据文件由一个个数据块block组成默认块大小通常是8KB。理论上数据块是Oracle读写的最小单位也就是说哪怕你只查一行数据库也是把整个块加载到内存缓冲区里。块里面需要放块头保留格式化信息和事务列表、数据行、闲散空间。行是紧挨着存放的。关键点在于你INSERT一条记录并不是永远一成不变的。用户可能随后UPDATE把VARCHAR2从ABC改成ABCDEFGHIJKLMNOPQRSTUVWXYZ甚至把原来为NULL的字段填上一大段文本。如果这个块的剩余空间刚好够插入原始行但不够应对更新后的膨胀这一行就只能整体挪走在原位置留一个指针。这就是著名的行迁移。行迁移导致读取两次I/O更新后查询性能明显下降。pctfree就是为了应对这种情况在块中预先保留一块空闲区域只给将来可能的UPDATE用不让新的INSERT把这块空间占掉。pctused则解决另一个完全不同的问题——一个块在被删除行释放出空间之后何时重新允许插入新行。理解这两个参数核心是要先建立块状态的概念。手动段空间管理MSSM模式下块要么在空闲列表上可以被分配插入要么从列表上摘除暂时不参与插入。pctused控制的就是这个来回切换的门槛。而自动段空间管理ASSM模式则换了一套基于位图的管理办法这一点我后面细说。说白了这两个参数直接影响一个表中的行如何分布在数据块里、空闲空间使用效率如何、以及更新频繁的表会不会出现大量行迁移。对于OLTP系统这俩参数设置错了往往不是立刻出问题而是等数据量起来了性能衰减才暴露那时候再收拾就麻烦得多。2. PCTFREE到底是什么怎么影响块分配2.1 PCTFREE的定义和工作机制pctfree是指在数据块中为将来更新行时可能增加的长度而预留的空间比例。语法上它可以出现在CREATE TABLE的STORAGE子句里也可以是ALTER TABLE后修改。拿常用的8KB块来说如果pctfree10含义是当块内数据已经占用了大约90%的可用空间时这个块就不再允许插入新的行剩下的那10%空间保留着不参与INSERT分配只用于已存在的行通过UPDATE变大后的空间需求。注意这只是一个简化的说法。Oracle内部还要扣掉块头、表目录、行目录的开销真正能用的数据空间会比块大小小一些但比例计算的基本逻辑就是这样。具体插入的时候怎么判断当需要插入一行时Oracle会检查目标空闲块中可用空间是否足够容纳新行。如果可用空间减掉新行需要的空间后剩余的可用比例已经低于pctfree设定的预留值这个块就不再接收新行Oracle把指针移动到下一个有足够空间的块。换句话说pctfree设得越低块被填得越满能容纳的行数量就越多但更新膨胀的空间越少行迁移风险越大。2.2 PCTFREE对UPDATE的影响很多人只记住了pctfree是预留空间却忽略了一个细节即使块的剩余空间已经小于新行插入所需的空间里面的旧行仍然可以被Update而且是膨胀到超过pctfree预留甚至把块的可用空间用光为止。也就是说pctfree是在INSERT层面的限额但UPDATE即便超过了限额也能继续在这个块里发生。只有在整个块剩余空间不足以容纳整行膨胀后的长度时才会发生行迁移。这也就解释了块满了为什么还可以更新。如果你的表UPDATE非常频繁而且更新通常把行变大那么pctfree应该适当调高。比如说你统计每行平均长度200字节每次更新通常增加30字节那么30/(20030) ≈ 13%则pctfree至少应该设15留一点余量。如果更新很少只是INSERT多pctfree设5甚至0可以最大化空间利用率比如日志归档表、只读消息表。2.3 设置PCTFREE前需要计算的东西直接给推荐值是不够的还是要结合自己的数据特征。下面是我常用来估算的步骤从DBA_TABLES看一下这个表的平均行长AVG_ROW_LEN没有统计信息先DBMS_STATS.GATHER_TABLE_STATS。找到业务上最典型的增改情况。比如订单状态表每次更新只是把VARCHAR2(20)的状态码从2个字符改成3个字符增长的字节量很小pctfree用默认10就够。如果有一个CLOB或VARCHAR2(4000)字段初始为空后续回填大量文本这个增长幅度非常大通常会把pctfree设到30甚至更高否则行基本百分百迁移。可以用一个公式感受一下假设块可用数据空间约8000字节8KB块扣除开销每行平均200字节一个块理想可以放40行。如果pctfree10则INSERT最多填满约7200字节实际大约放36行。预留约800字节给更新。如果你评估平均每次更新增长80字节那这800字节还能允许约10行发生膨胀。一旦超过就开始出现行迁移。这样你就能估算出pctfree调到多少才算匹配。2.4 PCTFREE的经典坑设置pctfree遇到最常见的问题是为了省空间压得太低。我在某项目上见过一个流水表设pctfree0结果跑了两个月后表里的统计数据反映行迁移率超过25%查询性能明显下滑。空间是省了点但得不偿失。反过来如果OLTP表更新很少你却设pctfree40每个块松松垮垮地装几行全表扫描的块数变多缓冲区利用率下降也可能拖慢查询。没有万能值必须按表的访问模式去设定。3. PCTUSED的作用以及它在ASSM下为何不重要3.1 手动段空间管理MSSM中的PCTUSED在Oracle 9i之前或者说当你将表空间设置为SEGMENT SPACE MANAGEMENT MANUAL时每个段表、索引会维护一个或多个空闲列表freelist。INSERT时Oracle从空闲列表中取块。当块内已用空间达到100 - pctfree的水平时该块就从空闲列表中移除进入已满状态不再被视为可插入块。一段时间后块内的一些行被DELETE释放了空间但满了的块并不会因为还有少量空间就立刻回到空闲列表。直到释放出的空间足够多让块中的已用空间比例下降到pctused设定的百分比以下Oracle才把它重新挂到空闲列表上允许后续INSERT使用。举个例子块大小为8KBpctfree10pctused40。则初始新块可以一直插入数据直到已用空间达到90%。之后这个块不再接受新插入的行。然后假设块内发生大量删除已用空间下降到35%此时低于pctused40该块又被加回空闲列表又能插入新行了。你看一个块从可以INSERT到不可INSERT再看一个块从不可INSERT到可以INSERT分别由两个完全不同的参数控制。那么为什么不把一个满了之后的块一旦有了一点空闲空间就立刻允许插入因为频繁在空闲列表上挂入挂出会引发一系列内部锁竞争与队列操作在并发插入场景下非常拖累性能。设置一个较低的pctused例如40意味着块需要释放足够多的空间才会重新进入列表从而减少状态切换的频率。3.2 PCTUSED和PCTFREE的关系这两个参数的数值之和最好不超过100且一般建议pctfree pctused小于等于100。在经典的手动空间管理里如果两者之和等于100甚至大于100看起来pctused永远无法达到因为不会在低于pctused时同时块内空间又高于100-pctfree可能导致这类块永远不被用作插入实际中Oracle有处理但为避免逻辑混乱推荐设置和小于100。举例说明pctfree10pctused40那么块内已用空间在40%到90%之间的时候块处于空闲列表上可以插入状态超过90%后移除低于40%后重新挂上。索引块一般不关心pctused因为索引条目被删除后空间复用有自己的机制。3.3 ASSM为什么PCTUSED不再参与Oracle 9i开始推荐使用ASSM自动段空间管理每个段使用位图管理块的状态取代了空闲列表。ASSM将块的使用程度分为四类0-25%空闲25%-50%空闲50%-75%空闲75%-100%空闲。插入时Oracle根据块的空闲程度选择合适的块比如优先使用25%-50%空闲的块这样既能够给更新留空间又能保持相对稳定的填充率。在这种机制下pctused参数就被忽略掉了。在ASSM段的表中执行ALTER TABLE ... STORAGE(PCTUSED 60)并不会报错但不会生效。查询USER_TABLES.PCT_USED时很多ASSM表显示为0并不是说默认是0而是不再使用该列。所以如果你还在为pctused纠结先确认你的表空间是不是ASSM。从Oracle 10g开始新创建的表空间默认就是ASSM。绝大多数现代环境里你根本不需要调整pctused。3.4 什么时候还会再去碰PCTUSED只有在你维护老系统仍然使用MSSM表空间或者是某些特殊场景必须用manual空间管理时pctused显得重要。在MSSM中通常的平衡值是pctfree10, pctused40。如果并发插入极高希望减少空闲列表的切换频率可以适当调低pctused比如30让块释放更多才会被重新加入。如果表空间紧张希望空间尽快复用可以把pctused调高比如60但副作用是状态切换频繁DML性能可能受影响。在ASSM环境里忘掉它就好。4. 实操查看和调整这两个参数的正确姿势4.1 查看当前表的PCTFREE和PCTUSED数据字典里就能直接看到SELECT table_name, pct_free, pct_used, avg_row_len, num_rows FROM user_tables WHERE table_name ORDER_HEADER;对于分区表可以从USER_TAB_PARTITIONS查看每个分区的参数。这里有几个容易误读的地方pct_used为0不一定是MSSM下真的设了0更多是ASSM下的无效占位。判断段空间管理方式可以查询DBA_TABLESPACESSELECT tablespace_name, segment_space_management FROM dba_tablespaces;结果AUTO表示ASSMMANUAL就是MSSM。4.2 修改PCTFREE和PCTUSED修改参数很简单ALTER TABLE ORDER_HEADER STORAGE (PCTFREE 20 PCTUSED 40);但这里有个非常容易踩的坑这个ALTER语句只对之后新建的数据块生效。也就是说表里已经存在的大量块其实际填充率不会因为这句修改而立刻变除非你后续做MOVE或在线重定义。如果你已经发现存在大量行迁移光是改pctfree而不重组表效果要等很久才能体现出来。想要立即生效最简单的方式ALTER TABLE ORDER_HEADER MOVE;MOVE会重建表重新分配数据块新块会按照新的存储参数来排布。不过MOVE期间表会加锁业务需要停机或使用在线重定义技术来减少影响具体根据自己的可用性窗口来权衡。4.3 如何查行迁移率要确认pctfree设置是否合理可以通过行迁移检测。先创建存放行迁移结果的表脚本在$ORACLE_HOME/rdbms/admin/utlchain.sql中通常名为CHAINED_ROWS$ORACLE_HOME/rdbms/admin/utlchain.sql然后对目标表分析ANALYZE TABLE ORDER_HEADER LIST CHAINED ROWS INTO CHAINED_ROWS;查询迁移行数SELECT count(*) FROM chained_rows WHERE table_name ORDER_HEADER;再对比表的行数算出迁移比例。如果这个比例超过5%就需要考虑调大pctfree并重组表了。注意行链接row chaining也会被记录到CHAINED_ROWS里如果是因为行长超过块大小比如有大的CLOB调pctfree没有作用那是另一个问题。4.4 通过数据字典估算实际空间使用想看看表各个块的填充情况可以使用DBMS_SPACE包。比如SET SERVEROUTPUT ON DECLARE l_free_blocks NUMBER; l_used_blocks NUMBER; l_total_blocks NUMBER; BEGIN DBMS_SPACE.SPACE_USAGE( segment_owner USER, segment_name ORDER_HEADER, segment_type TABLE, used_blocks l_used_blocks, unformatted_blocks l_free_blocks, total_blocks l_total_blocks ); DBMS_OUTPUT.PUT_LINE(已格式化使用块: || l_used_blocks || 未格式化块: || l_free_blocks || 总块: || l_total_blocks); END; /进一步可以查询块内空间分布直方图针对ASSMDBMS_SPACE.SPACE_USAGE(..., partition_name NULL, ... -- 下面的b1-b5分别对应0-25% free, 25-50% free, 50-75% free, 75-100% free, full blocks );参数多写起来有点啰嗦但结果是直观的。如果大量块分布在full状态说明块填充很满如果大量块落在75-100% free这一档说明还有很多空间可以利用可能是PCTFREE偏高或者删除频繁。5. 常见误区与排错速查5.1 误区一PCTFREE只在MSSM下有用纠正无论ASSM还是MSSMpctfree始终有效它控制块内INSERT的封锁阈值。ASSM只是替代了pctused并没有替代pctfree。即便新建的默认表空间是ASSM你仍然会看到表定义里有PCTFREE而且实际生效。5.2 误区二PCTUSED设置高一点可以提高空间利用率在MSSM下pctused调高确实会让块在释放相对少空间后就被重新放回空闲列表理论上能更快复用空间但副作用是块在空闲列表上频繁进出进而引发插入性能抖动。更关键的是pctused设置过高会导致块内可用空间碎片化——一个块只剩5%的空闲空间也被放回空闲列表等下一条INSERT需要的长度超过这5%的空间该块又根本无法容纳新行Oracle还得继续搜索下一个块实际上是白白增加了空闲列表的访问开销。所以教科书通常建议pctused40这算是一个历史经验值。5.3 误区三把PCTFREE和缓冲池缓存命中率联系有时候网上说降低pctfree可以增加一块的记录数提高缓存命中率这话只在一部分场景成立。如果表的行数固定降低pctfree确实让一个块容纳更多行全表扫描需要读的块数变少但若伴随行迁移问题读取迁移行会导致额外I/O反而抵消了块数减少的优势。非要压缩空间前提是确认这个表没有明显的更新增长需求或者你愿意承担行迁移风险。5.4 常见症状与排查方向速查表现象可能原因处理方向查询慢但逻辑读不太高物理读增高UPDATE导致行迁移增加查CHAINED_ROWS调大PCTFREE并MOVE一张表全表扫描块数很多空间利用率低PCTFREE设过高或删除频繁分析空间使用直方图考虑调低PCTFREEINSERT很慢热块集中在少数几个块MSSM下PCTUSED设置不当freelist竞争改为ASSM或调整PCTUSED/增加FREELISTS后台出现大量buffer busy waits且有索引块的等待索引经常分裂块填充率过高考虑索引的PCTFREE设置通常10及重建修改PCTFREE后感觉没有效果旧块未重组参数只影响新分配块执行MOVE或在线重定义5.5 开发人员最常见的“手滑”细节在CREATE TABLE的列定义后写PCTFREE 10 PCTUSED 40时位置没有关系但要确认是写在表级别而不是写在某个列边。有一些工具生成的DDL会把存储子句放在TABLESPACE之后只要语法对就不影响。其次修改PCTFREE并不会释放任何已有空间如果你是想回收表空间应该用SHRINK SPACE或MOVE。6. 不同业务场景下的参数选择参考场景建议PCTFREE说明流水日志表只insert不update且会定期清理0-5追求空间利用率订单主表状态频繁更新且更新会写入备注15-25预留足够的行扩展空间配置表数据量小读写均衡10默认无需特别调整大字段表含CLOB、VARCHAR2大文本初始为空后回填30以上尽量避免行迁移再不行考虑大字段单独存放频繁delete的历史归档表10-20同时要配合周期回收空间这些值不是死教条。设置前一定要去了解一下业务到底怎么用这张表的。我在实际生产环境里干过一件蠢事把一张轨迹回填表的PCTFREE设为10当时想的是默认值应该没问题结果业务上线一个月后大量行因为后续回填了20KB的JSON串而发生迁移某些热点账户的查询慢到不可接受。后来分析了一下行长的变化把PCTFREE调到40再MOVE了一次问题立刻消失。再有就是如果你用了压缩表表级压缩或行压缩PCTFREE的作用就没那么直观了。压缩表块内数据的填充逻辑由压缩算法介入PCTFREE更多是留出压缩处理时的临时空间一般不建议再按照普通表的思路去估算。设置了压缩就别乱调遵循Oracle默认参数更稳妥。实际上对于现代版本19c及以后我几乎没有手动设置过pctused因为默认ASSM早就接管了。而pctfree我还会根据表特征去改一改尤其是那些更新后行长度可能翻倍的表。建议大家在建立核心业务表的时候跟开发对一下更新场景别一股脑用默认值。最后分享一个我一直在用的检查单创建新表之后先通过USER_TABLES确认PCTFREE/PCTUSED符合设计预期上线前塞入一批模拟数据跑一轮增删改再用ANALYZE TABLE LIST CHAINED ROWS检查迁移率。运维过程中每季度抽查一次大表迁移率。这套流程看起来不起眼但能有效避免很多存储参数引发的隐性性能问题。
