Oracle-L: Re: max open cursors case

From: Yechiel Adar <adar76_at_inter.net.il>
Date: Tue, 08 Apr 2003 09:28:55 -0800
Message-ID: <F001.0057CF2A.20030408092855@fatcity.com>

max open cursors caseWe had a problem like this but with database connections. I run a script every hour that showed how many connections are open and who uses them. These statistics help me convince the developers that they had a problem. You could see the open connection creeping up and up. Maybe you can do the same.

Yechiel Adar
Mehish

Original Message ----- From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Tuesday, April 08, 2003 3:39 PM 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: Yechiel Adar
INET: adar76_at_inter.net.il

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 - 12:28:55 CDT