Топ-100
 
CerebroSQL

Oracle session manager

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

 
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 immediate key)

  • Enable tracing for selected session

  • 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

 

Session list

-> Request to retrieve data <-

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 the manager's tabs, the following fields must be present in the requests:

  • SID

  • SERIAL#

  • EVENT

  • SQL_ID

The rest of the fields can be changed.

 

Viewing a description of a session pending

  • Select a session in the list of sessions.

  • In the context menu, select "Show event description"

Oracle session manager - event description
 
Parallel session tab

Displays a list of sessions executing requests in parallel in multiple threads

Oracle session manager - parallel sessions

 

List of sessions performing parallel computing

-> Request to retrieve data <-

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

-> Request to retrieve data <-

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 pending "resource" releases in a tree view. Root node in the tree - session holding the lock

Oracle session manager - blocking tree

Building a tree

-> Request to retrieve data <-

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_WA) 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 selecting a session in 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

-> Request to retrieve data <-

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

-> Request to retrieve data <-

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

-----

Session statistics - non-zero system session statistics values

-> Request to retrieve data <-

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 - blocking objects by session

-> Request to retrieve data <-

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

-----

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

-> Request to retrieve data <-

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 - plan for the execution of the current query

-> Request to retrieve data <-

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

-----

Rolback - statistics of using the rollback segment for the session

-> Request to retrieve data <-

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 waiting

-> Request to retrieve data <-

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 - data of the DBMS_SQLTUNE system package analyzing the query execution process

-> Request to retrieve data <-

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

-----

Query statistics - statistics on query execution (specific sql_id)

-> Request to retrieve data <-

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

-----