Shareware software


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