Oracle-L: max open cursors case

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Tue, 08 Apr 2003 05:39:04 -0800
Message-ID: <F001.0057CAEF.20030408053904@fatcity.com>

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: Jamadagni, Rajendra
  INET: Rajendra.Jamadagni_at_espn.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).



text/plain attachment: ESPN_Disclaimer.txt

Received on Tue Apr 08 2003 - 08:39:04 CDT