Tweet
5.5.5 Bug 8507644 - ORA-01000 Maximum Open Cursors Exceeded
FYI,
Using 5.5.5.2 we experienced an "ORA-01000 Maximum Open Cursors Exceeded" error during order integration and were able to trace the problem to the following SQL
select distinct order_release_gid from order_release where order_base_gid=:1
After about an hour this lead to an out of memory condition on the app server necessitating a restart.
We reported this to Oracle and they quickly replied that this is a known issue and provided a patch for bug 8507644 (which we are now testing).
If you experience a similar cursor leak the following SQL's may be helpful in finding the problem.
-- Monitor OTM Cursor usage
select c.user_name, c.sid, count(*), sql.SQL_TEXT, o.object_name Package_name,
sql.PROGRAM_ID, sql.PROGRAM_LINE# Line#
from v$open_cursor c, v$sql sql, dba_objects o,
(select s.sid 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'
-- More than 50 cursors open in a session
and a.value > 50 and s.username = 'GLOGDBA') t1
where c.sql_id = sql.sql_id
and o.object_id(+) = sql.PROGRAM_ID
and c.sid = t1.sid
group by c.user_name, c.sid, sql.SQL_TEXT, o.object_name, sql.PROGRAM_ID, sql.PROGRAM_LINE#
having count(*) > 20
order by sid, count(*) desc;
--total cursors open, by username & machine
select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur,
s.username, s.machine
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'
group by s.username, s.machine
order by 1 desc;
Thanks,
Alan
Using 5.5.5.2 we experienced an "ORA-01000 Maximum Open Cursors Exceeded" error during order integration and were able to trace the problem to the following SQL
select distinct order_release_gid from order_release where order_base_gid=:1
After about an hour this lead to an out of memory condition on the app server necessitating a restart.
We reported this to Oracle and they quickly replied that this is a known issue and provided a patch for bug 8507644 (which we are now testing).
If you experience a similar cursor leak the following SQL's may be helpful in finding the problem.
-- Monitor OTM Cursor usage
select c.user_name, c.sid, count(*), sql.SQL_TEXT, o.object_name Package_name,
sql.PROGRAM_ID, sql.PROGRAM_LINE# Line#
from v$open_cursor c, v$sql sql, dba_objects o,
(select s.sid 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'
-- More than 50 cursors open in a session
and a.value > 50 and s.username = 'GLOGDBA') t1
where c.sql_id = sql.sql_id
and o.object_id(+) = sql.PROGRAM_ID
and c.sid = t1.sid
group by c.user_name, c.sid, sql.SQL_TEXT, o.object_name, sql.PROGRAM_ID, sql.PROGRAM_LINE#
having count(*) > 20
order by sid, count(*) desc;
--total cursors open, by username & machine
select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur,
s.username, s.machine
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'
group by s.username, s.machine
order by 1 desc;
Thanks,
Alan
Comment