We have a database parameter to set a maximum cursor limit for database connections.
Due to improper report design or code logic, there are possibilities that it can exceed the maximum cursor limit and cause slow response for users.
We never know when any user action will exceed the maximum cursor limit, so this monitoring can be automated with an escalation.
Escalation:
Object: DUMMY_TABLE
Condition:
exists ((SELECT a.sql_id FROM V$sqltext A,(SELECT sid, sql_id , Count (*) Count_Cursors From V$open_Cursor Where sid In (SELECT sid From V$open_Cursor GROUP BY sid HAVING Count (*) > 650) GROUP BY sid, sql_id ) B WHERE A.Sql_Id = B.Sql_Id ) )
Escalation point:
Just select the Repeats check box.
The blue color highlighted number is the limit for alert. If the open cursors are greater than this value, we can trigger a communication template to System Administrator with below query to monitor the Database.
SELECT b.cursors_count, b.sid, b.sql_Id, c.sql_text FROM V$SQLTEXT a,(SELECT sid, sql_id, count (*) cursors_count FROM V$OPEN_CURSOR WHERE sid in (SELECT sid From V$OPEN_CURSOR GROUP BY sid HAVING count (*) > 650) GROUP BY sql_id, sid) b , V$SQL c WHERE a.sql_id = b.sql_id and a.sql_id = c.sql_id ORDER BY b.SID, b.cursors_count DESC, b.sql_id, a.piece;Reference: http://goo.gl/DHCbaq
