-- Check:
OE@salespdb>show parameter optimize

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_adaptive_features          boolean     TRUE
optimizer_adaptive_reporting_only    boolean     FALSE
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      12.1.0.2
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_inmemory_aware             boolean     TRUE
optimizer_mode                       string      ALL_ROWS
optimizer_secure_view_merging        boolean     TRUE
optimizer_use_invisible_indexes      boolean     FALSE
optimizer_use_pending_statistics     boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE
plsql_optimize_level                 integer     2

OE@salespdb>set linesize 150

OE@salespdb> SELECT product_name
    FROM   order_items o, products p
    WHERE  o.unit_price = 15
    AND    quantity > 1
   AND    p.product_id = o.product_id;

OE@salespdb>SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(FORMAT => 'ADAPTIVE'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7uakdt0mvnx6u, child number 1
-------------------------------------
SELECT product_name     FROM   order_items o, products p     WHERE
o.unit_price = 15     AND    quantity > 1    AND    p.product_id =
o.product_id


Plan hash value: 1581076404


-----------------------------------------------------------------------------------------------------------
|   Id  | Operation                      | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT               |                        |       |       |    11 (100)|          |
|     1 |  NESTED LOOPS OUTER            |                        |     5 |   360 |    11   (0)| 00:00:01 |
|  *  2 |   HASH JOIN                    |                        |     4 |   128 |     7   (0)| 00:00:01 |
|-    3 |    NESTED LOOPS                |                        |     4 |   128 |     7   (0)| 00:00:01 |
|-    4 |     STATISTICS COLLECTOR       |                        |       |       |            |          |
|  *  5 |      TABLE ACCESS FULL         | ORDER_ITEMS            |     4 |    48 |     3   (0)| 00:00:01 |
|-    6 |     TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION    |     1 |    20 |     1   (0)| 00:00:01 |
|- *  7 |      INDEX UNIQUE SCAN         | PRODUCT_INFORMATION_PK |     1 |       |     0   (0)|          |
|     8 |    TABLE ACCESS FULL           | PRODUCT_INFORMATION    |     1 |    20 |     1   (0)| 00:00:01 |
|     9 |   TABLE ACCESS BY INDEX ROWID  | PRODUCT_DESCRIPTIONS   |     1 |    40 |     1   (0)| 00:00:01 |
|  * 10 |    INDEX UNIQUE SCAN           | PRD_DESC_PK            |     1 |       |     0   (0)|          |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("I"."PRODUCT_ID"="O"."PRODUCT_ID")
   5 - filter(("O"."UNIT_PRICE"=15 AND "QUANTITY">1))
   7 - access("I"."PRODUCT_ID"="O"."PRODUCT_ID")
  10 - access("D"."PRODUCT_ID"="I"."PRODUCT_ID" AND
              "D"."LANGUAGE_ID"=SYS_CONTEXT('USERENV','LANG'))
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

37 rows selected.
==> This example shows how the optimizer can choose a different plan based on
information collected at runtime. An adaptive query plan for this statement shows two possible plans, one with a nested
loops join and the other with a hash join.
The statistics collector buffers enough rows coming from the order_items table to
determine which join method to use. If the row count is below the threshold
determined by the optimizer, then the optimizer chooses the nested loops join;
otherwise, the optimizer chooses the hash join. In this case, the row count coming from
the order_items table is above the threshold, so the optimizer chooses a hash join
for the final plan