cursor leak and open cursors [message #659663] |
Thu, 26 January 2017 11:29 |
shrinika
Messages: 306 Registered: April 2008
|
Senior Member |
|
|
Hello, I am planning to setup an alert for couple scenarios.
Scenario 1
Alert when total number of open cursor for a session is exceeding 250.
select sum(a.value), s.username, 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'
and s.username='APPREAD'
group by s.sid,s.username
having sum(a.value) > 250
Scenario 2
Alert when more than 10 cursor opened for the same sql_id.
SELECT
(select name from v$database) as db_name,
s.username,
voc.SQL_ID,
voc.SQL_TEXT,
count(1) as open_count
FROM v$sesstat a,
v$statname b,
v$session s,
v$open_cursor voc
WHERE a.statistic# = b.statistic#
AND voc.USER_NAME = s.USERNAME
AND voc.SID = s.SID
AND s.sid = a.sid
AND b.name = 'opened cursors current'
AND s.status <>'KILLED'
AND (s.username in('APPREAD'))
AND a.value > 200
group by s.username,voc.SQL_ID,voc.SQL_TEXT
having count(1) > 10
Just checking if the query is good enough to handle these two scenarios.
Please let me know! thank you again and appreciate your support.
[Updated on: Thu, 26 January 2017 11:30] Report message to a moderator
|
|
|
Re: cursor leak and open cursors [message #659664 is a reply to message #659663] |
Thu, 26 January 2017 11:36 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Are you aware that the database comes with the Server Generated Alert system? Enable it by setting thresholds with the dbms_server_alert package. The OPEN_CURSORS_CURRENT metric sounds relevant.
|
|
|
|
|
|
|
|