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;