Oracle Performance and Engineering

I did a capacity planning for a client few days back. I suggested - based on their budget and application usage - 4 Intel Xeon 2.8Ghz(+) CPUs with hyperthreading enabled. Today I got a very interesting question -

"For processors on your hardware suggestions, you specified you suggested Intel Xeon 2.8GHz (+), but nothing was specified about cache or Front Side Bus, which both make a huge difference in cost. Assuming these are the options, what would you suggest?

* 3GHz /4MB/ 400 MHz FSB
* 3.2GHz/1MB/800Mhz FSB"

Hmmm..So what are these things ?

3GHz is the clock-speed of the CPU. 4 (or 1) MB is the CPU Cache and 800 (or 400) MHz is the front-side bus speed.

That's the maximum frequency at which the CPU can operate.

What's "front-side bus"?

It does what a muni bus does i.e., transfer stuff. Rather than people, it transfers data from other components like RAM, hard disks etc.

A hash join is CPU intensive. The CPU needs to sort the data. All these things use "bus" to go to CPU.

What's "CPU Cache"?

You use RAM to cache data from hard disk because hard disk access is slow. Similarly, CPU uses CPU cache to cache data from RAM since CPU Cache access is faster than RAM access.

Guess what? There're terms like 'Cache Hit' and 'Cache Miss' too.

What's hyper-threading?

It's the ability of one single CPU to process two threads (say, Oracle processes) at the same time. Intel introduced this with their Xeon processors.

Here's what I wrote back to the client -

"My answer -

BTW, we suggested Xeon (or, hyper-threaded CPU) because database benchmarks typically run about 25% faster on a hyper-threaded box compared to a similar clock-speed CPU without hyper-threading box.

Hyper-threading enabled Xeon CPU will share two threads (or, processes) on the same physical CPU. Depending the type and scale of the application these two sharing processes could actually be working against each other (say, reading huge data from different parts of memory). That typically raises "bus contention". I quickly checked some benchmarks - and it seems Hyper-threading performance scales better with an increased bus, than it scales with an increased cache.

In other words, just increase in CPU clock-speed for such an app running hyper-threaded could be futile without an associated increase in bus.

Thus, logically and factually, my vote would be for having the bigger bus your money could buy, i.e., the second option here.

* 3.2GHz/1MB/800Mhz FSB

Another off-topic thing, just in case it's raised later - To use the hyper-threading (or, any other app for that matter) Oracle does *not* need to run in a multi-threaded mode.

BTW, if you want to do more research on this -

For hyper-threading basics

Some Crude Benchmarks

Excellent Hardware Site - Reviews, How-tos

Even more hyper-threading