MIME-Version: 1.0
References: <CAEjw_fjraKwNds2y+Ftzc5V0CjP_-SE0dVzaBEmvCcrpA4i=nQ@mail.gmail.com>
 <CAEjw_fi9J0ZO8OAcvhzZmNFKp+rUx9kvZ2vry5X8-qPkdXeUqA@mail.gmail.com>
 <CAGtsp8=TPb1Nn=C_x7_MG9wFw9NUc74LCGPgrDxZxgCpoE+4mA@mail.gmail.com>
 <CA+riqSVaqDFyabwGiVJOE7WHDbXKDaahFdQOz6-J_ZoH3sBuDA@mail.gmail.com>
 <CAEjw_fgLvtZP5zv+xCf_+zmsFUydU5DAE1rQ3WWAuzZVd+ZWbg@mail.gmail.com>
 <CAGtsp8mOX+kVxhzCj+WBcia6Wj-6TN5kXpScZZFO8M2sQJ-d7w@mail.gmail.com>
 <CAEjw_fhaB-2XFOnf=NpT5vmnFp3AJiu5KDdWvFYXhAEXs21dFw@mail.gmail.com>
 <CAGtsp8kBvvDidZsU_oF-6nN6H9TVKfQ28PdZYQb53inAtP-Rxg@mail.gmail.com> <CAEjw_fgsUVOO3aDHbjwm6qHwTd2N-acSOrVDntTrzetAPSwueg@mail.gmail.com>
In-Reply-To: <CAEjw_fgsUVOO3aDHbjwm6qHwTd2N-acSOrVDntTrzetAPSwueg@mail.gmail.com>
From: Jonathan Lewis <jlewisoracle@gmail.com>
Date: Sun, 10 Oct 2021 21:29:05 +0100
Message-ID: <CAGtsp8kHewm5szdX75yHYPdQM5ZJC1j1scfcpSspe0MTiLXJ6g@mail.gmail.com>
Subject: Re: question on table access by index rowid batched
To: Oracle L <oracle-l@freelists.org>
Content-Type: multipart/alternative; boundary="00000000000061b97405ce05776c"
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: jlewisoracle@gmail.com
--00000000000061b97405ce05776c
Content-Type: text/plain; charset="UTF-8"

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@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.
>

--00000000000061b97405ce05776c
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: base64

PGRpdiBkaXI9Imx0ciI+PGRpdiBkaXI9Imx0ciI+PGRpdj48YnI+PC9kaXY+PGRpdj5Zb3VyIHVu
ZGVyc3RhbmRpbmcgaXMgY29ycmVjdCAtIHdoZW4gb3B0aW1pemVyX2FkYXB0aXZlX3JlcG9ydF9v
bmx5ID0gdHJ1ZSBPcmFjbGUgd2lsbCBmb2xsb3cgdGhlIHR3byBuZXN0ZWQgbG9vcHM7IHdoZW4g
aXQgaXMgZmFsc2UgdGhlbiBhdCBGSVJTVCBleGVjdXRpb24gT3JhY2xlIGNhbiBkZWNpZGUgd2hp
Y2ggb2YgNCBwb3NzaWJsZSBwYXRocyB0byB0YWtlIGFuZCBpbiB5b3VyIGNhc2UgaXQgdG9vayB0
d28gaGFzaCBqb2lucy48YnI+PC9kaXY+PGRpdj48YnI+PC9kaXY+PGRpdj5TdHJpcHBpbmcgb3V0
IGFsbCB0aGUgZXhjZXNzIHRleHQgZnJvbSBvcGVyYXRpb25zIDAgdG8gMTE8L2Rpdj48ZGl2PlRo
ZSBtb25pdG9yIGRpc3BsYXllZDo8L2Rpdj48ZGl2Pj09PT09PT09PT09PT09PT09PT09PT09PT09
PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT08YnI+fCBJZCB8IMKgIMKgIMKgIMKg
IMKgIMKgIMKgIMKgIMKgIMKgIMKgT3BlcmF0aW9uPGJyPj09PT09PT09PT09PT09PT09PT09PT09
PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT08YnI+fCDCoDAgfCBTRUxFQ1Qg
U1RBVEVNRU5UPGJyPnwgwqAxIHwgwqAgRklMVEVSPGJyPnwgwqAyIHwgwqAgwqBORVNURUQgTE9P
UFMgT1VURVI8YnI+fCDCoDMgfCDCoCDCoCBORVNURUQgTE9PUFMgT1VURVI8YnI+fCDCoDQgfCDC
oCDCoCDCoEhBU0ggSk9JTiBPVVRFUjxicj58IMKgNSB8IMKgIMKgIMKgIE5FU1RFRCBMT09QUyBP
VVRFUjxicj58IMKgNiB8IMKgIMKgIMKgIMKgU1RBVElTVElDUyBDT0xMRUNUT1I8YnI+fCDCoDcg
fCDCoCDCoCDCoCDCoCBORVNURUQgTE9PUFMgT1VURVI8YnI+fCDCoDggfCDCoCDCoCDCoCDCoCDC
oEhBU0ggSk9JTiBPVVRFUjxicj58IMKgOSB8IMKgIMKgIMKgIMKgIMKgIE5FU1RFRCBMT09QUyBP
VVRFUjxicj58IDEwIHwgwqAgwqAgwqAgwqAgwqAgwqBTVEFUSVNUSUNTIENPTExFQ1RPUjxicj58
IDExIHwgwqAgwqAgwqAgwqAgwqAgwqAgTkVTVEVEIExPT1BTIE9VVEVSPGJyPj09PT09PT09PT09
PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT08YnI+PC9k
aXY+PGRpdj48YnI+PC9kaXY+PGRpdj5CdXQgd2hlbiBvcHRpbWl6ZXJfYWRhcHRpdmVfcmVwb3J0
aW5nX29ubHkgPSBGQUxTRSwgdGhlIHJ1bi10aW1lIGVuZ2luZSBjb3VsZCBoYXZlIGNob3NlbiBh
bnkgb25lIG9mIHRoZSBmb2xsb3dpbmcgNDo8L2Rpdj48ZGl2Pjxicj48L2Rpdj48ZGl2PlR3byB0
ZXN0ZWQgbG9vcHM8YnI+PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09
PT08YnI+fCBJZCB8IMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgT3BlcmF0aW9uPGJy
Pj09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PGJyPnwgwqAwIHwg
U0VMRUNUIFNUQVRFTUVOVDxicj58IMKgMSB8IMKgIEZJTFRFUjxicj58IMKgMiB8IMKgIMKgTkVT
VEVEIExPT1BTIE9VVEVSPGJyPnwgwqAzIHwgwqAgwqAgTkVTVEVEIExPT1BTIE9VVEVSPGJyPnwg
wqA1IHwgwqAgwqAgwqBORVNURUQgTE9PUFMgT1VURVI8YnI+fCDCoDcgfCDCoCDCoCDCoCBORVNU
RUQgTE9PUFMgT1VURVI8YnI+fCDCoDkgfCDCoCDCoCDCoCDCoE5FU1RFRCBMT09QUyBPVVRFUjxi
cj58IDExIHwgwqAgwqAgwqAgwqAgTkVTVEVEIExPT1BTIE9VVEVSPGJyPj09PT09PT09PT09PT09
PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PGJyPjxicj5IYXNoIGpvaW4gdGhlbiBuZXN0
ZWQgbG9vcDxicj49PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PTxi
cj58IElkIHwgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqBPcGVyYXRpb248YnI+PT09
PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT08YnI+fCDCoDAgfCBTRUxF
Q1QgU1RBVEVNRU5UPGJyPnwgwqAxIHwgwqAgRklMVEVSPGJyPnwgwqAyIHwgwqAgwqBORVNURUQg
TE9PUFMgT1VURVI8YnI+fCDCoDMgfCDCoCDCoCBORVNURUQgTE9PUFMgT1VURVI8YnI+fCDCoDQg
fCDCoCDCoCDCoEhBU0ggSk9JTiBPVVRFUjxicj58IMKgNyB8IMKgIMKgIMKgIE5FU1RFRCBMT09Q
UyBPVVRFUjxicj58IMKgOSB8IMKgIMKgIMKgIMKgTkVTVEVEIExPT1BTIE9VVEVSPGJyPnwgMTEg
fCDCoCDCoCDCoCDCoCBORVNURUQgTE9PUFMgT1VURVI8YnI+PT09PT09PT09PT09PT09PT09PT09
PT09PT09PT09PT09PT09PT09PT09PT08YnI+PGJyPk5lc3RlZCBsb29wIHRoZW4gaGFzaCBqb2lu
PGJyPj09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PGJyPnwgSWQg
fCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoE9wZXJhdGlvbjxicj49PT09PT09PT09
PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PTxicj58IMKgMCB8IFNFTEVDVCBTVEFU
RU1FTlQ8YnI+fCDCoDEgfCDCoCBGSUxURVI8YnI+fCDCoDIgfCDCoCDCoE5FU1RFRCBMT09QUyBP
VVRFUjxicj58IMKgMyB8IMKgIMKgIE5FU1RFRCBMT09QUyBPVVRFUjxicj58IMKgNSB8IMKgIMKg
IMKgTkVTVEVEIExPT1BTIE9VVEVSPGJyPnwgwqA3IHwgwqAgwqAgwqAgTkVTVEVEIExPT1BTIE9V
VEVSPGJyPnwgwqA4IHwgwqAgwqAgwqAgwqBIQVNIIEpPSU4gT1VURVI8YnI+fCAxMSB8IMKgIMKg
IMKgIMKgIE5FU1RFRCBMT09QUyBPVVRFUjxicj49PT09PT09PT09PT09PT09PT09PT09PT09PT09
PT09PT09PT09PT09PT09PTxicj48YnI+VHdvIGhhc2ggam9pbnM8YnI+PT09PT09PT09PT09PT09
PT09PT09PT09PT09PT09PT09PT09PT09PT09PT08YnI+fCBJZCB8IMKgIMKgIMKgIMKgIMKgIMKg
IMKgIMKgIMKgIMKgIMKgT3BlcmF0aW9uPGJyPj09PT09PT09PT09PT09PT09PT09PT09PT09PT09
PT09PT09PT09PT09PT09PGJyPnwgwqAwIHwgU0VMRUNUIFNUQVRFTUVOVDxicj58IMKgMSB8IMKg
IEZJTFRFUjxicj58IMKgMiB8IMKgIMKgTkVTVEVEIExPT1BTIE9VVEVSPGJyPnwgwqAzIHwgwqAg
wqAgTkVTVEVEIExPT1BTIE9VVEVSPGJyPnwgwqA0IHwgwqAgwqAgwqBIQVNIIEpPSU4gT1VURVI8
YnI+fCDCoDcgfCDCoCDCoCDCoCBORVNURUQgTE9PUFMgT1VURVI8YnI+fCDCoDggfCDCoCDCoCDC
oCDCoEhBU0ggSk9JTiBPVVRFUjxicj58IDExIHwgwqAgwqAgwqAgwqAgTkVTVEVEIExPT1BTIE9V
VEVSPGJyPj09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PGJyPjxi
cj48L2Rpdj48ZGl2PlRoYW5rcyBmb3IgbGV0dGluZyBtZSBrbm93IGFib3V0IGZpcnN0X3Jvd3Mg
LSBJIG1heSBlbmQgdXAgZG9pbmcgYSBmZXcgZXhwZXJpbWVudHMgd2l0aCBhZGFwdGl2ZSBwbGFu
cyBhbmQgdGhhdCAobGVnYWN5KSBzZXR0aW5nIGlmIEkgY2FuIGZpbmQgc29tZSB0aW1lLjwvZGl2
PjxkaXY+PGJyPjwvZGl2PlJlZ2FyZHM8L2Rpdj48ZGl2PkpvbmF0aGFuIExld2lzPC9kaXY+PGRp
dj48YnI+PC9kaXY+PGJyPjxkaXYgY2xhc3M9ImdtYWlsX3F1b3RlIj48ZGl2IGRpcj0ibHRyIiBj
bGFzcz0iZ21haWxfYXR0ciI+T24gU3VuLCAxMCBPY3QgMjAyMSBhdCAyMDo0NCwgUGFwICZsdDs8
YSBocmVmPSJtYWlsdG86b3JhY2xlLmRldmVsb3BlcjM1QGdtYWlsLmNvbSI+b3JhY2xlLmRldmVs
b3BlcjM1QGdtYWlsLmNvbTwvYT4mZ3Q7IHdyb3RlOjxicj48L2Rpdj48YmxvY2txdW90ZSBjbGFz
cz0iZ21haWxfcXVvdGUiIHN0eWxlPSJtYXJnaW46MHB4IDBweCAwcHggMC44ZXg7Ym9yZGVyLWxl
ZnQ6MXB4IHNvbGlkIHJnYigyMDQsMjA0LDIwNCk7cGFkZGluZy1sZWZ0OjFleCI+PGRpdiBkaXI9
Imx0ciI+PGJyPlRoYW5rIHlvdSBKb25hdGhhbi48YnI+RG8geW91IG1lYW4gdG8gc2F5IHRoZSBw
bGFuIHdoaWNoIHNob3dzIGFjdHVhbGx5IGV4YWN0bHkgdGhlIHNhbWUgaWYgd2Ugc2VlIHRoZSBm
aXJzdCAxMSBzdGVwcyBpbiBib3RoIGNhc2VzIGJlbG93IGlzIG5vdCBhY3R1YWxseSB0aGUgY2Fz
ZSwgd2hlbiBpdCYjMzk7cyBiZWVuIG9wdGVkIGluIHJ1biB0aW1lLiBCYXNpY2FsbHkgYWxsIG9m
IHRoZSBzdGVwcyA0LDUsOCw5IGFyZSBub3Qgb3B0ZWQgYnkgYm90aCBvZiB0aGUgY2FzZXMuIFRo
ZSAmcXVvdDtuZXN0ZWQgbG9vcHMgb3V0ZXImcXVvdDsgYXQgc3RlcC01IGFuZCBzdGVwLTkgYXJl
IGFjdHVhbGx5IG9wdGVkIGluIHJ1bnRpbWUgd2hpbGUgb3B0aW1pemVyX2FkYXB0aXZlX3JlcG9y
dGluZ19vbmx5ID0gdHJ1ZSwgd2hlcmVhcyBzdGVwLTggYW5kIHN0ZXAtNCBpLmUuICZxdW90O2hh
c2ggam9pbiBvdXRlciZxdW90OyBhcmUgdGhlIG9uZXMgd2hpY2ggd2VyZSBmb2xsb3dlZCBpbiBy
dW4gdGltZSBieSB0aGUgb3B0aW1pemVyIHdoaWxlIHdlIGhhZCBvcHRpbWl6ZXJfYWRhcHRpdmVf
cmVwb3J0aW5nX29ubHk9ZmFsc2UuIEJ1dCB0aGUgc3FsIG1vbml0b3IgY29udGFpbnMgYWxsIG9m
IHRob3NlIGZvdXIgY29tYmluYXRpb25zIGp1c3QgYmVjYXVzZSBvZiBpdHMgYWRhcHRpdmUgcGxh
bi4gSXMgdGhpcyB1bmRlcnN0YW5kaW5nIGNvcnJlY3Q/PGJyPjxicj5BbmQgeWVzLCBpdCYjMzk7
cyBjdXJyZW50bHkgdXNpbmcgdGhlIGZpcnN0X3Jvd3MgaGludC4gSSB3aWxsIGNoZWNrIHdpdGgg
Zmlyc3Rfcm93c18xMDAgaGludCByYXRoZXIgdGhhbiBmaXJzdF9yb3dzIGhpbnQgdG/CoHNlZSBp
ZiB0aGUgYmVoYXZpb3VyIGNoYW5nZXMuPC9kaXY+PC9ibG9ja3F1b3RlPjwvZGl2PjwvZGl2Pg0K
--00000000000061b97405ce05776c--
--
http://www.freelists.org/webpage/oracle-l