RE: session_cached_cursors pros & cons
- From: Wolfson Larry - lwolfs <lawrence.wolfson@xxxxxxxxxx>
- To: "'Bobak, Mark'" <Mark.Bobak@xxxxxxxxxxxxxxx>, Wolfson Larry - lwolfs <lawrence.wolfson@xxxxxxxxxx>, joseph.armstrong-champ@xxxxxxxxx, anjo.kolk@xxxxxxxxxxx
- Date: Tue, 28 Sep 2004 19:08:34 -0500
Thanks for the feedback Mark
Larry
-----Original Message-----
From: Bobak, Mark [mailto:Mark.Bobak@xxxxxxxxxxxxxxx]
Sent: Tuesday, September 28, 2004 2:27 PM
To: Wolfson Larry - lwolfs; joseph.armstrong-champ@xxxxxxxxx;
anjo.kolk@xxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: session_cached_cursors pros & cons
Um, well, if the question is:
"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 =
(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 = 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 = b.sid
and b.type = '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 =
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 =
kept
> open and that memory is kept pinned. That means that a larger shared =
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.
>=20
> In fact caching SQL statements increases performance, but could cause =
a
> fragmentation and other shared pool problems.
>=20
> So make sure that your shared pool is big enough.
>=20
--
//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,
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
Other related posts:
