Re: question on table access by index rowid batched

It's interesting to note that the SQL Monitor report says DONE (ALL_ROWS) even when the first_rows_N optimisation is set. (Perhaps it means it's reporting after all the rows have been returned, of course.)

I've spent too much time already looking at this and explaining details (some of which is on my blog), so I'm not going to chase it any further.

An important detail you need to pursue before anything else is why the optimizer can estimate a cardinality of 1 at operation 28 by doing a hash join (outer) between two rowsources of 2M rows each. I had assumed initially that this mighe be a side effect of a first_rows_1 or first_rows optimisation, but it clearly isn't give that it still appears at first_rows_100.

You might try first_rows_1 (more for curiosity than because it's a sensible option). One extra little detail I found with a small example of adaptive optimisation was that when the estimated number of rows from a join was more than the N in a first_rows_N optimisation (or first_rows(N) hint), then the optimizer trace reported:

"AP: Adaptive joins bypassed for query block SEL$xxxxxxxx due to query block optimized with first k rows"

Whether or not this observation is relevant is something I leave to you to look at.

Regards
Jonathan Lewis

On Mon, 11 Oct 2021 at 13:08, Pap <oracle.developer35_at_gmail.com> wrote:

> Thank You Jonathan.
>
> I also tried to see how these options behave with the first_rows(n) hint.
> I tried collecting sql monitors with first_rows(100) hints for
> optimizer_adaptive_report_only as false and true both. And another case
> with no_adaptive_plan hint. As attached, I see in all of those cases the
> query opting for the blocking operation i.e. hash_join_outer atleast once
> in the path and thus it delays the return of initial ~100 rows.
>
>
>
>
> On Mon, Oct 11, 2021 at 1:59 AM Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>>
>> 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