Shulou(Shulou.com)05/31 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.