Oracle Performance Tuning Cheat Sheet

buffer cache hit ratio

SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) "Cache Hit Ratio" FROM v$sysstat cur, v$sysstat con, v$sysstat phy WHERE cur.name = 'db block gets' AND con.name = 'consistent gets' AND phy.name = 'physical reads';

====================================================================

fragmentation in tables


select table_name,round((blocks*8),2) "size (kb)" ,
                            round((num_rows*avg_row_len/1024),2) "actual_data (kb)",
                            (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) "wasted_space (kb)"
from dba_tables
where (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2)) and table_name='FUNDSTRANSFER_LOG'
order by 4 desc

===========================================================================

data dictionary cache hit ratio


SELECT (1 - (SUM (getmisses) / SUM (gets))) * 100 "Hit Ratio"
 FROM v$rowcache;



=================================================================================

library cache hit ratio

SELECT SUM (pins) / (SUM (pins) - SUM (reloads)) * 100 "Hit Ratio"
 FROM v$librarycache;


===========================================================================================

To find out physical reads on a query 



SELECT disk_reads, sql_text
 FROM v$sqlarea
 WHERE disk_reads > 1000
ORDER BY disk_reads DESC;

select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session
where status='ACTIVE' and type<>'BACKGROUND' and last_call_et>30;