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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Bobak, Mark
Sent: Tuesday, September 28, 2004 10:50 AM
To: joseph.armstrong-champ_at_tufts.edu; anjo.kolk_at_oraperf.com
Cc: oracle-l_at_freelists.org
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Joe Armstrong-Champ
Sent: Tuesday, September 28, 2004 11:46 AM
To: anjo.kolk_at_oraperf.com
Cc: oracle-l_at_freelists.org
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


--
http://www.freelists.org/webpage/oracle-l
--
http://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.

--
http://www.freelists.org/webpage/oracle-l