Adaptive Query Optimization – Adaptive Plans – A primer

Based on the available statistics on the objects, the Oracle optimizer could incorrectly estimate the cardinality of certain row sources, resulting in a sub-optimal plan. Adaptive plans are a technique used by the Oracle 12c optimizer to adapt a plan, based on information learned during execution, to pick a better plan.

For eg: If the optimizer estimated the where clause item_id = 20, is going to generate 10 rows and in reality if there were 100,000 rows in the table with item_id = 20, the optimizer might have chosen a nested loops join to this table. With adaptive plans, the optimizer gets the opportunity to switch this nested loops to a hash join, during the execution of this statement.

When the sql statement is parsed, the optimizer creates what is called a “default plan”. If there are some incorrect cardinality estimates performed by the optimizer, then it is probable that it has picked an execution plan with wrong join methods. An adaptive plan, contains multiple pre-determined “Sub plan’s”. A subplan is a portion of the plan that the optimizer can switch to as an alternative at runtime. The optimizer cannot change the whole execution plan at Execution time, it can adapt portions of it.

There are multiple “Statistics Collector’s”, inserted as rowsource’s, at key points in the execution plan.

During execution the statistics collector buffers some rows received by the subplan. Based on the information observed by the collector, the optimizer chooses a specific subplan.

Based on the observations of the statistics collectors a “Final plan” is then chosen by the optimizer and executed. After the first execution, any subsequent execution of this statement, uses this “final plan”. ie it does not incur the overhead of the statistics collection at execution time.

Let us take a look at the following sql statement and its execution plan.

SELECT product_name
FROM order_items o, product_information p
WHERE o.unit_price = 15
AND quantity > 1
AND p.product_id = o.product_id

This is a sql where the oracle 12c optimizer, decides to use Adaptive optimization.
If we use dbms_xplan.display_cursor to show the execution plan, it shows the following. This is the “final plan”

SQL_ID  971cdqusn06z9, child number 0                                                                                                                                      
SELECT product_name   FROM   order_items o, product_information p                                                                                                          
WHERE  o.unit_price = 15  AND    quantity > 1   AND    p.product_id =                                                                                                      
Plan hash value: 1553478007                                                                                                                                                
| Id  | Operation          | Name                | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                 
|   0 | SELECT STATEMENT   |                     |       |       |     7 (100)|          |                                                                                 
|*  1 |  HASH JOIN         |                     |     4 |   128 |     7   (0)| 00:00:01 |                                                                                 
|*  2 |   TABLE ACCESS FULL| ORDER_ITEMS         |     4 |    48 |     3   (0)| 00:00:01 |                                                                                 
|   3 |   TABLE ACCESS FULL| PRODUCT_INFORMATION |     1 |    20 |     1   (0)| 00:00:01 |                                                                                 
Outline Data                                                                                                                                                               
      FULL(@"SEL$1" "P"@"SEL$1")                                                                                                                                           
      USE_HASH(@"SEL$1" "P"@"SEL$1")                                                                                                                                       
      FULL(@"SEL$1" "O"@"SEL$1")                                                                                                                                           
      LEADING(@"SEL$1" "O"@"SEL$1" "P"@"SEL$1")                                                                                                                            
Predicate Information (identified by operation id):                                                                                                                        
   1 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")                                                                                                                           
   2 - filter(("O"."UNIT_PRICE"=15 AND "QUANTITY">1))                                                                                                                      
   - this is an adaptive plan           

Since the Notes section says that this is an adaptive plan, we can use dbms_xplan to show the “full plan”, which includes the “default plan” and the “sub plan’s”
We have to use the ADAPTIVE keyword in dbms_xplan to display the ‘full plan’.


SQL_ID  971cdqusn06z9, child number 0                                                                                                                                      
SELECT product_name   FROM   order_items o, product_information p                                                                                                          
WHERE  o.unit_price = 15  AND    quantity > 1   AND    p.product_id =                                                                                                      
Plan hash value: 1553478007                                                                                                                                                
|   Id  | Operation                     | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |                   
|     0 | SELECT STATEMENT              |                        |      1 |        |     13 |00:00:00.01 |      24 |     20 |       |       |          |                   
|  *  1 |  HASH JOIN                    |                        |      1 |      4 |     13 |00:00:00.01 |      24 |     20 |  2061K|  2061K|  445K (0)|                   
|-    2 |   NESTED LOOPS                |                        |      1 |      4 |     13 |00:00:00.01 |       7 |      6 |       |       |          |                   
|-    3 |    NESTED LOOPS               |                        |      1 |      4 |     13 |00:00:00.01 |       7 |      6 |       |       |          |                   
|-    4 |     STATISTICS COLLECTOR      |                        |      1 |        |     13 |00:00:00.01 |       7 |      6 |       |       |          |                   
|  *  5 |      TABLE ACCESS FULL        | ORDER_ITEMS            |      1 |      4 |     13 |00:00:00.01 |       7 |      6 |       |       |          |                   
|- *  6 |     INDEX UNIQUE SCAN         | PRODUCT_INFORMATION_PK |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |                   
|-    7 |    TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION    |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |                   
|     8 |   TABLE ACCESS FULL           | PRODUCT_INFORMATION    |      1 |      1 |    288 |00:00:00.01 |      17 |     14 |       |       |          |                   
Predicate Information (identified by operation id):                                                                                                                        
   1 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")                                                                                                                           
   5 - filter(("O"."UNIT_PRICE"=15 AND "QUANTITY">1))                                                                                                                      
   6 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")                                                                                                                           
   - this is an adaptive plan (rows marked '-' are inactive)        

In the plan we can see the statistics collector row source. We can see that the Index access “Access Method” was evaluated and discarded by the optimizer. You can see that the optimizer estimated 1 row from product_information and actually it returned 288 rows. The optimizer calcluates an inflection point after which the nested loops operation becomes less efficient and chooses the full scans followed by the hash join.

As indicated in the Note section, the lines that have a – at the beginning of the line, are inactive in the “Final Plan”.

Let us check the flags from v$sql

select is_reoptimizable,is_resolved_adaptive_plan
from v$sql where
sql_id = '971cdqusn06z9'

- -

The output indicates that the statement is re-optimizable and that it was resolved by using an adaptive plan.

Every step in the ‘full plan’ is stored in v$sql_plan, the information regarding which steps are ‘on’ or ‘off’ in the ‘final plan’ is stored in the column other_xml, under the xml element, display_map.

This is how it looks for sqlid 971cdqusn06z9


The op= property in the xml, maps to the id column in v$sql_plan (ie the step number). The skp= property indicates whether the step is active in the final plan or not. A value of 1 indicates that, that row is skipped in the final plan. You can display it in a row format with the following sql

select * from
  (SELECT dispmap.*
  FROM v$sql_plan,
    XMLTABLE ( '/other_xml/display_map/row' passing XMLTYPE(other_xml) COLUMNS
      op  NUMBER PATH '@op',    
      dis NUMBER PATH '@dis',   
      par NUMBER PATH '@par',   
      prt NUMBER PATH '@prt',   
      dep NUMBER PATH '@dep',   
      skp NUMBER PATH '@skp' )  
  AS dispmap
  WHERE sql_id     = '&sql_id'
  AND child_number = &sql_child
  AND other_xml   IS NOT NULL

      OP        DIS        PAR        PRT        DEP        SKP
---------- ---------- ---------- ---------- ---------- ----------
         1          1          0          0          1          0
         2          1          1          0          1          1
         3          1          1          0          1          1
         4          1          1          0          1          1
         5          2          1          0          2          0
         6          2          1          0          1          1
         7          2          1          0          1          1
         8          3          1          0          2          0

This output can be joined with v$sql_plan to produce an output of the “final plan” .

  NVL(map.dis,0) id,
  map.par         parent,
  map.dep         depth,
  lpad(' ',map.dep*1,' ')
  || sp.operation AS operation,
FROM v$sql_plan sp,
  (SELECT dispmap.*
  FROM v$sql_plan,
    XMLTABLE ( '/other_xml/display_map/row' passing XMLTYPE(other_xml) COLUMNS
      op  NUMBER PATH '@op',
      dis NUMBER PATH '@dis',
      par NUMBER PATH '@par',
      prt NUMBER PATH '@prt',
      dep NUMBER PATH '@dep',
      skp NUMBER PATH '@skp' )
  AS dispmap
  WHERE sql_id     = '&sql_id'
  AND child_number = &sql_child
  AND other_xml   is not null
  ) map
WHERE             = map.op(+)
AND sp.sql_Id           = '&sql_id'
AND sp.child_number     = &sql_child
AND nvl(map.skp,0)     != 1
ORDER BY nvl(map.dis,0)

If you look at the 10053 trace for the sql, you can see how the optimizer calculates an inflection point.


What’s new in 12c

Adaptive Plans Inflection points

Adaptive Dynamic Sampling – Oracle 12c – Some notes

Adaptive Query optimization, was a set of new capabilities, introduced in oracle 12c, to allow the optimizer to discover additional information regarding statistics and make run-time adjustments to execution plans to make them better. This is a major change in the optimizer behaviour from 11g.

I would recommend anyone who is planning an upgrade to 12c, that they make themselves familiar with the following white papers from oracle.

Understanding Optimizer Statistics with Oracle 12c

Best practices for gathering statistics with Oracle 12c

What to expect with the optimizer, with Oracle 12c.

In this article, i want to talk about some of the important concepts behind Dynamic Statistics, which is one of the components of Adaptive query optimizations.

In the section’s that are following, i show some commands to turn some of these features off. I want to be clear that I am not recommending that you turn anything off. I would prefer that customer’s adopt these new features,that are designed to improve the execution plans. Also keep in mind that the following are accurate (Afaik) on as off the time of writing of this article, and are subject to change.

Dynamic statistics has two interesting effects, that DBA’s tend to notice initially.
– Ever so slightly, longer parse times for queries.
– Execution plan changes (Compared to what they had before upgrading) for the same query. (Sometimes unwelcome changes, especially for deployments that value stability more than performance gains).

Dynamic sampling was introduced by oracle in 9i Release 2 to improve the optimizer’s functioning. The amount of dynamic sampling done , and when it kicks in, is controlled by the parameter optimizer_dynamic_sampling. With 12c there is a new concept of Adaptive Dynamic sampling. Adaptive Dynamic Sampling is different from the pre-12c traditional dynamic sampling, in the following aspects.

– Adaptive Dynamic sampling could kick in even when optimizer_dynamic_sampling is set to 2.

– Especially for parallel queries on large tables.

– Adaptive Dynamic sampling kicks in when optimizer_dynamic_sampling is set to 11.
– Adaptive Dynamic sampling issues more queries than dynamic sampling used to do.

– The traditional dynamic sampling, used to issue, atmost, 1 query per table SQL.
– It is not uncommon to see 10’s of Adaptive Dynamic sampling queries being issued for a     single SQL. Multiple dynamic sampling queries for the same table.(It dpends on the volume of data, number of indexed columns, complexity of     the predicates etc).
– If you run a 10046 trace on the query, you will see a lot of additional queries in there that     have the DS_SVC hint in them, which are the queries issued by the Adaptive Dynamic             Sampling.

Setting OPTIMIZER_ADAPTIVE_FEATURES=FALSE does NOT turn off Adaptive Dynamic Sampling.

You can set Optimizer_Dynamic_Sampling = 0 to turn Adaptive Dynamic Sampling off. However this would be like throwing the baby out with the bath water. Setting Optimizer_Dynamic_Sampling=0 completely sets dynamic sampling off (Including the old style pre-12c dynamic sampling).

You can do an ALTER SESSION SET “_fix_control”=’7452863:0′; to turn just Adaptive Dynamic Sampling off, if you so desire.

Adaptive Dynamic Sampling also uses oracle Results Cache. If results cache is enabled in the database (usually by setting result_cache_max_size to a value > 0), then Adaptive dynamic sampling uses the results cache to store the results that it queries up. This is done so that, if there are multiple query parses that have to do dynamic sampling, and they are looking at the same data, the optimizer can just look that value up from the results cache (As opposed to having to query the tables again and again).

If you have a system, that has a lot of hard parses (Due to not using bind variables), you could pottentialy see latch free waits on “Results Cache: rc latch”. Please refer to Mos note 2002089.1, that suggest’s setting “_optimizer_ads_use_result_cache” = FALSE; to work around this. Keep in mind that setting this parameter does not prevent the optimizer from using Adaptive dynamic sampling. All it does it prevent the Adaptive dynamic sampling from using the results cache.

The following Mos note’s and a presentation from Trivadis, have a lot of great information in this regard.

Automatic Dynamic Statistics (Doc ID 2002108.1)

Different Level for Dynamic Statistics (Dynamic Sampling) used than the Level Specified (Doc ID 1102413.1)

High Waits for ‘cursor: pin S wait on X’ due to Dynamic Sampling Against Parallel Queries (Doc ID 2006145.1)

Adaptive Dynamic Sampling – Trivadis