应用“ORA-01000：超出最多允许打开的游标数”故障排除

问题诶经

应用在做压力测试时出现了“ORA-01000：超出最多允许打开的游标数”的错误，当时暂时没有想到解决办法，事后调整平台代码，释放资源可以修正该bug，但无法确定bug具体原因，

现使用下面方法可找到具体问题出现位置：

1、查看当前系统设置的一个会话允许的游标数量，并记录，测试完成后恢复。 

SQL> show parameter open_cursors; 

  调整数据库游标数，设置为一个较小数如500

SQL> alter system set open_cursors=500 scope=both;

2、重启数据库实例，数据恢复初始状态。
3、应用数据库连接池连接数设置为1。
4、启动应用并执行出现问题的场景（继续压力测试）。
5、查看应用，出现错误后停止测试，但保持应用处于启动状态方便收集现有数据。
6、查看会话占用的游标数，此处可看到使用了500+游标

--用户会话使用游标数  下面是SCOTT用户
select o.sid, osuser, machine, count(*) num_curs 
from v$open_cursor o, v$session s
where user_name = 'SCOTT' and o.sid=s.sid 
group by o.sid, osuser, machine
order by num_curs desc;

7、找出占用游标没有释放的sql  sid根据实际情况修改

--查看使用游标的sql
select q.sql_text 
from v$open_cursor o, v$sql q
where q.hash_value=o.hash_value and o.CURSOR_TYPE='OPEN' and o.sid = 28;

相关说明：


1、在v$open_cursor表中查看的游标数量包括sql占用游标与会话缓存游标，但会话缓存游标不在配置的参数之内，如使用
show parameter open_cursors;查看配置游标数为500，但查看v$open_cursor中总数为550，在使用下面语句查看后发现
500个游标是打开的，另外50个是缓存占用的，网上很多资料这地方解释不够清晰

--查看当前v$open_cursor中的游标使用情况 sid根据实际情况修改
SELECT SID, n.NAME para_name, s.VALUE used
FROM SYS.v_$statname n, SYS.v_$sesstat s
WHERE n.NAME IN ('opened cursors current', 'session cursor cache count')
AND s.statistic# = n.statistic#
AND SID = 28;

--查看会话缓存使用情况

show parameter session_cached_cursors;

2、若想查看当前使用的游标情况，建议使用下面语句

--查看已打开游标
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';

应用在测试过程中也可以使用下面的方法来观察是否有游标泄露


撑爆法：
注意:此方法没有在正式环境测试，不知道这样撑爆数据库会出现什么实际后果，建议测试前备份数据。

1、查看当前系统设置的一个会话允许的游标数量，并记录，测试完成后恢复。 

SQL> show parameter open_cursors; 

   调整数据库游标数，设置为一个较小数如500

SQL> alter system set open_cursors=500 scope=both;

2、重启数据库实例，数据恢复初始状态。
3、应用数据库连接池连接数设置为1。
4、正常测试应用，一周或一月后踢出所有用户，收集数据库数据


使用下面sql查看数据库状态，看是否有大量的游标占用

--查看已打开游标
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';

5、若有大量游标占用，此时固定使用一个sql循环去访问数据库，但访问过程中不关闭statement，等待数据库报出“ORA-01000：超出最多允许打开的游标数”异常

6、此时按照上面压力测试故障查找方法，找到对应sql，并排除这次撑爆数据库游标使用的sql，拿到的即为出现问题的sql。

深入探查“ORA-01000：超出最多允许打开的游标数”可以参考：http://blog.itpub.net/308563/viewspace-171889/
理解V$OPEN_CURSOR与session_cached_cursor可以参考：http://www.cnblogs.com/rootq/archive/2009/08/27/1554747.html