Sun T-Series servers and Oracle database

In this article I am going to narrate a rather strange performance issue that I was pulled into. One of our client's called me to inform me that they had an unexplainable situation. They were trying to run a lot of migrate scripts that load a ton of data for some production cutover on a Sun T5 server that had 512 CPUs! And they said the process was extremely slow compared to their test server that had far less CPUs but same volume of data. So I went my merry way looking at the OS performance, the usual AWR, SQLs, locks, I/O etc. The machine was idle most of the time but any process they ran would take 5 times longer than their test bed. Ok, with most avenues turning empty, I started comparing the servers, then things started to unravel. Even though the test server was smaller, it was a totally different Sun server line (M-Series) compared to the Sun T-series in Prod. So what? Doesn't more horsepower count for anything. No... not in the case I found out. To cut a long story short, the Sun T-series servers have a fundamentally different architecture when it comes to process execution. The architecture implements what is known as Chip Multithreading (CMT), wherein instead of software multithreading which most of us are aware of, this server implements hardware multithreads. So what does this mean? To put it simply, most computers today execute an instruction very fast on a chip and then have to yield that instruction because of items that instruction needs from memory. So then the memory operation takes some time after which it goes back to the CPU for execution and so on. The problem is that while the CPU speeds have increased dramatically, the memory operational speeds have remained stagnant. Since you cannot really execute faster than the speed of your memory operations, one is pretty much stuck to whatever speed memory can run with. To circumvent this issue, CMT was introduced where a CPU in essence had dedicated hardware threads that can execute multiple process and always keep busy even while instructions on one of its thread is blocked on a memory operation, other threads can continue to execute. This is essentially what the Sun T-series does, it is meant to deliver throughput provided you can keep all the hardware threads busy!! This is where we were running into a wall, single threaded Oracle processes that especially have bad SQLs will run slow as hell on these boxes. So by default if you do decide to hoist an Oracle database on a Sun T-series either make sure you have enough fast transactions and a boatload of concurrent users, else parallelize the heck out of all your SQLs. This is what we did, the client's database was Oracle 11g release 2. We turned on PARALLEL_DEGREE_POLICY=AUTO. This means Oracle will automatically decide degree of parallelism on ALL SQL statements. The parallelism attribute on tables and indexes have no effect. Next, we set PARALLEL_MAX_SERVERS to 64 and PARALLEL_SERVERS_TARGET to 32. We then unleashed the SQLs! The box lit up on all cylinders and the load scripts started screaming! Moral of the story if you ask me is that not all servers are same especially when it comes to running Oracle databases. Architects beware!!