OraFAQ Forum: General » Open Cursor count in the database

Home » Other » General » Open Cursor count in the database (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Open Cursor count in the database [message #674574]

Mon, 04 February 2019 03:51

saipradyumn
Messages: 419
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

Report message to a moderator

Re: Open Cursor count in the database [message #674594 is a reply to message #674574]

Mon, 04 February 2019 10:00

BlackSwan
Messages: 26766
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?

Report message to a moderator

Re: Open Cursor count in the database [message #674619 is a reply to message #674594]

Wed, 06 February 2019 00:55

saipradyumn
Messages: 419
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

Report message to a moderator

Re: Open Cursor count in the database [message #674620 is a reply to message #674619]

Wed, 06 February 2019 01:43

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Open Cursor count in the database [message #674633 is a reply to message #674620]

Wed, 06 February 2019 08:31

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Report message to a moderator

Previous Topic:	Translation of English data into Arabia (Saudi Arabia),,,
Next Topic:	Oracle Licencing

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 04 06:24:27 CST 2025