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

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

Control buttons

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

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

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

Rolback - session rollback segment usage statistics

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

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

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)