Kerry Osborne's Oracle Blog

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)’ »

Well the talk went pretty OK. I did feel pressed for time though. Mainly because I had 53 slides for a 45 minute talk, but we also were running behind schedule by the time I got to do my bit. But we got through it. I didn’t get to go into nearly as much detail as I would have liked but that may not have been such a bad thing. The one thing I didn’t say that I had intended to was that 11g looks very similar to 10g unless you actively implement some of the new features. So DBA’s that are familiar with 10g should quickly begin to feel comfortable with 11g. By the way, I’m quite happy to entertain any questions on the presentation here since we ran out of time.

So anyway, without further ado, here is the link to the power point presentation: Upgrading to Oracle Database 11g Release 2

I also took a bunch of pictures during the tour of the stadium which I’ll post later. Here’s the most interesting part of the stadium for a bunch of computer nerds like us though, the data center!

Apparently, even the computer operators get to watch TV in Jerry’s house!

Update: – here’s a few more pictures!

Video monitors everywhere – including some just to show the logo …

Here’s my co-presenter Cary Millsap:

… just before we got to go into the Dallas Cowboy Cheerleaders dressing room. That explains the look on his face!

Continue reading ‘11g Release 2 Technology Day – Dallas Cowboy Stadium’ »

I am signed up to do a talk next week at the Dallas Cowboys’ new stadium (known affectionately around here as “Jerry World”).

The stadium cost approximately 1.8 billion dollars to build. It’s supposed to be pretty impressive, I guess we’ll find out. U2 played in the stadium last week. Jerry had to move the big jumbo-tron screen above the floor in order for U2 to get their elaborate stage set up. The rumor was that it cost about $1 million to move it. Small change compared to the overall cost of the project.

We’re supposed to be in the press room which only seats about 100. This should be a fun event not only because of the interesting venue, but because the list of presenters includes a couple of good friends (Cary Millsap of Method R and Sean Turner now with Oracle). The theme is 11g R2. I had been told the event was full for a while and originally the sign up page just had a number to call for overflow seating, but there is a link to register on the page now, so get in while you can. Here’s the link to the info page:

11g Release 2 Technology Day

Hope to see you there.

Well it’s been a few weeks since Oracle made 11gR2 available for general use. After looking through the new features document, I made a short list of new features that I thought would be worthy of further investigation. As I spent a little time looking at them though, there turned out to be very little that was all that exciting. There were a couple of exceptions, but for the most part it seems that Oracle concentrated on stability rather than drastically new features. That’s not necessarily a bad thing and is probably an indication of the product (and the development process at Oracle) maturing. Nevertheless, here are a few things that I think are interesting:

1. Edition Based Redefinition – Rolling out new code that requires schema changes has always been a challenge. Keeping up with the changes, providing a mechanism to roll them back if things don’t go well, and allowing some code to continue to work as is, instead of having to change every single piece of code that accesses a changed object – these are a few of the challenges that come along with making schema changes. It’s a bit a chicken and egg thing. One way to mitigate the risk of these types of changes has been to use views to provide an abstraction layer that allows the code to be changed over time as opposed to the big bang approach of all code that accesses an object being forced to change at the same time. Edition Based Redefinition is designed to allow Oracle to manage that complexity via a new thing called an Edition. It’s pretty cool and in my opinion is hands down the best new feature of 11gR2.
2. File Watcher – The job scheduler has been enhanced to be able to monitor a directory and kick off a job when a file shows up. No more writing the same shell script over and over.
3. Stored Outline Migration – There is now a utility to migrate Outlines into 11g’s SQL Plan Management framework. This feature may actually be useful for migrating from older versions of Oracle directly to 11g. It basically converts Outlines to Baselines.

And here’s the list of things I initially thought might be interesting but that I have really not warmed up to (yet anyway).

1. Instance Caging – Putting multiple instances on a single machine is attractive from a licensing standpoint but can cause problems from a performance perspective. Instance caging is a new feature for controlling the amount of resources (CPU) that an instance can use. It is a good idea; however, there are already numerous options available from most of the major hardware vendors that can accomplish the same thing (think AIX LPARs, Solaris Zones/Containers, HPUX vPars, etc…).
2. ACFS – A true clustered file system? This one may be more interesting after I get a chance to play with it more, but come on, file systems just aren’t that exciting.
3. ASM FS Snapshot – Snapshoting capabilities have been added to ASM. Looks like an attempt to compete with the SAN guys. There are lots of people that use Snapshot technology for backup and disaster recovery scenarios, so maybe this will grow on me as well.
4. OCR Enhancements – OCR files may be stored in ASM, ho hum. At least we don’t need a separate Lun.
5. Cluster Time Service – There have been problems with RAC nodes getting evicted because of clock drift. But there are other ways to deal with this (NTP for example). But this is a nice addition, just not that interesting.
6. Segment Creation on Demand – No need to pre-build segments. Maybe useful by saving some storage for a while. But presumably you’ll be needing the space eventually or you wouldn’t have built the objects in the first place. Delaying the allocation of space just seems to me like an opportunity for a user to get an error when dynamic allocation of space occurs “automatically”. I think I’d prefer to do it ahead of time in most cases.
7. Hybrid Columnar Compression – This feature is actually very interesting. It has been hyped a little as Column Oriented Storage, but is in fact simply a compression technique (see Kevin Clossan’s post: Oracle Switches To Columnar Store Technology ). It may be highly effective though as the compression possibilities provided by column oriented storage models are a big part their advantage. Unfortunately, the fact that it’s only available as part of the Exadata storage hardware, means it is not going to be very widely applicable unless the Exadata product really takes off.

Anyway, that’s my thoughts on 11gR2 so far. I really don’t mean to be negative. I like getting my hands on new toys as much as the next guy, but I do believe that concentrating on improving stability and incremental performance gains is absolutely the right approach. It looks to me like Oracle has made a change to a more mature and disciplined development / release model. And I think that’s a good thing.

Let me know if you have another favorite new feature that I may have overlooked.