不可终止的 Oracle 会话正在等待“来自客户端的 SQL*Net 消息”事件

在 Oracle 11gR2 上，我最近遇到了一个非常有趣的情况，涉及阻塞（但空闲！）MERGE挂在 a 上的声明“来自客户端的 SQL*Net 消息”事件，导致后续并发执行MERGE通过以下方式阻止第一个语句的语句“光标：引脚 S 等待 X”事件。在 Oracle Enterprise Manager 中，可以观察到以下情况：

这种情况变得更加严重，因为上述 Session-ID 1204 无法通过以下任一方法终止：

alter system kill session 'sid,serial#';
alter system kill session 'sid,serial#' immediate;

我们的DBA有时能够杀死操作系统进程，但很多时候，整个数据库需要重新启动。幸运的是，到目前为止，仅在测试系统上进行，从未投入生产。

Note:

我知道这可能是一个与这个相当模糊的问题中报告的类似问题：Oracle 更新/插入卡住、DB CPU 为 100%、并发度高、来自客户端的 SQL*Net 等待消息 https://stackoverflow.com/q/14043668/521799。我仍然会再次报告它，因为我有一个清晰的复制路径，我将报告它作为答案。

这似乎是 Oracle 中的一个错误CLOB数据类型用作传递给的值MERGE声明的ON条款。假设这个数据库：

CREATE TABLE t (
  v INT, 
  s VARCHAR2(400 CHAR)
);

使用内联值进行复制

现在，在任何 Oracle 客户端（包括 SQL*Plus、SQL Developer 或 JDBC）中运行以下语句，这有助于非常轻松地重现问题（我使用的是 Oracle 11g XE 11.2.0.2.0）：

MERGE INTO t                      
USING (
  SELECT 
    1 v, 
    CAST('abc' AS CLOB) s 
  FROM DUAL
) s 
ON (t.s = s.s) -- Using a CLOB here causes the bug.
WHEN MATCHED THEN UPDATE SET
  t.v = s.v        
WHEN NOT MATCHED THEN INSERT (v, s) 
VALUES (s.v, s.s);

这个例子很愚蠢，而且CLOB因“意外”而被束缚在这里。尽管如此，这样的语句不应在 Oracle 中创建僵尸会话，但它确实存在。我在 SQL*Plus 中运行上述语句 3 次，然后运行这个...

SELECT 
  s.sid,
  s.serial#,
  s.sql_id,
  s.event,
  s.blocking_session,
  q.sql_text
FROM v$session s
JOIN v$sql q
ON s.sql_id = q.sql_id
WHERE s.username = 'TEST'
AND UPPER(TRIM(q.sql_text)) LIKE 'MERGE%';

...我得到：

sid serial# sql_id          event                       blocking_session
9   3       82a2k4sqzy1jq   cursor: pin S wait on X     92
49  89      82a2k4sqzy1jq   cursor: pin S wait on X     92
92  13      82a2k4sqzy1jq   db file sequential read     

请注意报告的事件有何不同（“数据库文件顺序读取”）来自原始事件（“来自客户端的 SQL*Net 消息”），它使用了绑定变量

使用绑定值进行复制

var v_s varchar2(50)
exec :v_s := 'abc'

MERGE INTO t                      
USING (
  SELECT 
    1 v, 
    CAST(:v_s AS CLOB) s 
  FROM DUAL
) s 
ON (t.s = s.s) -- Using a CLOB here causes the bug.
WHEN MATCHED THEN UPDATE SET
  t.v = s.v        
WHEN NOT MATCHED THEN INSERT (v, s) 
VALUES (s.v, s.s);

上述语句在 SQL*Plus 中运行也会产生错误：

sid serial# sql_id          event                           blocking_session
8   1       4w9zuxrumumgj   SQL*Net message from client     
90  7       4w9zuxrumumgj   cursor: pin S wait on X         8
94  21      4w9zuxrumumgj   cursor: pin S wait on X         8

PL/SQL 中无法复制

有趣的是，以下 PL/SQL 语句避免了该错误：

DECLARE
  v_s CLOB := 'abc';
BEGIN
  MERGE INTO t                      
  USING (
    SELECT 
      1 v, 
      CAST(v_s AS CLOB) s 
    FROM DUAL
  ) s 
  ON (t.s = s.s) -- Using a CLOB here causes the bug.
  WHEN MATCHED THEN UPDATE SET
    t.v = s.v        
  WHEN NOT MATCHED THEN INSERT (v, s) 
  VALUES (s.v, s.s);
END;
/

我越来越：

          CAST(v_s AS CLOB) s
          *
ERROR at line 8:
ORA-06550: line 8, column 11:
PL/SQL: ORA-00932: inconsistent datatypes: expected - got CLOB
ORA-06550: line 4, column 7:
PL/SQL: SQL Statement ignored

看起来 PL/SQL 引擎可以保护客户端免受此 SQL 引擎错误的影响。