Oracle-L: RE: max open cursors case

From: Goulet, Dick <DGoulet_at_vicr.com>
Date: Tue, 08 Apr 2003 06:18:38 -0800
Message-ID: <F001.0057CB96.20030408061838@fatcity.com>

Raj,

Try the following query instead. I find it provides a better look see at open cursors:

SELECT USER_NAME, A.SID, NVL(OSUSER,'UNKNOWN'), NVL(MACHINE,'*'),

                   NVL(PROGRAM, 'UNKNOWN'), COUNT(B.SID)
            FROM V$SESSION A, SYS.V_$OPEN_CURSOR B
            WHERE A.SADDR = B.SADDR
              AND SQL_ADDRESS = ADDRESS
              AND SQL_HASH_VALUE = HASH_VALUE
            GROUP BY USER_NAME, A.SID, OSUSER, MACHINE, PROGRAM;

Dick Goulet

-----Original Message-----
Sent: Tuesday, April 08, 2003 9:39 AM
To: Multiple recipients of list ORACLE-L

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: Goulet, Dick
INET: DGoulet_at_vicr.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 - 09:18:38 CDT