RE: session_cached_cursors pros & cons

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Tue, 28 Sep 2004 15:27:15 -0400
Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CF09EFEB92@bosmail00.bos.il.pqe>

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

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_at_acxiom.com] Sent: Tuesday, September 28, 2004 12:41 PM To: Bobak, Mark; joseph.armstrong-champ_at_tufts.edu; anjo.kolk_at_oraperf.com Cc: oracle-l_at_freelists.org
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_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 = =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

--

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,=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.

--

http://www.freelists.org/webpage/oracle-l Received on Tue Sep 28 2004 - 14:24:12 CDT