It would not have been fair to show how Oracle 11g optimizer can now re-parse a query based on the execution statistics associated with a bind value compared to the previous executions of the same query with different bind values. It would not have been fair, to show you that without showing what happens when the second execution is faster that the first one, even with a sub optimal plan. This is what I’ll do now !
Basically, what is done in the example below it the opposite of what I’ve done in my previous post.
The example in this post is intended to be use as a demonstration of the new bind peeking algorithm of 11g. You’ll have to understand how it works before screaming! If it significantly enhances the behavior of the optimizer, I’ll show you in a next post it also has its own drawbacks, you’ll have to overcome…
To avoid being too far behind you guys that are already upgrading to 11g, I’ve decided to invest on 11g during the Beta Program. This has been a lot of fun and I wish I can share some of my findings with you. For example, did you know that 11g can go faster without any change at all ? The queries here will illustrate the change made to the Nested Loop Algorithm in 11g :
Thanks to Jeremy Schneider for quick info that Oracle 11g is available for download on OTN.
Now for some logrolling at its finest. I thought I’d try to help Sheeri, the MySQL She-BA, spread the word about the 21st episode of her OurSQL podcast as it is the second of a two-part interview with Pythian pres. Paul Vallée. The topic is, “The Rise of the MySQL DBA.”
I am sad to report that my RSS feed for oracle-wtf definitely made me say WTF! but not in a good way. Actually visiting the site confirmed my suspicion, it’s been hi-jacked by a splogger.
The 56th edition of Log Buffer, the weekly compendium of database blogs, has been published on Hasan Tongu Yilmaz’s Oracle Blog.
Yes, I know, this subject of detailed extent map of the datafiles is a rather old one, and a few different solutions have been provided by several professionals, including the famous Tom Kyte. But none of the answers I found did exactly what I wanted, and therefore, I chose to write my own solution. OEM does provide this, but for a price — the Tablespace Map is part of the Oracle Tuning Pack — and I like the free stuff and the extra flexibility I have using queries.
come from a MySQL background, and I have been given the challenge of learning Oracle. So I decided to install my own Oracle database, which I’ll be free to destroy in every way I can think of… and of course, free to bring it back to life. Recovering from crashes will probably be the most difficult part of my adventures in the Oracle world, but let’s take one step at a time, shall we? This tutorial was based on a document which can be found here. I have adapted it for Oracle 11g.
The 55th edition of Log Buffer, the weekly review of database blogs, is published on the Ardent Performance Computing Blog, thanks to Jeremy Schneider.