First you need to know how many cursors are you session using at maximum
select max(value) from V$sesstat natural join v$statname
where name = ‘opened cursors current’;
Then you can give some safe e.g. 30% and do an
alter system set open_cursors=&New_value_plus_20pct;
Select amount,Opened_cursos_rounded*10+10 from
(select trunc(value/10) Opened_cursos_rounded,count(*) Amount from V$sesstat natural join v$statname
where name = ‘opened cursors current’
group by trunc(value/10) order by 1);
AMOUNT OPENED_CURSOS_ROUNDED*10
———- ————————
35 0
59 10
10 20
3 30
2 40
1 60
1 70
1 80
1 130
1 180
1 190
1 200
1 220
2 230
14 rows selected.
On my example you can see that most of sessions are using just 10 cursors, but my max are at 230.
So put a limit on 300 is sounds good.
