top of page
CerebroSQL

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.

dashboard open cursor

Opening a window

  • Action menu - Open cursor

  • In the main window, clicking on the line "open cursor"

CerebroSQL - 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"

CerebroSQL - open cursor details

 

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

CerebroSQL - open cursor details

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

bottom of page