RE: max open cursors case

From: Goulet, Dick <DGoulet_at_vicr.com>
Date: Tue, 08 Apr 2003 07:23:43 -0800
Message-ID: <F001.0057CC58.20030408072343@fatcity.com>

Raj,  

    Have had similar problems. It takes time & data to prove that the duhvelopers are in error.  

Dick Goulet

-----Original Message-----

Sent: Tuesday, April 08, 2003 10:54 AM
To: Multiple recipients of list ORACLE-L

Thanks ...  

Your sql is better than mine ... I used mine just to show them counts ...  

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 !

-----Original Message-----

Sent: Tuesday, April 08, 2003 10:19 AM
To: Multiple recipients of list ORACLE-L

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 - 10:23:43 CDT