The developers have introduced a cursor leak somewhere. That number
is PER USER. They need to fix their code.
--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton
On Tue, 8 Apr 2003, Jamadagni, Rajendra wrote:
> 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: Jeremiah Wilton
INET: jwilton_at_speakeasy.net
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 - 09:13:48 CDT
