Oracle Sun Exadata Database Machine

Larry Ellison recently bragged about the Exadata Database Machine v2. This appliance was previously aimed at database warehousing use in version 1. Now it sets its target as online transaction processing systems. Version 2 is supposed to be twice as fast as v1. Larry says the thing outperforms the best machines from IBM. He seems to be taking personal initiative on this project.

The Exadata Database Machine has a number of parts. Of course is hosts an Oracle 11g database. It also has an Exadata Storage Server from Sun. The storage server has 12 disks and 2 CPUs. However the real power comes from the huge amount of flash storage in the storage server. We are talking a couple hundred Gigabytes here.

The storage server component in this system is a smart one. It makes use of highly parallel operations. It can also do query processing itself. There is a speed gain from doing the processing close to where the data resides. The box also supports compression to drastically reduce data size.

Although the system comes preconfigured, you still need to do some fine tuning on the device. The cost can be a little more than $1M. However when you factor all the licensing and support costs, and get a bigger box, the cost can be a few million. This is obviously the high end OLTP market. Oracle is going after the market previously dominated by manufacturers such as IBM and EMC.

Performance Problems

There have been times recently when our production system has been painfully slow. The DBA Team took the lead to find out what was wrong. They could not isolate the problem. So they brought up the matter with Oracle Corporation.

Oracle could find no defect with the database software. Their analysis resulted them in saying that there must be a defect in our source code. The only value add they provided was some queries which were taking a long time on average.

This was most disappointing. Any monkey with a tool can identify the worst performing queries in the system. Now this is not to say that a query might not be poor. However these SQL statements were doing updates using only a primary key in the WHERE clause.

Sooner or later this problem will come around to our team. I told our project manager that I expected we would need a dedicated Oracle performance engineer. We used to have such individuals assigned to our project. They have since been replaced by consultants who are competent, but not performance experts.

NULLs and OUT Params

Recently I came across a big stored procedure in our code. I needed to make some changes to it. That's when I decided this procedure could not go on with its size and complexity. It was time to refactor it.

After I broke the procedure into many procedures and functions, I ran some regression tests. Initially I broke the functionality. I went back to good old debugging techniques. I wrote out a bunch of variables to a table. The values were always NULL.

Then it hit me. I had a bunch of OUT params that were not initialized. Therefore they were NULL by default. You can avoid this by always initializing OUT parameters. These were numeric variables. Incrementing NULL lefts the values NULL when I had the bug. Explicitly setting them to zero solved my problem.

Out of Sequence

I want to follow up on my post from yesterday with more details. A junior developer had to write a PL/SQL script that would update upwards of 10 million rows in the database. To make things run fast, she put FOR UPDATE in the cursor SQL. Then the actual update used WHERE CURRENT OF. This sounds like a solid design.

The problem was that a batching commit mechanism was added in the middle of the loop through the cursor. The result was that after the first batch was committed, the next iteration through the loop caused an ORA-01002: fetch out of sequence. The thing to know is that the FOR UPDATE works up until the next commit. After that you are done with the FOR UPDATE cursor. But the script was coded so that it kept on trying to loop after the commit. Is there a solution for batch commits with a cursor? Yes. Don't use FOR UPDATE. Or break up your cursor into many cursors, each of which does a single COMMIT.