09 March 2007

It's an SGA Explosion!

So now that I have an instance riding on some 64-bit hardware, I decided that it's time to raise the SGA. On our current 32-bit production and development machines, we have it set to roughly 1.5 GB, and the maximum SGA for 32-bit is roughly 1.75 GB. However with 64-bit addressing, the max is now ... I don't even know what it is. As Kim Jong-Il said in Team America: "NO ONE DOES." Basically it's up there where astronauts and angels dream.

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:

SQL> startup
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.
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.

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.

No comments:

Post a Comment