Re: Max open cursors

From: alexjunq <alexjunqNOalSPAM_at_softwaredesign.com.br.invalid>
Date: Tue, 07 Mar 2000 22:51:24 -0800
Message-ID: <2005744d.a9f241a9_at_usw-ex0103-024.remarq.com>

Hello,

> I checked the v$open_cursor view and there are 14111
> cursors!!! How can this be?!! I have the OPEN_CURSORS
> parameter set to 5000?
>

  This view just show you all cursors that are compiled   and remains in SGA (been used or not)... they're not   all of yours, but include ORACLE internal cursors too,   and all the cursors of another sessions... about   the OPEN_CURSORS parameter, it tells ORACLE how many   cursors each session could handle (not the total number   of cursors your instance could handle) ... then if you   have 3 sessions opened, you could achieve up to 15000   cursors..

  To get an clue about how many cursors you've been used   and how many are currently been used, try this statement:

---

 select ss.username||'('||se.sid||') ' user_process,

        sum(decode(name,'recursive calls',value))
           "Recursive Calls",
        sum(decode(name,'opened cursors cumulative',value))
           "Opened Cursors",
        sum(decode(name,'opened cursors current',value))
           "Current Cursors"
 from   v$session ss,
        v$sesstat se,
        v$statname sn

 where se.statistic# = sn.statistic#

   and (name like '%opened cursors current%'
    OR  name like '%recursive calls%'
    OR  name like '%opened cursors cumulative%')

   and se.sid = ss.sid
   and ss.username is not null
 group by ss.username||'('||se.sid||') ';

---

  It will show u some interesting values for each opened   session at your DB.

> Also, how do I clean these up? After the guilty process
> failed, these cursors still show up in the view.

  You don't. This table is dynamically used by ORACLE,   which means, when it needs more space, it'll remove   some unused entries without external interference...

  About your code, try to check if
  "ROMS_Utility_package.getNextTableId" is doing its work   well... all your code use implicit cursors ... this   usually don't lead to "max open cursors" problems...   generally you get this kind of problem when you're using   explicit cursors (dbms_sql, open cursor, etc) ... check   the function above to see if it has some of them ...   and if its true, checkout if in every possible way you're   closing the cursors.

  Hope it helps

  Alexandre Junqueira

• Sent from RemarQ http://www.remarq.com The Internet's Discussion Network * The fastest and easiest way to search and participate in Usenet - Free!

Received on Wed Mar 08 2000 - 07:51:24 CET