Oracle Session Management

10 min read

ADVERTISEMENT

Oracle Session Management involves managing and monitoring database sessions for efficient and optimized performance. Below is a collection of SQL scripts categorized by their specific purposes to assist in session management tasks.

Check Session Details

set line 200 pages 200
col USERNAME for a15
col STATUS for a15
col OSUSER for a15
col machine for a15
col spid for a10
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
SELECT s.sid, s.serial#, s.username, s.osuser,s.STATUS, p.spid,s.sql_id, s.machine, p.terminal,s.LOGON_TIME, s.program
FROM v$session s, v$process p
WHERE s.paddr = p.addr and s.sid='&SID';

Terminate All Sessions for a Specific SQL_ID

select 'alter system kill session ' ||''''||SID||','||SERIAL#||' immediate ;' from v$session  
where sql_id='&sql_id';

FOR RAC- kill all sessions of a sql_id

select 'alter system kill session ' ||''''||SID||','||SERIAL#||',@'||inst_id||''''||' immediate ;'  
from gv$session where sql_id='&sql_id'

Kill all session of a user

BEGIN 
FOR r IN (select sid,serial# from v$session where username = 'W3BUDDY') 
LOOP 
EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid  
|| ',' || r.serial# || ''''; 
END LOOP; 
END; 
/

Inactive session check

set lines 300 pages 200
col machine for a30
col status for a20
select sid,serial#,username,program,machine,status,sql_id,logon_time from v$session where status='INACTIVE' and type <> 'BACKGROUND' and sql_id is NULL;

Inactive session check for a user

set lines 300 pages 200
col machine for a30
col status for a20
select sid,serial#,username,program,machine,status,sql_id,logon_time from v$session where status='INACTIVE' and username='W3BUDDY' and type <> 'BACKGROUND';

Inactive session check for a user where sql_id is NULL

select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where status='INACTIVE' and username='W3BUDDY' and type <> 'BACKGROUND' and sql_id is NULL;

Generate dynamic commands to kill all the inactive session for a user

select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where status='INACTIVE' and username='W3BUDDY' and type <> 'BACKGROUND';

Check Blocking Sessions (My Favourite)

set lines 200
col sess format a15
SELECT DECODE(request,0,'Holder: ','  Waiter: ')||sid sess,id1,id2, lmode,inst_id, request, type,ctime
FROM GV$LOCK    WHERE (id1, id2, type)
IN
(SELECT id1, id2, type FROM GV$LOCK WHERE request>0)     ORDER BY id1,       request
/

Check Blocking Sessions

set pages 100
col USERNAME for a20
col WAIT_CLASS for a30
set lines 10000
select 
blocking_session, 
sid, 
serial#, 
username,
status,
wait_class,
sql_id,
seconds_in_wait
from 
v$session
where 
blocking_session is not NULL order by 6;

Find all blocked sessions and who is blocking them

select sid,blocking_session,username,sql_id,event,machine,osuser,program,last_call_et from v$session where blocking_session > 0;
select * from dba_blockers
select * from dba_waiters

Find what the blocking session is doing

select sid,blocking_session,username,sql_id,event,state,machine,osuser,program,last_call_et from v$session where sid=746;

Find the blocked objects

select owner,object_name,object_type from dba_objects where object_id in (select object_id from v$locked_object where session_id=540 and locked_mode =10);

Find current running sqls

set lines 200 pages 200
select sesion.sid,sesion.username,optimizer_mode,hash_value,address,cpu_time,elapsed_time,sql_text 
from v$sqlarea sqlarea, v$session sesion where sesion.sql_hash_value = sqlarea.hash_value and sesion.sql_address = sqlarea.address 
and sesion.username is not null;

Find active sessions in database

set echo off 
set lines 10000
set pages 400
set linesize 95 
set head on 
set feedback on 
col sid head "Sid" form 9999 trunc 
col serial# form 99999 trunc head "Ser#" 
col username form a8 trunc 
col osuser form a7 trunc 
col machine form a20 trunc head "Client|Machine" 
col program form a15 trunc head "Client|Program" 
col login form a11 
col "last call" form 9999999 trunc head "Last Call|In Secs" 
col status form a6 trunc 
select sid,serial#,sql_id,substr(username,1,10) username,substr(osuser,1,10) osuser, 
substr(program||module,1,15) program,substr(machine,1,22) machine, 
to_char(logon_time,'ddMon hh24:mi') login, 
last_call_et "last call",status 
from gv$session where status='ACTIVE' 
order by 1 
/

Find wait events in database

select a.sid,substr(b.username,1,10) username,substr(b.osuser,1,10) osuser, 
substr(b.program||b.module,1,15) program,substr(b.machine,1,22) machine, 
a.event,a.p1,b.sql_hash_value 
from v$session_wait a,V$session b 
where b.sid=a.sid 
and a.event not in('SQL*Net message from client','SQL*Net message to client', 
'smon timer','pmon timer') 
and username is not null 
order by 6 
/

Find sessions generating undo

select a.sid, a.serial#, a.username, b.used_urec used_undo_record, b.used_ublk used_undo_blocks 
from v$session a, v$transaction b 
where a.saddr=b.ses_addr;

Find sessions generating lots of redo

set lines 2000 
set pages 1000 
col sid for 99999 
col name for a09 
col username for a14 
col PROGRAM for a21 
col MODULE for a25 
select s.sid,sn.SERIAL#,n.name, round(value/1024/1024,2) redo_mb, sn.username,sn.status,substr (sn.program,1,21) "program", sn.type, sn.module,sn.sql_id 
from v$sesstat s join v$statname n on n.statistic# = s.statistic# 
join v$session sn on sn.sid = s.sid where n.name like 'redo size' and s.value!=0 order by 
redo_mb desc;

Check parallel sessions

set lines 400 pages 400
col sql format a38
col username format a21
col secs format 999999999
col machine format a12
col event format a27
col state format a10
col inst for 9999
select /*+ rule */ distinct
w.inst_id inst,w.sid,s.username,substr(w.event,1,25) event,substr(s.machine,1,12) machine,substr(w.state,1,10) state,s.SQL_ID,
substr(q.sql_text,1,35) "SQL",round(s.LAST_CALL_ET) SECS
from gv$session_wait w,gv$session s,gv$sql q where 
w.sid=s.sid
and s.SQL_HASH_VALUE=q.HASH_VALUE
and s.username is not null
order by "SECS";

Get parallel query details

col username for a9 
col sid for a8 
set lines 299 
select 
s.inst_id, 
decode(px.qcinst_id,NULL,s.username, 
' - '||lower(substr(s.program,length(s.program)-4,4) ) ) "Username", 
decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" , 
to_char( px.server_set) "Slave Set", 
to_char(s.sid) "SID", 
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID", 
px.req_degree "Requested DOP", 
px.degree "Actual DOP", p.spid 
from 
gv$px_session px, 
gv$session s, gv$process p 
where 
px.sid=s.sid (+) and 
px.serial#=s.serial# and 
px.inst_id = s.inst_id 
and p.inst_id = s.inst_id 
and p.addr=s.paddr 
order by 5 , 1 desc 
/

Monitor parallel queries

select 
s.inst_id, 
decode(px.qcinst_id,NULL,s.username, 
' - '||lower(substr(s.program,length(s.program)-4,4) ) ) "Username", 
decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" , 
to_char( px.server_set) "Slave Set", 
to_char(s.sid) "SID", 
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID", 
px.req_degree "Requested DOP", 
px.degree "Actual DOP", p.spid 
from 
gv$px_session px, 
gv$session s, gv$process p 
where 
px.sid=s.sid (+) and 
px.serial#=s.serial# and 
px.inst_id = s.inst_id 
and p.inst_id = s.inst_id 
and p.addr=s.paddr 
order by 5 , 1 desc;

Get sid from os pid ( server process)

col sid format 999999 
col username format a20 
col osuser format a15 
select b.spid,a.sid, a.serial#,a.username, a.osuser 
from v$session a, v$process b 
where a.paddr= b.addr 
and b.spid='&spid' 
order by b.spid;

Check long running sessions

col SOFAR format 99999999999999999
col TOTALWORK format 9999999999999999
col username format a12
col event format a21
select s.sid,s.username,l.SOFAR,l.TOTALWORK,
round(l.SOFAR*100/l.TOTALWORK) "Percent Complete",
l.TIME_REMAINING , substr(s.event,1,20) event
from v$session_longops l,v$session s
where round(l.SOFAR*100/l.TOTALWORK) <> 100
and s.sid=l.sid
order by 6;

Find long running operations

select sid,inst_id,opname,totalwork,sofar,start_time,time_remaining from gv$session_longops where totalwork<>sofar;

Find blocking sessions that were blocking for more than 15 minutes + objects and sql

select s.SID,p.SPID,s.machine,s.username,CTIME/60 as minutes_locking, do.object_name as locked_object, q.sql_text
from v$lock l
join v$session s on l.sid=s.sid
join v$process p on p.addr = s.paddr
join v$locked_object lo on l.SID = lo.SESSION_ID
join dba_objects do on lo.OBJECT_ID = do.OBJECT_ID 
join v$sqlarea q on  s.sql_hash_value = q.hash_value and s.sql_address = q.address
where block=1 and ctime/60>15

Check who is blocking who in RAC

SELECT DECODE(request,0,'Holder: ','Waiter: ') || sid sess, id1, id2, lmode, request, type
FROM gv$lock
WHERE (id1, id2, type) IN (
SELECT id1, id2, type FROM gv$lock WHERE request>0)
ORDER BY id1, request;

Check who is blocking who in RAC, including objects

SELECT DECODE(request,0,'Holder: ','Waiter: ') || gv$lock.sid sess, machine, do.object_name as locked_object,id1, id2, lmode, request, gv$lock.type
FROM gv$lock join gv$session on gv$lock.sid=gv$session.sid and gv$lock.inst_id=gv$session.inst_id
join gv$locked_object lo on gv$lock.SID = lo.SESSION_ID and gv$lock.inst_id=lo.inst_id
join dba_objects do on lo.OBJECT_ID = do.OBJECT_ID 
WHERE (id1, id2, gv$lock.type) IN (
SELECT id1, id2, type FROM gv$lock WHERE request>0)
ORDER BY id1, request;

Find Top 25 Wait Events

set lines 180
set pages 1000
col event format a50

PROMPT --> Top 25 Wait Events

select * from (
select inst_id,event,count(*) E_COUNT from gv$session_wait
where event <> 'SQL*Net message from client'
group by inst_id,event order by 3 desc)
where rownum < 26
order by 3
/
col sql format a35
col username format a20
col child format 999
col secs format 9999
col machine format a12
col event format a25
col state format a10

select /*+ rule */ distinct
w.sid,s.username,substr(w.event,1,25) event,substr(s.machine,1,12) machine,substr(w.state,1,10) state,s.SQL_ID,--q.CHILD_NUMBER CHILD,
substr(q.sql_text,1,33) "SQL",s.WAIT_TIME_MICRO/1000000 SEC
from gv$session_wait w,gv$session s,gv$sql q where w.event like '%&event%'
and w.sid=s.sid
and s.SQL_HASH_VALUE=q.HASH_VALUE(+)
and s.status='ACTIVE'
and s.username is not null
and substr(w.event,1,25) not like 'SQL*Net message from clie%'
order by "SEC"
/

Find sessions consuming lot of CPU

col program form a30 heading "Program" 
col CPUMins form 99990 heading "CPU in Mins" 
select rownum as rank, a.* 
from ( 
SELECT v.sid, program, v.value / (100 * 60) CPUMins 
FROM v$statname s , v$sesstat v, v$session sess 
WHERE s.name = 'CPU used by this session' 
and sess.sid = v.sid 
and v.statistic#=s.statistic# 
and v.value>0 
ORDER BY v.value DESC) a 
where rownum < 11;

ADVERTISEMENT

You might like

Leave a Reply

Your email address will not be published. Required fields are marked *