Session details


Session statistics by username


select  nvl(ss.USERNAME,'ORACLE PROCESS') username,
    se.SID,
    sn.NAME stastic,
    VALUE usage
from     v$session ss, 
    v$sesstat se, 
    v$statname sn
where      se.STATISTIC# = sn.STATISTIC#
and      se.SID = ss.SID
and    se.VALUE > 0
and     ss.USERNAME = upper ('&user_name')
order      by se.SID, sn.NAME, se.VALUE desc;

Session CPU usage

select     nvl(ss.USERNAME,'ORACLE PROC') username,
        se.SID,
        VALUE cpu_usage
from    v$session ss,
        v$sesstat se,
        v$statname sn
where   se.STATISTIC# = sn.STATISTIC#
and     NAME like '%CPU used by this session%'
and     se.SID = ss.SID
order   by VALUE desc;


Sessions I/O usage

select    nvl(ses.USERNAME,'ORACLE PROC') username,
    OSUSER os_user,
    PROCESS pid,
    ses.SID sid,
    SERIAL#,
    PHYSICAL_READS,
    BLOCK_GETS,
    CONSISTENT_GETS,
    BLOCK_CHANGES,
    CONSISTENT_CHANGES
from    v$session ses, v$sess_io sio
where     ses.SID = sio.SID
and     ses.username='&user_name' 
and     rownum < 20
order     by PHYSICAL_READS desc, ses.USERNAME;

Comments: Leave Comment

* The email will not be published on the website.