Oracle open cursor

Viewing information about open cursors by sessions in Oracle DBMS

DB parameter open_cursors

Specifies the maximum number of open cursors (SQL private area handles) that a session can have at the same time. You can use this option to prevent a session from opening too many cursors. It's important to set OPEN_CURSORS high enough to prevent running out of open cursors in your application. The number will vary from one application to another. Assuming the session does not open the number of cursors specified in OPEN_CURSORS, there is no additional cost to setting this value higher than necessary.

Opening a window

Action menu - Open cursor
In the main window, clicking on the line "open cursor"

select a.value,
s.username,
nvl(s.OSUSER,'-')||' ('|| nvl(s.MACHINE,'-')||')' US,
s.sid||':'||s.serial# SS,
'300' MAX_OPEN
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'
and s.username is not null
order by a.value desc

Viewing detailed information

Select a session in the "List session"

Session information - session information from v$session

select status,schemaname,osuser,machine,program,logon_time from v$session where sid=$$SID

Cursor tab lists all session cursors with their state

select SQL_ID, SQL_TEXT, cursor_type from V$OPEN_CURSOR oc where oc.sid=$$SID

Viewing details about SQL queries

In the list of cursors on the Cursor tab, double-click on any line

window structure

Query text - the text of the SQL query

select SQL_FULLTEXT, HASH_VALUE from v$sql where sql_id='$$SQL_ID'

Query plan execute - SQL query execution plan

select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR('$$SQL_ID'))

Event - waiting for sessions when executing a request (data for the last 10 minutes). The information is retrieved from a local database. Data is collected by the system when the Monitoring mode is enabled - Use mode "Real-time" in the program settings

select count(*) "CountRW",
c_event
from cer_ash_database c
where c_dbname = $$CONNECTALIAS
and c_dbts>= datetime('now','-600 seconds','localtime')
and c_dbts< datetime('now','-1 seconds','localtime')
and c_sql_id="$$SQL_ID"
group by c_event

order by count(*) desc

Session list - a list of sessions that have completed the request in the last 10 minutes. The information is retrieved from a local database. Data is collected by the system when the Monitoring mode is enabled - Use mode "Real-time" in the program settings

select c_sid,

c_serial,

c_username||'@'||c_machine "hm"

from cer_ash_database c

where c_dbname = "TESTDB2"

and c_dbts>= datetime('now','-600 seconds','localtime')

and c_dbts< datetime('now','-1 seconds','localtime')

and c_sql_id="$$SQL_ID"

group by c_sid||':'||c_serial||'['||c_username||'@'||c_machine||']'

order by c_sid