------------------------------------------------------------------------------------------------------------------
---***--- This script gives you statiscs by session
---***---
---***---
---***--- NOTE: YOU MUST RUN THIS SCRIPT AS SYSTEM OR SYS
------------------------------------------------------------------------------------------------------------------
---***---
---***--- Realised by Ghazi Ben Youssef, MBA
---***--- Oracle Dba 
---***--- email : ghazi.ben.youssef@ssss.gouv.qc.ca
---***---
------------------------------------------------------------------------------------------------------------------

set linesize 150;

COLUMN name FORMAT a30
COLUMN username FORMAT a30
COLUMN osuser FORMAT a30
COLUMN value FORMAT 99,999,999,999,999 heading "value"


SELECT se.SID, ses.username, ses.osuser, n.NAME, se.VALUE
FROM v$statname n,  v$sesstat se,   v$session ses
WHERE n.statistic# = se.statistic#
AND se.sid = ses.sid 
AND NAME IN ('CPU used by this session','db block gets','consistent gets','physical reads','free buffer requested',
'table scans (long tables)','table scan rows gotten','sorts (memory)','sorts (disk)','sorts (rows)', 
'session uga memory max' ,'session pga memory max')
order by sid, n.statistic#;