Sizing the SGA when physical reads climb but DB time does not
A single-instance 19c database on a 64 GB host runs SGA_TARGET=12G and PGA_AGGREGATE_TARGET=8G. DB time is flat month over month, yet the 'physical reads' statistic climbs steadily. The shared pool shows heavy 'library cache: mutex X' waits during a nightly batch that parses thousands of unshared statements. The buffer cache advisory reports a poor estimated hit ratio in the default pool at every simulated size. One 40 GB table, ORDERS_HIST, is read only by a monthly report; the genuinely hot data is about 6 GB. Current allocation: default buffer cache 5G, shared pool 4G, no KEEP pool configured. The application team will not change SQL and will not accept a schema change.
1. Before resizing any component, how do you decide whether the rising physical reads are buffer cache pressure at all?
Read the worked answer
Split the statistic rather than trusting the aggregate. In v$sysstat, compare physical reads with physical reads direct and physical reads cache. Direct path reads (serial direct scans of a large table, or parallel query) never enter the buffer cache, so enlarging the cache cannot remove them - they are a function of scan volume and parallelism.
Then attribute the reads: v$segment_statistics shows which segments generate them. If ORDERS_HIST dominates and the monthly report runs in parallel, the 'problem' is the report, not the cache.
The advisory is also being misread. 'Estimated hit ratio' is a weak metric for a workload that scans a table far larger than the cache; the decision-grade output is estimated physical reads and the marginal benefit curve. If the curve flattens, extra cache buys almost nothing. With 6 GB hot and 5 GB of cache, thrashing of the hot set is the more plausible story - and that is a KEEP pool question, not a 'make the default cache bigger' question.
2. You decide a KEEP pool is justified. State the change, and explain what it does to SGA_TARGET and to the other components.
Read the worked answer
Set DB_KEEP_CACHE_SIZE explicitly (say 7G, covering the 6 GB hot set with headroom) and move the hot segments into it with ALTER TABLE ... STORAGE (BUFFER_POOL KEEP).
Key mechanism: when SGA_TARGET is set, the default buffer cache, shared pool, large pool, Java pool and streams pool are auto-tuned, but DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE are not. They are subtracted from the target as fixed allocations and are not shrunk by the MMAN advisor. So the KEEP pool must be funded by raising SGA_TARGET or by explicitly lowering a component - and totals must not exceed the target.
Also question the 4G shared pool. Mutex X waits on the library cache during a parse-heavy batch usually mean hard parsing of non-shared SQL, not an undersized pool; a bigger shared pool can even increase mutex contention by lengthening bucket chains. Check v$librarycache, the parse counts in v$sysstat, and whether cursor sharing or forced matching is appropriate. On a 64 GB host with 8G PGA, watch total OS memory before raising SGA_TARGET further.
3. How do you prove the change worked, and what single metric would make you reverse it?
Read the worked answer
Measure at the segment and session level, not with a global ratio:
v$segment_statisticsphysical reads (and buffer gets) for the hot segments before and after; they should not be re-read from disk.v$bh/ the KEEP pool's block count to confirm hot blocks actually persist.- Batch elapsed time and
library cache: mutex Xtime fromv$active_session_historyorv$system_eventover equivalent windows. - Free memory and paging on the host, plus
v$pgastat'over allocation count' to confirm PGA was not starved.
Reverse trigger: if the host starts swapping, or PGA over-allocation appears, or the KEEP pool sits mostly cold (v$bh shows few blocks of the tagged segments resident, meaning the 6 GB estimate was wrong), reclaim the memory. Also revert if batch elapsed time is unchanged - that would confirm the reads were direct path and the cache was never the bottleneck.