-- ############################################################################################# -- -- %Purpose: Show Sessions with bad Buffer Cache Hit Ratio in % -- -- ############################################################################################# -- set feed off; set pagesize 10000; set wrap off; set linesize 200; set heading on; set tab on; set scan on; set verify off; -- ttitle left 'Show Sessions with bad Buffer Cache Hit Ratio in %' - skip 2 -- select substr(a.username,1,12) "User", a.sid "sid", b.consistent_gets "ConsGets", b.block_gets "BlockGets", b.physical_reads "PhysReads", 100 * round((b.consistent_gets + b.block_gets - b.physical_reads) / (b.consistent_gets + b.block_gets),3) HitRatio from v$session a, v$sess_io b where a.sid = b.sid and (b.consistent_gets + b.block_gets) > 0 and a.username is not null order by HitRatio asc;