REM
REM DBAToolZ NOTE:
REM This script was obtained from DBAToolZ.com
REM It's configured to work with SQL Directory (SQLDIR).
REM SQLDIR is a utility that allows easy organization and
REM execution of SQL*Plus scripts using user-friendly menu.
REM Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM
REM File:
REM s_all_ratios.sql
REM
REM SGA MAINT
REM
REM Author:
REM ??
REM ??
REM
REM Purpose:
REM
REM THIS Script reports various performance ratios
REM
REM
REM Usage:
REM s_all_ratios.sql
REM
REM Example:
REM s_all_ratios.sql
REM
REM
REM History:
REM ??-??-???? ???????? Created
REM 08-01-1999 VMOGILEV Added to DBATOOLZ library
REM 02-19-2002 Cory Brooks
REM (cory.brooks@duke.edu) fixed problem with zero divide
REM
REM
spool s_all_ratios.log
prompt 'Library Cache (Shared SQLAREA) tune shared_pool_size'
prompt 'Ratio should be below 1% (1.00)'
select sum(pins), sum(reloads),
sum(reloads)/sum(pins)*100
from v$librarycache
/
prompt 'Data Dictionary Cache - tune shared_pool_size'
prompt 'Ratio should be less than 10 to 15% (10.00)'
select sum(gets), sum(getmisses),
sum(getmisses)/sum(gets)*100
from v$rowcache
/
prompt 'Buffer Cache Hit Ratio - tune db_block_buffers'
prompt 'Ratio should be greater than 60% to 70% '
select
sum(decode(name, 'consistent gets',value, 0)) "Consis Gets",
sum(decode(name, 'db block gets',value, 0)) "DB Blk Gets",
sum(decode(name, 'physical reads',value, 0)) "Phys Reads",
(sum(decode(name, 'consistent gets',value, 0)) +
sum(decode(name, 'db block gets',value, 0)) -
sum(decode(name, 'physical reads',value, 0)))
/
(sum(decode(name, 'consistent gets',value, 0)) +
sum(decode(name, 'db block gets',value, 0)) ) * 100 "Hit Ratio"
from v$sysstat
where name in ('db block gets',
'consistent gets',
'physical reads'
)
/
prompt 'Redo log space requests - tune log_buffer
prompt 'value should be near 0'
select name, value
from v$sysstat
where name = 'redo log space requests'
/
column latch_name format a20
prompt REDO LATCHES
select name latch_name, gets, misses, immediate_gets "Immed Gets", immediate_misses "Immed Misses"
from v$latch
where name like 'redo%'
/
prompt Redo allocation latch.
prompt For contention of redo allocation latch, decrease the value
prompt of log_small_entry_max_size in init.ora.
prompt
prompt All ratios should be <= 1%
rem
select (misses/decode(gets,0,1,gets))*100 Ratio1,
(immediate_misses/decode(immediate_misses+
immediate_gets,0,1,
immediate_misses+immediate_gets)*100) Ratio2
from v$latch l
where l.name = 'redo allocation'
/
prompt Redo copy latch
prompt For contention of redo latch, increase the value
prompt of log_simultaneous_copies in init.ora.
prompt
prompt all ratios should be <= 1%
rem
select (misses/decode(gets,0,1,gets))*100 Ratio1,
(immediate_misses/decode(immediate_misses+
immediate_gets,0,1,
immediate_misses+immediate_gets)*100) Ratio2
from v$latch l
where l.name = 'redo copy'
/
prompt 'Rollback segment contention - create more rollbacks'
prompt 'Needed if ratio greater than 1%'
select w.class, (sum(w.count)/sum(s.value)) * 100
from v$waitstat w , v$sysstat s
where w.class in ('system undo header','system undo block',
'undo header', 'undo block')
and s.name in ('db block gets','consistent gets')
group by w.class
/
column rbs_name format a10
prompt 'Rollback shrinkage contention - '
prompt 'If shrinks high then add or increase size'
prompt 'If waits add 1 rollback for each segment that had waits'
prompt 'Waits should be as close to 0 as possible'
prompt 'Gets to waits ratio should be > 99%'
select name rbs_name, extents, gets, waits, shrinks, extends, hwmsize
from v$rollstat s, v$rollname r
where s.usn = r.usn
/
prompt 'SORT_AREA_SIZE'
select name, value
from v$sysstat
where name like 'sorts%'
/
prompt 'The Average Length of the Write Request Queue'
prompt 'Anything above 100 indicates a problem'
prompt ' '
column "Write Request Length" format 999,999.99
select sum( decode (name, 'summed dirty queue length', value))
/
sum( decode (name, 'write requests', value)) "Write Request Length"
from v$sysstat
where name in ( 'summed dirty queue length'
,'write requests')
and value > 0
/
prompt The following list of users has a hit ratio less than 80%.
prompt Investigate what they are doing to determine if it could be done more efficiently.
col user_session format a25
select se.username||'('|| se.sid||')' User_Session,
sum(decode(name, 'consistent gets',value, 0)) "Consis Gets",
sum(decode(name, 'db block gets',value, 0)) "DB Blk Gets",
sum(decode(name, 'physical reads',value, 0)) "Phys Reads",
(sum(decode(name, 'consistent gets',value, 0)) +
sum(decode(name, 'db block gets',value, 0)) -
sum(decode(name, 'physical reads',value, 0)))
/
decode( (sum(decode(name, 'consistent gets',value, 0)) +
sum(decode(name, 'db block gets',value, 0))) ,0,1,
(sum(decode(name, 'consistent gets',value, 0)) +
sum(decode(name, 'db block gets',value, 0)) ) ) * 100 "Hit Ratio"
from v$sesstat ss, v$statname sn, v$session se
where ss.sid = se.sid
and sn.statistic# = ss.statistic#
and value != 0
and sn.name in ('db block gets', 'consistent gets', 'physical reads')
group by se.username, se.sid
having
(sum(decode(name, 'consistent gets',value, 0)) +
sum(decode(name, 'db block gets',value, 0)) -
sum(decode(name, 'physical reads',value, 0)))
/
decode( (sum(decode(name, 'consistent gets',value, 0)) +
sum(decode(name, 'db block gets',value, 0))) ,0,1,
(sum(decode(name, 'consistent gets',value, 0)) +
sum(decode(name, 'db block gets',value, 0)) ) ) * 100
< 80
/
SELECT *
FROM v$sgastat
WHERE name IN ('free memory', 'db_block_buffers', 'log_buffer',
'dictionary cache', 'sql area', 'library cache')
/
prompt Latches Held
SELECT n.name "Latch Held", p.username "User Holding Latch"
FROM v$process p,v$latchholder l, v$latch n
WHERE l.pid = p.pid
and l.laddr = n.addr
/
prompt 'session waits'
select event, count(*)
from v$session_wait
group by event
/
spool off