Re: cursors-related oracle parameters

Syltrem wrote:

> Hi
>
> There must be something I don't understand (well probably lots of things but
> this one is particularly bugging me today).
> I would like to find a way to best calculate a good value for
> session_cached_cursors. I thought setting it to a value that would be enough
> to serve most client sessions, should be fine (i.e. if most clients have
> less than 100 cursors open, set it to 100).
>
> So I ran this query :
>
> SQL> select avg(value), max(value), sum(value)
> 2 from V$SESSTAT s, v$statname n
> 3 where n.STATISTIC#=s.STATISTIC# and name='opened cursors current' ;
>
> AVG(VALUE) MAX(VALUE) SUM(VALUE)
> ---------- ---------- ----------
> 246.798535 1124 67376
>
> One session actually had 1124 opened cursors and the total number of opened
> cursors is way more the the value of parameter open_cursors.
>
> SQL> show parameter open_cursor
> open_cursors integer
> 9000
>
> How can that be ?
>
> And how should I determine the best value for session_shared_cursor?
>
> Thanks for your help/advice
>
> --
> Syltrem
>
> OpenVMS 7.3-1 + Oracle 8.1.7.4
> http://pages.infinit.net/syltrem (OpenVMS related web site, en français)
> ---zulu is not in my email address---

If I had 1124 open cursors I would not be investing my time in calculating a higher number. I'd be investing my effort in tracking down the bad code and crushing it with a brick.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
http://www.outreach.washington.edu/extinfo/certprog/aoa/aoa_main.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)