CerebroSQL: Oracle session manager
[EN]
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
Update the data
Open the Query Editor
Open the Active Session Manager
End a dedicated session (the session is terminated with the immediate key)
Enable dedicated session tracing
Finish tracing the session highlighted in the Eracing is enabled for window (the session should be highlighted in the Eracing is enabled for list)
Enable automatic data updates every 5 seconds.
Turn on the backlight of active sessions and sessions that are in the lock state
The Sessions tab
SQL query
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 changed in the "Program SQL" editor
For all the manager's tabs to work correctly, the following fields must be present in the requests:
SID
SERIAL#
EVENT
SQL_ID
The remaining fields can be changed.
Viewing the description of the waiting session
Select a session in the session list.
In the "Details" block, double-click on the "Event" item
The "Parallel session" tab
Displays a list of sessions executing requests in parallel in several threads
List of parallel computing sessions
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
The "Blocking tree" tab
Displays sessions in the form of a tree that are waiting for the release of a "resource". The root node in the tree is the session holding the lock
SQL query
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 the session is highlighted 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
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-zero 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 - locking objects 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 time of execution of long-term operations in the database (reading data from a table, sorting) and the estimated time of completion 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 - the plan for executing the current request
select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR('$$SQL_ID' ))
Rollback - statistics on the usage of the rollback segment for the session
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
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 - данные системного пакета DBMS_SQLTUNE анализирующего процесс выполнение запроса
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)
select * from V$SQLSTATS where sql_id = '$$SQL_ID'
[RU]
Менеджер сессий позволяет отслеживать работу процессов в базе данных Oracle. Управлять сессиями, включать/выключать трассировку сессий и многое другое
Кнопки управления
Обновить данные
Открыть редактор запросов
Открыть менеджер активных сессий
Завершить выделенную сессию (выполняется завершение сессии с ключом immediate)
Включить трассировку выделенной сессии
Завершить трассировку выделенной в окне Eracing is enabled for сессии (сессия должна быть выделена именно в списке Eracing is enabled for)
Включить автоматическое обновление данных каждые 5 секунд.
Включить подсветку активных сессий и сессий находящихся в состоянии блокировки
Вкладка "Sessions"
SQL Запрос
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
Запрос может быть изменен в редакторе "Program SQL"
Для корректной работы всех вкладок менеджера необходимо наличие в запросах следующих полей:
SID
SERIAL#
EVENT
SQL_ID
Остальные поля могут быть изменены.
Просмотр описания ожидания сессии
Выделить сессию в списке сессий.
В блоке "Details" дважды нажать на пункт "Event"
Вкладка "Parallel session"
Отображает список сессий выполняющих запросы параллельно в несколько потоков
Список сессий выполняющих параллельные вычисления
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
Системная статистика по выполнению параллельных процессов
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"
Отображает в виде дерева сессии ожидающие освобождения "ресурса". Корневой узел в дереве - сессия удерживающая блокировку
SQL Запрос
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)
Дополнительная информация по сессиям
При выделении сессии в сетках, программа отображает дополнительные данные. Программа отображает информацию в зависимости от активности того или иного листа.
Cursors - сведения о открытых курсорах в сессии
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 - текст текущего SQL запроса в сессии
select sql_fulltext
from v$sql
where sql_id='$$SQL_ID'
Session 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 - блокировки объектов сессией
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 - данные по времени выполнения длительных операций в БД (чтение данных из таблицы, сортировка) и ориентировочное время завершения операции
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 - план выполнения текущего запроса
select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR('$$SQL_ID' ))
Rolback - статистика использования сегмента отката для сессии
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 - статистика по текущему ожиданию сессии
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 - данные системного пакета DBMS_SQLTUNE анализирующего процесс выполнение запроса
SELECT DBMS_SQLTUNE.report_sql_monitor(
sql_id => '$$SQL_ID',
type => 'TEXT',
report_level => 'ALL') AS report
FROM dual
Query statistics - статистика по выполнению запроса (конкретного sql_id)
select * from V$SQLSTATS where sql_id = '$$SQL_ID'