调试ORA-01000

Question

我正在修改一个遗留Spring应用程序，以使用动态配置的连接池。整个系统中的所有查询都使用JdbcTemplate，或者通过Jms连接到Oracle -我已经检查过，每次获得ResultSet时，都会在最后{}块的框架代码中关闭它。在我转到动态配置的连接池之前，这个应用程序从未遇到过任何问题。我目前使用C3P0作为我的连接池，但是使用DBCP2和Atomikos池也会出现同样的问题。以下是问题所在：

经过任何时间的处理(根据应用程序所做的事情似乎有所不同，但最终总是发生这种情况)，我的连接就用完了。出于绝望，我把连接数提高到了10k，但这只是延长了不可避免的时间。我运行以下查询：

select a.value, s.username, s.sid, s.serial# from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid and b.name = 'opened cursors current'
    and s.username = '<my app>' order by a.value asc ;

当然了，

 VALUE USERNAME                              SID    SERIAL#
------ ------------------------------ ---------- ----------
     1 MY_APP                                 69      16665
     2 MY_APP                                149       9703
     3 MY_APP                                 13      38401
     4 MY_APP                                100       8629
     4 MY_APP                                145      26291
    29 MY_APP                                 49      30425
  2997 MY_APP                                147      33539
  5317 MY_APP                                 52      12599
  6425 MY_APP                                102      14803
 10000 MY_APP                                 19      18469

好吧，我已经知道我快用完游标了。我知道我在web上看到的下一条建议是如何查找失控的资源泄漏，即运行类似以下查询的内容：

select sql_text, sid, count(*) from v$open_cursor where user_name = '<my app>'
group by sql_text, sid having count(*) > 1 ;

我一共赢了九排.没人指望这些人超过三人。如果我删除“total”子句并总计所有游标计数，它就相当于大约120个游标。不是10k！

select sql_text, count(*) from v$open_cursor where sid = 19 group by sql_text having count(*) > 1 ;

SQL_TEXT                                                       COUNT(*)
--------------------------------------------------------     ----------
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#         41

这是应用程序从JdbcTemplate正确关闭(也是隐式)的insert语句中隐式获取一个新的序列值。即使如此，41并不在10k的范围内！

我知道v$open_cursor表只表示“缓存”游标，并且在“打开游标当前”统计中计算的一些游标可能已经标记为“关闭”。有什么方法可以找出这些开放游标所涉及的确切进程或sql吗？

我尝试过几种方法来尝试通过应用程序来了解到底发生了什么。我创建了一个特殊的数据源包装器，用于包装所有连接、语句、准备好的语句、可调用语句及其所有生成的结果集，将它们保存在内存中的特殊集合中，并在关闭时将它们删除。我想我可以这样抓住它--相反，通过这个池打开的每一个资源都被关闭了。最后一个。我甚至在连接池和spring引擎的内部进行调试，以查看Oracle瘦ForwardOnlyResultSet被标记为已关闭。

我几乎可以怀疑问题在于与AQ的交互，只是这种机制在旧版本的应用程序中运行得很好。

我甚至尝试通过显式捕获ORA-01000错误来解决这个问题，并使用它作为触发器硬重置我的连接池。只要它在处理简单的任务，它就能工作，但是对于某些正在执行数千个数据库操作的大型进程，在进程完成之前，我就没有连接了；硬重置连接池会使事务无效，整个过程就会变成一个无限循环。糟糕的情况，到处都是。

我完全不知所措..。从应用程序端看，它看起来完全是水密闭的，而从db端来看，通过查看v$open_cursor，它看起来不像是有很多打开游标，只是在统计数据中。不幸的是，统计数字似乎很重要。

帮助?

Answer 1

第一个SQL显示会话19有10000个打开游标，第三个SQL显示其中只有一个是共享的。结论，会话19有近10,000个独特的开放游标。造成这种情况的通常原因是使用不可共享的SQL。ie，您在代码中没有使用任何绑定变量。这意味着每次应用程序运行SQL语句时，数据库都会为该语句打开一个新的游标。当你的应用程序运行了10000条语句时，你就会碰壁。

要解决这个问题，可以设置数据库参数CURSOR_SHARING=FORCE。这将导致数据库自动将绑定变量替换为SQL中的文字变量，从而使SQL语句可以共享。这样，每次应用程序运行SQL语句时，就不会有一个单独的游标打开。

这里有一篇关于CURSOR_SHARING=FORCE：force.htm的短文

如果您使用的是Oracle9i或更高版本，则可以使用CURSOR_SHARING=SIMILAR。(尽管一些DBA建议在Oracle 11或更高版本之前不要使用类似的，因为bug)

设置CURSOR_SHARING应该被认为是一个快速修复，而不是一个最终的解决方案。从长远来看，您应该重写您的应用程序，以便它使用绑定变量，而不是使用CURSOR_SHARING进行补偿。下面是一个很好的解释为什么：ID:5180609822543

最后，应该谨慎地使用设置CURSOR_SHARING，并注意到您可能没有得到您所期望的优化方案。