-- -------------------------------------------------------
-- Roqet cursor checking script copyright 2007 roqet - http://www.roqet.org.
-- This script can be distributed and modified under the terms of the
-- GNU General Public License: http://www.gnu.org/copyleft/gpl.html
--   ~~~
--   'l'
--    -
-- -------------------------------------------------------

-- The session cursor cache can be constrained by either the session_cached_cursors parameter, 
-- or the open_cursors parameter. This script reports the current maximum usage in any session 
-- with respect to these limits.
-- If either of the Usage column figures approaches 100%, then the corresponding parameter should 
-- normally be increased. 

SELECT
  'session_cached_cursors'  parameter,
  LPAD(value, 5)  value,
  DECODE(value, 0, '  n/a', to_char(100 * used / value, '990') || '%')  usage
FROM
  (SELECT
      MAX(s.value)  used
    FROM
      v$statname  n,
      v$sesstat  s
    WHERE
      n.name = 'session cursor cache count' and
      s.statistic# = n.statistic#
  ),
  (SELECT
      value
     FROM
      v$parameter
    WHERE
      name = 'session_cached_cursors'
  )
UNION ALL
SELECT
  'open_cursors',
  LPAD(value, 5),
  to_char(100 * used / value,  '990') || '%'
FROM
  (SELECT
      MAX(sum(s.value))  used
    FROM
      v$statname  n,
      v$sesstat  s
    WHERE
      n.name in ('opened cursors current', 'session cursor cache count') and
      s.statistic# = n.statistic#
    GROUP BY
      s.sid
  ),
  (SELECT
      value
    FROM
      v$parameter
    WHERE
      name = 'open_cursors'
  )

--

--To increase cursors, run this as SYS (this also changes the spfile value (required for restart)):

ALTER SYSTEM SET open_cursors = 1500 SCOPE=MEMORY;

ALTER SYSTEM SET open_cursors = 1500 SCOPE=SPFILE;

ALTER SYSTEM SET session_cached_cursors = 2500 SCOPE=SPFILE;

-----

SELECT count(*) FROM v$open_cursor

----------------------

SELECT N.NAME, S.VALUE 
FROM V$STATNAME N , V$SYSSTAT S 
WHERE N.STATISTIC# = S.STATISTIC# AND 
S.STATISTIC# in (2,3)

SELECT C.SID AS "OraSID", 
       C.ADDRESS || ':' || C.HASH_VALUE AS "SQL Address", 
       COUNT(C.SADDR) AS "Cursor Copies" 
  FROM V$OPEN_CURSOR C 
GROUP BY C.SID, C.ADDRESS || ':' || C.HASH_VALUE 
HAVING COUNT(C.SADDR) > 2 
ORDER BY 3 DESC 

----------------------

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' order by a.value DESC;

----------------------