First International NoCoug SQL Challenge March 31, 2009

Spring is in the air. Days are longer, sun is shining, flowers blooming and a new SQL Challenge arrives to our community.

Its a nice coincidence. Exactly one year ago, Eddie Awad and I closed the Obfuscated SQL contest, and put the entries up to final vote. I posted an analysis of the entries and Laurent posted a funky photo of me. Eddie and I talked of running another Obfuscated SQL contest this year, but I have to admit that I forgot all about it.

But spring is not really spring without a juicy SQL challenge to whack your brains! So I think the SQL community is lucky that Iggy Fernandez talked both NoCoug and Apress into sponsoring a new SQL challenge.

The challenge is open to everyone. Not just NoCoug members, not just Oracle community. Anyone who can SQL, can enter. Did I mention there are prizes? Apress will give 6 books to the winner, by his choice.

Oh, and Dan Tow is one of the judges! He’s a great SQL expert, so I expect interesting commentary about the problem and the various solutions.

You can read all the rules in the NoCoug website. But the challenge itself is quite simple:

Consider a twenty sided die. Each side of the die has a different number on it. For each side, there is a certain probability that the die will fall on that side. Iggy collected all the probabilities in a table. Now he wants to know what is the probability that he will get certain sums when the die is thrown N times is succession.

Go ahead. Try to solve it. Not as easy as it looks? When Iggy first sent me the problem description, I thought it was impossible. I sent it to few friends, just to annoy them. And turned out that it is far from impossible. In the process, the challenge description was leaked, and we have few terrific solutions already: Rob Van-Wijk used Model, Laurent used connect-by and Vadim used 11gR2!

Iggy did not publish his own solution (and he can’t participate anyway since he is one of the judges), but to tease me a bit, he sent me a graph with the time it took his code to run for different numbers of dice throws. He has O(N^2) complexity, not exponential! From what I’ve seen not one of the published solutions is even close!

I hope for a lot of participants. I hope someone finds a solution that is at least as fast as Iggy’s, because he’s been gloating about it for two weeks now. I also hoping for lots of non-Oracle participants, because I’m curious how different their solutions will be, or maybe SQL is just SQL and all the solutions will look the same.

Adventures Installing 11g Clusterware March 31, 2009

Let me start by announcing that 11g clusterwear is easy to install. Really. Straightforward, simple, no issues at all. A crazy Dane can do it with both hands tied behind his back. The adventures I’ll describe below are 100% my own fault and have nothing to do with the quality of the product, which is excellent.

Before describing my adventures, I want to talk a bit about mountain biking. Mountain biking is a fine hobby, but riding on rocks and roots requires some skill. As a beginner, you find yourself riding very slowly and walking a lot around difficult sections. It is frusturating, but this way, you rarely crash at all. Experts, of course, ride very fast and rarely walk at all, and they still rarely crash. On the way from beginner to expert, there is a time where you gain some confidence in your skills, so you start riding faster. Unfortunately, this confidence often arrives before you actually have the skills you need to ride fast. The result is about 6 to 12 month of frequent crashes – until skills improves and confidence is reduced to the point that they match again.

I think I just hit this dangerous stage in DBAing. I now have some confidence in my understanding of how Oracle works, so I do not constantly refer to the docs. Which means that I  make more mistakes than I did as a newbie.

Back to 11g clusterware:

Installation went fine. About 2 minutes of “next-next-next install” and 5 minutes of waiting for the install to finish. It is nearly identical to 10g installation, except that the automatic configuration of the VIP actually works, even if your public IP happened to be 192.168.X.X, so no need to run VIPCA manually after the install. Nice.

But then I discovered that I installed clusterware in the wrong directory. Not a big deal, but I dislike non-standard installations, and since it was so easy to install, I decided to take another 15 minutes to uninstall and install it again.

How do I uninstall?

I assumed that you uninstall clusterware just like you uninstall the database software – just run the installer UI, select the right product and click on uninstall. Why bother checking  the docs when you can make convenient assumptions?

Click-click-click and the product should be uninstalled. There was some error message about files it could not remove. I decided to ignore it – the new installation will be in a different directory, and I can always remove extra files later.

When I tried to install it again, the installer complained that VIP is taken.

Strange. Didn’t I uninstall the clusterware? I ran crs_stat to check, and was somewhat worried that it actually worked. Returning all resources with status “unknown”.

I decided that I need to reboot that nodes. At least this should get rid of the VIP.

10 minutes later I found out that the nodes can’t stop rebooting. They start, and 30 seconds later they crash again. Those of you who have some experience with clusterware can already guess what was wrong. /etc/init.d/init.crs – the script that starts clusterware on boot was still there, attempting to start a partially uninstalled cluster, and failing. I did not even bother checking the logs, but I assume they’d show either that the VD is no longer there or that the interconnect is not configured, which leads each node to decide on a split brain and crash.

Over and over again. Thanks RedHat for interactive boot, which allowed me to stop this madness.

When the servers came back up, at least VIP was gone. So I decided to try another install. This time it ran all the way until the point it attempted to configure the notification services. This failed in a rather unhelpful fashion. The log error just said “configuration failed”. Thanks.

I decided to go for extreme cleanup, and simply delete ever related file I could find on the servers – in /etc, $ORACLE_BASE, $CRS_HOME, VD, OCR. Everything I could think of.

Attempting to install again. Again Notification Services fail. At least I know enough not to ignore this error. My redeeming virtue, I guess.

When all else fails, read the docs. Which was not as easy as you would believe. I kind of fell out of practice with the documentation, and 11g did move things around a bit. I could not find the RAC installation guide. Looking under “Grid”, I found RAC administration guide and Clusterware administration guide. Both contained advice on how to remove a node from the cluster, but nothing about how to remove the entire cluster.

Searching for “clusterware uninstall”, led to Overview of Deinstallation Process, which seemed promising. It contains this good advice: “Refer to Oracle Clusterware Installation Guide for your platform for Oracle Clusterware deinstallation procedures.” , but it did not link anywhere. I did find the installation guide, under “Installation” (duh), and it did contain uninstall instructions. I’m still a bit annoyed that searching for “uninstall clusterware” did not come up with this document.

Following the documentation turned out the best idea I’ve had that day. It reminded me that I should run rootdelete.sh, and then rootdeinstall.sh and only then run ./runInstaller -deinstall -removeallfiles.

Since I caused significant manual damage prior to following the documentation, I was not surprised by a long list of complaints that each of these scripts had for me.

But after following the uninstall documentation, I was finally able to install clusterware 11g, successfully, in the right directory.

5 hours after I decided on a small 15 minute solution. It was time to go home.

BTW. Now that I think of it, it is quite possible that in 10.2, it was impossible (or at least undocumented) to uninstall clusterware on Linux. I cannot find the instructions in 10.2 documentation at all (The OpenVMS docs do contain uninstall instructions). Our internal procedure was always just “reimage the servers”.

NoCoug Winter Conference and Method-R Seminar March 23, 2009

North California DBAs – Mark your calanders because May is going to be a busy month

On May 19-20 Cary Millsap and Karen Morton will give their famous “Oracle Performance for Developers” seminar in Pleasanton. Register on Method-R website, but don’t forget to register to NoCoug first, so you’ll get a member discount on the seminar.

Then, May 21 is NoCoug’s Winter Conference. Cary Millsap will give the keynote, Karen Morton will talk about managing statistics (and hopefully do her magic tricks!), Gaja Krishna Vaidyanatha will give two hours of stand-up comedy about the SAN, Dave Abercrombie will talk about the user of session tags for end-to-end troubleshooting and monitoring.

I’ll also be there, this time presenting “Troubleshooting Streams”. It is an updated version of my OOW presentation. There won’t be a live demo this time, and I’ll talk much more about the way streams work and how to build systems that will require less troubleshooting in the future.

Reading Blocks – Nomenclature March 21, 2009

Looking at the time statistics the database collects , I often get confused between the different terms and what exactly they mean and measure. For my convinience (and hopefully yours), here are the terms used to describe block reads and what they mean.

LIO = Logical IO. Logical IO is any attempt by the database to read a block of data. This can be done from the buffer cache or from the disk.

PIO = Physical IO. Those are reads that cannot be satisfied from the buffer cache. Note that we still don’t know whether the disk is actually involved, operating systems and storage have cache too.

DB Block Get = current mode get. Attempt to read the most current image of a block. DML operations need to get the current image (no point in updating historical images).

Consistent Gets = consistent reads (sometimes abbreviated as CR). Attempt to read a consistent image of the block. Consistent means the version that was current when the statement began. This may require applying undo information and to roll back changes that occured after the statement began.

If you have RAC, you’ll also see “gc current block” and “gc CR” events. These are the same as consistent reads and current gets, except that they were satisfied from the buffer cache of a remote node.

Would You Rather Maintain SQL or PL/SQL? March 20, 2009

Rob Van Wijk wrote about the reputation that SQL has as unmaintainable. He claims that SQL is not inherently less maintainable, but rather that developers who say that SQL is difficult to maintain are simply not as skilled in SQL as they are in PL/SQL, and they certainly don’t document their SQL to the same level they document their PL/SQL.

I mostly agree with Rob. It is not a coincidence that developers are more comfortable with PL/SQL than with SQL. PL/SQL is a procedural language. Nearly everyone learned programming with a procedural language, and probably programmed in a procedural language for at least three years before being introduced to SQL.

SQL is a different beast. It is a data maniplation language based (roughly) on the relational model, and most of its operators are based in relational algebra. Quite different from Pascal, C or Java. Its not really surprising that most programmers are never entirely comfortable around SQL and keep being surprised by its behavior.

I think there are few other reasons that SQL is percieved as less maintainable though:

1. Readability of SQL is heavily dependent on the underlying data model. A good model will lead to more readable and maintainable SQL. When the data model does not match the reports that need to be generated, PL/SQL may have an advantage, its readability being less impacted by the data model.
2. Differences in how SQL statements are written can lead to differences in performance. It is somewhat true in PL/SQL as well, but I think SQL is more sensitive. Performance often takes priority over readable code. Not using subquery factoring because it results in sub-optimal optimizer decision is one consequence. Of course, the reasons for writing SQL in a specific way are very rarely documented, making the code even less maintainable.
3. As a result of #2 – developers who will gladly refactor PL/SQL code to make it more readable, will think at least twice before refactoring SQL. It is nearly trivial to predict how a given code change will impact PL/SQL performance, but nearly impossible to do the same for SQL (unless you are Jonathan Lewis).
4. Debugging is a big issue with SQL. In PL/SQL it is easy to add few put_line messages to track what exactly your code is doing and why. There are even nice commercial debuggers. Finding bugs in SQL is still a bit of a black art that must be done without any tools or methods.

I’m not sure if there are any guidelines on when to prefer SQL and when PL/SQL is better, but at least the discussion is a bit more complete.

HotSos 2009 Highlights March 17, 2009

HotSos was amazing. Here are some of the things I loved best about it:

1. The people. Attendees and presenters a like. Everyone was smart, insightful and had a good story to share. People who met me there may have trouble believing that I’m normally very shy and quiet. But everyone was so nice and interesting that I found myself talking to many more people than I normally do. This was an incredible social event (yes, and I’m geeky enough to consider conferences as social events).
2. Chris Date presentation was fascinating. I usually stay away from “useless theory”, but Chris Date gave a good show, and from now on I’ll always remember that “An Object is not a Relation!”.
3. Karen Morton did magic tricks on her presentation! That was by far the coolest thing I’ve seen on any technical conference.
4. Henry Poras was the best discovery of the symposium. Everyone says that Queue Theory is important, but only Henry can show you how to use it to identify bottlenecks.
5. Toons Koppelaars had some very good insights about what went wrong with app development, and he created great graphics to explain them.
6. Ah, my own presentation was… well, I’m not sure. I got mixed feedback.  A lot of people showed up, I hope most of them enjoyed it, and I learned a lot on how to improve my presentation style.
   BTW. I put up a presentation page, which contains my paper, slides, code etc.
7. Seeing the HotSos staff dressed as pirates! It was a great party.
8. Jonathan Lewis training day was great. I did not think I can stay fascinated for 8 hours.
9. People came up to me and said “I like your blog”. You have no idea how happy this made me

A Year with Streams March 3, 2009

Whenever Tom Kyte is asked about replication, he has a default answer: Don’t.

Well, last month marked our first year anniversary of our use of streams based replication to create a reporting DB.

In many ways, I’m very proud of this solution. Our reporting DB allows our software-as-a-service to offer very flexible integrations with all kinds of reporting systems without having us worry about impact on our production environment. Our senior DBA designed a terrific architecture that allows for relatively pain-free maintenance on the system. We have several customers using this solution and it seems to be making some profit.

For a year anniversary, we celebrate by announcing a formal SLA for the service. SLA discussions are always interesting. We are looking at 3 important components:

1. Setup times – How long after the customer requested this service do we expect to deliver it?
2. Availability – How many hours a month do we expect the service to be down?
3. Time to replication – A change was made on production. How soon can we show it on the reporting DB?

Setup times are our most volatile component – we range between 15 minutes when everything goes well, to around 2 month when we run into an Oracle bug, Oracle offers no work-around and patch after patch fails to solve the problem.

With that kind of variance, offering any kind of SLA seems a bit futile. Next week I’m at HotSos, Maybe Robyn Sands has the solution. BTW. I find it incredible that until last month I’ve never heard of Robyn Sands, and now not only I hear about her all over the place, the more I hear about her, the more I like her. A lady who’s an Oracle pro, knows her stats and wants to stop root cause madness. I can’t wait to meet her.

Back to the point,  we don’t see how we can offer an SLA on the setup time, which is bad. What about the availability after the service is up? We are doing much better here, in the sense that downtimes are predictable. The problem is that we have many of them. We have mandatory 2 hours a month due to an Oracle bug that requires us to rebuild the environment from scratch every month. We also have an additional monthly downtime for applying the various patches we need in order to solve other bugs.

In short, Streams is still buggy. So even if you design your replication to be very safe, put primary keys on all your tables, and get everything else correct, you still have trouble maintaining high availability and predictable setup times. This is for a product that has been out since 9.*

What about replication times? Here, I feel like the guy who jumped off the 50th floor window. Somewhere around floor 30 someone else looks out his window and asks him “hey! how are you doing?” and he replies “So far, so good!”. So far, we’ve been able to resolve all the performance problems we had with replication (#1 tip – don’t use “like” in replication rules).

With all the troubles we are having with Streams, would I use this solution again? I think that given all the constraints we had to work with, replicating data to a reporting DB was the best solution.

I would have preferred a different solution. I would have liked our product to have a strong, consistent, web services API that would allow integration with external systems without any need to a direct access to the database. Unfortunately, the web services API  arrived late, and we have to support versions that don’t have it yet. Also, integrating with web services API seems to require more work than database level integration and business was very eager to offer an easier solution.

Another solution that might have been preferable (although I’m less sure), is a reporting utility where we could load customized queries for our customers, run them as a low priority process on production and send the customers the results. The problem with this solution is that it may require more work than Streams (keeping an eye on all those custom code running on production), and guaranteeing response times for the reports would have been an issue. With the reporting database we somehow managed not to have any guarantee regarding performance of the reports.

To sum things up, when streams is good it is very very good, and when it is bad, it is still somewhat better than the alternatives. At least it gives me lots of blog material.