So I went about setting sga_target and sga_max_size to 8 GB (8192M) and restarted the instance ... or attempted to. The instance refused to start again, giving me this:
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device
Some searching around led me to this post from Paul Moen over at Pythian (friends of this blog ... more on that later). It seems we had undervalued our shmall as do a lot of people. As Paul wrote:
The real godfather, the wizard behind the curtain is shmall. Its value determines the maximum amount of memory that ALL shared memory can take.Another review of puschitz (puschitz real good) confirmed things. After some tinkering by my SA, we now have an 8 GB SGA. I did originally set it to 16 GB, which might be a goal in production, but probably best to start modest with 8 GB for a multi-instance development box.
Just to make it fun, the actual setting is derived…
the maximum amount of memory = shmall * pagesize
where pagesize = getconf PAGE_SIZE and shmall = cat /proc/sys/kernel/shmall
Making shmall larger than free RAM is a recipe for paging hell and much gnashing of teeth. Oracle recommends half the RAM, we pushed the envelope and chose 75% as 8 gigabytes of free for OS and cache is just wasteful.
Especially given Oracle is already caching hot blocks in its memory.