36

What is the query to find the number of current open cursors in an Oracle Instance?

Also, what is the accuracy/update frequency of this data?

I am using Oracle 10gR2

General Grievance
  • 4,555
  • 31
  • 31
  • 45
M.N
  • 10,899
  • 13
  • 47
  • 49

8 Answers8

47

Total cursors open, by session:

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';

Source: http://www.orafaq.com/node/758

As far as I know queries on v$ views are based on pseudo-tables ("x$" tables) that point directly to the relevant portions of the SGA, so you can't get more accurate than that; however this also means that it is point-in-time (i.e. dirty read).

Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158
  • 6
    These 'opened cursors current' are lazily reaped by the Oracle table server; so the number you see for your application may be anomalously high without meaning you made a mistake. See http://www.orafaq.com/node/758 – O. Jones Dec 16 '09 at 20:10
  • If this doesn't work for you, try first granting yourself the required privileges with command: sqlplus "/ as sysdba" – Woodchuck Aug 29 '21 at 23:52
13
select  sql_text, count(*) as "OPEN CURSORS", user_name from v$open_cursor
group by sql_text, user_name order by count(*) desc;

appears to work for me.

Sirko
  • 72,589
  • 19
  • 149
  • 183
Mark Kluepfel
  • 131
  • 1
  • 3
9

Here's how to find open cursors that have been parsed. You need to be logged in as a user with access to v$open_cursor and v$session.

COLUMN USER_NAME FORMAT A15

SELECT s.machine, oc.user_name, oc.sql_text, count(1) 
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
;

If gives you part of the SQL text so it can be useful for identifying leaky applications. If a cursor has not been parsed, then it does not appear here. Note that Oralce will sometimes keep things open longer than you do.

WW.
  • 23,793
  • 13
  • 94
  • 121
  • 2
    Actually, this is a complex situation. v$open_cursor happens to show cached statements. Open cursors (the resource that can be hammered if you have a cursor / ResultSet leak) is in v$sessstat in a row named 'opened cursors current'. – O. Jones Dec 16 '09 at 20:09
  • 1
    @Ollie: But how does that help you identify the SQL statement that is leaking? – WW. Dec 16 '09 at 22:25
  • This select shows you the actual SQL code that opened the cursors, great for debugging ! +1 from me – Marius Ion Sep 28 '12 at 14:00
2

1)your id should have sys dba access 2) 

select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur, 
 s.username, s.machine
 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' 
 group by s.username, s.machine
 order by 1 desc;
praveen s
  • 21
  • 1
1

Oracle has a page for this issue with SQL and trouble shooting suggestions.

"Troubleshooting Open Cursor Issues" http://docs.oracle.com/cd/E40329_01/admin.1112/e27149/cursor.htm#OMADM5352

Rana Ian
  • 724
  • 7
  • 6
0

I use something like this:

select 
  user_name, 
  count(*) as "OPEN CURSORS" 
from 
  v$open_cursor 
group by 
  user_name;
John
  • 3,458
  • 4
  • 33
  • 54
0

This could work:

SELECT    sql_text "SQL Query", 
          Count(*) AS "Open Cursors" 
FROM      v$open_cursor 
GROUP BY  sql_text 
HAVING    Count(*) > 2 
ORDER BY  Count(*) DESC;
Srikrishnan Suresh
  • 729
  • 2
  • 13
  • 31
0

I would use this quick SQL to compare the highest current use compared to the max allowed. This will allow you to immediately see if any process has a dangerously high use of cursors.

SELECT MAX (a.VALUE) AS highest_open_cur, p.VALUE AS max_open_cur
FROM v$sesstat a, v$statname b, v$parameter p
WHERE a.statistic# = b.statistic#
AND b.name = 'opened cursors current'
AND p.name = 'open_cursors'
GROUP BY p.VALUE;

As is probably clear from the above SQL, the OPEN_CURSORS value is an Oracle parameter and can be found at runtime like this.

SELECT * FROM v$parameter WHERE NAME = 'open_cursors';

You may need to be sysdba or equivalent to query the above tables.

DAB
  • 1,631
  • 19
  • 25