How does Oracle view the open_cursors&session_cached_cursors that has been used
This article mainly introduces Oracle how to view the open_cursors&session_cached_cursors that has been used, the article is very detailed, has a certain reference value, interested friends must read it!
View the maximum number of open cursor and cached cursor currently used by session:
SELECT 'session_cached_cursors' PARAMETER
LPAD (VALUE, 5) VALUE
DECODE (VALUE, 0, 'nAccord), 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')
Note: if the queried session open cursor reaches 100% client, it is likely to report an incorrect ORA-01000.
Check the specific program of session open cursor and the number of open cursor:
SELECT a.inst_id
A.sid
A.USERNAME
A.SCHEMANAME
A.OSUSER
A.machine
A.TERMINAL
A.LOGON_TIME
A.PROGRAM
A.STATUS
B.name
B.used
FROM gv$session a
(SELECT n.inst_id
Sid
N.name
S.VALUE used
FROM gv$statname n, gv$sesstat s
WHERE n.name IN ('opened cursors current'
'session cursor cache count')
AND s.statistic# = n.statistic#
AND n.inst_id = s.inst_id) b
WHERE a.sid = b.sid
AND a.inst_id = b.inst_id
AND b.name 'session cursor cache count'
ORDER BY b.used DESC
Check the specific SQL of session open cursor (this method is the result of joint v$open_cursor and may be inaccurate. But the SQL of the most session open_cursors must be in the running result)
SELECT distinct a.inst_id
A.sid
A.USERNAME
A.SCHEMANAME
A.OSUSER
A.machine
A.TERMINAL
A.LOGON_TIME
A.PROGRAM
A.STATUS
B.name
B.used,c.sql_id
FROM gv$session a
(SELECT n.inst_id, sid, n.name, s.VALUE used
FROM gv$statname n, gv$sesstat s
WHERE n.name IN
('opened cursors current'
'session cursor cache count')
AND s.statistic# = n.statistic# and n.inst_id=s.inst_id) b _
WHERE a.sid= b.sid and a.inst_id = b.inst_id and a.sid=c.sid and c.CURSOR_TYPE in ('OPEN','OPEN-PL/SQL','OPEN-RECURSIVE')
And b.name 'session cursor cache count'
Order by b.used desc
The above is all the content of the article "how to check the open_cursors&session_cached_cursors that has been used by Oracle". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!
