Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: session_cached_cursors

Re: session_cached_cursors

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Fri, 29 Mar 2002 13:30:56 +0000
Message-ID: <3CA46C90.38C4@yahoo.com>


Jonathan Lewis wrote:
>
> open_cursors and session_cached_cursors
> don't really have anything to do with each other,
> although the cursors kept "open" by caching do
> (I think) get counted under the open_cursors
> limit.
>
> There are a couple of statistics in v$sesstat
> that will help:
> session cursor cache count - how many are cached
> session cursor cache hit - how much use caching has been.
>
> If the COUNT is less than the init.ora parameter,
> then there is no point in increasing the parameter.
>
> The possible overhead is a small overhead on
> memory for the session, and a small overhead
> in CPU scanning cache (which I believe is little
> more than a set of objects holding hash values
> and pointers into the SGA). However, if the
> value is set too high, it is possible that you will
> find the SGA is being used more aggressively
> by all the extra SQL cursors that are being
> held as open.
>
> --
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Next Seminar - UK, April 3rd - 5th
> http://www.jlcomp.demon.co.uk/seminar.html
>
> Host to The Co-Operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> Author of:
> Practical Oracle 8i: Building Efficient Databases
>
> Stan wrote in message <3CA40547.7090106_at_yahoo.com>...
> >hi all,
> >
> >solaris 8.0, 8.1.7.2 32b
> >
> >Will there be any performance issues of increasing
> >session_cached_cursors ? already i have open_cursors set to 1000. Also
> >is there anyway i can find the optimal value for my session cached
> >cursor based on my application usuage.
> >
> >Thanks in Advance,
> >-Stan
> >

... and Bjorn Engsig (in one of his articles) alludes to anything larger than 100 could typically results it being longer to scan the session cursor table than if it were not there at all..

I have not tested this hypothesis.

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."
Received on Fri Mar 29 2002 - 07:30:56 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US