Oracle 23c New Features for DBA'sIf you have read the previous post on automatic query reoptimation, this post is the continuation of the same, by looking them closely.
All the examples used are from oracle white paper.
Parameters that influence optimizer to use adaptive features
SQL> show parameter adaptive NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_adaptive_features boolean FALSE optimizer_adaptive_reporting_only boolean FALSE Note 1:- optimizer_adaptive_features = True , Enables new 12c adaptive features optimizer_adaptive_reporting_only = True, Rather implementing the adaptive features they may just report you. Note 2:- 1) Two new columns in v$sql is_resolved_adaptive_plan = Null means not an adaptive plan is_reoptimizable = N Means optimizer see there is no optimization requires since the estimation and actual rows matches Note 3:- V$shared_sql_cursor = used_feedback_stats
We will see adaptive execution plans in this post how does this works. With combination of those parameters above.
Case 1: Switch off features (both params false)
select * from table(dbms_xplan.display_cursor(NULL,NULL,format=>'ADAPTIVE +report')); SQL_ID 69ky4556y8shw, child number 0 ------------------------------------- SELECT o.order_id, v.product_name FROM orders o, ( SELECT order_id, product_name FROM order_items o, product_information p WHERE p.product_id = o.product_id AND list_price < 50 AND min_price < 100 ) v WHERE o.order_id = v.order_id Plan hash value: 173749393 -------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | O/1/M | -------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 7 (100)| | | | | | 1 | NESTED LOOPS | | 196 | 7840 | 7 (0)| 00:00:01 | | | | |* 2 | NESTED LOOPS | | 196 | 7056 | 7 (0)| 00:00:01 | 1321K| 1321K| 2/0/0| |* 3 | TABLE ACCESS FULL | PRODUCT_INFORMATION | 55 | 1540 | 5 (0)| 00:00:01 | | | | | 4 | INDEX FAST FULL SCAN| ORDER_ITEMS_UK | 665 | 5320 | 2 (0)| 00:00:01 | | | | |* 5 | INDEX UNIQUE SCAN | ORDER_PK | 1 | 4 | 0 (0)| | | | | -------------------------------------------------------------------------------------------------------------------------- SQL> select sql_id,plan_hash_value,child_number,is_resolved_adaptive_plan, is_reoptimizable from v$sql where sql_id='9vc2g7duayw4t'; SQL_ID PLAN_HASH_VALUE CHILD_NUMBER I I ------------- --------------- ------------ - - 69ky4556y8shw 173749393 0 N SQL> Notes:- Optimizer uses nested loops and not an adaptive plan which is normal in case.
Case 2:- Turn on reporting mode only
SQL> alter system set optimizer_adaptive_features=true;
System altered.
SQL> alter system set optimizer_adaptive_reporting_only=true;
System altered.
SQL> show parameter adaptive
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_adaptive_features boolean TRUE
optimizer_adaptive_reporting_only boolean TRUE
parallel_adaptive_multi_user boolean TRUE
SQL>
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,format=>'ADAPTIVE ALL ALLSTATS'));
SQL_ID 69ky4556y8shw, child number 0
-------------------------------------
SELECT o.order_id, v.product_name FROM orders o, ( SELECT
order_id, product_name FROM order_items o,
product_information p WHERE p.product_id = o.product_id AND
list_price < 50 AND min_price < 100 ) v WHERE o.order_id =
v.order_id
Plan hash value: 35479787
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
| 1 | NESTED LOOPS | | 196 | 7840 | 7 (0)| 00:00:01 |
| * 2 | HASH JOIN | | 196 | 7056 | 7 (0)| 00:00:01 |
|- 3 | NESTED LOOPS | | 196 | 7056 | 7 (0)| 00:00:01 |
|- 4 | STATISTICS COLLECTOR | | | | | |
| * 5 | TABLE ACCESS FULL | PRODUCT_INFORMATION | 55 | 1540 | 5 (0)| 00:00:01 |
|- 6 | TABLE ACCESS BY INDEX ROWID BATCHED| ORDER_ITEMS | 4 | 32 | 2 (0)| 00:00:01 |
|- * 7 | INDEX RANGE SCAN | ITEM_PRODUCT_IX | 665 | | 2 (0)| 00:00:01 |
| 8 | INDEX FAST FULL SCAN | ORDER_ITEMS_UK | 665 | 5320 | 2 (0)| 00:00:01 |
| * 9 | INDEX UNIQUE SCAN | ORDER_PK | 1 | 4 | 0 (0)| |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
5 - filter(("LIST_PRICE"<50 AND "MIN_PRICE"<100))
7 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
9 - access("O"."ORDER_ID"="ORDER_ID")
Note
-----
- this is an adaptive plan (rows marked '-' are inactive)
SQL> select sql_id,plan_hash_value,child_number,is_resolved_adaptive_plan,is_reoptimizable
from v$sql where plan_hash_value=35479787;
SQL_ID PLAN_HASH_VALUE CHILD_NUMBER I I
------------- --------------- ------------ - -
69ky4556y8shw 35479787 0 N N
Notes:-
1) The plan is still not adaptive (N) since the reporting mode is on.
2) In execution plan the execution ID, 3,4,6,7 will be discarded once the final plan is executed,
- are inactive execution ID
3) Also observe there is a new thing called statistics collector, this gather information about execution and
buffers a portion of rows coming into the subplan. In our case initially a nested loops has been choosen (ID 3) ,
where the statistics collectors see there is a difference in the rows that coming in,
and hence chooses a different access method, from nested loops to hash join. Once this is collected statistics collector
will stop monitoring.
Case 3: Adaptive features true and reporting mode is false i.e execute the final plan
SQL_ID 9pamqb0nqdrpq, child number 0
-------------------------------------
SELECT o.order_id, v.product_name FROM orders o, ( SELECT
order_id, product_name FROM order_items o,
product_information p WHERE p.product_id = o.product_id AND
list_price < 50 AND min_price < 100 ) v WHERE o.order_id =
v.order_id
Plan hash value: 35479787
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
| 1 | NESTED LOOPS | | 196 | 7840 | 7 (0)| 00:00:01 |
| * 2 | HASH JOIN | | 196 | 7056 | 7 (0)| 00:00:01 |
|- 3 | NESTED LOOPS | | 196 | 7056 | 7 (0)| 00:00:01 |
|- 4 | STATISTICS COLLECTOR | | | | | |
| * 5 | TABLE ACCESS FULL | PRODUCT_INFORMATION | 55 | 1540 | 5 (0)| 00:00:01 |
|- 6 | TABLE ACCESS BY INDEX ROWID BATCHED| ORDER_ITEMS | 4 | 32 | 2 (0)| 00:00:01 |
|- * 7 | INDEX RANGE SCAN | ITEM_PRODUCT_IX | 665 | | 2 (0)| 00:00:01 |
| 8 | INDEX FAST FULL SCAN | ORDER_ITEMS_UK | 665 | 5320 | 2 (0)| 00:00:01 |
| * 9 | INDEX UNIQUE SCAN | ORDER_PK | 1 | 4 | 0 (0)| |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
5 - filter(("LIST_PRICE"<50 AND "MIN_PRICE"<100))
7 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
9 - access("O"."ORDER_ID"="ORDER_ID")
Note
-----
- this is an adaptive plan (rows marked '-' are inactive)
SQL> select sql_id,plan_hash_value,child_number,is_resolved_adaptive_plan,is_reoptimizable from v$sql where sql_id='69ky4556y8shw';
SQL_ID PLAN_HASH_VALUE CHILD_NUMBER I I
------------- --------------- ------------ - -
69ky4556y8shw 35479787 0 Y N
SQL>
Notes:
1) As you see now the plan is adaptive
Case 4: Adaptive plans with parallelism Hybrid hash
In parallel execution, optimizer uses broadcast and HASH method to distribute the work between PQ's Where in 12c, using Hybrid Hash mechanism the optimizer can defer its distribution until execution and distributes based on the rows that monitors by statistics collector on fly, taking into consideration that distribution can switch from Broadcast to HASH Method. Also it determines how much parallelism can be used. Hybrid Hash Execution in 12c. PLAN_TABLE_OUTPUT -------------------------------------- Plan hash value: 948598645 ------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 27 | 621 | 4 (0)| 00:00:01 | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10003 | 27 | 621 | 4 (0)| 00:00:01 | Q1,03 | P->S | QC (RAND) | | 3 | HASH GROUP BY | | 27 | 621 | 4 (0)| 00:00:01 | Q1,03 | PCWP | | | 4 | PX RECEIVE | | 27 | 621 | 4 (0)| 00:00:01 | Q1,03 | PCWP | | | 5 | PX SEND HASH | :TQ10002 | 27 | 621 | 4 (0)| 00:00:01 | Q1,02 | P->P | HASH | | 6 | HASH GROUP BY | | 27 | 621 | 4 (0)| 00:00:01 | Q1,02 | PCWP | | |* 7 | HASH JOIN | | 106 | 2438 | 4 (0)| 00:00:01 | Q1,02 | PCWP | | | 8 | PX RECEIVE | | 27 | 432 | 2 (0)| 00:00:01 | Q1,02 | PCWP | | | 9 | PX SEND HYBRID HASH | :TQ10000 | 27 | 432 | 2 (0)| 00:00:01 | Q1,00 | P->P | HYBRID HASH| | 10 | PX BLOCK ITERATOR | | 27 | 432 | 2 (0)| 00:00:01 | Q1,00 | PCWC | | | 11 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 | Q1,00 | PCWP | | | 12 | PX RECEIVE | | 107 | 749 | 2 (0)| 00:00:01 | Q1,02 | PCWP | | | 13 | PX SEND HYBRID HASH (SKEW)| :TQ10001 | 107 | 749 | 2 (0)| 00:00:01 | Q1,01 | P->P | HYBRID HASH| | 14 | PX BLOCK ITERATOR | | 107 | 749 | 2 (0)| 00:00:01 | Q1,01 | PCWC | | | 15 | TABLE ACCESS FULL | EMPLOYEES | 107 | 749 | 2 (0)| 00:00:01 | Q1,01 | PCWP | | -------------------------------------------------------------------------------------------------------------------------------
Next Post, Automatic Reoptimization
-Thanks
Geek DBA
Follow Me!!!