32 lines
775 B
SQL
32 lines
775 B
SQL
SET lines 1000
|
|
SET pagesize 32767
|
|
TTITLE "###### Hit Ratio for Each User ###### "
|
|
|
|
COLUMN sid FORMAT 9999
|
|
COLUMN username FORMAT a10
|
|
COLUMN osuser FORMAT a10
|
|
COLUMN logon_time FORMAT a14
|
|
COLUMN sql_text FORMAT a50
|
|
COLUMN "%Hit" FORMAT 999.99
|
|
|
|
select s.sid
|
|
, s.username
|
|
, s.osuser
|
|
, to_char(s.logon_time,'DD-MM-YY HH24:MI') logon_time
|
|
, round(100* (1 - (io.physical_reads/ (io.block_gets + io.consistent_gets))),2) "%Hit"
|
|
, io.physical_reads
|
|
, st.sql_text
|
|
from v$session s, v$sess_io io, v$sqltext_with_newlines st
|
|
where s.sql_address = st.address
|
|
and s.sql_hash_value = st.hash_value
|
|
and s.sid = io.sid
|
|
and io.block_gets + io.consistent_gets > 0
|
|
and s.username is not null
|
|
order by 5 desc
|
|
/
|
|
|
|
SET lines 80
|
|
SET pagesize 80
|
|
TTITLE OFF
|
|
|