Fogo de Chão - Chicago

OK not exactly the BBQ I usually get, but some darn good meat for sure! I think I eat enough meat last night to feed a village some where. Excellent cuts and salty which I really like. Had great conversion with a SQL Server guy, whom I'm working on converting to the light side. Great place for a good dinner for those coming to Chi-Town, Fogo de Chão.

Count(*)

For years I've heard folks promote the idea that COUNT(1) is better then COUNT(*). This is not true, and I'm pretty sure this has never been true. Here is a very simple test in 11.1.0.7. If some one can prove to me that this works different in a different version of Oracle, please let me know.

Here is a count(*):



Here is count of a constant:



Both plans do exactly the same thing, a fast full index scan on the primary key column of the table. Oracle will use an index on a non-null column for the count, it doesn't have to be the primary key.

Intelligent Cursor Sharing in 11.1.0.7

I've installed 11.1.0.7 and it does seem that Intelligent Cursor Sharing is smarter in this version. When I did the testing in 11.1.0.6, I had a SQL statement that generated 10 child cursors, with 11.1.0.7 the same test only generates 6 child cursors. So it's better.

The test case is this SQL:

select /* RVD */ count(*) from big_tab where object_type = :obj_typ
/

The object type column has some good skew to it, some values only appear tens of times and others many thousands. The bind (:obj_typ) is then set to 12 different values.

With 11.1.0.6 I got the following when I looked at V$SQL:




With 11.1.0.7 I got the following when I looked at V$SQL:



I know the code here is hard to read, an important column is the first one which is the "IS_SHARABLE" column. This more of less says will this cursor be used in the future. With it set to N it wouldn't be. So in both these versions only the last two cursors are sharable (usable).

There are only two different plan for each version. An index range scan, and an index fast full scan.

For the 11.1.0.6 version the index range scan is use for child cursor 0, 2, 3, 4, 5, 6, 7, 8, and 9. Only child cursor 1 and 10 have the index fast full scan.

For the 11.1.0.7 version the index range scan is used for child cursors 0, 2, 3, 4, and 5. Again only the second and last (child number 1 and 6) have the index fast full scan.

This is definitely an improvement. I hope to see that we get to a point with a simple query like this that we only have 3 plans, cursor 0 and then the two plans that are used. Because of the nature of how Intelligent Cursor Sharing works, cursor 0 will always be an unused cursor once Intelligent Cursor Sharing kicks in.

Windows....

Last night I was trying to apply the 11.1.0.7 patch to my 11 Database on my laptop. I kept getting this error:





I checked and checked, all services were shutdown, no other programs were running. I check MetaLink for anything like this, I search with Google. Nothing.

So out of desperation I reboot my laptop. Sure enough the install runs without a hitch...

Intelligent Cursor Sharing in 11.1.0.6

In my presentation at the 2009 Hotsos Symposium I talked about a new 11g feature called Intelligent Cursor Sharing. It was originally called Adaptive Cursor Sharing. The basic premises of the new feature is that a cursor with a bind might have more then one best path depending on which value the bind happens to be. This is a classic problem that we all have had to deal with at one level or another. We have a data column with just enough skew in it that a maybe a full table scan is the right thing to do for some values and that an index scan is right for others.

A histogram will let the optimizer know about the skew but with bind peeking, that histogram will only be used at the initial hard parse. So who ever gets there first will get the right plan of them, however if the next run would be better with a different plan then the performance will suffer for queries wanting a different plan.

Intelligent Cursor Sharing to the rescue! Now with this feature you will still suffer some pain, but over time the pain will subside, that’s the plan anyway. Now the sequence of event should go like this.

Someone runs a plan with a value that is best done with an index scan for example.

The next person’s value would be best with a full table scan. This person will still suffer the pain of having the wrong plan for this initial run. But the next time they run this Oracle will know that it wasn’t such a good idea and re-parse the plan. There will now be a couple of child cursors for the statement. The idea being based on which value comes in Oracle will pick the right plan for the given values.

The problem appears to be that the optimizer doesn’t seem to know that the plan it came up with might be the same one it just used. I showed a very simple statement, ran it with 12 different values, and ended up with 11 cursors, but only 2 plans. 8 of the cursors had one plan (an index range scan), only the second one and that last one had a different plan (both were an index fast full scan). One member of the audience told me he had a client with over 32,000 child cursors! Clearly this isn’t working quite as expected.

I had a few folks tell me this is fixed in 11.1.0.7, I have just pulled that patch set down and will be testing this over the next few days.

BBQ in Rochester NY

Speaking of BBQ, the week before I was in Dallas for the Sym I was in Rochester NY. I hit two quite good BBQ places. Dinosaurs BBQ and Sticky Lips.

This is pulled pork at Dinosaurs:



And a full rack of ribs at Sticky Lips, one half with sauce and the other with a dry rub. I really like a good dry rub and this was a good one!



Both are good. I'd recommend both to anyone wanting some BBQ. Sticky Lips was a bit harder to find. But not that hard. Neither one has parking really close by. Dinosaurs is right down town on the river so if your lucky (like I was) you can parallel park on the street out front, or you parallel parking gives you the shakes, across the bridge there is a lot you can park in. Sticky Lips there is a large parking lot just a short walk behind the restaurant.

Hotsos Sym 2009

Once again a great symposium has come to an end. Outstanding speakers great friends and coworkers, super food and of course a great Pirate Party on Tuesday night!



Chris Date started us off with a drop back to academia to let us all know what we are doing wrong with our databases. Always good to get back to the Fundamentals. Just like in sports if your team doesn't do the fundamentals right, then it unlikely that your team will reach the playoffs.

I had the honor of working directly with Chris to help him set things up and keep things moving smoothly for him. We had the opportunity to just sit and chat a couple of times. He was quite interested in talking about many other things outside of the database world and that was refreshing to step out of the professional world for a moment.



I did a presentation on the new 11g feature "Intelligent Cursor Sharing", I must admit I was a bit hung over from the party the night before so I did get off to a rough start. But we had a lively conversation with about what appears to be a bug with this in 11.1.0.6, hopefully this is fixed in the .7 patch set and I will be checking that shortly.

Jonathan Lewis gave the one day of training on Thursday and it was great as always. Great talk on troubleshooting.

One disappointment was there was not much on the BBQ fount for food this year. I'll have to make a comment about that...

Do what you can to get there next year!