Alex Lima

How to check/find session information (wait, io, lock, time)

Find SQL currently executing in a session:
http://oraclespin.com/2010/05/09/how-to-find-sql-running-for-a-usersid/

Session running waiting on
SYS@TESTDB> SELECT event, state, p1, p2, p3, seconds_in_wait FROM v$session_wait where sid = 247;

EVENT STATE
P1 P2 P3 SECONDS_IN_WAIT
—————————————————————- —————
—- ———- ———- ———- ——————-
db file scattered read WAITED KNOWN TIME
122 1188369 128 30

1 row selected.

SYS@TESTDB> SELECT * FROM v$event_name WHERE name = ‘db file scattered read’;

EVENT# EVENT_ID NAME
PARAMETER1 PARAMETE
R2 PARAMETER3
WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
———- ———- ———————————————————-
—— —————————————————————- ——–
——————————————————– ———————–
—————————————– ————- ———– ————
—————————————————-
117 506183215 db file scattered read
file# block#
blocks
1740759767 8 User I/O

So Parameter #1: file#=122, Parameter #2: block#=1188369, Parameter #3: blocks=128

IO information consistents gets – reads from db buffers, physical reads – Physical reads, block changes – block changes
SQL> select * from V$sess_io where sid = 247;

SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES
———- ———- ————— ————– ————- ——————
247 6 1355 25 4 0

Detail user session information:

SQL> select vsn.name, vst.value
from v$sesstat vst, v$statname vsn
where vsn.statistic# = vst.statistic#
and vst.value 0
and vst.sid = 247
order by vst.value;

NAME VALUE
—————————————————————- ————-
logons cumulative 1
sorts (memory) 1
logons current 1
enqueue releases 1
enqueue requests 1
…

Find current locks by session:
— ID1 – is the object id the locks is on
— CTIME – Time elapsed since the object was locked in seconds
SQL> SELECT * FROM V$lock where SID = 247;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
—————- —————- ———- — ———- ———- ———- ———- ———- ———-
000000006934A5B0 000000006934A5D8 247 TM 13828 0 3 0 72989 0
0000000069376700 0000000069376888 24 TX 65539 2262 6 0 72989 0

List session time for various operations
Using V$SESS_TIME_MODEL it displays the session time for various operations. The time reported is the total elapsed or CPU time (in microseconds).
Example:
SQL> select stat_name, value
from V$SESS_TIME_MODEL
where sid = 247
order by value desc;

STAT_NAME VALUE
—————————————————————- ———-
DB time 111174
DB CPU 107996
parse time elapsed 90664
hard parse elapsed time 64358
connection management call elapsed time 53179
sql execute elapsed time 32974
failed parse elapsed time 963
repeated bind elapsed time 148
background elapsed time 0
RMAN cpu time (backup/restore) 0
…