How to finding Max SESSION_CACHED_CURSORS in use

Перепечатываю с блога Oracle Logbook

select max(value)
from v$sesstat natural join v$statname
where name = 'session cursor cache count';

-- How to finding maximum session_cached_cursors
select session_cached_cursors * 30+30 Cached_Cursors_Rounded,
       sessions_count
  from (
        select trunc(value/30)  SESSION_CACHED_CURSORS,
               count(*) sessions_count
          from V$sesstat natural join v$statname
         where name = 'session cursor cache count'
         group by trunc(value/30) order by 1
       );

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

7 rows selected.

В последней строке результата значение 210 это именно то значение в которое необходимо установить параметр SESSION_CACHED_CURSORS.

Необходимо время от времени мониторить состояние и корректировать параметр, но не переусердствуйте, память сервера используется и для других процессов.

Версия для Oracle 11g с использованием LISTAGG, отображает какие usernames сколько курсоров используют:

set linesize 254
col sessions format a30
col usernames format a70

select session_cached_cursors * 30+30 Cached_Cursors_Rounded,
       sessions_count, 
       sessions, 
       usernames
  from (
        select SESSION_CACHED_CURSORS,
               sum(sessions_count) sessions_count,               
               LISTAGG(sessions, ',') WITHIN GROUP (ORDER BY sessions) sessions,               
               LISTAGG(username || '(' || user_cnt || ')', ',') WITHIN GROUP (ORDER BY sessions) usernames
          from (
                select trunc(value/30)  SESSION_CACHED_CURSORS,
                       count(*) sessions_count,                        
                       username,
                       LISTAGG(sid, ',') WITHIN GROUP (ORDER BY sid) sessions,
                       count(username) user_cnt 
                       --LISTAGG(username, ',') WITHIN GROUP (ORDER BY sid) usernames 
                  from v$sesstat ss natural join v$statname sn 
                  natural join v$session s 
                 where name = 'session cursor cache count' 
                 group by trunc(value/30), username
               )
         group by SESSION_CACHED_CURSORS
       )
 order by SESSION_CACHED_CURSORS;

Apply new SESSION_CACHED_CURSORS

SQL> alter system set session_cached_cursors=210 scope = spfile;
shutdown immediate;
startup;