Oracle加索引导致锁表如何避免？

Question

在Oracle数据库中，在线加索引操作（如使用CREATE INDEX）虽支持并发DML，但在某些场景下仍可能引发锁表现象，尤其是在大表上创建索引时占用大量资源，导致DML语句阻塞。常见问题是：为何在添加索引时，表级锁会被持有较长时间，影响业务正常写入？如何通过NOLOGGING、并行创建或使用在线重定义等手段减少锁争用？特别是在主键或唯一约束隐式创建索引时，如何避免DDL期间的长时间锁定？需深入分析加索引过程中的锁机制及优化策略。

Answer 1

一、Oracle在线加索引的锁机制与并发DML支持概述

在Oracle数据库中，CREATE INDEX 操作默认支持“在线”模式（即使用 ONLINE 关键字），允许在创建索引的同时执行DML操作（INSERT、UPDATE、DELETE）。然而，尽管官方宣称“支持并发”，实际生产环境中仍频繁出现DML阻塞现象，尤其是在大表上创建索引时。

其根本原因在于：虽然Oracle通过ITL（Interested Transaction List）和临时回滚段实现了部分行级锁隔离，但在DDL执行的关键阶段，仍需获取特定类型的表级锁以保证元数据一致性。

1.1 典型锁类型分析

锁模式	名称	说明	是否阻塞DML
3	Row Share (RS)	允许并发DML	否
4	Share (S)	用于创建主键/唯一约束索引	是（部分）
5	Share Row Exclusive (SRX)	高争用，常导致阻塞	是
6	Exclusive (X)	完全独占，禁止所有DML	是

例如，在添加主键约束时，即使指定了USING INDEX ONLINE，Oracle仍会在验证数据唯一性阶段申请Mode 4 (Share)锁，该锁会阻塞所有INSERT和UPDATE操作。

二、为何大表加索引会导致长时间表级锁？

当对包含数亿行记录的表执行CREATE INDEX ONLINE时，以下三个阶段可能引发锁等待：

1. 初始扫描阶段：读取表数据构建索引条目，此时持有Row Share锁，一般不影响DML。
2. 合并变更阶段（Merge Phase）：应用在索引构建期间产生的DML变更（记录于Journal表），此阶段需短暂升级为Share Lock (Mode 4)，造成瞬时阻塞。
3. 元数据提交阶段：将新索引注册到数据字典，需Dictionary DDL Lock，若存在长事务则等待加剧。

特别地，若未提前收集统计信息或系统I/O负载过高，扫描阶段耗时延长，间接增加整体锁持有时间窗口。

三、优化策略对比与实践建议

为降低锁争用风险，可采用多种技术手段组合使用：

3.1 使用 NOLOGGING + 并行创建加速过程

ALTER SESSION ENABLE PARALLEL DML;

CREATE INDEX idx_large_tbl_col 
ON large_table(col) 
PCTFREE 0 
NOLOGGING 
PARALLEL 8 
ONLINE;
    

NOLOGGING减少重做日志生成，提升写入性能；并行度设置合理值（如CPU核心数的70%）可显著缩短执行时间，从而压缩锁窗口。

3.2 在线重定义（DBMS_REDEFINITION）规避DDL锁定

适用于超大表且无法容忍任何阻塞场景。流程如下：

graph TD A[创建中间结构表] --> B[启动重定义任务] B --> C[复制原始数据] C --> D[同步增量变更] D --> E[交换表名完成切换] E --> F[删除原表]

整个过程对应用透明，仅在最终交换时有秒级不可用，极大提升了可用性。

3.3 主键/唯一约束创建的特殊处理

隐式创建索引时，默认不走纯Online路径。推荐分步操作：

• 先手动创建UNIQUE INDEX ONLINE；
• 再添加约束：ALTER TABLE t ADD CONSTRAINT pk_t PRIMARY KEY (id) USING INDEX idx_id;
• 避免一次性执行ADD CONSTRAINT ... PRIMARY KEY触发全表扫描+共享锁。

四、监控与诊断方法

实时检测锁争用情况至关重要。可通过以下SQL定位问题：

SELECT do.object_name, 
       lo.session_id, 
       lo.oracle_username,
       lo.locked_mode,
       sq.sql_text
FROM   v$locked_object lo
JOIN   dba_objects do ON lo.object_id = do.object_id
LEFT JOIN v$session s ON lo.session_id = s.sid
LEFT JOIN v$sql sq ON s.sql_id = sq.sql_id
WHERE  do.object_name = 'LARGE_TABLE';
    

结合AWR报告中的“Segment SQL Statistics”分析索引创建期间的Top SQL与等待事件（如enq: TM - contention）。

五、高级调优技巧与最佳实践总结

综合多年运维经验，提出以下进阶建议：

场景	推荐方案	预期效果
百亿级表加索引	DBMS_REDEFINITION + 分区交换	零停机窗口
高并发OLTP系统	NOLOGGING + PARALLEL 4~8	减少50%以上时间
归档模式下安全要求高	延迟应用归档日志补偿	平衡性能与恢复能力
主键添加需求	预建唯一索引 + 约束启用	避免全表锁
频繁失败的Online Index	清理Journal表（sys.journal_xxx）	解决ORA-00600等内部错误

此外，启用_fast_index_split隐含参数（需评估稳定性）可优化B-tree分裂效率，进一步提升构建速度。