-------------------------------------------------------------------
-- Description: quick & dirty script to read some useful performance
-- and efficiency indicators for an Oracle instance and database from
-- the system data dictionary (v$sysstat, v$sgastat, v$waitstat,
-- v$rollstat, v$rowcache, v$librarycache, v$latch, v$latchname)
-- handy in performance tests and benchmarking
--
-- Author: David Clement
-- Modifications: 03-AUG-2004 Corrected two errors in comments
-- (changed 'low' to 'high').
-----------------------------------------------------------------------
set echo off
set feedback off
set head off
set termout off
set trimspool on
set verify off
column dtcol new_value tody noprint
select to_char (sysdate, 'YYMMDDHH24MI') dtcol from dual;
spool sga&tody
-----------------------------------------------------------------
-- Buffer Cache Hit Ratio
-- Measures what percentage of requests for data is satisfied by data
-- in the buffer cache. Higher percentages imply faster responses.
------------------------------------------------------------------
select 'Buffer Cache Hit Ratio = '|| round ((1 -
(pr.value / (bg.value + cg.value))) * 100, 2)
from v$sysstat pr, v$sysstat bg, v$sysstat cg
where pr.name = 'physical reads'
and bg.name = 'db block gets'
and cg.name = 'consistent gets'
/
-----------------------------------------------------------------
-- Dictionary Cache Hit Ratio
-- Measures what percentage of requests for reference information
-- about the database is satisfied by data in the dictionary cache.
-- Higher percentages imply faster responses.
------------------------------------------------------------------
select 'Dictionary Cache Hit Ratio = '
|| round (sum (gets - getmisses) * 100 / sum (gets), 2)
from v$rowcache
/
--------------------------------------------------------------------
-- Sorts in Memory
-- Measures what percentage of data sorts occur within memory rather
-- than in the sort segments on disk. Should be 100.
--------------------------------------------------------------------
select 'Sorts in Memory = '
|| round ((mem.value / (mem.value + dsk.value)) * 100, 2)
from v$sysstat mem, v$sysstat dsk
where mem.name = 'sorts (memory)'
and dsk.name = 'sorts (disk)'
/
-------------------------------------------------------------------
-- Shared Pool Free
-- Measures the percentage of the shared pool not currently in use.
-- Low free values are not a cause for concern except in combination
-- with other factors that imply Oracle is out of RAM, such as poor
-- dictionary cache hit ratio.
------------------------------------------------------------------
select 'Shared Pool Free = '
|| round ((sum (decode (name, 'free memory', bytes, 0))
/ sum (bytes)) * 100, 2)
from v$sgastat
/
------------------------------------------------------------------
-- Shared Pool Reloads
-- Measures the percentage of SQL and PL/SQL statements reloaded
-- into the library cache as opposed to pinned in the cache. This
-- should be low, because the more statements are found in the cache,
-- the more efficiently Oracle will execute them.
--------------------------------------------------------------------
select 'Shared Pool Reloads = '
|| round (sum (reloads) / sum (pins) * 100, 2)
from v$librarycache
where namespace in ('SQL AREA', 'TABLE/PROCEDURE', 'BODY', 'TRIGGER')
/
--------------------------------------------------------------------
-- Library Cache Get Hit Ratio
-- Measures the percentage of requests for *any* object in the library
-- cache that were satisfied by the cache, without reading from disk.
---------------------------------------------------------------------
select 'Library Cache Get Hit Ratio = '
|| round (sum (gethits) / sum (gets) * 100, 2)
from v$librarycache
/
------------------------------------------------------------------
-- Library Cache Pin Hit Ratio
-- Measures the percentage of attempts to pin an object that were
-- satisfied by finding the object already in memory.
------------------------------------------------------------------
select 'Library Cache Pin Hit Ratio = '
|| round (sum (pinhits) / sum (pins) * 100, 2)
from v$librarycache
/
-----------------------------------------------------------------
-- Recursive Calls versus Total Calls
-- Measures the proportion of recursive SQL calls as opposed to the
-- total number of SQL calls, where "recursive" is Oracle jargon for
-- SQL generated by the RDBMS background processes for internal
-- purposes such as table sizing. This should be a low percentage;
-- high percentages indicate that the RDBMS is doing a large amount
-- of internal maintenance work, so it is probably not well tuned.
--------------------------------------------------------------------
select 'Recursive Calls vs Total Calls = '
|| round ((rcv.value / (rcv.value + usr.value)) * 100, 2)
from v$sysstat rcv, v$sysstat usr
where rcv.name = 'recursive calls'
and usr.name = 'user calls'
/
-------------------------------------------------------------------
-- Short versus Total Table Scans
-- Measures the proportion of full table scans that occur on short
-- tables. A full table scan is faster than an index access if the
-- table is small, so this figure should be high. If it is low, the
-- system may be missing some indexes, or poorly coded SQL may be
-- forcing the optimizer to choose against indexes.
-------------------------------------------------------------------
select 'Short vs Total Table Scans = '
|| round ((shrt.value / (shrt.value + lng.value)) * 100, 2)
from v$sysstat shrt, v$sysstat lng
where shrt.name = 'table scans (short tables)'
and lng.name = 'table scans (long tables)'
/
--------------------------------------------------------------------
-- Redo Space Wait Ratio
-- Measures the proportion of redo space requests that are on hold
-- pending a redo log write. If the figure is high, there is not
-- enough redo log space for these requests to be quickly satisfied.
--------------------------------------------------------------------
select 'Redo Space Wait Ratio = '
|| round ((req.value / wrt.value) * 100, 2)
from v$sysstat req, v$sysstat wrt
where req.name = 'redo log space requests'
and wrt.name = 'redo writes'
/
--------------------------------------------------------------------
-- Redo Log Allocation Latch Contention
-- Compare this with the following latch query to see if either or
-- both of the redo log latches is blocking. The redo logs are where
-- recovery data is written in case of a system or hardware crash.
-- The user process first grabs the copy latch, then grabs the
-- allocation latch, allocates space in the redo log for a redo
-- entry, releases the allocation latch,
-- and then copies data into the allocated redo buffer and releases
-- the copy latch.
--------------------------------------------------------------------
select 'Redo Log Allocation Latch Contention = '
|| round (greatest ((sum (decode (ln.name, 'redo allocation',
misses, 0))
/ greatest (sum (decode (ln.name, 'redo allocation', gets, 0)), 1)),
(sum (decode (ln.name, 'redo allocation', immediate_misses, 0))
/ greatest (sum (decode (ln.name, 'redo allocation', immediate_gets,
0))
+ sum (decode (ln.name, 'redo allocation', immediate_misses, 0)), 1))
) * 100, 2)
from v$latch l, v$latchname ln
where l.latch# = ln.latch#
/
-------------------------------------------------------------------
-- Redo Log Copy Latch Contention
-- See previous comment.
-------------------------------------------------------------------
select 'Redo Log Copy Latch Contention = '
|| round (greatest ((sum (decode (ln.name, 'redo copy', misses, 0))
/ greatest (sum (decode (ln.name, 'redo copy', gets, 0)), 1)),
(sum (decode (ln.name, 'redo copy', immediate_misses, 0))
/ greatest (sum (decode (ln.name, 'redo copy', immediate_gets, 0))
+ sum (decode (ln.name, 'redo copy', immediate_misses, 0)), 1))
) * 100, 2)
from v$latch l, v$latchname ln
where l.latch# = ln.latch#
/
-------------------------------------------------------------------
-- Chained Fetch Ratio
-- Measures how many data fetches resulted in row chaining, i.e.,
-- a write to more than one data buffer. This figure should be very
-- low. Otherwise, buffer block size or table design or both are
-- poorly tuned.
------------------------------------------------------------------
select 'Chained Fetch Ratio = '
|| round ((cont.value / (scn.value + rid.value)) * 100, 2)
from v$sysstat cont, v$sysstat scn, v$sysstat rid
where cont.name = 'table fetch continued row'
and scn.name = 'table scan rows gotten'
and rid.name = 'table fetch by rowid'
/
------------------------------------------------------------------
-- Free List Contention
-- Measures how many processes have had to wait on availability of a
-- free list for writing to a table. This figure should be very low.
-----------------------------------------------------------------
select 'Free List Contention = '
|| round ((sum (decode (w.class, 'free list', count, 0))
/ (sum (decode (name, 'db block gets', value, 0))
+ sum (decode (name, 'consistent gets', value, 0)))) * 100, 2)
from v$waitstat w, v$sysstat
/
------------------------------------------------------------------
-- CPU Parse Overhead
-- Measures the proportion of database CPU time spent parsing. This
-- figure should be very low. A high value shows that there is a
-- large amount of once-only code in the database (e.g., dynamic
-- SQL creation without bind variables) or that the shared SQL area
-- is too small.
-----------------------------------------------------------------
select 'CPU Parse Overhead = '
|| round ((prs.value / (prs.value + exe.value)) * 100, 2)
from v$sysstat prs, v$sysstat exe
where prs.name like 'parse count (hard)'
and exe.name = 'execute count'
/
------------------------------------------------------------------
-- Willing-to-Wait Latch Gets
-- Measures the proportion of attempts to get a latch by processes
-- that are not critical for system integrity and recoverability.
-- This figure should be very high (waiting on latches is not a good
-- sign).
-----------------------------------------------------------------
select 'Willing-to-Wait Latch Gets = '
|| round (((sum (gets) - sum(misses)) / sum (gets)) * 100, 2)
from v$latch
/
------------------------------------------------------------------
-- Immediate Latch Gets
-- Measures the proportion of attempts to get a latch by processes
-- that are critical for system integrity and recoverability. This
-- figure should be very high.
------------------------------------------------------------------
select 'Immediate Latch Gets = '
|| round (((sum (immediate_gets) - sum (immediate_misses))
/ sum (immediate_gets)) * 100, 2)
from v$latch
/
-------------------------------------------------------------------
-- Rollback Segment Contention
-- Measures the proportion of waits on rollback segment access. This
-- figure should be very low.
-------------------------------------------------------------------
select 'Rollback Segment Contention = '
|| round (sum (waits) / sum (gets) * 100, 2)
from v$rollstat
/
spool off