Настройка shared cursors

http://psoug.org/reference/cursor_sharing.html
http://www.orafaq.com/node/758
http://www.freelists.org/post/oracle-l/RE-shared-pool-latch,2
http://kerryosborne.oracle-guy.com/2008/09/flush-a-single-sql-statement/
http://orachat.com/oracle-library-cache-tuning-real-case-study/

AWR-Report In-Depth Analysis

–total cursors open, by session
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = ‘opened cursors current’;

–total cursors open, by username & machine
select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur,
s.username, s.machine
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = ‘opened cursors current’
group by s.username, s.machine
order by 1 desc;

SQL> select max(a.value) as highest_open_cur, p.value as max_open_cur
2> from v$sesstat a, v$statname b, v$parameter p
3> where a.statistic# = b.statistic#
4> and b.name = ‘opened cursors current’
5> and p.name= ‘open_cursors’
6> group by p.value;

–session cached cursors, by session
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = ‘session cursor cache count’ ;

select c.user_name, c.sid, sql.sql_text
from v$open_cursor c, v$sql sql
where c.sql_id=sql.sql_id  — for 9i and earlier use: c.address=sql.address
and c.sid=&sid