Mistake #8: Not properly sizing the database memory footprint
Many things in information technology use caching to improve performance by doing less work. Oracle databases are no exception – they use in-memory caching to improve database performance. For example, they cache database records to lessen the number of times they are read from disk; they cache parsed SQL statements to avoid the expensive hard parsing process the next time the same statement is run.
Often I see servers that are dedicated for Oracle-only use, with considerable free RAM and a relatively undersized database memory footprint. Usually this happened one of two ways: a too-low default was accepted at the time the database was created, or the database was created many years ago and the memory footprint size, while reasonable back then, is much too small for the larger RAM allocations we have these days.
Less commonly, I see the database memory footprint sized larger than available free memory, causing the memory footprint, or parts of it, to be paged/swapped into and out of on-disk swap. This is completely counterproductive, as the whole idea of a cache is to do less work, not more!
A too-small or too-large memory footprint can dramatically reduce the throughput and performance of your database.
Is your database memory footprint doing the best it can do?