oracle超过最大游标数异常分析（转贴）

时间：2024-04-17 22:21:02
问题描述 
Oracle 使用 OPEN_CURSORS 参数指定一个会话一次最多可以打开的游标的数量。超过此数量时，Oracle 将报告 ORA-01000 错误。当此错误传播到 WebLogic Server 时，就会抛出 SQLException。
java.sql.SQLException: ORA-01000: maximum open cursors exceeded

本模式阐述在使用 WebLogic Server 时出现该错误的可能成因及解决办法。

故障排除
请注意，并非下面所有任务都需要完成。有些问题仅通过执行几项任务就可以解决。

快速链接

诊断查询 
以下 SQL 查询有助于诊断 ORA-01000 问题。要执行这些查询，需要以管理员身份登录数据库，或获得数据库管理员从那些 v$ 视图中进行选择的授权。

1. 检查数据库中的 OPEN_CURSORS 参数值。
Oracle 使用 init.ora 中的初始化参数 OPEN_CURSORS 指定一个会话一次最多可以拥有的游标数。缺省值为 50。遗憾的是，此缺省值通常对 WebLogic Server 这样的系统来说过小。要获得数据库中 OPEN_CURSORS 参数的值，可以使用以下查询：
SQL> show parameter open_cursors;

NAME TYPE VALUE 
------------------------------------ ----------- --------------- 
open_cursors integer 1000

重要的是将 OPEN_CURSORS 的值设置得足够大，以避免应用程序用尽所有打开的游标。应用程序不同，该值也不同。即便会话打开的游标数未达 OPEN_CURSORS 指定的数量（即设置的值高于实际需要的值），也不会增加系统开销。

2. 获取打开的游标数。
下面的查询按降序显示用户“SCOTT”为每个会话打开的游标数。
SQL> select o.sid, osuser, machine, count(*) num_curs
2 from v$open_cursor o, v$session s
3 where user_name = 'SCOTT' and o.sid=s.sid 
4 group by o.sid, osuser, machine
5 order by num_curs desc; 
SID OSUSER MACHINE NUM_CURS 
----------------------------------------------------- 
217 m1 1000
96 m2 10 
411 m3 10 
50 test 9 
在 WebLogic Server 中使用连接池时，此查询中的 user_name 应为用于创建连接池的 user_name（假定是从连接池得到连接）。该查询结果还给出了计算机名称。请在查询结果中找出打开游标数量大的 SID 和运行 WebLogic Server 的计算机的名称。

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

3. 获取为游标执行的 SQL。使用在以上查询结果中找到的 SID 运行下面的查询：
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 = 217;

SQL_TEXT 
------------------------------------------------------------------------------------------------------------------------
select * from empdemo where empid='212' 
select * from empdemo where empid='321' 
select * from empdemo where empid='947' 
select * from empdemo where empid='527' 
...

结果将显示正在连接上执行的查询。它提供了一个入手点，让您可以反向跟踪到打开游标的来源。

返回页首

常见成因及解决办法 
下面是查找问题成因及可能的解决办法的步骤。

代码惯例 
此问题的最常见成因是未正常关闭 JDBC 对象。使用诊断查询中第三个查询的结果在应用程序代码中反向跟踪，确保将所有 JDBC 对象都正常关闭。BEA 建议在 finally 块中显式关闭 Connection、Statement 和 ResultSet 等 JDBC 对象，以确保无论是在正常还是异常情况下都将所有 JDBC 对象关闭。下面是一个常规示例：
Connection conn = null; 
Statement stmt = null; 
ResultSet rs = null;

try { 
conn = getConnection(); //Method getConnection will return a JDBC Connection 
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from empdemo"); 
// do work 
} catch (Exception e) { 
// handle any exceptions
} finally { 
try { 
if(rs != null) 
rs.close(); 
} catch (SQLException rse) {}
try { 
if(stmt != null) 
stmt.close(); 
} catch (SQLException sse) {}
try { 
if(conn != null) 
conn.close(); 
} catch (SQLException cse) {}
}
请避免采用任何放弃 JDBC 对象的代码惯例。下面的代码惯例在每个循环迭代中都获得一个新的 Connection、Statement 和 ResultSet，但它没有关闭每个迭代的 JDBC 对象。因此，它会导致 JDBC 对象泄漏。
Connection conn = null; 
Statement stmt = null; 
ResultSet rs = null; 
String[] queries = new String[10]; 
//Define queries

try { 
for(int i = 0; i < 10; i++) { 
conn = getConnection(); 
stmt = conn.createStatement(); 
rs = stmt.executeQuery(queries[i]); 
// do work 
} 
} catch (Exception e) { 
// handle any exceptions
} finally { 
try { 
if(rs != null) 
rs.close(); 
} catch (SQLException rse) {}
try { 
if(stmt != null) 
stmt.close(); 
} catch (SQLException sse) {}
try { 
if(conn != null) 
conn.close(); 
} catch (SQLException cse) {}
}
尽管根据 JDBC 规范的规定，关闭 Connection 时正常情况下也会将 Statement 和 ResultSet 关闭，但好的做法是：如果在一个 Connection 对象上创建了多个 Statement，则在使用完 Statement 和 ResultSet 后立即显式将它们关闭。如果未立即显式关闭 Statement 和 ResultSet，游标可能会积聚并在关闭 Connection 前超过数据库允许的最大数量。例如，在以下代码片断中，正常情况下通过 finally 块关闭 Connection 时，也会将 ResultSet 和 Statement 关闭。不过，此代码片断在一个连接上创建了多个 Statement 和 ResultSet。因此在循环完成前，可能已发生“超出最多允许打开的游标数”问题。
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) {}
}
返回页首

语句缓存
为提高性能，WebLogic Server 提供了一种功能，让您可以在使用连接池时将预处理语句和可调用语句载入缓存。当 WebLogic Server 将预处理语句或可调用语句载入缓存时，在许多情况下，DBMS 将为每个打开的语句都保留游标。因此，语句缓存可能是“超出最多允许打开的游标数”问题的成因。“语句缓存大小”属性决定在每个连接池实例中为每个连接缓存的预处理和可调用语句的总数。如果缓存的语句过多，可能会导致超过数据库服务器上打开游标数的上限。

请注意，各版本 WebLogic Server 的缺省语句缓存大小是有差异的。示例：

要确定“超出最多允许打开的游标数”问题是否与语句缓存有关，可以通过将语句缓存大小设置为 0 将此功能关闭或减少缓存大小，再确认是否仍会出现错误。如果在减少缓存大小后问题没有发生，则说明连接池原有的语句缓存过大或 DBMS 中打开游标数的上限过低。可能需要考虑调整其中的一个值。如果发现连接上打开的游标数持续增加，但在将语句缓存大小设置为 0 后没有出现这种现象，则可能说明存在游标泄漏问题。这可能是由使用的 JDBC 驱动程序所致，也可能是 WebLogic Server 本身的一个错误。请尝试使用其它 JDBC 驱动程序。如果使用其它 JDBC 驱动程序后仍发生同样的问题，请将此问题报告给 BEA，这样支持工程师可以对问题做进一步探查，以确定该问题是否为 WebLogic Server 自身的一个错误。

返回页首

数据库驱动程序 
“超出最多允许打开的游标数”问题的另一个可能成因是 JDBC 驱动程序有问题。为分清问题是驱动程序问题还是 WebLogic 连接池问题，如果有可重现的测试案例，可以尝试执行以下步骤。

1. 直接从驱动程序获取连接。
在测试案例中，绕过 WebLogic 连接池直接从驱动程序获取 JDBC 连接。但请不要关闭连接，只需让它们以数组或某种其它结构形式保持打开状态，然后确认游标泄漏是否仍然存在。不关闭连接是因为要模拟使用连接池时的行为。使用连接池时，connection.close() 并未真正地关闭物理连接，而是将连接返回到池中。

2. 尝试使用其它 JDBC 驱动程序。
可以尝试使用其它供应商的 JDBC 驱动程序或升级版的驱动程序，然后确认问题是否仍然存在。可以使用元数据来验证所使用的驱动程序是否正确。示例代码与下面的类似：
Connection conn = getConnection(); 
DatabaseMetaData dmd = conn.getMetaData();
System.out.println("JDBC Driver Name is " + dmd.getDriverName()); 
System.out.println("JDBC Driver Version is " + dmd.getDriverVersion()); 

3. XA 驱动程序错误。
如果使用的是 Oracle XA 驱动程序，并且数据库中出现了大量类似“SELECT count (*) FROM SYS.DBA_PENDING_TRANSACTIONS”的查询，则可能是 Oracle XA 驱动程序存在游标泄漏问题。在有关 MetaLink 的案例 3151681 中有对此问题的描述，并且版本 10.1.0.2 中已修正了该问题。
此外，在使用 XA 驱动程序时，请确保按在 http://e-docs.bea.com/wls/docs81/jta/thirdpartytx.html#1075181 (English) 中所述，在 Database Server 上启用 XA（例如，grant select on dba_pending_transactions to public）。

如果问题是 JDBC 驱动程序问题，但又不得不使用该驱动程序，一种以变通方式解决游标泄漏问题的方法是不时重设 WebLogic 连接池，或收缩连接池。有关重设或收缩连接池的方法，请参阅 WebLogic 文档（如果是 8.1 版本，该文档位于 http://e-docs.bea.com/wls/docs81/ConsoleHelp/domain_jdbcconnectionpool_control.html (Enlish)）。

返回页首
已知问题
您可以定期查看所用 WLS 版本的“Release Notes”，了解 Service Pack 中的“Known Issues”或“Resolved Issues”的详细信息及浏览与 ORA-01000 /游标泄漏有关的问题。方便起见，下面提供了这些发行说明的链接：

对于需要特别注意之处，请参阅以下 CR，在相应版本 Service Pack 的发行说明中注明了已有针对它们的解决方法：

  • CR129379 - 如果 EJB 事务创建了许多新实体或以其它方式占用了许多 Bean，而这些 Bean 均使用 JDBC，WebLogic Server 就存在 Oracle 游标数量不足的风险，因为 WebLogic Server 在尝试避免发生可疑的 Oracle 驱动程序错误时会将对 JDBC 语句的关闭推迟到事务结束后进行，同时也将语句的游标一直保留到那个时候。WLS 6.1 SP7、WLS 7.0 SP5 和 WLS 8.1 SP3 中对此行为做了改动，会话已不必将游标一直保留到事务结束。
  • CR179600 - WLS 8.1 SP3 - 发生某些语句故障时，连接池的语句缓存会遗漏关闭语句的操作，进而导致 DBMS 会话中发生游标泄露。为解决此问题，已在 WLS 8.1 SP4 中做了改动。
  • WLS 8.1 SP3 (English) 中对用于实现 XA 语句缓存的底层回收对象进行了修正。
  • CR188814 - WLS 8.1 SP2 - 使用 EJB 2.0 CMP 时，更新 BLOB 或 CLOB 列后未关闭 PreparedStatement 和 ResultSet。WebLogic Server 现在通过 WLS 8.1 SP4 实现了在更新 BLOB/CLOB 时关闭 PreparedStatement 和 ResultSet。

使用搜索功能也可以搜索到“Release Notes”，还可以搜索到其它支持解决办法及与 CR 有关的信息，如需要更多帮助？中所提到的内容。如果客户签订了技术支持合同，则可以登录 http://support.bea.com/，登录后会看到为 Solutions 和 Bug Central 提供的 Browse portlet，可在其中按产品版本浏览最新提供的 CR。