Query to count the open cursors for the sessions.
Customer faces 5000~8000 open cursor every day and due to this Java Listeners goes down.SELECT
OC.SID,
OC.USER_NAME,
S.PROGRAM,
COUNT(*) COUNTER,
OC.SQL_ID,
OC.SQL_TEXT
FROM
V$OPEN_CURSOR OC,
V$SESSION S
WHERE
OC.SQL_TEXT NOT LIKE ''%obj#,%''
AND OC.SQL_TEXT NOT LIKE ''%grantee#,%''
AND OC.SQL_TEXT NOT LIKE ''%privilege#%''
AND OC.SQL_TEXT NOT LIKE ''%/*+ rule */%''
AND OC.SQL_TEXT NOT LIKE ''%col#%''
AND OC.SQL_TEXT NOT LIKE ''%sys.mon_mods$%''
AND OC.SQL_TEXT NOT LIKE ''%obj#=%''
AND OC.SQL_TEXT NOT LIKE ''%update$,%''
AND OC.SID=S.SID
AND OC.USER_NAME NOT IN
(''SYS'',''DBSNMP'',''SYSTEM'',''SYSMAN'',''RMAN'')
AND lower(SQL_TEXT) like ''%enqueue%''
GROUP BY
OC.SID,
OC.USER_NAME,
S.PROGRAM,
OC.SQL_ID,
OC.SQL_TEXT
HAVING COUNT(*)>1
ORDER BY
COUNT(*) desc,
OC.USER_NAME,
OC.SID,
OC.SQL_TEXT;
Subscribe to:
Post Comments
(
Atom
)
No comments :
Post a Comment
Note: only a member of this blog may post a comment.