Re: question on table access by index rowid batched

From: Jonathan Lewis <jlewisoracle_at_gmail.com>
Date: Sun, 10 Oct 2021 21:29:05 +0100
Message-ID: <CAGtsp8kHewm5szdX75yHYPdQM5ZJC1j1scfcpSspe0MTiLXJ6g_at_mail.gmail.com>

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_at_gmail.com> 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.
>

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Oct 10 2021 - 22:29:05 CEST