Топ-100
 
CerebroSQL

Oracle Database Session Manager

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

 
Control buttons
Oracle session manager - menu button

​​

  • Update the data

  • Open Query Editor

  • Open the active session manager

  • End the selected session (the session is terminated with the key immediate )

  • Enable selected session tracing

  • End tracing of the session selected in the " Eracing is enabled for " window (the session must be selected in the " Eracing is enabled for " list)

  • Enable automatic data refresh every 5 seconds.

 
Session tab
Oracle session manager

 

List of sessions

Panel

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

Panel

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

Panel

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

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

Panel

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

Panel

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

Panel

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

​​

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

Panel

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

Panel

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

Panel

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.MESSAGEFROM   v$session s,       v$session_longops slWHERE  s.sid     = sl.sidAND    s.serial# = sl.serial# and    s.SID = $$SIDorder by sl.START_TIME desc

​​

  • Plan - execution plan for the current request

Panel

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

​​

  • Rolback - session rollback segment usage statistics

Panel

SELECT rs.segment_name, 
       round(r.rssize/1024/1024)||' MB' "size", 
       r.status
FROM v$transaction t,
       v$session s,
       v$rollstat r,
       dba_rollback_segs rs 
WHERE s.saddr = t.ses_addr 
   AND t.xidusn = r.usn 
   AND rs.segment_id = t.xidusn
   AND s.sid = $$SID   
   AND s.serial# = $$SERIAL#
ORDER BY t.used_ublk DESC 

​​

  • Session wait - statistics on the current session wait

Panel

select s.EVENT,
      s.SECONDS_IN_WAIT,
      s.P1,
      s.P1TEXT,
      s.P2,
      s.P2TEXT,
      s.P3
 from V$SESSION_WAIT s  
where sid=$$SID
order by SECONDS_IN_WAIT desc 

​​

  • SQL Monitoring - system package data  DBMS_SQLTUNE parsing query execution process

Panel

SELECT DBMS_SQLTUNE.report_sql_monitor(
      sql_id       => '$$SQL_ID',
      type         => 'TEXT',
      report_level => 'ALL') AS report
FROM dual

​​

  • Query statistics - query execution statistics (specific sql_id)

Panel

select * from V$SQLSTATS where sql_id = '$$SQL_ID'