RE: session_cached_cursors pros & cons
- From: Wolfson Larry - lwolfs <lawrence.wolfson@xxxxxxxxxx>
- To: "'Mark.Bobak@xxxxxxxxxxxxxxx'" <Mark.Bobak@xxxxxxxxxxxxxxx>, joseph.armstrong-champ@xxxxxxxxx, anjo.kolk@xxxxxxxxxxx
- Date: Tue, 28 Sep 2004 11:40:40 -0500
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:
