Oracle Database 11g New Features Adaptive Cursor Sharing

Will we ever get the question of the use of bind variables over literals in our SQL answered?  Mix together the following in your Oracle database environment: large tables, skewed data, histograms (or lack of), bind variables, literals, the CURSOR_SHARING initialization parameter and bind variable peeking.  The mix can lead to uneven and poor performance.

Oracle Database 11g provides an advanced solution, another answer to the question: Adaptive Cursor Sharing.  Via feedback from the runtime engine, 11g can deliver multiple solutions (execution plans) for the same query, without code changes.

In this tutorial you will receive a brief review of the terms previously mentioned and learn what feedback-based optimization and adaptive cursor sharing is. A demonstration is included.

This free training is segmented in several separate lessons:

  1. Introduction to Oracle Adaptive Cursor Sharing (2:13)
  2. Review SQL Statement Processing (2:14)
  3. Common Optimization Problems and 9i / 10g Solutions (10:00)
  4. 11g Feedback Based Optimization (2:19)
  5. Oracle 11g Adaptive Cursor Sharing Concepts (1:58)
  6. How 11g Adaptive Cursor Sharing Works (5:12)
  7. Demonstration of 11g Adaptive Cursor Sharing (7:23) (click on video below)
  8. 9i and 10g Features with 11g ACS (1:50)
  9. Adaptive Cursor Sharing Summary (1:58)

Date: Feb 8, 2012

NOTE: Some corporate firewalls will not allow videos hosted by YouTube.



Demonstration of 11g Adaptive Cursor Sharing

We will trace a very simple query on a test table called “T,” and we’ll change the bind variable a couple of times. There are zero or only one row where column ID contains a value one and there are hundreds of thousands of rows where the value is 99.

What we’d like to do is see if ACS will kick in and give us a different plan. So let’s give it a go.

You can see I’m running version, 2.0.3, and we’ve just run our script and we have zero rows where the value is one and — I misspoke – not hundreds of thousands but 144,000 rows where the value is 99. We ran that query again. I’m going to change it the value back to 1 and run it again.

John: Right, Dave. Now, if I can interject at this point.
Dave: Please.

John: We would hope that the bind variable peeking means that it would have expanded column N to one on that first execution.

Dave: Absolutely correct.

So let’s take a look. Now, I had already gone through the process of going and getting the trace file and tracing it, and let’s just take a quick look at what happened here. So, in my first query, it used the index on the column ID and, by the way, I used TKPROF with explain feature, just to sort of show you how that can be a little bit dangerous and misleading at times – but here’s how it looked the first time.

I did get a hard parse, you can see here. And, we did use the index. There were very little rows. In this case, zero rows [returned], and so we know that peeking did occur and the index was used. The explained plan, in fact, is correct. So EXPLAIN= is a dubious option, but it worked out this time.

In the second execution of the query, we still have the same plan. The easy way to tell is to look at the plan-hash value, 1-7-8-9 and so forth, and it’s the same value. So we know we have the same plan, but we also know that we changed the value to 99. And this wasn’t the exact explain plan. You can see I changed my data a little bit since I ran this, but you can see that we got a significantly different number of rows from the index range [scan] and the table access. It still used the index, no hard parse.

Explain plan is still correct according to the row-source operation. However, the third execution, which is the second execution with the variation, the great variation of number of rows returned and selectivity, the two litmus tests to turn this thing on – so the third execution of the query, we see that the plan-hash values change. We definitely have a different plan. Something must be different.

Something forced the hard parse. What forced it was adaptive cursor sharing. So it reached out, the SQL engine reached out, gave some information back, put it in the cursor cache and Oracle said wait, the selectivity is very different, the number of rows returned is very different. This query had been marked for re-optimization.

Sure enough, we can see that we have a new plan with a full table scan. And the same kind of problem that always happens with explain-plan commands happens in my trace – when I ask for it. You can see clearly that it is incorrect. Explain doesn’t peek on the first time you use a query that has bind variables in it, nor does it peek here then say, “Oh, it’s now we’re using 99 rather than one as a value.” So just be aware of that.

Fourth and final execution, remember that we change the value back to one. Notice that the plan hash went back to the original plan hash. So we’re back to using the index, which we hoped we would be, and sure enough, the index is used to solve this particular query.

So it takes several executions in order for ACS to kick in. Now, several is a vague term, and I don’t think we can qualify what “several” means. In this case, it was so extreme that several only meant really the second execution was re-optimized with the drastically different bind variable value of 99, but it’s not going to always be this clear cut ( to satisfy the two litmus tests).

John: That was an excellent demonstration, Dave. With regard to how long it takes, if this were a production website where you’re executing the same query millions of times in an hour, my experience is it’s only going to take a few minutes for things to stabilize and then you’ll get a set of plans there,parsed, ready and waiting, that should handle virtually all the variable combinations that come in. That’s the idea, anyway.

Dave: If you know the situation is going to occur and it’s going to occur very fast – I mean, I think, John, with his experience with this in the field, sheds some very good light on it. It’s going to happen fast. If it becomes an incredibly difficult problem because it’s not happening fast enough, you know the query should be marked bind aware – there is a hint that you can use. I’ll mention those on the subsequent slide.

Copyright 2017

Free Online Registration Required

The tutorial session you want to view requires your registering with us.

It’s fast and easy, and totally FREE.

And best of all, once you are registered, you’ll also have access to all the other 100’s of FREE Video Tutorials we offer!