--Active Session Info
SELECT b.sid, b.serial#, a.spid, b.program, a.username, b.osuser,
b.machine, b.STATUS,b.logon_time, c.sql_text
FROM v$process a, v$session b, v$sqltext c
WHERE a.addr=b.paddr
AND c.hash_value = b.sql_hash_value
AND b.STATUS = 'ACTIVE'
ORDER BY a.spid, c.piece

--Active Table Locking
SELECT b.sid, b.serial#, b.program, b.osuser, b.machine, b.TYPE, b.action,
c.sql_text,b.logon_time, e.owner, e.object_name "Table Lock"
FROM
v$session b,
v$sqltext c,
v$locked_object d,
dba_objects e
WHERE b.sql_address = c.address
AND b.sid = d.session_id
AND d.object_id = e.object_id
AND b.STATUS = 'ACTIVE'
ORDER BY b.sid, c.piece

--Gather Statistics Summary
SELECT owner,table_name,last_analyzed,sample_size,num_rows FROM all_tables
WHERE owner = 'INV'
ORDER BY last_analyzed DESC

--Cache Hit Ratio
SELECT ROUND(((1-(SUM(DECODE(name,
'physical reads', value,0))/
(SUM(DECODE(name, 'db block gets', value,0))+
(SUM(DECODE(name, 'consistent gets', value, 0))))))*100),2)
|| '%' "Buffer Cache Hit Ratio"
FROM v$sysstat;

--Hit Ratio By User
SELECT Username,
OSUSER,
Consistent_Gets,
Block_Gets,
Physical_Reads,
100*( Consistent_Gets + Block_Gets - Physical_Reads)/
( Consistent_Gets + Block_Gets ) "Hit Ratio %",
V$SESSION.SID, serial#, status
FROM V$SESSION ,V$SESS_IO
WHERE V$SESSION.SID = V$SESS_IO.SID
AND ( Consistent_Gets + Block_Gets )>0
AND username IS NOT NULL
ORDER BY Username,"Hit Ratio %";