Database Performence Tuning Scripts

Performence Tuning Scripts:

The below are some of the scripts usefull for Performence tuning.

Prompt
Prompt Cache hit ratio
prompt
select 1-(phy.value / (cur.value + con.value)) "Cache Hit Ratio",round((1-(phy.value / (cur.value + con.value)))*100,2) "% Ratio"from v$sysstat cur, v$sysstat con, v$sysstat phywhere cur.name = 'db block gets' andcon.name = 'consistent gets' andphy.name = 'physical reads';
/

Prompt
Prompt Another Buffer Cache hit ratio Calculation
prompt
column "logical_reads" format 99,999,999,999 column "phys_reads" format 999,999,999 column "phy_writes" format 999,999,999 select a.value + b.value "logical_reads", c.value "phys_reads",round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO" from v$sysstat a, v$sysstat b, v$sysstat cwhere a.statistic# = 38 and b.statistic# = 39 and c.statistic# = 40;
/

prompt
prompt Data Dictionary Hit Ratio should be over 90 percent
prompt
select sum(gets) "Data Dict. Gets",sum(getmisses) "Data Dict. Cache Misses",round((1-(sum(getmisses)/sum(gets)))*100) "DATA DICT CACHE HIT RATIO",round(sum(getmisses)*100/sum(gets)) "% MISSED"from v$rowcache;
/

prompt
prompt Library Cache Miss Ratio
prompt
select sum(pins) "executions",sum(reloads) "Cache Misses",round((1-(sum(reloads)/sum(pins)))*100) "LIBRARY CACHE HIT RATIO",round(sum(reloads)*100/sum(pins)) "% Missed" from v$librarycache;
/

prompt
prompt More Library Cache stats
prompt
select namespace,trunc(gethitratio*100) "Hit Ratio",trunc(pinhitratio*100) "Pin Hit Ratio",reloads "Reloads"from v$librarycache;/

prompt
prompt Another Library Cache Calculation, total reloads should be as close to 0 as possible.
prompt
column libcache format 99.99 heading 'Percentage' jus censelect sum(pins) "Total Pins", sum(reloads) "Total Reloads",sum(reloads)/sum(pins) *100 libcachefrom v$librarycache;
/

prompt
prompt Redo Log Buffer should be as close to 0 as possible
prompt
select substr(name,1,30),value from v$sysstat where name ='redo log space requests';
/

prompt
prompt Redo Log Contention, all ratios less than 1
prompt
SET feedback OFFCOLUMN name FORMAT a15COLUMN gets FORMAT 99999999COLUMN misses FORMAT 999999COLUMN immediate_gets FORMAT 99999999 HEADING 'IMM_GETS'COLUMN immediate_misses FORMAT 99999999 HEADING 'IMM_MISSES'
PROMPT Examining Contention for Redo Log Buffer Latches...
PROMPT ----------------------------------------------------
SELECT name, gets, misses, immediate_gets, immediate_misses,Decode(gets,0,0,misses/gets*100) ratio1,Decode(immediate_gets+immediate_misses,0,0,immediate_misses/(immediate_gets+immediate_misses)*100) ratio2FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
/

prompt
prompt Disk Vs. Memory Sorts. Try to keep the disk/memory ratio to less than .10 by increasing the sort_area_size
prompt
SET HEADING OFFSET FEEDBACK OFFCOLUMN name FORMAT a30COLUMN value FORMAT 99999990
SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');
/

prompt
prompt Initialization Parameters
prompt
select substr(name,1,35) "Parameter" ,substr(value,1,35) "Value" from v$parameter order by name asc;
/