| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Designer and the SCM (was RE: max open cursors case)
We use it with Designer and it's great. I'm not saying that you cannot find
the same functionality elsewhere, but it does what it's supposed to do. We
also use the SCM for version control of the .sql files on our filesystem
(all of our PL/SQL).
Also, our SCM is on a separate machine from our production db's.
david
-----Original Message-----
Sent: Tuesday, April 08, 2003 11:39 AM
To: Multiple recipients of list ORACLE-L
My initial reaction is that 1024 cursors are unbelievable let alone 3000. Funny though that I'm currently installing Oracle Software Configuration Manager and came across the following recommendation in the Oracle SCM Repository Installation Guide:
"Set OPEN_CURSORS to at Least 3000
We recommend setting OPEN_CURSORS to at least 3000."
Am I looking for trouble in attempting to use Oracle SCM? Any opinions on using this product with Oracle Designer?
Eric Harrington
-----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
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
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Abadir INET: davidabadir_at_carfax.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting servicesReceived on Tue Apr 08 2003 - 14:04:05 CDT
---------------------------------------------------------------------
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).
 |
 |