Um, well, if the question is: =20 "Optimally, what should I set session_cached_cursors to in my = instance?", then I think that script give you a pretty good idea. I just ran it on one of my production instances, and got the following output: SQL> @session_cursor_cache PARAMETER VALUE USAGE ----------------------------- ----- ----- session_cached_cursors 50 100% open_cursors 2000 7% CURSOR_CACHE_HITS SOFT_PARSES HARD_PARSES ----------------- ----------- ----------- 91.16% 8.80% 0.03% MAX_CACHEABLE_CURSORS --------------------- 817 So, this tells me that I've currently got it set to 50. At 50, all of them are being used, so, it's certainly not too large. Now, that last number, "MAX_CACHEABLE_CURSORS" is the absolute largest you'd ever want to set, from the point of view that anything larger than that is just a pure waste. But, as you can see, I've only got 50, and I'm getting a pretty good benefit, as 91.16% of my parses are cursor cache hits. Another 8.8% are soft parses and a very small 0.03% are hard parses. There is a chance that I could increase session_cached_cursors, say to 100 or may even 200, and improve my cursor cache hit percentage beyond what it is. But, parsing and library cache latching is not a significant component of any of of my critical processes' response time profiles, so, making that type of change is going to be of little or no value. Don't chase percentages and ratios! Ideally, the questions that ought to precede the first one in this posting are: "Do I have a performance problem" "Is parsing a significant component of my application's response time?" -Mark -----Original Message----- From: Wolfson Larry - lwolfs [mailto:lawrence.wolfson@xxxxxxxxxx] Sent: Tuesday, September 28, 2004 12:41 PM To: Bobak, Mark; joseph.armstrong-champ@xxxxxxxxx; anjo.kolk@xxxxxxxxxxx Cc: oracle-l@xxxxxxxxxxxxx Subject: RE: session_cached_cursors pros & cons Mark, I don't see that script answers Joe's question. I'd like to know too. Syed, You didn't mention what type of application you're evaluating. If it's home grown you could ask the application group to recode the worst offenders to reduce the need for the cached cursors. If that's too much = or it's a purchased application you could identify the offending user or = code and use a logon trigger to ALTER SESSION SET SESSION_CACHED_CURSOR =3D (appropriate value) This script should show you who has all the open cursors Larry SET PAGESIZE 10000 col machine for a19 col osuser for a12 col username for a11 compute sum of open_cursors on report -- SPOOL opencur.$ORACLE_SID select count(*) from v$open_cursor; select a.sid ,machine ,osuser ,username ,count(*) "OPEN_CURSORS" from v$open_cursor a ,v$session b where a.sid =3D b.sid group by a.sid ,osuser ,username ,machine having count(*) > 0 order by count(*) / select a.sid ,machine ,osuser ,username ,count(*) "OPEN_CURSORS" from v$open_cursor a, v$session b where a.sid =3D b.sid and b.type =3D 'USER' group by a.sid ,osuser ,username ,machine having count(*) > 0 order by count(*) / spool off -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Bobak, Mark Sent: Tuesday, September 28, 2004 10:50 AM To: joseph.armstrong-champ@xxxxxxxxx; anjo.kolk@xxxxxxxxxxx Cc: oracle-l@xxxxxxxxxxxxx Subject: RE: session_cached_cursors pros & cons Joe, Go to: http://www.ixora.com.au/scripts/library.htm and look for the session_cursor_cache.sql script. -Mark -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Joe Armstrong-Champ Sent: Tuesday, September 28, 2004 11:46 AM To: anjo.kolk@xxxxxxxxxxx Cc: oracle-l@xxxxxxxxxxxxx Subject: Re: session_cached_cursors pros & cons Anjo, Is there any documentation that can help in deetermining how much bigger = =3D the shared pool needs to be when using session_cached_cursors? Thanks. Joe Anjo Kolk wrote: > I haven't read all the replies but one thing to keep in mind with > session_cached_cursors is that cursors that are normally closed are = =3D kept > open and that memory is kept pinned. That means that a larger shared = =3D pool is > needed to keep all the 'open' cursors in the shared pool. It is also > responsible for a larger fragmentation of the shared pool. >=3D20 > In fact caching SQL statements increases performance, but could cause = =3D a > fragmentation and other shared pool problems. >=3D20 > So make sure that your shared pool is big enough. >=3D20 -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l ********************************************************************** The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination,=20 distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You. -- //www.freelists.org/webpage/oracle-l