Oracle加索引锁表异常

项目场景：

根据现场反馈，各条产线出现设备宕机报警CIM回复超时，人工站点MES系统无响应的情况。

---

原因分析：

直接切入MES DB，查询发生时间点执行的SQL语句的历史记录。
SQL语句如下：

select sample_time, sql_id, event, blocking_session, blocking_session_serial#, wait_time
from gv$active_session_history 
where sample_time > TO_TIMESTAMP('2024-08-30 13:11:00.000000', 'SYYYY-MM-DD HH24:MI:SS:FF6')
and sample_time < TO_TIMESTAMP('2024-08-30 13:14:00.000000', 'SYYYY-MM-DD HH24:MI:SS:FF6')
order by sa!mple_time asc;

根据下图查询出的结果可以看出，大部分事务都被事务4264所阻塞，唯一号为61195。
另外在执行dml期间，为防止对与dml相关的对象进行修改，执行dml的进程必须对该表获得TM锁，若在获得TM锁的过程中发生争用，则等待enq:TM-contention事件，与现状一致。

寻找根源，再用相同的方式进行二次查询，这次我们将阻塞源头加入查询条件；

select 
sample_time,sql_id,event,blocking_session,blocking_session_serial#,wait_time
from gv$active_session_history 
where session_id = '4264'
and session_serial# = '61195';

下一步我们根据SQL_ID看看这个SQL具体执行的是什么内容。
但是我们从gv$sql、gv$sqlarea和gv$sqltext三个视图中都查询不到这个SQL_ID对应的信息，最后在gv$sql_monitor中查出了这条SQL。这里比较奇怪，可能是因为这条SQL只执行了一次所以没有被记录在那些记录频繁执行的SQL语句的视图中。

到这里问题的根因就水落石出了，Oracle在给表建立索引时不使用ONLINE选项可能会导致表被锁定，在默认情况下，当你创建一个索引时，Oracle会在事务处理（DML）操作之前获取一个表的 exclusive 锁。这意味着在索引创建期间，表将被锁定，无法进行插入、更新或删除操作。
使用ONLINE选项可以避免长时间锁表，ONLINE索引建立允许在不锁定表的情况下创建索引。这是通过在创建索引期间复制表的一部分来完成的，从而减少了锁定时间，但这也要求有额外的空间来存储复制的数据。

当然我们也可以通过Oracle EM13C工具进行分析更加简单方便，在SQL监视中我们可以发现‘6xpa7j8v2b88a’这个SQL在问题发生的时间段占用了大量IO，再进行上面最后一步操作找到问题点。

解决方案：

解决方法是在创建索引时使用ONLINE选项。这样可以最大程度地减少对表的影响。

CREATE INDEX idx_tablename_colname ON tablename(colname) ONLINE;