Re: session_cached_cursors and parsing and performance

Could you tell us how setting session_cached_cursors improved your performance? I'm interested in real life examples of using this parameter. My production database has 10 on this parameter.

100 open_cursors is not high. Mine is set to 200.

If you can't change your application to use bind variables, try setting cursor_sharing to force if you're using 8i. If it doesn't cause serious problems (as seen in alert.log), then keep it.

As a general rule, when you have performance problems, you need to look at v$session_wait. Tell us what you have in there.

Yong Huang
yong321_at_yahoo.com

In article <980508139.27453.0.nnrp-12.c2de6f3d_at_news.demon.co.uk>,   "Neil Cudd" <neil_at_cudd.demon.co.uk> wrote:
> My second question today ...
>
> I have a poorly performing database (well the users complain anyway),
 in as
> much as the CPU is 0% idle and and the last time I checked the SQL
 Area Get
> Hit Ratio was below 30%. So I set session_cached cursors to 50 (from
 0) to
> see if I can reduce parsing which I suspected was where the CPU was
 being
> hit.
>
> Now my question is around open cursors - How many open cursors would
 you
> expect a session to have at any one time ? Checking via enterprise
 manager,
> there are sessions with 100+ open cursors. This seems excessive to
 me, but
> I'd like a second opinion ....
>
> Any comments welcome,
>
> Neil Cudd.

Sent via Deja.com
http://www.deja.com/ Received on Fri Jan 26 2001 - 11:47:55 CST