Your understanding is correct - when optimizer_adaptive_report_only = true
Oracle will follow the two nested loops; when it is false then at FIRST
execution Oracle can decide which of 4 possible paths to take and in your
case it took two hash joins.
Stripping out all the excess text from operations 0 to 11
The monitor displayed:
=============================================================
| Id | Operation
=============================================================
| 0 | SELECT STATEMENT
| 1 | FILTER
| 2 | NESTED LOOPS OUTER
| 3 | NESTED LOOPS OUTER
| 4 | HASH JOIN OUTER
| 5 | NESTED LOOPS OUTER
| 6 | STATISTICS COLLECTOR
| 7 | NESTED LOOPS OUTER
| 8 | HASH JOIN OUTER
| 9 | NESTED LOOPS OUTER
| 10 | STATISTICS COLLECTOR
| 11 | NESTED LOOPS OUTER
=============================================================
But when optimizer_adaptive_reporting_only = FALSE, the run-time engine
could have chosen any one of the following 4:
Two tested loops
============================================
| Id | Operation
============================================
| 0 | SELECT STATEMENT
| 1 | FILTER
| 2 | NESTED LOOPS OUTER
| 3 | NESTED LOOPS OUTER
| 5 | NESTED LOOPS OUTER
| 7 | NESTED LOOPS OUTER
| 9 | NESTED LOOPS OUTER
| 11 | NESTED LOOPS OUTER
============================================
Hash join then nested loop
============================================
| Id | Operation
============================================
| 0 | SELECT STATEMENT
| 1 | FILTER
| 2 | NESTED LOOPS OUTER
| 3 | NESTED LOOPS OUTER
| 4 | HASH JOIN OUTER
| 7 | NESTED LOOPS OUTER
| 9 | NESTED LOOPS OUTER
| 11 | NESTED LOOPS OUTER
============================================
Nested loop then hash join
============================================
| Id | Operation
============================================
| 0 | SELECT STATEMENT
| 1 | FILTER
| 2 | NESTED LOOPS OUTER
| 3 | NESTED LOOPS OUTER
| 5 | NESTED LOOPS OUTER
| 7 | NESTED LOOPS OUTER
| 8 | HASH JOIN OUTER
| 11 | NESTED LOOPS OUTER
============================================
Two hash joins
============================================
| Id | Operation
============================================
| 0 | SELECT STATEMENT
| 1 | FILTER
| 2 | NESTED LOOPS OUTER
| 3 | NESTED LOOPS OUTER
| 4 | HASH JOIN OUTER
| 7 | NESTED LOOPS OUTER
| 8 | HASH JOIN OUTER
| 11 | NESTED LOOPS OUTER
============================================
Thanks for letting me know about first_rows - I may end up doing a few
experiments with adaptive plans and that (legacy) setting if I can find
some time.
Regards
Jonathan Lewis
On Sun, 10 Oct 2021 at 20:44, Pap <oracle.developer35@xxxxxxxxx> wrote:
Thank you Jonathan.
Do you mean to say the plan which shows actually exactly the same if we
see the first 11 steps in both cases below is not actually the case, when
it's been opted in run time. Basically all of the steps 4,5,8,9 are not
opted by both of the cases. The "nested loops outer" at step-5 and step-9
are actually opted in runtime while optimizer_adaptive_reporting_only =
true, whereas step-8 and step-4 i.e. "hash join outer" are the ones which
were followed in run time by the optimizer while we had
optimizer_adaptive_reporting_only=false. But the sql monitor contains all
of those four combinations just because of its adaptive plan. Is this
understanding correct?
And yes, it's currently using the first_rows hint. I will check with
first_rows_100 hint rather than first_rows hint to see if the behaviour
changes.
