生产排故_ORA-01000: maximum open cursors exceeded

一森咖记 2019-11-07

3539

【此为"一森咖记"公众号——第68篇文章】

本文预计阅读15分钟

【引言】

项目组今天来找，反馈应用程序日志记录了一连接在昨晚发生连接中断现象，让查一查数据库端有何异常。对应昨晚异常发生时间点，在库alert日志中找到相应时间段的报错信息，报错信息如下：

【报错信息】

Wed Nov 06 20:23:03 2019
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x337FA00010000] [PC:0x400000000714AE40, $cold_kqdReleaseACursor()] [flags: 0x0, count: 1]
Errors in file oracle/diag/diag/rdbms/ethandb/ethandb/trace/ethandb_ora_8260.trc  (incident=766554):
ORA-07445: exception encountered: core dump [kqdReleaseACursor()] [SIGSEGV] [ADDR:0x337FA00010000] [PC:0x400000000714AE40] [Address not mapped to object] []
ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
Incident details in: oracle/diag/diag/rdbms/ethandb/ethandb/incident/incdir_766554/ethandb_ora_8260_i766554.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.

问题现象也很明显：见上述alert日志报错“ORA-01000: maximum open cursors exceeded”。

原因分析

Oracle 使用 OPEN_CURSORS 参数指定一个session会话一次最多可打开的游标数。超过此数量时，Oracle 将报ORA-01000 错误。当此错误反应在 WebLogic Server 时，显示为抛出 SQLException。

之前的一篇微信推文

《排故篇：ADG实例被LGWR进程宕掉？！》介绍过cursor。

【分析过程】

1. 查询数据库定义的单个session可以打开游标的最大数

SYS@ethandb> show parameter open_cursors


NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
open_cursors                         integer
2000

Oracle 的OPEN_CURSORS缺省值为 50。将 OPEN_CURSORS 的值设置得足够大，以避免应用程序用尽所有打开的游标。

注意：

应用程序不同，调用cursor数也不同。单个session实际打开的游标数未达 OPEN_CURSORS 指定的数量，也不会增加内存开销。

2. 显示特定用户为每个会话打开的游标数

col MACHINE format a50

col OSUSER format a50

set linesize 999

select o.sid, osuser, machine, count(*) num_curs

from v$open_cursor o, v$session s

where user_name = 'USERNAME' and o.sid=s.sid

group by o.sid, osuser, machine

order by num_curs desc;

此查询中的 'USERNAME' 应为用于创建连接池的用户。

提醒：

v$open_cursor 可以跟踪会话中 PARSED 和 NOT CLOSED 的动态游标（使用 dbms_sql.open_cursor() 打开的游标）。但不会跟踪未经分析（但已打开）的动态游标。在应用程序中使用动态游标并不常见。

3. 获取为游标执行的 SQL

使用在以上查询结果中找到的 SID 运行下面的查询，将显示哪些sql正在连接执行。

SQL> select q.sql_text

2 from v$open_cursor o, v$sql q

3 where q.hash_value=o.hash_value and o.sid = SID;

【解决方案】

大致为两种方式。

方式1 ：首先恢复应用，调大参数open_cursors，该参数为动态参数，可立刻生效。

sql > alter system set open_cursors=3000 scope=both;

注意：

如果应用程序使用的是tomcat，一定重启下tomcat, 否则修改不生效。

方式2：应用程序正确编码，调整应用代码

在java引用代码中,灭此执行conn.createStatement()和conn.prepareStatement()时，数据库中会打开了一个cursor。如createStatement和prepareStatement是写在一个while循环或者for循环中，则非常容易导致单个session超出open_cursors值。因为游标一直在不停的打开，没有及时关闭。

通常写Java代码时，createStatement和prepareStatement应放在循环外，且应及时关闭。建议每调用一次executeQuery、executeUpdate等之后，如不再使用结果集（ResultSet）中数据，调用close()方法进行关闭。

根据 JDBC 规范，关闭 Connection 时正常情况下也会将 Statement 和 ResultSet 关闭，但好的做法是：如果在一个 Connection 对象上创建了多个 Statement，则在使用完 Statement 和 ResultSet 后立即显式将它们关闭。如果未立即显式关闭 Statement 和 ResultSet，游标可能会积聚并在关闭 Connection 前超过数据库允许的最大数量。

示例如下：

Connection conn = null;

try{

conn = getConnection();

for(int i = 0; i < NUM_STMT; i++) {

Statement stmt = null;

ResultSet rs = null;

stmt = conn.createStatement();

rs = stmt.executeQuery(/*some query*/);

//do work

}

} catch(SQLException e) {

// handle any exceptions

} finally {

try{

if(conn != null)

conn.close();

} catch(SQLException ignor) {}

}

上述代码中，通过 finally 块关闭 Connection 时，也会将 ResultSet 和 Statement 关闭；但一个连接上for循环创建了多个 Statement 和 ResultSet。如循环数超出了数据库open_coursors的设定值，则会发生“java.sql.SQLException: ORA-01000: maximum open cursors exceeded”报错。

【划重点】

为提高性能，WebLogic Server 提供语句缓存功能。当 WebLogic Server 将预处理语句或可调用语句载入缓存时，DBMS 将为每个打开的语句都保留游标；“语句缓存大小”属性决定在每个连接池实例中为每个session缓存的预处理和可调用语句的总数。如果缓存的语句过多，可能会导致超过数据库服务器游标上限，报错ORA-01000: maximum open cursors exceeded。

简要介绍下WebLogic 缓存Statement Cache Size，详见【参考2】链接

JDBC Connection Pool的调优受制于WebLogic Server线程数的设置和数据库进程数,游标的大小。通常我们在一个线程中使用一个连接,所以连接数并不是越多越好,为避免两边的资源消耗，建议设置连接池的最大值等于或者略小于线程数。同时为了减少新建连接的开销,将最小值和最大值设为一致。

增加Statement Cache Size对于大量使用PreparedStatement对象的应用程序有帮助,WebLogic能够为每一个连接缓存这些对象,此值默认为10。在保证数据库游标大小足够的前提下,可以根据需要提高Statement Cache Size。比如当你设置连接数为25,Cache Size为10时,数据库可能需要打开25*10="250个游标。

注意：

各版本 WebLogic Server 的缺省语句缓存大小是有差异；WebLogic Server 6.1 中为0；WebLogic Server 7.0中为5；WebLogic Server 8.1 中为10。如对旧版本 WebLogic Server 进行了升级，语句缓存的行为变化可能会影响打开游标的数量。

排查过程如下：

1. 先将语句缓存大小设置为 0 将此功能关闭或减少缓存大小，再确认是否仍会出现错误；

2. 如果在减少缓存大小后问题没有发生，则说明连接池原有的语句缓存过大或 DBMS 中打开游标数的上限过低；那么调小WebLogic Cache Size 或者增大数据库的open_cursors；

3. 若打开的游标数持续增加，将语句缓存大小设置为 0 后不再出现这种现象，则说明存在游标泄漏，可能是由使用的 JDBC 驱动程序所致，也可能是 WebLogic Server 本身一错误；

4. 尝试使用其它 JDBC 驱动程序。如仍发生同样现象，再确定该问题是否为 WebLogic Server 自身错误，找WebLogic 原厂协助处理。

排查分析，至此结束。

最后，介绍下常用的cursor 查询语句

1. 查看系统中已打开游标的最大数和允许打开有表达最大数

SELECT MAX(a.value) AS highest_open_cur,

p.value AS max_open_cur

FROM v$sesstat a,

v$statname b,

v$parameter p

WHERE a.statistic# = b.statistic#

AND b.name = 'opened cursors current'

AND p.name = 'open_cursors'

GROUP BY p.value;

2.查看系统中打开游标数的SID

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'

ORDER BY 1 DESC;

3. 通过SID去查找未关闭PreparedStatement的SQL

SELECT oc.sid,

oc.hash_value,

oc.sql_text,

COUNT(*) how_many

FROM v$open_cursor oc

GROUP BY sid,

hash_value,

sql_text

ORDER BY 4 DESC;

【结语】

1. 本文介绍了ORA-01000: maximum open cursors exceeded的原因分析和排查过程；

2. 常见的原因分析有三种，要么连接池JDBC有问题，要么weblogic的Statement Cache Size大小设置不当，或者是数据库的open_cursors数值小导致；排查顺序为检查故障时最大的cursor连接数，修改open_cursors;然后检查最近weblogic是否升级，调整语句缓存大小；接着排查JDBC，尝试使用其他版JDBC。

3. 考虑到JDBC和weblogic最近均未改动或升级，为不影响业务，本文解决方案是增加了数据库open_cursorsz大小，有2000调整为3000，因该参数为动态参数，不用停启库。后续现象留待观察。

【参考1】

https://www.cnblogs.com/huanghongbo/p/5569034.html

【参考2】

https://www.cnblogs.com/kaka-bing/p/3191332.html

【参考3】

https://my.oschina.net/zenglingfan/blog/62746