cursors-related oracle parameters

From: Syltrem <syltremzulu_at_videotron.com>
Date: Wed, 27 Aug 2003 14:31:41 -0400
Message-ID: <JN63b.349$G1.1688@tor-nn1.netcom.ca>

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---

Received on Wed Aug 27 2003 - 13:31:41 CDT