Why us?
	Our Oracle tuning techniques can yield 100%+ performance gains.
	Your customer and employee satisfaction guaranteed.
	No further applications need to be installed.
	We operate at the hardware / OS / kernel / network and rdbms level for max Oracle performance tuning gains.
	Our work is seamless and unobtrusive. Yet Powerful.

1.0 Oracle buffer Cache Hit Ratio - Oracle memory tuning
Calculate buffer cache hit ratio in the database. Make sure it is more than 80 for an oltp environment and 99 is the best value.

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 c
where
a.statistic# = 38
and
b.statistic# = 39
and
c.statistic# = 40;

TOP

2.0 Calculate Oracle dictionary Cache Ratio - Oracle memory tuning
Aim for a 90% hit ratio on Dictionary Cache. Entries for dc_table_grants, d_user_grants, and dc_users should be under 5% each in the MISS RATE % column

select
parameter,
gets,
Getmisses ,
getmisses/(gets+getmisses)*100 "miss ratio",
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
from v$rowcache
where gets+getmisses <>0
group by parameter, gets, getmisses ;

TOP

3.0 Calculate Individual Cache Hit Ratios - Oracle memory tuning
Reduce the Reloads and try to increase the hit ratios to above 85%

ttitle center 'LIBRARY CACHE STATS' skip 2
column namespace format a8 heading 'Library'
column gets format 9,999,990 heading 'GETS'
column gethits format 9,999,990 heading 'GETHITS'
column gethitratio format 99.90 heading 'GET|HIT|RATIO'
column pins format 999,999,990 heading 'PINS'
column pinhits format 999,999,990 heading 'PINHITS'
column pinhitratio format 99.90 heading 'PIN|HIT|RATIO'
column reloads format 999,990 heading 'RELOADS'
compute sum of gets on report
compute sum of gethits on report
compute sum of pins on report
compute sum of pinhits on report
compute sum of reloads on report
break on report
select
namespace,gets,gethits,gethitratio,pins,pinhits,
pinhitratio, reloads
from v$librarycache
where gets+gethits+pins+pinhits>0;

TOP

4.0 Calculate the Oracle Library Cache Ratio for the Whole system - Oracle memory tuning
This should be near 0.

column libcache format 99.99 heading 'Percentage' jus cen
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache

TOP

5.0 Calculating the hit ratio for multiple pools - Oracle tuning
To run this script you must get connect as user sys

select name,
1 - ( physical_reads / ( db_block_gets +
consistent_gets)) "HIT RATIO"
from sys.v$buffer_pool_statistics
where db_block_gets + consistent_gets > 0

TOP

6.0 Check Session Level Hit Ratio - Oracle tuning
The Hit Ratio should be higher than 90%

select Username,
OSUSER,
Consistent_Gets,
Block_Gets,
Physical_Reads,
100*( Consistent_Gets + Block_Gets - Physical_Reads)/
( Consistent_Gets + Block_Gets ) "Hit Ratio %"
from V$SESSION,V$SESS_IO
where V$SESSION.SID = V$SESS_IO.SID
and ( Consistent_Gets + Block_Gets )>0
and username is not null
order by Username,"Hit Ratio %";

TOP

7.0 List Session Specific Memory - Oracle memory tuning
List the UGA and PGA used by each session on the server

column name format a25
column total format 999 heading 'Cnt'
column bytes format 9999,999,999 heading 'Total Bytes'
column avg format 99,999,999 heading 'Avg Bytes'
column min format 99,999,999 heading 'Min Bytes'
column max format 9999,999,999 heading 'Max Bytes'
ttitle 'PGA = dedicated server processes - UGA = Client machine process'

compute sum of minmem on report
compute sum of maxmem on report
break on report

select se.sid,n.name,
max(se.value) maxmem
from v$sesstat se,
v$statname n
where n.statistic# = se.statistic#
and n.name in ('session pga memory','session pga memory max',
'session uga memory','session uga memory max')
group by n.name,se.sid
order by 3
/

TOP

8.0 List the size of Oracle stored procedures and use it to tune Oracle shared pool - Oracle memory tuning
This script lists the size of stored objects

column num_instances heading "Num" format 999
column type heading "Object Type" format a12
column source_size heading "Source" format 99,999,999
column parsed_size heading "Parsed" format 99,999,999
column code_size heading "Code" format 99,999,999
column error_size heading "Errors" format 999,999
column size_required heading "Total" format 999,999,999
compute sum of size_required on report

select count(name) num_instances
,type
,sum(source_size) source_size
,sum(parsed_size) parsed_size
,sum(code_size) code_size
,sum(error_size) error_size
,sum(source_size)
+sum(parsed_size)
+sum(code_size)
+sum(error_size) size_required
from dba_object_size
group by type
order by 2/

TOP

9.0 Redo Latch Contention Monitor - Oracle memory tuning
Try to reduce the contention by reducing all the ratios to be less than 1

SET feedback OFF
COLUMN name FORMAT a15
COLUMN gets FORMAT 99999999
COLUMN misses FORMAT 999999
COLUMN 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) ratio2
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');

TOP

10.0 Oracle sorts Monitoring Scripts - Oracle memory tuning
Monitor the sorts in memory vs disk. Try to keep the disk/memory ratio to less than .10

SET HEADING OFF
SET FEEDBACK OFF
COLUMN name FORMAT a30
COLUMN value FORMAT 99999990

SELECT name, value FROM v$sysstat
WHERE name IN ('sorts (memory)', 'sorts (disk)');

TOP