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_7.sql
REM
REM TRACE
REM
REM Author:
REM GWANG
REM
REM Purpose:
REM
REM Oracle V7 performance tuning script
REM REPORTS MANY RATIOS and other INFO as well.
REM
REM
REM Usage:
REM s_all_ratios_7.sql
REM
REM Example:
REM s_all_ratios_7.sql
REM
REM
REM History:
REM 06-15-1994 GWANG Created
REM 08-01-2001 VMOGILEV Added to DBATOOLZ library
REM
REM
rem
rem Oracle V7 performance tuning script
rem
rem This scrip is designed for gathering database performance
rem data at a point-in-time. It helps DBA to determine whether
rem certain database tuning is necessary.
rem
rem This script must be run with dba privilege. Also, run tuning.vw
rem before run this script.
rem
rem 15-jun-94 gwang Creation
rem 28-jun-94 gwang Added comments on each turning item.
rem
rem
set pagesize 100
prompt ****************************************************
prompt SGA TUNING
prompt The sga is comprised of data block buffers, redo log
prompt buffers, library cache, data dictionary. All of these
prompt members must be proporly tuned.
prompt ****************************************************
prompt DATA BLOCK BUFFERS
prompt Tuning data block buffer is to increase the value of
prompt data_block_buffers in init.ora. Following oracle7
prompt dba guide to select a optimum value since too large
prompt a value is a waste of memory.
prompt
prompt Hit ratio should be >= 90%
rem
select (1-a.value/(b.value+c.value))*100 hitratio
from v$sysstat a, v$sysstat b, v$sysstat c
where a.name = 'physical reads'
and b.name = 'db block gets'
and c.name = 'consistent gets'
/
prompt Miss ratio should be <= 0.5%
rem
select (a.count/(b.value+c.value))*100 missratio
from v$waitstat a, v$sysstat b, v$sysstat c
where a.class = 'data block'
and b.name = 'db block gets'
and c.name = 'consistent gets'
/
prompt REDO LOG BUFFERS
prompt Tuning redo log buffer is to increase the log_buffer parameter
prompt in init.ora.
prompt
prompt Value of redo log space requests should be 0(or near 0 as possible)
rem
select value
from v$sysstat
where name = 'redo log space requests'
/
prompt LIBRARY CACHE
prompt All parsed shared sql and pl/sql statements are stored in this
prompt cache. The size of the buffer should be set big enough so that
prompt freguently used statements won't need to be reparsed. Tuning
prompt of this buffer id to increate shared_pool_size parameter in
prompt init.ora. Review open_cursor parameter to allow the user
prompt processed to take advantage of the increase in memory.
prompt
prompt Miss ratio <= 1% and reloads = 0(or near 0 as possible)
rem
select (sum(reloads)/sum(pins))*100 missratio, sum(reloads) reloads
from v$librarycache
/
prompt DATA DICTIONARY CACHE
prompt Date dictionary cache is part of shared pool size. Tuning is
prompt done by adjusting shared_pool_size parameter in in init.ora.
prompt You may want try increasing it by the difference of getmisses
prompt to count or usage to count whichever is greater.
prompt
prompt count >= getmisses and count >= usage and getratio >= 90%
rem
select sum(count) "COUNT", sum(gets) gets, sum(getmisses) getmisses,
sum(usage) usage, (1-(sum(getmisses)/sum(gets)))*100 getratio
from v$rowcache
/
prompt ****************************************************
prompt I/O TUNING
prompt In general, i/o tuning has 3 main objectives, to
prompt distribute load across all disk as much as possible,
prompt to avoid fragmenttation, chaining and migration of
prompt data across various extents, to reduce uncessary i/o.
prompt ****************************************************
prompt DISTRIBUTING I/O
prompt Identify heavily loaded tablespaces and datafiles,
prompt reorganize the file system.
prompt
select ts.name,substr(i.name,1,40) datafile,x.phyrds, x.phywrts, x.readtim,
x.writetim, x.phyblkrd, x.phyblkwrt
from v$filestat x, sys.ts$ ts, v$dbfile i, sys.file$ f
where i.file# = f.file#
and ts.ts# = f.ts#
and x.file# = f.file#
order by ts.name
/
prompt SORT AREA
prompt Tuning in sort area is to increase the sort_area_size parameter
prompt in init.ora. You may also consider setting the value of
prompt sort_area_retainted_size. This parameter is the amount of memory
prompt that oracle will restore to user processes if it belives the
prompt sort area data will not be referenced in the near future.
prompt This will allow you to save on memory.
prompt
prompt disk ratio <= 10% and disk sort = 0 (or as near to 0 as possible)
rem
select a.value memory_sort,
b.value disk_sort,
((b.value/(b.value+a.value))*100) disk_ratio
from v$sysstat a, v$sysstat b
where a.name = 'sorts (memory)'
and b.name = 'sorts (disk)'
/
prompt CHAINED BLOCKS (do item step manually)
prompt Using ANALYZE to gather data for chained rows on the tables
prompt to be tuned, and keep the data in the table created by utlchain.sql.
prompt For the rows selected from the ANALYZE, copy them into a temp table
prompt and then copy back. At this point, the rows should not be chained
prompt anymore(repeat ANALYZE to verify). Modify pctfree and pctused if
prompt necessary to ensure that there will be no more chained rows.
prompt
prompt FRAGMENTTATION, FREE SPACE AND DEAD FRAGMENTS
prompt In tablespace, if average free space is much lower than the total
prompt free space, meaning there are more smaller chucks than big chucks,
prompt then it is time for defragmenting.
prompt The only solution to defragment a tablespace is to do export and
prompt import. But you should also consider reassigning the various
prompt storage parameter.
rem
select e.tsp TABLESPACE,
e.blocks, sum(f.length) Free_space, avg(f.length) Avg_free,
count(f.length) Fregments
from sys.fet$ f, t_extsize e
where e.ts# = f.ts#
group by e.tsp,e.blocks
having count(f.length) > 1
/
prompt If the percentage of dead spaces to total free space is high,
prompt then it is time for defragmenting. The number of dead space should
prompt also be compared with the number of fragments or free space in
prompt above data. If the raito of the number of dead space to that of the
prompt fragments is high, meaning, most of the small chucks are dead space,
prompt then consider defragmenting the table space.
rem
select e.tsp TABLESPACE, e.blocks, sum(f.length) Free_space,
sum(decode(sign(f.length-e.smallest),-1,f.length,0)) Dead_space,
count(decode(sign(f.length-e.smallest)-1,1,0)) Count,
(sum(f.length)-sum(decode(sign(f.length-e.smallest),-1,f.length,0)))
Available_space
from sys.fet$ f, t_extsize e
where e.ts# = f.ts#
group by e.tsp, e.blocks
having sum(decode(sign(f.length-e.smallest),-1,1,0)) >0
/
prompt NEAR MAX EXTENTS
prompt It is desirable to have smaller number of extents for a segment.
prompt If additional extents are needed, this causes dynamic extenstion.
prompt The tuning is to recreate the segment with better storage
prompt parameter.
prompt
prompt Segments with number of extents approaching the max extents
prompt They need attention.
rem
select substr(segment_name,1,30) segment_name,
segment_type,tablespace_name,substr(owner,1,10) owner,extents,
max_extents,blocks,bytes
from sys.dba_segments
where extents > (max_extents -3)
/
prompt 'Database Objects that will have Trouble Throwing Extents'
column owner format a10;
column segment_name format a22;
column segment_type format a10;
column tablespace_name format a14;
column next_extent format 999,999,999;
SELECT seg.owner, seg.segment_name,
seg.segment_type, seg.tablespace_name,
t.next_extent
FROM sys.dba_segments seg,
sys.dba_tables t
WHERE (seg.segment_type = 'TABLE'
AND seg.segment_name = t.table_name
AND seg.owner = t.owner
AND NOT EXISTS
(select tablespace_name
from dba_free_space free
where free.tablespace_name = t.tablespace_name
and bytes >= t.next_extent ))
UNION
SELECT seg.owner, seg.segment_name,
seg.segment_type, seg.tablespace_name,
DECODE (seg.segment_type,
'CLUSTER', c.next_extent)
FROM sys.dba_segments seg,
sys.dba_clusters c
WHERE (seg.segment_type = 'CLUSTER'
AND seg.segment_name = c.cluster_name
AND seg.owner = c.owner
AND NOT EXISTS
(select tablespace_name
from dba_free_space free
where free.tablespace_name = c.tablespace_name
and bytes >= c.next_extent ))
UNION
SELECT seg.owner, seg.segment_name,
seg.segment_type, seg.tablespace_name,
DECODE (seg.segment_type,
'INDEX', i.next_extent )
FROM sys.dba_segments seg,
sys.dba_indexes i
WHERE (seg.segment_type = 'INDEX'
AND seg.segment_name = i.index_name
AND seg.owner = i.owner
AND NOT EXISTS
(select tablespace_name
from dba_free_space free
where free.tablespace_name = i.tablespace_name
and bytes >= i.next_extent ))
UNION
SELECT seg.owner, seg.segment_name,
seg.segment_type, seg.tablespace_name,
DECODE (seg.segment_type,
'ROLLBACK', r.next_extent)
FROM sys.dba_segments seg,
sys.dba_rollback_segs r
where (seg.segment_type = 'ROLLBACK'
AND seg.segment_name = r.segment_name
AND seg.owner = r.owner
AND NOT EXISTS
(select tablespace_name
from dba_free_space free
where free.tablespace_name = r.tablespace_name
and bytes >= r.next_extent ))
/
prompt ****************************************************
prompt RESOURCE TUNING
prompt ****************************************************
prompt ROLLBACK SEGMENTS
prompt Tuning is to add more rollback segments.
prompt
prompt Contention percentage out of the total data block request,
prompt should be wait_ratio <= 0.5%.
rem
select ((w1.count+w2.count+w3.count+w4.count)/
(st1.value+st2.value))*100 Wait_ratio
from v$waitstat w1, v$waitstat w2, v$waitstat w3, v$waitstat w4,
v$sysstat st1, v$sysstat st2
where w1.class = 'system undo header'
and w2.class = 'system undo block'
and w3.class = 'undo header'
and w4.class = 'undo block'
and st1.name = 'db block gets'
and st2.name = 'consistent gets'
/
prompt contention percentage out of the total request for a rollback
prompt segment, should be wait_ratio <= 5%
rem
select (sum(waits)/sum(gets)*100) Wait_raito
from v$rollstat
/
prompt
prompt percentage of waiting for a free block against total data request,
prompt should be wait_ratio <= 1%.
rem
select (w.count/(st1.value-st2.value))*100 Wait_ratio
from v$waitstat w, v$sysstat st1, v$sysstat st2
where w.class = 'free list'
and st1.name = 'db block gets'
and st2.name = 'consistent gets'
/
prompt ENQUEUE RESOURCE
prompt This is the number of resources that can be locked by the lock
prompt manager. Tuning is to increase the enqueue_resources in init.ora
prompt by ((processes-10)*2) + 55, but not more than 20 plus the values
prompt of ddl_locks and dml_locks in init.ora.
prompt
prompt Time out percentage and number of waits for the availability of an
prompt enqueue resource.
prompt Timeout_ratio <= 10% and Wait_count = 0
rem
select (st1.value/st2.value)*100 timeout_ratio,
st3.value Wait_count
from v$sysstat st1, v$sysstat st2, v$sysstat st3
where st1.name = 'enqueue timeouts'
and st2.name = 'enqueue requests'
and st3.name = 'enqueue waits'
/
prompt REDO LATCHES
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'
/