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.