Kerry Osborne's Oracle Blog

I just noticed that 11g Release 2 for the Solaris Operating System is now available for download:

Oracle 11g R2 Download Page

It didn’t lag the Linux release by too long!

I re-did my Upgrade to 11gR2 talk at the DOUG meeting in Dallas this afternoon. (I originally did it at the Cowboys Stadium for an Oracle Tech Day around Halloween). I promised that I’d post a link to the presentation, so here it is (just click on the image):

It’s the same presentation as the one from the original talk, although I got twice as much time to do it this time (worked out a lot better). Here’s a link to the post about that original Tech Day event with a bunch of pictures of the stadium, including a couple of Jerry’s data center: Cowboy Stadium Pictures

I’ve written before on the change Oracle made to their Hint based mechanisms (Outlines/Profiles/Baselines) in 10g here: Why Isn’t Oracle Using My Outline / Profile / Baseline. To quickly recap, prior to 10g, the design goal for Outlines appears to have been to “lock” execution plans. That is to allow the optimizer as little flexibility as possible. With 10g and 11g, it appears the goal has swung away from the “locking” concept and towards allowing the optimizer more flexibility. I’ll show you an example of what I mean in a minute, but I must say that I find this decision to be irritating at best. It seems to me that the whole reason for implementing one of these objects is to try to keep the optimizer from changing its mind. After all, it was originally called “Plan Stability” by the Oracle marketing guys.

One of the main offenders in this regard is the use of a new format available for index hints as of 10g. Prior to 10g, the index looked basically like this:

INDEX(TABLE_NAME INDEX_NAME)

Translation: If possible, use this index on this object.

As of 10gR2, there is a new possible format which appears to be used (at least most of the time) when Outlines/Profiles/Baselines are created. The new format looks like this:

INDEX(TABLE_NAME (TABLE_NAME.COLUMN_NAME TABLE.NAME.COLUMN_NAME …))

Translation: If possible, use any available index on any of these columns.

As you can see, this format leaves a lot more to the optimizer’s discretion than the older format. (Note: the older format is still valid)

Update 01/03/11: Thanks to Tony Hasler for making me get the syntax right (see comments below).

In my previous post I published a script for changing an individual SQL Profile hint, but recently I had a situation where there was a SQL Profile that had 20+ index hints where the statement was suffering from plan instability, despite the fact that it was using a SQL Profile. Rather than manually look up the correct index names and change the hints one by one (a very error prone proposition), I decided to write a script that would automatically change all the INDEX hints from the “non-specific column oriented” format to the “specific index name” format.

But before I give you the script – a little history. Here are Outline hints for the same statement from 9.2.0.8, 10.1.0.?, 10.2.0.4, and 11.2.0.1:

Continue reading ‘Fixing Bad Index Hints in SQL Profiles (automatically)’ »

I went to the Dallas 100 awards banquet last night at the Morton Meyerson Symphony Center. The Dallas 100 is an annual award for the fastest growing privately held companies that are based in the Dallas / Fort Worth area. Enkitec was number 81 this year – not that great, but still not too bad considering the state of the economy over the last couple of years (we were 42nd last year by the way).

Sam Wyly was the guest speaker at the presentation. He didn’t actually give a speech, but rather gave an interview which was interesting. He’s best known as the founder of Michael’s Stores which sold for around $4,000,000,000 a few years back.

As usual, there was a dinner after the presentation. And as usual it was some kind of meat with asparagus. I guess asparagus must be one of those things that can be cooked a little bit ahead of time or kept warm without too much trouble because it seems to be standard fare at these types of events. Anyway, it’s always nice to have a dinner with asparagus because you get reminded of it the next morning. I won’t go into the details here but if you’re interested I highly recommend this article on the Discovery web site. Whew!