Топ-100
 
CerebroSQL

Oracle open cursor

Viewing information about open cursors by sessions in Oracle DBMS

Opening a window

  • Action menu - Open cursor

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

CerebroSQL - open cursor.jpg

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.jpg

Session information - session information from v$session

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

Tab "Cursor" list of all cursors of the session 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.jpg
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