不可结束的Oracle会话正在等待“来自客户端的SQL * Net消息”事件 [英] Unkillable Oracle session waiting on "SQL*Net message from client" event
问题描述
在Oracle 11gR2上，我最近遇到一个非常有趣的情况，涉及一个阻塞（但是空闲！） MERGE 语句挂在<消息从客户端事件，导致随后，并发执行的 MERGE 语句在第一个语句通过cursor：pin S wait on X / em>事件。在Oracle企业管理器中，可以观察到以下内容：
这种情况变得更加严重，因为上面的Session-ID 1204不能用以下命令杀死：
alter system kill session'sid，serial＃';
alter system kill session'sid，serial＃'immediate;
我们的DBA有时能够杀死操作系统进程，但通常，整个数据库需要重新启动。幸运的是，到目前为止，只有一个测试系统，从不生产。
注意：
意识到这可能是一个类似的问题，报告在这个相当模糊的问题： Oracle更新/插入卡住，DB CPU在100％，并发高， SQL *来自客户端的净等待消息。
这似乎是Oracle中的一个错误，当
CLOB 数据类型用作传递给 MERGE code> 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（ts = ss） - 在这里使用CLOB会导致错误。
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中运行上面的语句三次，然后运行...
SELECT
.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'
和UPPER（TRIM（q.sql_text））LIKE '合并％';
...我得到：
sid serial＃sql_id事件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 message from client）中使用绑定变量
$ b $ b
使用绑定值重现
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（ts = ss） - 在这里使用CLOB会导致错误。
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事件blocking_session
8 1 4w9zuxrumumgj SQL *客户端的Net消息
90 7 4w9zuxrumumgj cursor：pin S wait on X 8
94 21 4w9zuxrumumgj cursor：pin S等待X 8
有趣的是，在以下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（ts = ss） - 在这里使用CLOB会导致错误。
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
*
第8行的错误：
ORA-06550：line 8，第11列：
PL / SQL：ORA-00932：不一致数据类型：expected - 获取CLOB
ORA-06550：第4行，第7列：
PL / SQL：SQL语句ignore
看起来好像PL / SQL引擎保护客户端不受此SQL引擎错误影响。
On Oracle 11gR2, I've recently encountered a very interesting situation involving a blocked (but idle!) MERGE statement that hangs on a "SQL*Net message from client" event, causing subsequent, concurrently executed MERGE statements to block on the first statement via "cursor: pin S wait on X" events. In Oracle Enterprise Manager, the following can be observed:
This situation turns even more severe, as the above Session-ID 1204 cannot be killed with either:
alter system kill session 'sid,serial#';
alter system kill session 'sid,serial#' immediate;
Our DBA is sometimes able to kill the operating system process, but often, the whole database needs to be restarted. Luckily, thus far, only on a test system, never in production.
Note:
I'm aware this is probably a similar issue as reported in this rather vague question: Oracle updates/inserts stuck, DB CPU at 100%, concurrency high, SQL*Net wait message from client. I'll still report it again, as I have a clear reproduction path, which I'll report as an answer.
This seems to be a bug in Oracle when CLOB data types are used as values that are passed to the MERGE statement's ON clause. Assume this database:
CREATE TABLE t (
v INT,
s VARCHAR2(400 CHAR)
);
Reproduction using inlined values
Now, run the following statement in any Oracle client, including SQL*Plus, SQL Developer or from JDBC, which helps reproducing the issue very easily (I'm using 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);
The example is silly, and the CLOB was bound here by "accident". Nonetheless, such a statement should not create a zombie session in Oracle, but it's there. I'm running the above statement three times in SQL*Plus and then running this...
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%';
... I get:
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
Notice how the reported event is different ("db file sequential read") from the original event ("SQL*Net message from client"), which was using bind variables
Reproduction using bind values
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);
The above statement run in SQL*Plus also produces the bug:
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
No reproduction in PL/SQL
Interestingly, the bug is avoided in the following PL/SQL statement:
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;
/
I'm getting:
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
It looks as though the PL/SQL engine saveguards clients from this SQL engine bug.
这篇关于不可结束的Oracle会话正在等待“来自客户端的SQL * Net消息”事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
