Examine the parameter for your database instance: You generated the execution plan for the following query in the plan table and noticed that the nested loop join was done. After actual execution of…

Starting in Oracle 12c, Adaptive Plans (part of Adaptive Query Optimization) let the optimizer produce a default plan containing embedded statistics collector operators at key join points. During execution, these collectors measure actual row counts flowing through the plan and, if cardinality differs significantly from estimates, the optimizer switches to an alternative sub-plan - for example replacing a nested loop with a hash join - without requiring a re-parse. This is precisely why EXPLAIN PLAN (which shows the compile-time default) can display a nested loop while the actual execution uses a hash join.

Automatic dynamic sampling refines cardinality estimates at parse time before generating the plan, so it cannot cause a runtime switch between two join methods during a single execution.

Cardinality feedback from re-optimization feeds corrected row count estimates back into the optimizer only for subsequent executions of the same query, not during the first execution described here.

Extended statistics improve join-column group cardinality estimates at parse time, but they do not create a mechanism for the optimizer to change join methods between the plan display and actual execution.