Home » RDBMS Server » Performance Tuning » Open cursor count is too high (Oracle 10.2.0.4, Solaris 9)
icon1.gif  Open cursor count is too high [message #418826] Tue, 18 August 2009 11:52 Go to next message
dasuwal@gmail.com
Messages: 20
Registered: June 2009
Junior Member
Hi Forum

I am runing into a issue where an application opens 2200 active cursors with around 12 spawning sessions on a 10 cpu machine. The max_cursor parametere is increased to 3000. I am in discussion phase with developers to finetune the code to decrease the active cursors (but as usual they blame it a DB issue).

The high usage of cursor just choke the database and there are serious performance issues.

Can anyone please guide the pitfalls that may result due to such a high cursor usage.

Any advice on this matter is most welcome.

Thanks,
Danny.
Re: Open cursor count is too high [message #418827 is a reply to message #418826] Tue, 18 August 2009 11:56 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Can anyone please guide the pitfalls that may result due to such a high cursor usage.
The high usage of cursor just choke the database and there are serious performance issues.


Patient says, "Doctor, It hurts when I poke myself in the eye."
Q. How can I stop the pain?
Doctor replies, "Stop poking yourself in the eye!"
Re: Open cursor count is too high [message #418829 is a reply to message #418826] Tue, 18 August 2009 11:58 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, that number of open cursors are usually cause by application bugs, where the application opens cursors without closing them again.

You can check the SQL that is used to open the cursors with the queries on the FAQ page.

So you can then tell the developers "this SQL here is opening a cusor and not closing it. That is an application bug. Fix it." Very Happy
Re: Open cursor count is too high [message #418834 is a reply to message #418826] Tue, 18 August 2009 12:58 Go to previous messageGo to next message
dasuwal@gmail.com
Messages: 20
Registered: June 2009
Junior Member
Thanks Guys...

Is there any way I can see contents of active cursors.

Re: Open cursor count is too high [message #418835 is a reply to message #418834] Tue, 18 August 2009 13:02 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
SQL> desc v$open_cursor
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 SADDR						    RAW(4)
 SID						    NUMBER
 USER_NAME					    VARCHAR2(30)
 ADDRESS					    RAW(4)
 HASH_VALUE					    NUMBER
 SQL_ID 					    VARCHAR2(13)
 SQL_TEXT					    VARCHAR2(60)
Re: Open cursor count is too high [message #418880 is a reply to message #418826] Wed, 19 August 2009 00:24 Go to previous message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Said Thomas right, you might consider about his words.
Then, I support to you 2 script to detect the opening cursors.

1. Total open cursors by session
--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';


2. Total open cursors by username
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;

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;

SELECT 'session_cached_cursors' parameter, LPAD (VALUE, 5) VALUE,
       DECODE (VALUE,
               0, '  n/a',
               TO_CHAR (100 * used / VALUE, '990') || '%'
              ) USAGE
  FROM (SELECT MAX (s.VALUE) used
          FROM v$statname n, v$sesstat s
         WHERE n.NAME = 'session cursor cache count'
           AND s.statistic# = n.statistic#),
       (SELECT VALUE
          FROM v$parameter
         WHERE NAME = 'session_cached_cursors')
UNION ALL
SELECT 'open_cursors', LPAD (VALUE, 5),
       TO_CHAR (100 * used / VALUE, '990') || '%'
  FROM (SELECT   MAX (SUM (s.VALUE)) used
            FROM v$statname n, v$sesstat s
           WHERE n.NAME IN
                     ('opened cursors current', 'session cursor cache count')
             AND s.statistic# = n.statistic#
        GROUP BY s.SID),
       (SELECT VALUE
          FROM v$parameter
         WHERE NAME = 'open_cursors')
/


Hope to help you!
Previous Topic: Virtual Memory Paging
Next Topic: Insert performance with index rebuild
Goto Forum:
  


Current Time: Thu Dec 08 04:03:55 CST 2016

Total time taken to generate the page: 0.10852 seconds