Oracle hit rate query, SGA Tuning

-- Data cache hit rate
-- Formula: 1-(physical reads/(db block gets + consistent gets ))
-- The hit rate should be better than 0.90

Select name, Value
From v $ sysstat
Where name in ('physical reads', 'db block gets', 'consistent gets ')
/

-- Hit rate of the cache area in the shared area
-- Formula: sum (pins-reloads)/sum (PINs)
-- The hit rate should be greater than 0.99

Select sum (pins-reloads)/sum (PINs)
From v $ librarycache
/

-- Shared area dictionary cache hit rate
-- Formula: sum (gets-getmisses-usage-fixed)/sum (gets)
-- The hit rate should be greater than 0.85

Select sum (gets-getmisses-usage-fixed)/sum (gets)
From v $ rowcache
/

-- Check the contention of rollback segments
-- The sum (waits) value should be less than 1% of the sum (gets) Value

Select sum (gets), sum (waits), sum (waits)/sum (gets)
From v $ rollstat
/

-- Check the number of rollback segments.

Select name, shrinks
From v $ rollstat, V $ rollname
Where V $ rollstat. USN = V $ rollname. USN
/

-- SGA tuning
(****): OS Memory + SGA + session * (sort_area_size + hash_area_size + 2 m) log_buffer: 128 K ---- 1 m usually has little problem, it should not be too large
large_pool_size: If MTS is not set, it is usually used in RMAN and opq, but it should be about 10 m-50 m.
java_pool_size: If Java is not used, it is usually enough to give 30 m
data buffer. After the preceding settings are made, any memory that can be provided to Oracle is provided, data Buffer = (db_block_size * db_block_buffers)
the shared_pool_size cannot be set to a large value. Generally, the shared_pool_size should be controlled at 200m--300m
, note that, based on the above (***) principles, you can refer to the following Settings
If 512 M Ram
shared_pool_size = 50 m is recommended, data Buffer = 200 m
if 1g RAM
shared_pool_size = 100 m, data Buffer = 500 m
If 2G
shared_pool_size = 150 m, data Buffer = 1.2G
the physical memory size is no longer related to the parameters.
assume that 64-bit Oracle
memory 4G
shared_pool_size = 200 m, data Buffer = 2.5 GB
8 GB memory
shared_pool_size = 300 m, data Buffer = 5G
memory 12g
shared_pool_size = 300m-----800m, data buffer = 8g