Oracle更新/插入卡住，数据库CPU占用100％，并发性高，来自客户端的SQL * Net等待消息 [英] Oracle updates/inserts stuck, DB CPU at 100%, concurrency high, SQL*Net wait message from client

问题描述

我们有一个使用瘦JDBC驱动程序针对Oracle 11g DB在Weblogic上运行的JavaEE应用程序. 最近，我们在生产中发生了一系列事件，这些事件导致对某个表的更新和插入被卡住或花费的时间比正常情况长得多，没有明显的原因. 这导致应用程序使用越来越多的数据库连接(通常在连接池中处于空闲状态)，数据库CPU和并发性急剧上升(如OEM所示)，整个数据库陷入瘫痪. 在这些事件中，DBA找不到插入和更新卡住的任何原因(没有数据库锁).他们看到的是很多"来自客户端的SQL * Net等待消息"事件.

We have a JavaEE app running on Weblogic against Oracle 11g DB, using thin JDBC driver. Recently we had a series of incidents in production where updates and inserts into a certain table got stuck or took much longer than normal, with no apparent reason. This caused the application to use more and more DB connections (normally idle in the connection pool), the DB CPU and concurrency shot up (as seen in OEM) and the whole DB ground to a halt. During these incidents the DBAs could not find any reason for Inserts and Updates to be stuck (no db locks). What they did see were a lot of "SQL*Net wait message from client" events.

他们的理论是，应用程序(jdbc客户端)由于某种原因与数据库无关，而在插入/更新语句期间卡住了，而没有确认数据库对这些语句的响应.该应用程序继续发出越来越多的这些语句占用越来越多的连接这一事实，这是CPU和并发性急剧上升，使数据库无法响应的原因.

Their theory is that the app (the jdbc client) got stuck, somehow, during insert/update statements, for a reason unrelated to the DB, while not acknowledging the DB response to these statements. And the fact that the app continued issuing more and more of these statements tying up more and more connections, was the reason that the CPU and concurrency shot up, making the DB unresponsive.

我不相信-如果所有会话都在忙于等待客户端，那么CPU为何这么高? 我们无法始终如一地重现这些事件，因此我们真的在黑暗中……

I'm not convinced - if all the sessions were busy waiting for clients, how come the CPU was so high? We weren't able to consistently reproduce these incidents so we are really in the dark here...

有人看到过这样的东西或有什么想法，建议可能是由什么引起的吗?

Has anyone seen anything like this or have any ideas, suggestions of what this might be caused by?

谢谢

推荐答案

我遇到了类似的问题，已在此处进行了记录:.就我而言，问题是由CLOB类型的绑定变量引起的，该绑定变量绑定到了CLOB似乎在Oracle中引起严重问题的地方.以下语句产生的行为与您观察到的相同:

I've encountered a similar issue, which I've documented here: Unkillable Oracle session waiting on "SQL*Net message from client" event. In my case, the problem was caused by a bind variable of type CLOB that was bound to a place where CLOBs seem to cause severe issues in Oracle. The following statement produces the same behaviour as you've observed:

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

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);

很可能，除了MERGE之外，还有其他一些情况也暴露了这种行为，从而产生了僵尸会话，因为Oracle似乎运行了一些无限循环，从而产生了观察到的CPU负载.

Probably, there are other occasions with other statements than MERGE that expose this behaviour producing zombie sessions as well, as Oracle seems to run some infinite loop producing the observed CPU load.

这篇关于Oracle更新/插入卡住，数据库CPU占用100％，并发性高，来自客户端的SQL * Net等待消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！