Oracle Top Sessions Monitor
Displays database sessions ordered by a selected metric (reads, executions, or CPU usage). Shows session details such as user, SID, status, module, machine, program, and logon time. Useful for identifying high-resource sessions and performance troubleshooting.
oraclesqlperformance-tuningv1.0.0
0 stars0 downloads15 views0 comments
By OracleDba • Created
Code
(39 lines)123456789101112131415161718192021222324252627282930313233343536373839
-- -----------------------------------------------------------------------------------
-- File Name : https://oracle-base.com/dba/monitoring/top_sessions.sql
-- Author : Tim Hall
-- Description : Displays information on all database sessions ordered by executions.
-- Requirements : Access to the V$ views.
-- Call Syntax : @top_sessions.sql (reads, execs or cpu)
-- Last Modified: 21/02/2005
-- -----------------------------------------------------------------------------------
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20
SELECT NVL(a.username, '(oracle)') AS username,
a.osuser,
a.sid,
a.serial#,
c.value AS &1,
a.lockwait,
a.status,
a.module,
a.machine,
a.program,
TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session a,
v$sesstat c,
v$statname d
WHERE a.sid = c.sid
AND c.statistic# = d.statistic#
AND d.name = DECODE(UPPER('&1'), 'READS', 'session logical reads',
'EXECS', 'execute count',
'CPU', 'CPU used by this session',
'CPU used by this session')
ORDER BY c.value DESC;
SET PAGESIZE 14