die Seilerwerks

Just dropping a line to let everyone know that the large part of my tech blogging will now be at the Pythian Group blog.  I’ll keep this blog space open and post links to my new posts over there.  I’m still searching for the balance of what will be strictly over there vs. what content will be just here, so please bear with me.

Today is my last day at my current employer. It has been an incredible learning experience, and I’ll be forever grateful to the man who gave me the opportunity almost 7 years ago having zero knowledge of either Oracle or database administration.

I have accepted an offer from noted consulting firm The Pythian Group to join their staff of DBAs doing remote administration. This new position should give me exposure to a diverse set of client environments and configurations and a chance to grow in this career path and learn from some of the best in the business. I will be flying out on Sunday for a 3-week stint at the worldwide headquarters in Ottawa, Ontario, Canada to immerse myself in the Pythian way. If I have free time with my nights I hope to finish porting LeagueSite to drupal.

I’ll also try to get more regular with the blogging. Things have been a bit hectic as of late.

I was doing a bit of studying last night when the material made a passing reference to Oracle’s INSERT ALL and INSERT FIRST, and then had a review question on it. So this morning I set out to do a bit of learning, and was pleased to find that not only had Pythian’s Babette Turner-Underwood recently written on the subject, but that Andrew followed up on her post with another creative use.

The latest Log Buffer is out, and this blog gets a mention for doing little other than posting about an older Feuerstein article. Honestly, I’ll gladly accept any non-negative mention of this site, so keep ‘em coming!

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> startupORA-27102: out of memoryLinux-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.

I had to do a triple-take when I actually saw my name in print in the latest Pythian LogBuffer post. Then I had to re-read again to see if it was positive or negative. It was just for my post linking to the SQL implementations page, but still fun.

I was going to go with a Sally Field-esque acceptance speech, but I’ll think it’s better to go with the #wilug standby, “I’m kind of a big deal around here.”