Kerry Osborne's Oracle Blog

This is the second post on follow up questions from the Redgate webinar I did on 12c Adaptive Optimization. The first post is here: 12c Adaptive Optimization – Part 1. Since there were several comments and questions about hints and how they interact with Adaptive Plans, I decided to limit this 2nd post to that topic.

Q: Regarding turning off the adaptive optimization (particularly adaptive joins), will there also be a hint to disable it for a particular SQL?
Q: can we pick and choose SQL’s not to run this collector for

A: There are no specific hints to enable or disable Adaptive Plans as of 12.1.0.1. However, the OPT_PARAM hint does work with both the OPTIMIZER_ADAPTIVE_FEATURES parameter and the “_optimizer_adaptive_plans” parameter.

Here’s an example:

SYS@db12c1> -- statement that wants to generate an adaptive plan
SYS@db12c1> select product_name
  2  from oe.order_items o, oe.product_information p
  3  where o.unit_price=15 and o.quantity > 1
  4  and o.product_id = p.product_id
  5  /

PRODUCT_NAME
--------------------------------------------------
Screws 
Screws 
Screws 
Screws 
Screws 
Screws 
Screws 
Screws 
Screws 
Screws 
Screws 
Screws 
Screws 

13 rows selected.

SYS@db12c1> @prev_sql

SQL_ID         CHILD  PLAN_HASH  EXECS  AVG_ETIME SQL_TEXT
------------- ------ ---------- ------ ---------- ----------------------------------------------------------------------
3ycnqgx5nc8nn      0 1553478007      1        .00 select product_name from oe.order_items o, oe.product_information p wh

SYS@db12c1> @dplan_adaptive
Enter value for sql_id: 3ycnqgx5nc8nn
Enter value for child_no: 

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3ycnqgx5nc8nn, child number 0
-------------------------------------
select product_name from oe.order_items o, oe.product_information p
where o.unit_price=15 and o.quantity > 1 and o.product_id = 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 |   NESTED LOOPS                |                        |       |       |            |          |
|-    3 |    NESTED LOOPS               |                        |     4 |   128 |     7   (0)| 00:00:01 |
|-    4 |     STATISTICS COLLECTOR      |                        |       |       |            |          |
|  *  5 |      TABLE ACCESS STORAGE FULL| ORDER_ITEMS            |     4 |    48 |     3   (0)| 00:00:01 |
|- *  6 |     INDEX UNIQUE SCAN         | PRODUCT_INFORMATION_PK |     1 |       |     0   (0)|          |
|-    7 |    TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION    |     1 |    20 |     1   (0)| 00:00:01 |
|     8 |   TABLE ACCESS STORAGE FULL   | PRODUCT_INFORMATION    |     1 |    20 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("O"."PRODUCT_ID"="P"."PRODUCT_ID")
   5 - storage(("O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1))
       filter(("O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1))
   6 - access("O"."PRODUCT_ID"="P"."PRODUCT_ID")

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)


Reoptimized plan:
-----------------
This cursor is marked for automatic reoptimization.  The plan that is
expected to be chosen on the next execution is displayed below.

Plan hash value: 1553478007

--------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                     |    13 |   416 |     8   (0)| 00:00:01 |
|*  1 |  HASH JOIN                 |                     |    13 |   416 |     8   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS STORAGE FULL| ORDER_ITEMS         |    13 |   156 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS STORAGE FULL| PRODUCT_INFORMATION |   288 |  5760 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("O"."PRODUCT_ID"="P"."PRODUCT_ID")
   2 - storage("O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1)
       filter("O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1)

Note
-----
   - this is an adaptive plan

60 rows selected.

SYS@db12c1> -- so the previous statement used an adaptive plan picking a HJ over the NLJ
SYS@db12c1>
SYS@db12c1> -- now let's turn off adaptive plans via the OPT_PARAM hint
SYS@db12c1> -- (set _optimizer_adaptive_plans or optimizer_adaptive_features to false)
SYS@db12c1>
SYS@db12c1> select /*+ OPT_PARAM('_optimizer_adaptive_plans','false') */ product_name
  2  from oe.order_items o, oe.product_information p
  3  where o.unit_price=15 and o.quantity > 1
  4  and o.product_id = p.product_id
  5  /

PRODUCT_NAME
--------------------------------------------------
Screws 
Screws 
Screws 
Screws 
Screws 
Screws 
Screws 
Screws 
Screws 
Screws 
Screws 
Screws 
Screws 

13 rows selected.

SYS@db12c1> @x

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  04g4xyu3788qm, child number 0
-------------------------------------
select /*+ OPT_PARAM('_optimizer_adaptive_plans','false') */
product_name from oe.order_items o, oe.product_information p where
o.unit_price=15 and o.quantity > 1 and o.product_id = p.product_id

Plan hash value: 1255158658

-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                        |       |       |     7 (100)|          |
|   1 |  NESTED LOOPS                |                        |       |       |            |          |
|   2 |   NESTED LOOPS               |                        |     4 |   128 |     7   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS STORAGE FULL | ORDER_ITEMS            |     4 |    48 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PRODUCT_INFORMATION_PK |     1 |       |     0   (0)|          |
|   5 |   TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION    |     1 |    20 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - storage(("O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1))
       filter(("O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1))
   4 - access("O"."PRODUCT_ID"="P"."PRODUCT_ID")


26 rows selected.

SYS@db12c1> -- So the plan has reverted to the NL Join and is not marked as adaptive

So, even though there is no specific hint at this point, the OPT_PARAM hint can be used to control this behavior on a statement by statement basis.

Q: how does AP (Adaptive Plans) treat query HINTS?
Q: Does adaptive join selection potentially override query hints?

A: As to whether AP can override hints, it does not appear that it can. If you specify a join method with a valid hint, a 10053 (Wolfgang) trace will show that AP’s are not used due to the hint. For example, if you use a hint to specify a nested loop join, the optimizer will not allow AP to override that directive and the 10053 trace will show this behavior.

Here’s an example:

SSYS@db12c1> select product_name
  2  from oe.order_items o, oe.product_information p
  3  where o.unit_price=15 and o.quantity > 1
  4  and o.product_id = p.product_id
  5  /

PRODUCT_NAME
--------------------------------------------------
Screws 
Screws 
Screws 
Screws 
Screws 
Screws 
Screws 
Screws 
Screws 
Screws 
Screws 
Screws 
Screws 

13 rows selected.

SYS@db12c1> @x

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3ycnqgx5nc8nn, child number 0
-------------------------------------
select product_name from oe.order_items o, oe.product_information p
where o.unit_price=15 and o.quantity > 1 and o.product_id = p.product_id

Plan hash value: 1553478007

--------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                     |       |       |     8 (100)|          |
|*  1 |  HASH JOIN                 |                     |    13 |   416 |     8   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS STORAGE FULL| ORDER_ITEMS         |    13 |   156 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS STORAGE FULL| PRODUCT_INFORMATION |   288 |  5760 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("O"."PRODUCT_ID"="P"."PRODUCT_ID")
   2 - storage(("O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1))
       filter(("O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1))

Note
-----
   - this is an adaptive plan


27 rows selected.

SYS@db12c1> -- from 10053
SYS@db12c1> !grep -i inflection adapt*trc
Searching for inflection point (join #1) between 0.00 and 12.76
AP: Computing costs for inflection point at min value 0.00
DP: Using binary search for inflection point search
DP: Costing Nested Loops Join for inflection point at card 0.00
DP: Costing Hash Join for inflection point at card 0.00
AP: Computing costs for inflection point at max value 12.76
DP: Costing Nested Loops Join for inflection point at card 12.76
DP: Costing Hash Join for inflection point at card 12.76
AP: Searching for inflection point at value 1.00
DP: Costing Nested Loops Join for inflection point at card 6.38
DP: Costing Hash Join for inflection point at card 6.38
AP: Searching for inflection point at value 6.38
DP: Costing Nested Loops Join for inflection point at card 3.19
DP: Costing Hash Join for inflection point at card 3.19
AP: Searching for inflection point at value 3.19
DP: Costing Nested Loops Join for inflection point at card 4.78
DP: Costing Hash Join for inflection point at card 4.78
AP: Searching for inflection point at value 4.78
DP: Costing Nested Loops Join for inflection point at card 5.58
DP: Costing Hash Join for inflection point at card 5.58
DP: Costing Nested Loops Join for inflection point at card 5.58
DP: Found point of inflection for NLJ vs. HJ: card = 5.58

SYS@db12c1> -- now with valid join hint
SYS@db12c1> select /*+ leading(o) use_nl(p) */ product_name
  2  from oe.order_items o, oe.product_information p
  3  where o.unit_price=15 and o.quantity > 1
  4  and o.product_id = p.product_id
  5  /

PRODUCT_NAME
--------------------------------------------------
Screws 
Screws 
Screws 
Screws 
Screws 
Screws 
Screws 
Screws 
Screws 
Screws 
Screws 
Screws 
Screws 

13 rows selected.

SYS@db12c1> @x

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  bytr421c0c2n7, child number 0
-------------------------------------
select /*+ leading(o) use_nl(p) */ product_name from oe.order_items o,
oe.product_information p where o.unit_price=15 and o.quantity > 1 and
o.product_id = p.product_id

Plan hash value: 1255158658

-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                        |       |       |    16 (100)|          |
|   1 |  NESTED LOOPS                |                        |       |       |            |          |
|   2 |   NESTED LOOPS               |                        |    13 |   416 |    16   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS STORAGE FULL | ORDER_ITEMS            |    13 |   156 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PRODUCT_INFORMATION_PK |     1 |       |     0   (0)|          |
|   5 |   TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION    |     1 |    20 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - storage(("O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1))
       filter(("O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1))
   4 - access("O"."PRODUCT_ID"="P"."PRODUCT_ID")


26 rows selected.

SYS@db12c1> !grep -i inflection /u01/app/oracle/diag/rdbms/db12c/db12c1/trace/db12c1_ora_32529.trc

SYS@db12c1> !grep AP: non-adapt*.trc
AP: Adaptive joins bypassed for table P @ SEL$1 due to join method is hinted
AP: Adaptive joins bypassed for table P @ SEL$1 due to join method is hinted

So it appears that AP will not override valid hints (as evidenced by the lines in the 10053 trace file showing that “Adaptive join bypassed … due to join method is hinted”). Keep in mind though that this is only one test case, so it’s possible that in some circumstances AP could override hints, but now you know what to look for to validate. 🙂

Here are a few more hint related questions:

Q: Does adaptive optimization reduce the need for using hints
A: Maybe. If you are hinting to avoid short comings in the optimizer where it chooses the wrong join method, you might not need to do that any more. Likewise, if you are hinting to force a particular distribution method for PX statements, you may not need to do that any more. It’s certainly a step in the right direction.

Q: Can we force dynamic sampling for a statement, regardless of what Oracle thinks it should do?
A: Yes, the DYNAMIC_SAMPLING hint has been available since 9.2.

Q: If we had SQL Plan Baseline set for a paticular SQL in version 11g and we were to upgrade to 12c version….would SQL Baseline be used or this adaptive plans are used ?
A: The hints in the Baseline would be used and should reproduce the 11g plan. See the example above where valid hints disable AP.

Q: Is there a way to grab a previous plan (good) using the profile technique and assign it to the current statement that changed the exec plan which is bad? So far I have been doing this manually using your scripts.
A: Yes – Profiles are just a collection of hints that get applied to a statement. So they can be used to control plans even if AP is enabled.

Q: How does this play with SQL Plan Management.. ?
A: Final plans can be captured and baselines created for them. This feature behaves as expected. Subsequent parses will try to reproduce final plan (using hints in the baseline if necessary).

So that’s it for the hint related questions. The final post in this series will cover the remainder of the questions.