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) (click on video below)
  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)
  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.



Common Optimization Problems and 9i / 10g Solutions

What are the common problems with respect to optimization? Well, bind variables and data skew:

Bind variables are variables inside our SQL query, inside the predicates, and depending on the selectivity of the value of the variable, you may have a need for different plans. So the developer and the DBA were always faced with a tough choice: Use bind variables for cursor re-use and adhere to our motto of, “Parse once, execute many,” and get reduced parsing and reduced memory use, or use literals for better plans.

The 9i solution [to this problem is] bind peeking. There are some issues there, and John’s going to talk about the older solutions in a moment. So I’ll defer to John on that.

We also have a common problem of cardinality mis-estimates, particularly in intermediate results in a complex plan. So a sub-step might join together two tables or might process a view and come up with an intermediate result set that has to be joined to yet another table or view, and mis-estimates, they are particularly troublesome. 10g has a solution – SQL profiling. However, again, there are some issues there.

So let me turn this over to John for just a moment and we’ll talk about some of the previous and even a couple of the current solutions that have been introduced.

John: Yes. Thank you, Dave.

I particularly wanted to talk on this slide here because I strongly believe that adaptive cursor sharing should be a major driver for your upgrades to 11g and I happen to have a really good example of this, because the first production upgrade we ever did to 11g was purely because of adaptive cursor sharing. I can’t tell you the company, I’m afraid because it’s a reference site, but it’s a nice example that shows all of the possible problems and previous solutions.

What we see here on this slide is first dynamic sampling, introduced by 9i and not really changed since. That allows the optimizer to take a quick look at the data when it first parses a statement, take a quick look at the data and check things like cardinality and check the skew in the data, but it’s a very quick look, a very quick look indeed.

The other major feature or one major feature that came with 9i was bind variable peeking. Now, we all seem familiar with the normal parse, bind, execute, fetch cycle. If any of you out there aren’t, you better to say so now, because that’s just our assumed knowledge, though we can go through it if necessary.

So we parse before we bind, and that means that at parse time, Oracle has no idea what data it wants. So, if the data is skewed, it if it’s a non-equality predicate – you know,

SELECT * FROM emp  WHERE sal > 0

is a bit different from

SELECT * FROM emp WHERE sal > 1000000

You’re going want a very different execution plan and with bind variables because Oracle has no idea what you want.

So, with peeking, you take parse-bind-execute-fetch and for the very first execution, you reverse it to bind-parse. So Oracle expands the bind variable to see what you want, then develops an execution plan and that plan is then used for the next hundred million executions. And you better hope that that first bind variable had a value that is typical for the rest of its lifetime at that instance or at least until the plan is aged out and the statement is re-parsed.

It helps a lot. It did give Oracle a lot more information than it ever had before. Now, it could also cause a major problem, though, that if that first variable was not typical, then your plans might be completely inappropriate for a long time subsequently.

The next facility to get around that, that predates bind variable peeking, which came with 8i was the ‘stored outline’, which is just a set of hints. So, rather that hinting it manually, you instruct the optimizer to generate a set of hints that freeze a plan. So parse the statements, save as a set of hints and use that from now onwards. That got around the problem of bind variable peeking with neurotic results. You always got the same plan, not necessarily the best plan but at least it was the same plan.

Profiles then came in. Profiles giving the optimizer additional information at parse time because it can bring in to the parsing algorithm. So, unlike a stored outline, you are all doing dynamic parsing, no question about that, but with a profile, the dynamic parsing is not based purely on the DDL of the objects, the syntax, the statements, the statistics and so on and the dynamically peeked variables. It also brings in the historical information of what happened when the profile is built, and what actually were the results of running the statement through different plans improving things a great deal.

11g introduced extended statistics and this is the business of maybe there are dependencies between columns. If you correlate columns, the correlations across columns might mean that overall selectivity is not quite what you might think it is.

Now, all of these were improvements, but the example I want to give – I wish I could name the company; I’m not allowed to. It’s a poor-man’s Amazon. They’re a fairly big eCommerce site and with that eCommerce site, whenever you go to their website and you search their catalog to buy a book or buy CDs or whatever, what you’re actually doing is populating about 20 bind variables that go through to a PL/SQL procedure and then your queries run. The results come back on the website and you choose whether or not to buy the book, and that website is doing thousands of executions a second as people search for things.

Now, the environment was a 4-node RAC, and what our client found is they were getting wildly divergent performance. One day, one hour the searches would run really well. The next day they’d run appallingly. And worse still, because it was a RAC, each instance in a cluster did its own parsing, and that made things even more complicated because each instance was building up its own execution plan. And if you’re going to the website and your query happened to be routed through the connection port, one instance it would run well. If it was routed through the connection port another instance, it would run appallingly. Why? Bind variable peeking. Because what was happening is whenever we restarted an instance or flushed the shared pool, the first query to hit that instance, the bind variables were peeked and the plan will be developed and that plan would then be used for hundreds of millions executions subsequently and there was just no stability.

So what do we did to fix it? Well, first off is we almost went down [the points on] this slide:
[Dynamic Sampling, Stored Outlines, SQL Profiles}

We began by pushing up the dynamic sampling. The default in 9i was only one, which is far too low. At 10g, it equals to two. I would normally recommend pushing it up to four or five ([but] we don’t have the time now to go to why). We pushed up dynamic sampling. Then we disabled the bind variable peeking because bind variable peeking is enabled by default. You can switch it off.

Dave, if you’ve got time, can you show me a quick query, that query that will show us [the name and value of the hidden parameter that controls peeking]? Oh, there it is. He got it right there. Thanks.

If you look there, we’ve got there we got the parameter underscore optimum peek user binds to enable peeking of user binds. That hidden parameter is what came in with 9i and of course it defaults to true. Well, what we did for our client was set that to false. If you set that to false, you disable the ability to peek the bind variables at all, and that means you get stability. In effect, you’re forcing the optimizer to revert to the rule-based optimizer. Instead of doing bind parse, it does parse bind. So it’s going to be relying purely on the syntax, the DDL and to a certain extent statistics, but it can’t take account of any skews in the data.

Back to the slide, please, Dave.

So having then disabled the bind variable peeking, at least we got stability, but what we got was stability with rubbish plans. So then we have to do some serious tuning, and we try putting hints in, but tuning with hints – there’s not much fun about that. So we reverted to stored outlines. We run the application in various ways, wait until we’ve got execution plans that didn’t seem too bad for most queries and froze them. That gave us stability with plans that were not half bad, but they certainly were not half good.

We tried profiles of course, but the only real solution is to upgrade to 11g because in 11g, we have adaptive cursor sharing and that really should be the solution to all these problems. Adaptive cursor sharing should give us the ability to handle the different values for bind variables where you have skew in the data, you have non-equality predicates, predicates involving LIKE, > (greater than), BETWEEN.

Now, these non-equality predicates are non-primary keys and what we were doing with dynamic sampling and peeking and stored outlines, disabling peeking, all this, was only work-arounds and this major driver, it was our first ever production upgrade and it should be a driver for you. It may well be the driver that convinces you to do the upgrade to 11g because adaptive cursor sharing is probably the best solution Oracle has come up with yet.

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!