-- sessions in non-RAC
set linesize 132
Prompt "Oracle Sessions"
column osuser format A08 Head "OS User"
column user format A08 Head "User"
column module format A15 Head "Module"
column action format A25 Head "Action"
column logon_time format A15 Head "Logon Time"
select sid,
s.serial#,
spid,
substr(osuser,1,12) osuser,
substr(s.username,1,15) "user",
status,
decode(p.background,'1','Y','N') background,
decode(s.lockwait,null,'N','Y') lockwait,
decode(p.latchwait,null,'N','Y') latchwait,
substr(to_char(logon_time,'dd-mon-yy hh24:mi'),1,18) logon_time,
substr(module,1,45) module,
action
from v$session s,
v$process p
where s.username not in (' ','SYS','PATROL')
and p.addr=s.paddr
order by status desc, logon_time
/
set lines 80
-- sessions in RAC
set linesize 132
Prompt "RAC Oracle Sessions"
column inst_id format 9 Head "Inst|Id."
column osuser format A08 Head "OS User"
column user format A08 Head "User"
column module format A15 Head "Module"
column action format A25 Head "Action"
column logon_time format A15 Head "Logon Time"
select inst_id, sid, serial#,
substr(osuser,1,12) osuser, substr(username,1,15) "user",
status,
substr(to_char(logon_time,'dd-mon-yy hh24:mi'),1,18) logon_time,
substr(module,1,45) module, action
from gv$session
where username not in (' ','SYS','PATROL')
order by status desc;
-- 'Session Reponse in terms of |Waiting|IO Operation|CPU Operation'
ttitle 'Session Reponse in terms of |Waiting|IO Operation|CPU Operation'
Column event format a30
Column sid format 9999
Column session_cpu heading "CPU|used"
Column physical_reads heading "physical|reads"
Column consistent_gets heading "logical|reads"
Column seconds_in_wait heading "seconds|waiting"
select a.sid,
a.value session_cpu,
c.physical_reads,
c.consistent_gets,
d.event,
d.seconds_in_wait
from v$sesstat a,
v$statname b,
v$sess_io c,
v$session_wait d
where a.sid like '&Sid'
and b.name = 'CPU used by this session'
and a.statistic# = b.statistic#
and a.sid=c.sid
and a.sid=d.sid;
-- WHAT IS SESSION doing, we have pid
SELECT /*+ ordered */ p.spid, s.sid, s.serial#, s.username,
TO_CHAR(s.logon_time, 'mm-dd-yyyy hh24:mi') logon_time,
s.last_call_et, st.value, s.sql_hash_value, s.sql_address, sq.sql_text
FROM v$statname sn, v$sesstat st, v$process p, v$session s, v$sql sq
WHERE s.paddr=p.addr
AND s.sql_hash_value = sq.hash_value and s.sql_Address = sq.address
AND s.sid = st.sid
AND st.STATISTIC# = sn.statistic#
AND sn.NAME = 'CPU used by this session'
AND p.spid = &osPID -- parameter to restrict for a specific PID
AND s.status = 'ACTIVE'
ORDER BY st.value desc;
-- v$session_wait, current waits on the system
SELECT event,
sum(decode(wait_time,0,1,0)) "Curr",
sum(decode(wait_time,0,0,1)) "Prev",
count(*)"Total"
FROM v$session_wait
GROUP BY event
ORDER BY count(*);
-- v$session_event, cummulative session waits
SELECT event, total_waits waits, total_timeouts timeouts,
time_waited total_time, average_wait avg
FROM v$session_event
WHERE sid = &sid
ORDER BY time_waited DESC;
-- V$SESSTAT, session-level summary of resource usage since session startup
SELECT ses.sid
, DECODE(ses.action,NULL,'online','batch') "User"
, MAX(DECODE(sta.statistic#,9,sta.value,0))
/greatest(3600*24*(sysdate-ses.logon_time),1) "Log IO/s"
, MAX(DECODE(sta.statistic#,40,sta.value,0))
/greatest(3600*24*(sysdate-ses.logon_time),1) "Phy IO/s"
, 60*24*(sysdate-ses.logon_time) "Minutes"
FROM V$SESSION ses
, V$SESSTAT sta
WHERE ses.status = 'ACTIVE'
AND sta.sid = ses.sid
AND sta.statistic# IN (9,40)
GROUP BY ses.sid, ses.action, ses.logon_time
ORDER BY
SUM( DECODE(sta.statistic#,40,100*sta.value,sta.value) )
/ greatest(3600*24*(sysdate-ses.logon_time),1) DESC;
-- V$SYSTEM_EVENT , Finding the Total Waits on the System
SELECT event, total_waits waits, total_timeouts timeouts,
time_waited total_time, average_wait avg
FROM V$SYSTEM_EVENT
ORDER BY 4 DESC;
