Oracle 12c使用ASH分析索引分裂性能延迟

在Oracle领域，经验丰富的DBA在分析索引分裂时，通常会基于ASH机制进行诊断。以下是对相关技术点的重新梳理。

没有直接标签，但索引分裂会留下诊断痕迹

一个核心判断需要明确：Oracle的ASH（Active Session History）机制本身并不会直接打上一个“此处在发生索引分裂”的标签。所以，指望在ASH里直接搜索index split这个词，那肯定是徒劳的。但是，索引分裂这种操作，尤其是高并发下的分裂，会在系统运行时留下非常明显的“指纹”。我们的任务，就是去识别这些痕迹。

比如说，最常见的信号就是buffer busy waits。当多个会话都在争抢同一个索引的叶块或者段头块，为了完成分裂前的pin/unpin操作时，这个等待事件就会飙升。在RAC环境里，分裂之后新产生的右块物理位置可能变得分散，导致跨节点的gc current block 2-way争用显著增加。还有一个容易被忽略的信号是db file sequential read，如果它伴随着异常的BLKS_GETS_PER_ACCESS，比如一张表本该做范围扫描顺序读10个块，但实际上逻辑读了15个以上，这就暗示着叶块的物理连续性已经被破坏了。

所以，诊断的关键在于组合分析，而不是只看一个点。你需要把current_obj#（对应到具体的索引对象ID）、sql_id以及会话状态session_state = 'ON CPU'这几个信息串联起来，看看它们是否都集中在索引维护的路径上。

没查到enq: TX - index contention就说明没有分裂？

这是个常见的误解。enq: TX - index contention这个事件，更多是出现在像递增主键这种右侧热点导致的ITL（Interested Transaction List）争用场景里。很多其它类型的索引分裂，压根儿就不会触发这个事件。所以说，别只等着这一个信号。

为什么V$ACTIVE_SESSION_HISTORY查不到最近的分裂线索？

这通常跟时间窗口和内存配置有关，具体来看有几个主要原因：

• 查询时间范围不够小：ASH默认保留大约1小时的数据，但在高负载下，缓冲区很快就会被新数据覆盖。如果不加WHERE sample_time > SYSDATE - 1/1440这个条件来限定过去1分钟的数据，很可能扫到的都是被冲刷过的老数据。
• ASH缓冲区（_ash_size）太小：默认配置通常是4MB，这个大小对于生产环境来说往往捉襟见肘。你可以通过查询SELECT * FROM v$sgastat WHERE name LIKE '%ASH%'来检查，如果bytes长期低于10MB，那么采样丢失的概率会非常高。
• 分裂本身太快、太短：一次典型的90-10分裂可能只持续几毫秒，而ASH是每秒才采样一次。这就像用一台慢速相机去抓拍一个瞬间的动作，很容易就错过了。

当遇到这种情况，就需要换个思路。可以退一步，用ANALYZE INDEX ... VALIDATE STRUCTURE命令来检查INDEX_STATS。如果发现DEL_LF_ROWS_LEN / LF_ROWS_LEN的比值超过了20%，这基本上就是分裂后空间未能有效复用的铁证了。

怎么把ASH数据和具体的索引精准关联起来？

光靠current_obj#对出索引名是不够的，还要验证这些会话的访问是不是真的落到了叶块上。步骤可以这样分解：

1. 定位可疑SQL与对象：先从ASH里找出有问题的sql_id，然后通过dba_objects将其关联到具体的表和索引对象ID。比如，可以运行类似SELECT object_name, object_type FROM dba_objects WHERE object_id IN (SELECT current_obj# FROM v$active_session_history WHERE sql_id = '...' ...)的查询。
2. 确认DML活动：接着检查这个索引是否正在被频繁的DML操作访问。查询dba_indexes中对应表的monitoring字段，如果它的值是'YES'，就说明近期有大量更新活动。
3. 核实执行计划：最后，查看相关SQL的执行计划，确认是否使用了该索引的RANGE SCAN或FAST FULL SCAN。同时，要特别关注access_predicates，看看是不是因为绑定变量值倾斜，导致了索引反复分裂。

有一点需要提醒的是，ASH里的sql_id可能因为SQL被淘汰而不再存在于V$SQL中了。这时候，优先去DBA_HIST_SQLTEXT中查找，或者使用12c及以后版本提供的DBMS_XPLAN.DISPLAY_ASH工具来还原上下文。

重建索引前，跑个ALTER INDEX ... COALESCE够用吗？

COALESCE确实有用，但它不是什么灵丹妙药，效果是有前提的。它只适用于那些叶块间虽然有很多删除操作（DEL_LF_ROWS很多），但总行数（LF_ROWS）不多，且总块数（BLOCKS）没显著增长的情况。它的优点是操作期间不锁整个索引，允许DML继续执行。但缺点也很明显：它无法降低索引的层级（BLEVEL），也不能清理枝节点（分支节点）的碎片。

如果从INDEX_STATS里看到BLOCKS数远大于LF_BLKS数，并且BLKS_GETS_PER_ACCESS的值大于5，那就说明索引分裂已经造成了物理上的严重离散。这时，COALESCE的效果就很有限了，最终还是得上REBUILD。

这里还有一个经常被忽略的陷阱：重建前，务必确认这个索引有没有被DBMS_SCHEDULER的job或者后台批量处理程序频繁调用。这些会话在ASH里通常表现为program LIKE '%j00%'。如果没把这些job先停掉，REBUILD操作很可能会因为等待DDL lock而一直卡住，反过来也会拖慢job的执行。