How to find session details in Oracle database



Many times DBA need to look at the session details for analysis. The details includes sql_id, stats ,locking ,wait events.Here I am giving few queries on How to find session details in Oracle database

 

To find session Information

col sid format 9999
col pid format 9999
col serial# format 99999
col process format a8 heading “unixPID”
col spid format a8 heading “unixPID”
col username format a9
col addr format a11
col program format a20 trunc
col logon_time format a18
col osuser format a8 heading unixUsr
col p_user format a9 heading unixUsr
col terminal format a7 heading unixtrm
col command format 99 heading Cd
col machine format a7
col action format a7
col module format a10
set pagesize 24

prompt “Enter the Oracle Session ID (SID) user in question”

select p.PID,
p.SERIAL#,
p.USERNAME p_user,
p.SPID,
to_char(s.logon_time,’DD-MON-YY HH24:MI:SS’) Logon_Time,
s.program,
s.command,
s.sid,
s.serial#,
s.username,
s.process,
s.machine,
s.action,
s.module,
s.osuser,
s.terminal
from v$process p,
v$session s
where s.sid = &sid
and p.addr = s.paddr
order by s.logon_time
/

To find more session information

set verify off pages 50000 linesize 80 heading off feedback off

col sid noprint
col serial# noprint
col rec_num noprint
col line_num noprint
col text format a80 word

select ses.sid, ses.serial#, 10 rec_num, 1 line_num,
rpad(‘=’,80,’=’)
||rpad(‘SID : ‘||ses.sid,40)
||rpad(‘Serial# : ‘||ses.serial#,40)
||rpad(‘Audit SID : ‘||ses.audsid,40)
||rpad(‘Status : ‘||ses.status,40)
||rpad(‘DB User : ‘||ses.username,40)
||rpad(‘OS User : ‘||ses.osuser,40)
||rpad(‘Module : ‘||ses.module,400)
||rpad(‘Action : ‘||ses.action,40)
||rpad(‘OS Server PID: ‘||prc.spid,40)
||rpad(‘OS Client PID: ‘||ses.process,40)
||rpad(‘Executions : ‘||sqa.executions,40)
||rpad(‘Rows Proc : ‘||sqa.rows_processed,40)
||rpad(‘Disk Reads : ‘||sqa.disk_reads,40)
||rpad(‘Buffer Gets : ‘||sqa.buffer_gets,40)
||rpad(‘LIOs/Row : ‘||round(sqa.buffer_gets/decode(sqa.rows_processed,0,1,sqa.rows_processed),2),40)
||rpad(‘LIOs/Exec : ‘||round(sqa.buffer_gets/decode(sqa.executions,0,1,sqa.executions),2),40)
||rpad(‘Event : ‘||wat.event,80)
||rpad(‘Machine : ‘||ses.machine,40)
||rpad(‘Logon Time : ‘||to_char(ses.logon_Time,’MM/DD/YY HH24:MI’),40)
||rpad(‘Statement : ‘,80) text
from v$sqlarea sqa
, v$process prc
, v$session ses
, v$session_wait wat
where sqa.address = ses.sql_address
and sqa.hash_value = ses.sql_hash_value
and prc.addr = ses.paddr
and ses.type != ‘BACKGROUND’
and ses.username is not null
and ses.audsid != userenv(‘SESSIONID’)
and wat.sid(+) = ses.sid
and ses.sid = &1
union
select ses.sid, ses.serial#, 20 rec_num, piece line_num
, sql_text text
from v$sqltext txt
, v$session ses
where txt.address = ses.sql_address
and txt.hash_value = ses.sql_hash_value
and ses.type != ‘BACKGROUND’
and ses.username is not null
and ses.audsid != userenv(‘SESSIONID’)
and ses.sid = &1
order by 1, 2, 3, 4
/
To find the wait history for the particular SID

set lines 120 trimspool on
col event head “Waited for” format a30
col total_waits head “Total|Waits” format 999,999
col tw_ms head “Waited|for (ms)” format 999,999.99
col aw_ms head “Average|Wait (ms)” format 999,999.99
col mw_ms head “Max|Wait (ms)” format 999,999.99
select event, total_waits, time_waited*10 tw_ms,
average_wait*10 aw_ms, max_wait*10 mw_ms
from v$session_event
where sid = &1
/

To find out the wait events for this session

SELECT sample_time, event, wait_time,sql_id
FROM gv$active_session_history
WHERE session_id = &1
AND session_serial# = &2;

To find old recent sample times

SELECT sample_time
from gv$active_session_history
WHERE session_id = 147
AND sample_time > SYSDATE-10/1440
ORDER BY 1;

 
To find the Sql text for the particular sesssion

SELECT ash.sql_id
, ash.sql_child_number
–, s.sql_text
, ash.sql_exec_start
, ash.sql_exec_id
, TO_CHAR(MIN(ash.sample_time),’hh24:mi:ss’) AS min_sample_time
, TO_CHAR(MAX(ash.sample_time),’hh24:mi:ss’) AS max_sample_time
FROM v$active_session_history ash
, v$sql s
WHERE ash.sql_id = s.sql_id (+)
AND ash.sql_child_number = s.child_number (+)
AND ash.session_id = &1
AND ash.session_serial# = &2
GROUP BY
ash.sql_id
, ash.sql_child_number
–, s.sql_text
, ash.sql_exec_start
, ash.sql_exec_id
ORDER BY
MIN(ash.sample_time);

CPU consumption of the session

select s.valuez
from v$sesstat s, v$statname n
where s.sid = &1
and n.statistic# = s.statistic#
and n.name = ‘CPU used by this session’

All the stats for the session
select name, value
from v$sesstat s, v$statname n
where sid = &1
and n.statistic# = s.statistic#
order by value desc
/

 

To find what sid are doing full table scan

column user_process heading “Name |SID” format a20;
column long_scans heading “Long Scans” format 999,999,999;
column short_scans heading “Short Scans” format 999,999,999;
column rows_retreived heading “Rows Retrieved” format 999,999,999;
set linesize 1000
set timing on
select ss.username||'(‘||se.sid||’) ‘ “USER_PROCESS”,
sum(decode(name,’table scans (short tables)’,value)) “SHORT_SCANS”,
sum(decode(name,’table scans (long tables)’, value)) “LONG_SCANS”,
sum(decode(name,’table scan rows gotten’,value)) “ROWS_RETRIEVED”
from v$session ss, v$sesstat se, v$statname sn
where se.statistic# = sn.statistic#
and (name like ‘%table scans (short tables)%’
or name like ‘%table scans (long tables)%’
or name like ‘%table scan rows gotten%’ )
and se.sid = ss.sid
and ss.username is not null
group by ss.username||'(‘||se.sid||’) ‘
order by LONG_SCANS desc
/
To Find  Historical Plans from the AWR Using SQLID

SET PAUSE ON
SET PAUSE ‘Press Return to Continue’
SET PAGESIZE 60
SET LINESIZE 300

SELECT * FROM TABLE(dbms_xplan.display_awr(‘&SQL_ID’))
/
 Show the Bind Variable for a Given SQLID.

SET PAUSE ON
SET PAUSE ‘Press Return to Continue’
SET PAGESIZE 60
SET LINESIZE 300

COLUMN sql_text FORMAT A120
COLUMN sql_id FORMAT A13
COLUMN bind_name FORMAT A10
COLUMN bind_value FORMAT A26

SELECT
sql_id,
t.sql_text sql_text,
b.name bind_name,
b.value_string bind_value
FROM
v$sql t
JOIN
v$sql_bind_capture b using (sql_id)
WHERE
b.value_string is not null
AND
sql_id=’&sqlid’
/

Checking whether stats is current

set lin 1000
set verify off
col owner format a15
col object_name format a25
col object_type format a12
col “LAST ANALYZED” format a13

select do.OWNER,do.OBJECT_NAME,OBJECT_TYPE,
decode (OBJECT_TYPE,’TABLE’ , (Select LAST_ANALYZED from dba_tables where owner=do.owner and TABLE_NAME=do.object_name) ,
‘INDEX’ , (Select LAST_ANALYZED from dba_indexes where owner=do.owner and INDEX_NAME=do.object_name) ,
‘UNKNOWN’) “LAST ANALYZED”,STATUS
from DBA_OBJECTS do
where OBJECT_TYPE in (‘TABLE’,’INDEX’)
and (OWNER,OBJECT_NAME) in (select OBJECT_OWNER,OBJECT_NAME from V$SQL_PLAN where HASH_VALUE=&1)
/


Leave a Reply