Wednesday, August 17, 2011

Checking Session Statistics Part -I


Here i have stared a series of posts that will spot some light on how to check the user session in accordance with the consumption of resources: -


Session I/O By User:


Username - Name of the Oracle process user
OS User - Name of the operating system user
PID - Process ID of the session
SID - Session ID of the session
Serial# - Serial# of the session
Physical Reads - Physical reads for the session
Block Gets - Block gets for the session
Consistent Gets - Consistent gets for the session
Block Changes - Block changes for the session
Consistent Changes - Consistent changes for the session


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
order  by PHYSICAL_READS, ses.USERNAME


Output:-