open cursors

[Asterault]

This rather unsettling statistic has me scratching my head:

SVRMGR> select name,value from v$sysstat where statistic#=3;
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 3,8655E+10
1 row selected.

......whereas........

VALUE NUMBER
SVRMGR> select name,value from v$sysstat where statistic#=192;
NAME VALUE
---------------------------------------------------------------- ----------
session cursor cache count 17504

........seems more reasonable yet the numbers don't add up

SVRMGR> select maximum,count from v$session_cursor_cache;
MAXIMUM COUNT
---------- ----------
300 0
1 row selected.

1 row selected.
SVRMGR> select value,sid from v$sesstat where statistic#=192 group by sid,value
2> /
VALUE SID
---------- ----------
0 1
0 2
0 3
0 4
0 5
0 6
0 7
0 8
0 9
154 10
17 11
24 13
33 15
79 16
9 17
32 18
14 19
30 20
8 21
79 22
44 24
199 25
4 27
12 28
10 29
91 30
8 36
40 37
53 38
4 39
172 41
15 42
96 44
199 47
61 48
99 50
14 51
0 52
7 53
57 54
10 55
14 56
34 57
26 59
8 62
193 63
66 66
62 67
13 68
90 69
18 70
66 71
139 72
53 rows selected.

... on a machine with 2G memory a lot seems to be eaten up unnecessarily

[jgmagnus]

Do your developers close their cursors in all their code??

example:

open C1;
fetch c1 into v_num;
close C1;

(then do their manipulations)

or do they leave C1 open as they manipulate?

[sambavan]

Check your session_cached_cursors and open_cursors options. These represent the number/session. so in your case the total seems to be almost there when you add up all the cursors/session. If you feel that the value is too high, check to make sure the following.

1. reduce the # of open cursors per session
2. session cached cursors

Sam

[davey23uk]

and you can see them all in v$open_cursor