Oracle数据库执行SQL语句卡住“正在执行”状态，如何排查和解决？

Oracle数据库SQL语句长时间卡在“正在执行”状态，通常由锁冲突、事务未提交或资源瓶颈导致，可通过检查会话锁、优化锁机制、管理事务、监控资源及调整参数解决。 以下是具体排查与解决步骤：

• 查询锁信息：使用以下SQL语句检查是否有其他会话持有SM_DICT_ITEM表中sm_grkey='CCFORM_TSGD_TYPE'记录的锁：

  SELECT s.sid, s.serial#, s.username, s.status, l.type, l.lmode, l.request, l.block, o.object_name, o.object_typeFROM v$session s, v$lock l, dba_objects oWHERE s.sid = l.sid AND l.id1 = o.object_id AND o.object_name = 'SM_DICT_ITEM' AND l.block > 0; -- 确认是否有阻塞会话

  若结果中block=1，表示该会话正在阻塞其他操作。记录阻塞会话的sid和serial#。

• 终止阻塞会话：若确认阻塞会话无必要运行，可通过以下命令终止（需DBA权限）：

  ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

• 减少锁范围：原SELECT ... FOR UPDATE会锁定整个结果集，若仅需更新特定行，可改用UPDATE直接操作，避免显式加锁。例如：

  -- 原卡住语句（需获取锁）SELECT * FROM SM_DICT_ITEM WHERE sm_grkey='CCFORM_TSGD_TYPE' FOR UPDATE;-- 替代方案（直接更新）UPDATE SM_DICT_ITEM SET sm_orderno=1 WHERE sm_grkey='CCFORM_TSGD_TYPE';

• 使用行级锁：若必须加锁，确保WHERE条件精确匹配单行（如主键），避免锁定多行。

• 识别长事务：查询未提交的事务，确认是否有会话长时间未提交或回滚：

  SELECT s.sid, s.serial#, s.username, s.status, t.start_time, t.status AS tx_statusFROM v$session s, v$transaction tWHERE s.saddr = t.ses_addr;

  若事务start_time过早且未结束，需联系用户提交或回滚。

• 优化事务设计：避免在事务中执行耗时操作（如网络调用、复杂计算），缩短事务持有锁的时间。

• 检查资源使用：通过操作系统工具（如top、iostat）或Oracle动态性能视图监控CPU、内存、I/O是否过载：-- 查询高CPU消耗会话SELECT s.sid, s.serial#, s.username, s.program, q.sql_text, q.cpu_time/1000000 AS cpu_secFROM v$session s, v$sql qWHERE s.sql_id = q.sql_id AND s.status = 'ACTIVE'ORDER BY q.cpu_time DESC;若资源不足，需扩容或优化SQL（如添加索引）。

• 修改锁等待超时：通过IDLTIME1参数设置锁等待超时时间（单位：百分之一秒），默认无限制。例如设置为5分钟（30000）：

  ALTER SYSTEM SET IDLTIME1=30000 SCOPE=BOTH;

  此参数需谨慎调整，避免影响正常业务。

• 其他参数优化：根据负载调整DB_WRITER_PROCESSES（I/O线程数）、PGA_AGGREGATE_TARGET（内存分配）等参数。

• 检查表状态：确认表是否处于LOCKED或CHECK等特殊状态：

  SELECT object_name, status FROM dba_objects WHERE object_name='SM_DICT_ITEM';

• 日志分析：检查告警日志（$ORACLE_BASE/diag/rdbms/<SID>/trace/alert_<SID>.log）是否有相关错误。

• 联系DBA：若问题复杂或涉及生产环境，建议由DBA进一步分析（如使用AWR报告、ASH采样）。

总结：优先通过锁查询定位阻塞源并终止会话，优化锁粒度与事务设计，监控资源瓶颈，最后调整参数。若仍无法解决，需深入分析执行计划或寻求专业支持。