Kerry Osborne's Oracle Blog

This week I attended the Hotsos Symposium – It was great as usual. There are more smart guys at this event every year than you can shake a stick at. In fact, I often learn as much from the attendees as I do from the presenters.  Here’s a fancy link to the presentation I gave:

Note: I struggled a bit with how to label myself, since I don’t really have an official title. I thought about calling myself a “Senior Oracle Specialist”, but that sounded a little too puffed up. Especially the “Specialist” part. So then I thought maybe “Senior Oracle Guy” would be a little more down to earth. That was better, but it sounded a little too old, like a Senior Citizen. And since I am still in my late 40’s (OK very late 40’s) I am still quite a ways from being a “Senior” I think. Then I thought maybe I should go with something more generic like “Nice Guy and All Around Prince of a Fellow”, but that seemed a little too uninformative (and beside, my former partner used to have that on his business cards). So I decided to go back to the “Oracle guy” idea and considered using something like “Very Experienced Oracle Guy”. That sounded OK, but “Very Experienced” is really just code for old. So I was back to that, how to say old, but not too old. “Oldish” – that’s what I ended up with, mainly because I ran out of time to think about it any more (probably a good thing).

I was originally scheduled to deliver my talk on Tuesday afternoon. But when I checked in on Monday morning, Becky Goodman asked me if I would mind swapping time slots with Stephan Haisley, who had a “conflict”. His slot was first thing in the morning on Wednesday. So I said sure. Only later did I find out that the conflict was related to the Tuesday night party, which has a tendency to stretch into the wee hours of the morning. Stephan’s a smart guy and he was thinking ahead. He realized that he probably wouldn’t be at his best, first thing on Wednesday morning. As Clint Eastwood said, “A man’s gotta know his limitations”.

Anyway, the talk went pretty well but I did have one embarrassing moment. I’ve been doing Oracle stuff for a long time, so I often run into people that I haven’t seen for a while (sometimes a very long while). I’m pretty good with faces and places, but names sometimes escape me. Isn’t it odd how our brains work? I can remember minute details about some arcane unix command that I haven’t used in 10 years, but a guy’s name that I worked closely with for half a decade can escape me. How does that happen?

I’ve gotten used to it, but occasionally something even more bizarre happens. Like getting a couple of bits of memory cross wired. This actually happens more often than you would think. Try this on a friend. Get them to say “Silk” five times as quickly as they can.  Like … “Silk, Silk, Silk, Silk, Silk” …  Then immediately ask them what cows drink. Almost without fail they will say “Milk”. Of course they know that cows don’t drink milk. They know that cows drink water. But for some reason the word “Milk” just comes rolling off their tongue. Why? Because the word “Milk” sounds almost the same as the word “Silk” and you’ve just made them access the part of their brain that stores the word “Silk” several times in a row. In addition, you have asked them a question with a word (cow) that is very closely associated with the word “Milk”. And finally, milk is a liquid that people drink. So there are 3 very strong associations in your brain, even though you know that it is not the correct answer to the question.

So what’s the point, well  … The first day of the Symposium, I ran into a guy that I have known for several years and that I had in fact shared office space with just a couple of years ago. His name is Jeff Holt and he co-wrote a book with a guy named Cary Millsap called Optimizing Oracle Performance. So I see Jeff, walk over with a big grin on my face, shake hands with him and say “Hi Kevin!”.

And he just looks at me like I’m crazy (which he does pretty well, by the way). And I realize what I’ve done and say “I’m sorry Jeff, I do know what your name is”. And he looks somewhat dubious but accepts my apology. The thing is, I have done this to Jeff several times in the past. I explained to Jeff that there is a perfectly reasonable explanation for me calling him by the wrong name. I used to work with a guy named Kevin Holt and for some inexplicable reason, Kevin’s name always comes out when I think about Jeff. Maybe it’s because my brain stores data by last_name and the cells holding the first names have become damaged in some way, maybe I’ve used the name “Kevin Holt” a lot more than the name “Jeff Holt”, maybe my brain was more impressionable when I was younger and so the earlier memory is stronger. I’m not sure. Anyway, I pretty much just wrote it off as one of those questions for which there is no answer.

But I digress, back to the embarrassing moment during my presentation: So the talk is going along well and I get to this page where I reference Cary and Jeff’s book and I look at the big overhead and the reference looks like this:

Cary Millsap & Kevin Holt. Optimizing Oracle Performance
O’Reilly, 2003.

Of course to me it looked like this:

Cary Millsap & Kevin Holt. Optimizing Oracle Performance
O’Reilly, 2003.

Yes that’s right. Not only did I call him by the wrong name when I ran into him, but I actually typed it wrong on my presentation. To make matters worse, Cary Millsap is in the audience with a puzzled look on his face. So I have to apologize to him while the rest of the audience looks on. Then as soon as the talk is over, I fix the presentation materials and resubmit them (hopefully wiping out any trace of my cross wired brain). This whole experience gets me really thinking about how my brain is working and why it continues to make this repeated error. It seems unlikely that just knowing two guys with the same last name would cause such a problem. I know lot’s of people with the same last name, and I don’t get their first names mixed up.

So I start racking my brain to see if I can come up with any other explanation. What other associations do I have with the name “Kevin”? Well for starters, my only brother’s name is Kevin. We were born only a year apart so when I was a kid, almost every time I heard my name it was closely followed by his name (usually it was at the top of some adult’s lungs due to some trouble we were stirring up). In fact, the old folks often couldn’t be bothered  to keep us straight, so even when we weren’t together (which was rare) they often just combined our names (Kervin was the most common version – Kevrry was a lot less common – for obvious reasons). So anyway, I do have a very strong association between my name and my brother’s name. Then it occurred to me that my first name sounds just like Cary Millsap’s first name. Hmmmm. Cary and Jeff are closely associated (at least in my mind) as I mentioned before. They co-authored the book and have worked together at the same company (first Hotsos and then Method-R) for the last decade or so. I’ve known Cary a long time, but only met Jeff 4 or 5 years ago. So I’ve only ever known Jeff to be associated with Cary. You probably can see where this is heading. I believe my brain does something like this old school fill in the blank problem:

__________________________________________________________________________________________

Fill in the Blank with the Word that Connects the Other Two Words

Cary (which sounds like Kerry)  ____________   Holt

__________________________________________________________________________________________

It’s like a little pattern matching or free association thing. My brain just wants to put the word “Kevin” in that spot as the link between the other two words.

By the way, there have been lots of studies done over the years about how our brains store memories, how we retrieve them, how we forget things, etc… Some of those studies have indicated that most long term memory is semantic based while short term memory is more acoustic based. So most people would tend to mix up words that sound alike (like milk and silk) in short term memory while mixing up words that mean the same thing (like auto and car) in long term memories. Of course there are other studies that prove we all have some long term acoustic memory (being able to identify specific accents for example). The fact that I am a long time musician and that I play mostly by ear is probably a contributing factor as well. I am said to have a “good ear” which means that I can reproduce music pretty accurately after a very short exposure to it. So I think all that extra exercise my brain has done has made me more likely to store long term memories with an acoustic or “sound alike” kind of memory organization. By the way, if you are interested in this kind of stuff, there is an excerpt from a survey of the literature which discusses several of these studies here: Human Memory by Elizabeth Loftus.

So that’s my story and my rationalization for why it happened. And for what ever it’s worth, I’m sorry Kevin, err… I mean Jeff! –  I guess my brain just has a mind of it’s own.

Karen Morton just posted a great paper on her blog about statistics gathering. The paper is titled “Managing Statistics for Optimal Query Performance“. I was excited to see it because I think gathering stats is one of the most important and least well understood aspects of managing an Oracle environment. I must admit that I was expecting a recommended method or framework for gathering stats, but actually the paper is really more about how the statistics are used along with general guidelines for gathering them, rather than a direct recommendation on how to gather them. Nevertheless, it is one of the best papers I’ve seen on the subject. She’s going to present the paper at the Hotsos Symposium to be held in Dallas the week of March 9th. I’m going to be there and am really looking forward to hearing what she has to say on the subject.

By the way, I can’t recommend this conference highly enough. If you really want to understand how Oracle works, this is the place to be. You should know that I am not generally a fan of formal training classes. I have often been disappointed because I felt like my time would have been better spent researching the subject matter myself. On the other hand, I have found a lot of value in working closely on a project with someone who knows the subject matter well, kind of like a mentor. But generally speaking, the formal classes have been less satisfying, except in the rare case where you get the great instructor that wrote the class. This symposium format on the other hand allows you to listen to a collection of really knowledgeable Oracle people packed into a short period of time. I have been to the Hotsos Symposiums for several years in a row and I always come away with pages of notes on things I want to investigate further. And the participants are, generally speaking, a collection of very bright Oracle people. So even the conversations between the presentations are often very interesting. Finally, they run two presentations at a time which allows you to pick the presentation that is most interesting. I have often found it hard to choose (don’t tell anyone, but I have on more than one occasion listened to the first half of one and then the second half of the other). So like I said, I find it to be a very productive few days.

But I digress, Karen’s paper is pretty long (24 pages) but it covers a vast amount of ground. There are a number of one liners that could be expanded into full papers. In the paper she discusses a number of topics including dealing with short comings of the optimizer in 10g. One of those issues is bind variable peeking (probably my least favorite optimizer feature, quirk, bug, … what ever you want to call it). I must say that I think it has caused far more problems than it solved, and I frankly don’t know what they were thinking when they put that feature in. I wrote a little about a way to get around it using outlines here. By the way, this reminds me of a cartoon I drew 20 years ago that looked very similar to this one (that I lifted off of Steve Karam’s blog)

Of course as Karen points out, the right way to deal with bind variable peeking issues it is to understand your data and use literals where they are appropriate, keeping in mind the number of additional statements that will need to be parsed and dealt with in the shared pool. She also points out that code could be written to selectively use literals for specific values, giving you a mix of literals and bind variables for the same statement. This approach should allow you to minimize the impact on the shared pool while still providing the optimizer with the data it needs to make good decisions (this is a great idea but I’ve never seen anyone actually do it). And of course she points out that 11g has a much better mechanism for dealing with this whole issue.

Another idea that really got me thinking was the use of dynamic sampling . Karen clearly shows one of the advantages of dynamic sampling in the case of correlated predicates (i.e. the optimizer assumes a query where car_model = ‘Mustang’ and car_make = ‘Ford’ are independent, when clearly they are not). She shows how dynamic sampling can be very useful in conjunction with normal statistics in this situation. (rats, now I’m going to have to go play around with that a bit – so much to do, so little time)

Finally, she discusses some of the statistics gathering options and differences in 9i, 10g, and 11g. The automatic creation of histograms is one of the main differences between 9i and 10g and she discusses this issue, but doesn’t go into to much detail on it. I must admit that I think 10g’s default setup does a very poor job when it comes to histograms. This is the one area I would have liked to see address a little more fully, but at 24 pages already I can understand why she had to draw the line somewhere. Anyway, by default 10g creates histograms on columns based on several factors including their use in where clauses. Unfortunately, histograms often get created on columns where their usefulness is questionable at best and they regularly get created with a very small sample sizes. The small sample sizes often result in significant inaccuracies. I personally think that allowing the gather stats job to automatically create histograms in 10g is really bad idea.

Anyway, this is a paper that is well worth reading in my opinion. Typical Hotsos Symposium fare!

The best Oracle conference in the galaxy.

I’ve attended the last 4 years in a row and I have to say that it’s been far and away the best conference or training event that I’ve ever participated in. Add to that the extremely high quality of the people in attendance and you get an awesome event. 

The 2009 Symposium will be held in Dallas (as always), March 8-12. Chris Date will be giving the key note address. He is one of the founding fathers of Relational Databases and I am very interested to hear what he has to say. 

I must say I am extremely honored to be able to present at this years symposium, and humbled to be in the company of the other presenters. Here’s a link to the speakers page for this years event. And here’s a link to the sign up page.

Hope to see you there!