Топ-100
top of page
Волнистый абстрактный фон
CerebroSQL

Oracle DBMS session manager

​

The session manager allows you to monitor the work of processes in the database Oracle. Manage sessions, enable/disable session tracing and more

​

list session
Кнопки управления

Control buttons

Oracle session manager - menu button

  • Update the data

  • Open Query Editor

  • Open active session manager

  • End the selected session (the session is terminated with the keyimmediate)

  • Enable selected session tracing

  • End trace selected in window Eracing is enabled for sessions (the session must be selected in the list Eracing is enabled for)

  • Enable automatic data refresh every 5 seconds.

Вкладка "Session"

Session tab

Oracle session manager

 

List of sessions

​

select s.SID,
       s.SERIAL#,
       s.SCHEMANAME,
       s.STATUS,

       s.EVENT,
       s.SQL_ID,
       s.OSUSER,
       s.MACHINE,
       s.MODULE,
       s.SERVER,
       s.PORT,
       s.LOGON_TIME,
       s.TERMINAL,
       s.PROGRAM,
       s.TYPE,
       s.SQL_HASH_VALUE,
       s.SQL_EXEC_START,
       s.ACTION,
       s.BLOCKING_INSTANCE,
       s.BLOCKING_SESSION_STATUS,
       s.BLOCKING_SESSION,
       s.FINAL_BLOCKING_SESSION_STATUS,
       s.FINAL_BLOCKING_SESSION,
       s.CLIENT_INFO,
       s.ROW_WAIT_ROW#,
       s.ROW_WAIT_BLOCK#,
       s.ROW_WAIT_OBJ#,
       s.ROW_WAIT_FILE#,
       s.PLSQL_SUBPROGRAM_ID,
       s.PLSQL_ENTRY_OBJECT_ID,
       s.PLSQL_ENTRY_SUBPROGRAM_ID,
       s.PLSQL_DEBUGGER_CONNECTED,
       s.PLSQL_OBJECT_ID,
       s.RESOURCE_CONSUMER_GROUP
  from v$session s

 

The query can be modified in the " Program SQL " editor

For the correct operation of all manager tabs, the following fields must be present in requests:

  • SID

  • SERIAL#

  • EVENT

  • SQL_ID

The rest of the fields can be changed.

 

View session wait description

  • Select a session in the list of sessions.

  • In the context menu select " Show event description "

​

​

Oracle session manager - event description
Вкладка "Parallel session"

Tab "Parallel session"

​

Displays a list of sessions executing queries in parallel on multiple threads

​

Oracle session manager - parallel sessions

​

List of sessions performing parallel computing

​

SELECT DECODE(px.qcinst_id, NULL, username, ' - '||
           LOWER(SUBSTR(pp.SERVER_NAME, LENGTH(pp.SERVER_NAME)-4,4))) "Username",
       s.sql_id, 
       DECODE(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave",       
       TO_CHAR(px.server_set) "SlaveSet", 
       s.program, 
       TO_CHAR(s.SID) "SID", 
       s.Serial#,       
       TO_CHAR(px.inst_id) "Slave INST", 
       DECODE(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) AS STATE,       
       CASE  sw.state 
          WHEN 'WAITING' THEN SUBSTR(sw.event,1,30) 
          ELSE NULL 
       END AS wait_event,           
       DECODE(px.qcinst_id, NULL, TO_CHAR(s.SID), px.qcsid) "QC SID",       
       TO_CHAR(px.qcinst_id) "QC INST",       
       DECODE(px.server_set,'',s.last_call_et,'') "Elapsed (s)"  
  FROM gv$px_session px, 
       gv$session s, 
       gv$px_process pp, 
       gv$session_wait sw
WHERE px.SID=s.SID(+)   
   AND px.serial#=s.serial#(+)   
   AND px.inst_id = s.inst_id(+)   
   AND px.SID = pp.SID(+)   
   AND px.serial#=pp.serial#(+)   
   AND sw.SID = s.SID   
   AND sw.inst_id = s.inst_id
ORDER BY username, DECODE(px.QCINST_ID,  NULL, px.INST_ID,  px.QCINST_ID), 
  px.QCSID,  DECODE(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP), px.SERVER_SET, px.INST_ID

 

System statistics on the execution of parallel processes

​

SELECT substr(NAME, 21,length(name)) "name", 
       VALUE,       
       ROUND((RATIO_TO_REPORT(VALUE) OVER ())*100, 2) || '%' PERC
 FROM V$SYSSTAT 
WHERE NAME LIKE 'Parallel%' 
ORDER BY NAME DESC

Blocking tree tab

Вкладка "Blocking TREE"

​

Displays a tree of sessions waiting for a "resource" to be freed. The root node in the tree is the session holding the lock

​

Oracle session manager - blocking tree

​

Building a tree

​

with
LOCKS as (select /*+ MATERIALIZE*/   * from gv$lock),
S     as (select /*+ MATERIALIZE*/ s.* from gv$session s),
BLOCKERS as (select distinct L1.inst_id, L1.sid
               from LOCKS L1, LOCKS L2
              where L1.block > 0
                and L1.ID1 = L2.ID1
                and L1.ID2 = L2.ID2
                and L2.REQUEST > 0),
 WAITERS as (select inst_id, sid from S where blocking_session is not null or blocking_instance is not null)
select
  LPAD(' ', (LEVEL - 1) * 2) || 'INST#' || s.inst_id || ' SID#' || sid as BLOCKING_TREE,
  s.sid,
  s.serial#,
  s.program,
  substr(s.USERNAME || ' ' || s.CLIENT_IDENTIFIER,1,40) as USERNAME,
  EVENT,
  last_call_et,
  seconds_in_wait as SECS_IN_WAIT,
  blocking_session_status as BLOCK_SESSTAT,
  pdml_enabled,
  s.sql_id,
  s.osuser,
  p.spid,
  s.machine as CLNT_HOST,
  s.process as CLNT_PID,
  s.port    as CLNT_PORT,
  substr(trim(NVL(sa1.sql_text,sa2.sql_text)), 1, 100) SQL_TEXT,
  decode(sign(nvl(s.ROW_WAIT_OBJ#, -1)), -1, 'NONE', DBMS_ROWID.ROWID_CREATE(1, s.ROW_WAIT_OBJ#, s.ROW_WAIT_FILE#, s.ROW_WAIT_BLOCK#, s.ROW_WAIT_ROW#)) req_rowid,
  p1text || ' ' || decode(p1text, 'name|mode', chr(bitand(p1,-16777216)/16777215)||chr(bitand(p1, 16711680)/65535)||' '||bitand(p1, 65535), p1text) as
  p1text,
  p1,
  p1raw,
  p2text || ' ' || decode(p2text, 'object #', o.object_name || ' ' || o.owner || '.' || o.object_name, p2text) as
  p2text,
  p2
from s
 left join gv$sqlarea sa1 on s.sql_id = sa1.sql_id and s.inst_id =  sa1.inst_id
 left join gv$sqlarea sa2 on s.prev_sql_id = sa2.sql_id and s.inst_id =  sa2.inst_id
 left join dba_objects o  on s.p2 = o.object_id
 left join gv$process p on s.paddr = p.addr and s.inst_id = p.inst_id
connect by NOCYCLE prior sid = blocking_session and prior s.inst_id = blocking_instance
start with (s.inst_id, s.sid)
           in (select inst_id, sid from BLOCKERS minus select inst_id, sid from WAITERS) 

Дополнительная информация по сессиям

Additional information on sessions

When highlighting a session in the grids, the program displays additional data. The program displays information depending on the activity of a particular sheet.

​

  • Cursors  - information about open cursors in the session

select oc.sql_text, 
       oc.sql_id 
  from V$OPEN_CURSOR oc, 
       v$SESSION s 
where oc.saddr=s.saddr 
   and s.sid=$$SID

​​

  • SQL Text - the text of the current SQL query in the session

​

select sql_fulltext 
  from v$sql 
where sql_id='$$SQL_ID'

​​

  • Session statistics - values of non-null session system statistics

​

select st.name, 
       se.value 
  from v$sesstat se,
       v$statname st 
where se.statistic#=st.statistic# 
   and se.value<>0 
   and sid=$$SID
order by 2 desc 

​​

  • Lock - object locks by the session

​

select distinct blocking_session, 
       event, 
       do.owner||'.'||do.object_name req_object, 
       sql_text 
 from v$session s, dba_objects do, v$sql s  
where s.ROW_WAIT_OBJ# = do.object_id    
   and s.sql_id = s.sql_id
   and blocking_session is not  null 
   and sid=$$SID

​​

  • Longops - data on the execution time of long operations in the database (reading data from the table, sorting) and the estimated completion time of the operation

​

SELECT sl.OPNAME,       
       sl.TARGET,       
       s.machine,       
       ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,       
       ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,       
       ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct,       
       sl.MESSAGE
  FROM v$session s,       
       v$session_longops sl                   
WHERE s.sid     = sl.sid
   AND s.serial# = sl.serial# 
   and s.SID = $$SID
order by sl.START_TIME desc

​​

  • Plan - execution plan for the current request

​

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

​​