Oracle-L: Re: max open cursors case

From: Steve Perry <sperry_at_sprynet.com>
Date: Tue, 08 Apr 2003 16:53:43 -0800
Message-ID: <F001.0057D8B3.20030408165343@fatcity.com>

max open cursors caseRaj,

This is probably late, but I don't get to read this list until I get off work. A couple of years ago, I had the same problem... same politics. I ended up granting the privs so they could run these 2 queries. They put in hooks to hit a hot key in their app that would display their cursors. v$SQL_CURSOR only shows their session's cursors. Low and behold, the person who was the most adament about it being a database problem was the one explaining his last minute change the "wouldn't impact anybody".

I ended up picking the most open minded developer (not the best coder) and it worked out really well. I didn't say it was a developer problem and he didn't say it was a database problem - it was a problem period... It took about a day and a half to find one try/catch block that didn't close a cursor.

Maybe it will help you.

Steve

select c.curno cursor_nbr


     , c.status

     , q.sql_text 

from v$sql q
   , v$sql_cursor c

where q.address=c.parent_handle
order by 1
/

--shows all cursor status' regardless of if they map to a sql statement

select c.curno cursor_nbr


     , c.status

     , q.sql_text 

from v$sqlarea q
   , v$sql_cursor c

where q.address(+) =c.parent_handle
order by 1
/

Original Message ----- From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Tuesday, April 08, 2003 8:39 AM Subject: max open cursors case

Okay ...

For a critical system, we have max_open_cursors set to 1024, but developers complained that they are getting number of open cursors exceeded at times. So a fellow DBA bumped it to 3000. There is no point telling developers to take a look at their code, because their managers believe that they only write the best in the world, and we (I mean I) just complain. I know there is no major downside other than memory usage, but isn't this a ridicules number?

I have been using following query to see what is the cursor status ...

select t.sid,

         sum(case when t.statistic# = 3 
                  then value 
                  else 0 end) "cursors current", 
         sum(case when t.statistic# = 2 
                  then value 
                  else 0 end) "cursors cumulative", 
         s.USERNAME, s.TERMINAL, s.PROGRAM

from gv$sesstat t, gv$session s

   where t.statistic# in (2,3) 
     and s.sid = t.sid 
     and t.inst_id = 2 
     and s.inst_id = 2

group by t.sid, s.USERNAME, s.TERMINAL, s.PROGRAM order by 2 desc
/

Current sample looks like this ...

SID cursors_current cursors_cumulative

--------------- ------------------ 34 51 180122 107 40 36394 98 39 7761 123 39 47416 105 30 41372 55 11 14531 17 10 3081

But what can I do, other than this query, to monitor max open cursors (on a live 24x7 production system) when it happens to I can send the gory details back to the development team?

Thanks in advance
Raj

Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Steve Perry
  INET: sperry_at_sprynet.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Apr 08 2003 - 19:53:43 CDT