Oracle

It’s always worth browsing through the list of Oracle’s bug fixes each time a new release or patch comes out because it can give you clues about where to look for problems in your production release – and how to anticipate problems on the upgrade. This article is an example of a fix that I found while while looking at the note for 11.2.0.3 quite recently.

We’ve just booked the first European venue for the Understanding Storage Masterclass. I will be presenting the Masterclass on April 24/25 2012 at Prospero House in London, tickets are available HERE. I’m pretty excited to host this training session in my home country, and I hope to see you there!

From MOS (Metalink) a search for “Patch Set – List of Bug Fixes by Problem” is a useful search, andother is “Availability and Known Issues”. Whenever you find some behaviour that looks like a bug, it’s worth checking the patch sets for the patches or release that are newer than the version that you’re running – you may find that your problem is a known bug with a patch that might be back-ported.

For ease of reference, here are some of the results I got (sorted in reverse order of version) from the searches; you will need a MOS account to follow the links:

You might think from the title that this little note is going to be about the index hash join – you would be half right, it’s also about how the optimizer seems to make a complete hash of dealing with index hash joins.

Let’s set up a simple data set and a couple of indexes so that we can take a closer look:

Lately I’ve been reading a lot more than writing as is evident by my low-frequency blogging. Here is some of the material I’ve been going through:

So I was just patting myself on the back for finishing my website clean up, then I happened on a few pages with broken links to Oracle documentation. That annoyed me, but I figured I better do a quick scan to see how many broken external links I had. The first attempt was a complete fail because the tool I used clicked all my Google Adsense adverts, making me a DotCom millionaire in about 3 minutes. I wrote to Google and apologised profusely. In my defense, the tool I used was right at the top of the list in the Chrome Web App Store…

Once I got a link checker that didn’t put me at risk of a jail sentence, things got a little more depressing. A very large number of my articles contain broken links to Oracle documentation. As I started looking at links it became apparent that Oracle have used at least 3 main URLs for documentation over the years:

Here’s a set of Instance Activity stats I’ve never seen before, and I’d rather never see again. From an active standby running 11.1.0.7 on AIX:

With apologies to Dr. Seuss.

Did I ever tell you the makers of RAC
had seven features and named each flashback?

Well they did, and it wasn’t a smart thing to do.
You see, when the customers wanted a clue
as to how to keep data from getting deleted
the RAC folks said “flashback” and customers heeded.

They turned on all seven of those flashback features
Each one was a slightly dissimilar creature.
Some used the UNDO, some used flashback files
Some just renamed tables to bin$ styles.

One was a place you keep things for recovering
Another was just for forensic discovering
With so many features called by the same name
when thinks broke no one knew just which one they should blame.

Before I joined Blue Gecko, I did independent remote DBA work, and called myself ORA-600 Consulting. Stemming from my hair-raising experiences in the trenches at Amazon in the late ’90s / early 2000s, I decided to specialize in emergency DBA work for companies in the midst of crises (I know, great idea for someone who wanted to get away from the Amazon craziness, right?).

One day in 2009, a company in Florida called my cell phone at 2AM. They described their problem as follows:

In my previous post, I made the comment:

In general, if you have a three-column index that starts with the same columns in the same order as the two-column index then the three-column index will be bigger and have a higher clustering_factor.

So what scenarios can you come up with that fall outside the general case ?
Alternatively, what argument could you put forward that justifies the general claim ?

I’ll try to respond to comments on this post a little more quickly than the last one, but I still have quite a lot of other comments to catch up on.