Join with v$session
v$sesstat is typically joined to
v$session (hence the name
v$sesstat) to query a session's statistical values:
select
sess.sid,
sess.serial#,
sess.username,
sess.osuser,
-- sess.osuser,
-- sess.program,
-- sest.sid,
stat.name,
sest.value,
sess.module,
sess.status
from
v$session sess join
v$sesstat sest on sess.sid = sest.sid join
v$statname stat on sest.statistic# = stat.statistic#
where
sess.type != 'BACKGROUND' and
-- sess.osuser = 'rene' and
-- sess.program = 'EXCEL.EXE' and
sest.value > 0 and
sess.sid = 1371 and
stat.name in (
'consistent gets' , -- Number of consistent block reads
'db block gets' , -- Number of current block reads
'session logical reads', -- "db block gets" + "consistent gets"
'consistent changes' , -- Number of times that rollback was applied to make a consistent read
'recursive calls' ,
'physical reads' ,
'redo size' ,
'sorts (memory)' ,
'sorts (disk)' ,
'sorts (rows)' ,
'rows processed'
)
order by
lower(name);
process last non-idle time, session connect time
The units of the values of process last non-idle time and session connect time are seconds since 1970-01-01 (UTC):
select
stat.name,
from_tz( cast( date '1970-01-01' + sest.value / 24 / 60 /60 as timestamp ), 'utc' ) at time zone sessiontimezone ts
from
v$sesstat sest join
v$statname stat on sest.statistic# = stat.statistic#
where
stat.name in ('process last non-idle time', 'session connect time') and
sest.sid = 78
;