Oracle Logbook

July 15, 2008

How to finding Maximum SESSION_CACHED_CURSORS in use

Filed under: Uncategorized — heliodias @ 7:57 pm

select max(value)
from v$sesstat natural join v$statname
where name = ‘session cursor cache count’ ;
Select amount,SESSION_CACHED_CURSORS*30+30 Cached_Cursors_Rounded from
(select trunc(value/30)  SESSION_CACHED_CURSORS,count(*) Amount
from V$sesstat natural join v$statname
where name = ‘session cursor cache count’
group by trunc(value/30) order by 1);

    AMOUNT CACHED_CURSORS_ROUNDED
———- ———————-
       223                     30
        32                     60
        15                     90
         6                    120
         2                    150
         3                    180
         5                    210

7 rows selected.
As we can see 210 will allow every session to cache what they need.

Advertisements

How Adjust Open_cursors on Oracle Database

Filed under: Uncategorized — heliodias @ 7:56 pm

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.

Blog at WordPress.com.