Home » Other » General » Open Cursor count in the database (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
Open Cursor count in the database [message #674574] Mon, 04 February 2019 03:51 Go to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Hi All,


We need query to get the opened cursors current in the total database for all sessions and for all users.

If I google it got the two different queries with two different result set.

Query1 :

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'



Query2:

SELECT s.machine, oc.user_name, oc.sql_text, count(1)  cnt
FROM v$open_cursor oc, v$session s
WHERE oc.sid = s.sid
GROUP BY user_name, sql_text, machine
--HAVING COUNT(1) > 2
ORDER BY count(1) DESC

Which one is appropriate to get the OPEN CURSOR COUNT in the database ?What is he difference between those queries and why it is giving the different result set ?

Thanks
SaiPradyumn



Re: Open Cursor count in the database [message #674594 is a reply to message #674574] Mon, 04 February 2019 10:00 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
saipradyumn wrote on Mon, 04 February 2019 01:51
Hi All,


We need query to get the opened cursors current in the total database for all sessions and for all users.



Why?

Most likely if you immediately re-run same query, then a different value will be returned;
since cursors are always being opened and closed based upon user activity.

How will the returned value be used?
Re: Open Cursor count in the database [message #674619 is a reply to message #674594] Wed, 06 February 2019 00:55 Go to previous messageGo to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Hi BlackSwan,

Thanks for your response.
We need to send the alert messages to the entire team when ever it reaches maximum value which defined in the database.
query to get the OPEN_CURSORS

Select *  from v$parameter  where name like  'open_cursors';

To calculate the % how much we are using, I am trying to calculate with help of those queries

Thanks
SaiPradyumn

Re: Open Cursor count in the database [message #674620 is a reply to message #674619] Wed, 06 February 2019 01:43 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
when ever it reaches maximum value which defined in the database.
Don't be afraid, Oracle will tell them when they'll exceed the value.

The most important question is "why they reach the maximum value?".
Is it the parameter is badly configured? Then increase the value.
Is it the application does not close the cursors? Then fix the application.

Note this parameter is a SESSION one, so alerting every session because one reaches the maximum is something not very smart.

Re: Open Cursor count in the database [message #674633 is a reply to message #674620] Wed, 06 February 2019 08:31 Go to previous message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
+1
Previous Topic: Translation of English data into Arabia (Saudi Arabia),,,
Next Topic: Oracle Licencing
Goto Forum:
  


Current Time: Wed Sep 30 17:18:19 CDT 2020