Hello All,
This week we had a problem with the cursors.:) ORA-1000
We made a change to our oracle portal website to use the App server Datasource instead of Apache BasicDataSource.
While testing this across instances we detected a cursor leak. As we all know again it was a problem with a stmt or result set object which was not closed.
Detecting the open result set/statement object is a bit tough job in a huge code base.
This is how the object was identified
Query to find our open cursors
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 username = 'EXTERNALPORTAL';
This gives the open cursor count and SID which opened it.
This returned me the sid 937 for my Application, used this sid against the V$open_cursor view.
for eg:
SELECT COUNT(*), address
FROM v$open_cursor
WHERE sid = 937
GROUP BY address HAVING COUNT(address) > 1 ORDER BY COUNT(*);
The result lists each cursor, which has been opened by the session more than once in descending order.
Returnes the count of cursor and the memory Address of the query which opened it.
SELECT sql_fulltext
FROM v$sql
WHERE address = '6C806444'
this returned me the query which created the issue. Just fixed the code. and monitored the cursor count for about a day it is good.
Thanks
Prem
