Originally posted by nabaig
Julian,

What happens when I know that my buffer cache is not enough? What I mean is, because of poorly written SQL's and poor indexing done on a legacy system, a lot of full table scans are taking place and my buffer cache hit ratio is 50-60%. How do I go about calculating the right size of the SGA?
I am in the process of fixing the full table scan problems but till then???

Thanks,
Nizar

Increase first DB_BLOCK_BUFFERS until

select 1 - (phy.value / (cur.value + con.value)) "BUFFER HIT RATIO (> 0.90 OK)"
from v$sysstat cur, v$sysstat con, v$sysstat phy
where cur.name = 'db block gets'
and con.name = 'consistent gets'
and phy.name = 'physical reads';

returns more than 0.9 There should be some activity in the DB in order to get the correct ratio. It might take some days to get the BUFFER HIT RATIO in the high 90s. How big is your DB, how much RAM do you have and what is the current value of DB_BLOCK_BUFFERS?

Ones you fixed the buffer's number, you can estimate the proper size of the shared pool.