Oracle Database 12c: Adaptive Execution Plans with Tom Kyte

Oracle Database 12c: Adaptive Execution Plans with Tom Kyte


Hi. This is Tom Kyte and I’m here today with Lance Ashdown and we’re going to be talking about Adaptive Execution Plans. In the past once the optimizer can up with a plan it pretty much stuck with it, at least for the first execution of that plan. So, if it looked at a four-table join and thought I’m going to get one row from the first table, two rows from the second table that I joined
to, ten rows from the third, and ten more rows from the fourth, it would probably come up with a plan that used indexes and nested loops joins. Now if those row counts were what actually happens in real life, then we got the right query plan. But think about what happens if we got 100,000 rows out the first line of that execution plan instead of just one. Then all of a sudden using an index and nested loops joins isn’t the right approach. We would like that query plan to change its mind and start using hash joins. That’s the goal of adaptive execution plans. The optimizer will have actually built two plans inside of one and as it executes the various row source steps in those plans it has the ability to look at the actual cardinality– the actual number of rows– that have flowed out of one step of the plan to the next and then pick the join operation it would like to perform. So in my example Of that first row source that we had expected one row to come out of and ended up with 100,000 rows the optimizer would be able to dynamically right then and there change its mind from a nested loops join to a hash join. Now I’m going to turn this over to Lance and he’s going to set up the data set and show you the circumstances under which this adaptive exeuction plan will kick in and when it won’t. Thank you, Tom. In this video I’ll demonstrate adaptive execution plans in Oracle Database 12c. The database uses adaptive plans when the OPTIMIZER_FEATURE_ENABLE initialization parameter is set to 12.1.0.1 or later and the OPTIMIZER_ADAPTIVE_REPORTING_ONLY initialization parameter is set to the default value of false. Let’s confirm that adaptive plans are enabled. They are. In our demo, we’ll start by explaining the
plans for a join of the order_items and product_information tables in the oe sample schema. Different join methods are possible for this
query. By running EXPLAIN PLAN we see the join method that the optimizer predicts will be the most optimal based on the stored optimizer statistics. As we can see in steps 1 and 2 the optimizer chooses a pair of nested loops. In a typical nested loop each row in the outer data set involves a scan of the inner data set. For this reason nested loops are best when the data sets are small. In our example as long as a large number of rows are not retrieved from the order_items table the nested loops will be an efficient way to retrieve the rows. Let’s execute the query. Now let’s look at the plan that the optimizer actually used to execute the statement. The note section tells us that the optimizer used an adaptive plan. The note also tell us that dashes in the ID column indicate steps in the plan that were considered but not used. Step 1 shows a hash join and steps 2 and 3 show nested loops. The optimizer did use the hash join step but did not use the nested loops steps. Why? Well, step 4 indicates that the optimizer used a statistics collector to make its choice between the hash join and the nested loops join. A statistics collector is a row source inserted into a plan to gather statistics at run time. During execution the collector buffered enough rows coming from the order_items table to decide which join to use. Step 5 shows us that the optimizer expected only one row from order_items but instead got 13. This means that the actual data set was 13 times greater than the expected data set. For this reason the optimizer rejected its original choice of a nested loops join in favor of a hash join. And that concludes our demo of adaptive optimization. Thanks for watching. For this demo user OE was granted privileges needed to access the V$PARAMETER view and DISPLAY_CURSOR function. Oracle recommends that you practice the principle of least privilege, granting users only the privileges needed to perform their job function. Grant special privileges carefully, for a short period of time, and for a particular purpose. For more information please see these topics in the Oracle Database online library.

Danny Hutson

2 thoughts on “Oracle Database 12c: Adaptive Execution Plans with Tom Kyte

  1. Cool, will the statistics gathered from the statistics collector affect other queries as well or are they ephemeral for the execution of the single query?

Leave a Reply

Your email address will not be published. Required fields are marked *