Home » SQL & PL/SQL » SQL & PL/SQL » cursor leak and open cursors (Oracle12c)
cursor leak and open cursors [message #659663] Thu, 26 January 2017 11:29 Go to next message
shrinika
Messages: 298
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 Go to previous messageGo to next message
John Watson
Messages: 7669
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.
Re: cursor leak and open cursors [message #659665 is a reply to message #659664] Thu, 26 January 2017 11:47 Go to previous messageGo to next message
BlackSwan
Messages: 26275
Registered: January 2009
Location: SoCal
Senior Member
What will you do when you are informed more than 10 cursor are opened for the same sql_id?
BTW - based upon what criteria was the threshold of 10 selected?
Re: cursor leak and open cursors [message #659666 is a reply to message #659665] Thu, 26 January 2017 12:12 Go to previous messageGo to next message
shrinika
Messages: 298
Registered: April 2008
Senior Member
John Watson, Thanks for your input. I will look at server generated alert..

BlackSwan,

What will you do when you are informed more than 10 cursor are opened for the same sql_id?
I will ask developers to look at and see if there is any cursor leak.

BTW - based upon what criteria was the threshold of 10 selected?
I just starting with 10. I may adjust this threshold value later.

Between, you have not answered for my question. Smile
Re: cursor leak and open cursors [message #659667 is a reply to message #659666] Thu, 26 January 2017 12:21 Go to previous messageGo to next message
Michel Cadot
Messages: 65971
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why should we when you do not answer ours and don't feedback?

http://www.orafaq.com/forum/m/658588/#msg_658588

Re: cursor leak and open cursors [message #659669 is a reply to message #659666] Thu, 26 January 2017 12:26 Go to previous messageGo to next message
BlackSwan
Messages: 26275
Registered: January 2009
Location: SoCal
Senior Member
SQL in SGA is a shared resource.
In a multi-user, OLTP environment you should expect more than 1 user to be executing same SQL at the same time.

IMO, any real "cursor" leak should be detected during Unit Testing & prior to deployment into Production.

Is application 3-tier?
Does application utilize Connection Pooling?
What is maximum number of concurrent end users accessing the application at any point in time?

When you start out asking the wrong question, it really does not matter what answer is provided.

Re: cursor leak and open cursors [message #659709 is a reply to message #659669] Fri, 27 January 2017 14:39 Go to previous message
shrinika
Messages: 298
Registered: April 2008
Senior Member
This thread is taking me to different direction from my original question.

I thank you so much for every one support for answering on this thread.

thank you for every one time!

Previous Topic: JSON Parsing
Next Topic: ORA-01722 for bind variable in dbms_sql
Goto Forum:
  


Current Time: Sun Nov 18 17:26:23 CST 2018