Топ-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
 

View 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

> Query to retrieve data <-

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

-----

  • Query plan execute - SQL query execution plan

> Query to retrieve data <-

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

> Query to retrieve data <-

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

> Query to retrieve data <-

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

-----