Remember I was talking POSSIBILITIES in my previous post, not certainties.
With the extra information you have now supplied I have a better
hypothesis, based on the fact that you have adaptive_plans_enabled (which I
should have realised in the first place), and that the plans you've
supplied are different in exactly the TYPE of reason I suggested, but not
for the exact reason. My basic hypothesis now is that when you enabled
adaptive plans but set it to reporting only Oracle will always include the
statistics collector operations, but it will not use them (i.e. not
counting etc,) to make a decision about whether to take a hash join or a
nested loop join. It will simply generate a path based on its first pass
calculations.
(You could check this with a suitable set of examples and testing with (a)
adaptive plans enable (b) adaptive plans enabled but reporting only (c)
adaptive plans disabled. You'd need to set up 4 possibilities: nlj
changing to hj due to adaptive plans, hj changing to nlj due to adaptive
plans, nlj not changing, hj not changing).
So Looking at your SQL Monitor reports - lines 41 and 42 as a starting
point:
WIth reporting only = TRUE
==============================================================================================================================================================================
| Id | Operation |
Name | Rows | Cost | Time | Start | Execs | Rows
| Read | Read | Mem |
| | |
| (Estim) | | Active(s) | Active | | (Actual) |
Reqs | Bytes | (Max) |
===============================================================================================================================================================================
| 41 | INDEX RANGE SCAN | TCX_PK
| 1 | 2 | 279 | +2 | 2M | 2M
| | | . |
| 42 | INDEX RANGE SCAN | TCX_PK
| 1 | 2 | | | |
| | | . |
With reporting only = FALSE
===============================================================================================================================================================================================
| Id | Operation |
Name | Rows | Cost | Time | Start | Execs | Rows
| Read | Read | Write | Write | Mem |
| | |
| (Estim) | | Active(s) | Active | | (Actual) |
Reqs | Bytes | Reqs | Bytes | (Max) |
===============================================================================================================================================================================================
| 41 | INDEX RANGE SCAN | TCX_PK
| 1 | 2 | | | |
| | | | | . |
| 42 | INDEX RANGE SCAN | TCX_PK
| 1 | 2 | 1 | +131 | 1 | 976K
| | | | | . |
These two lines are the options available as a result of a statistics
collector further up the plan, and one plan takes operation 41, the other
takes operation 42
Putting them into context (adding in some parent and sibling rows)
Reporting only = true ... plan based on estimates
==============================================================================================================================================================================
| Id | Operation |
Name | Rows | Cost | Time | Start | Execs | Rows
| Read | Read | Mem |
| | |
| (Estim) | | Active(s) | Active | | (Actual) |
Reqs | Bytes | (Max) |
===============================================================================================================================================================================
| 7 | NESTED LOOPS OUTER |
| 1 | 3M | 279 | +2 | 1 | 2M |
| | . | | |
| 8 | HASH JOIN OUTER |
| 1 | 3M | 279 | +2 | 1 | 2M |
| | . |
| 9 | NESTED LOOPS OUTER |
| 1 | 3M | 279 | +2 | 1 | 2M |
| | . |
| 10 | STATISTICS COLLECTOR |
| | | 279 | +2 | 1 | 2M |
| | . |
| 11 | NESTED LOOPS OUTER |
| 1 | 3M | 279 | +2 | 1 | 2M |
| | . |
...
| 41 | INDEX RANGE SCAN | TCX_PK
| 1 | 2 | 279 | +2 | 2M | 2M
| | | . |
| 42 | INDEX RANGE SCAN | TCX_PK
| 1 | 2 | | | |
| | | . |
| 43 | TABLE ACCESS BY INDEX ROWID | TC
| 1 | 2 | 279 | +2 | 2M | 2M
| 16037 | 125MB | . | | |
Reporting only = false ... plan adapts to counting
(OPTERATION 10 dictates a HASH JOIN where the estimated stats suggested NLJ)
===============================================================================================================================================================================================
| Id | Operation |
Name | Rows | Cost | Time | Start | Execs | Rows
| Read | Read | Write | Write | Mem |
| | |
| (Estim) | | Active(s) | Active | | (Actual) |
Reqs | Bytes | Reqs | Bytes | (Max) |
===============================================================================================================================================================================================
| 7 | NESTED LOOPS OUTER |
| 1 | 3M | 27 | +131 | 1 | 2M |
| | | | . |
| 8 | HASH JOIN OUTER |
| 1 | 3M | 155 | +3 | 1 | 2M |
3035 | 1GB | 3035 | 1GB | 309MB |
| 9 | NESTED LOOPS OUTER |
| 1 | 3M | 129 | +3 | 1 | 2M |
| | | | . |
| 10 | STATISTICS COLLECTOR |
| | | 129 | +3 | 1 | 2M |
| | | | . |
| 11 | NESTED LOOPS OUTER |
| 1 | 3M | 129 | +3 | 1 | 2M |
| | | | . |
...
| 41 | INDEX RANGE SCAN | TCX_PK
| 1 | 2 | | | |
| | | | | . |
| 42 | INDEX RANGE SCAN | TCX_PK
| 1 | 2 | 1 | +131 | 1 | 976K
| | | | | . |
| 43 | TABLE ACCESS BY INDEX ROWID | TC
| 1 | 2 | 27 | +131 | 2M | 2M
| 21549 | 168MB | | | . |
As you can see, the ability to switch plans makes a big difference to the
amount of time Oracle spends working before it starts executing step 7
(start time = +2 seconds vs. +131 seconds).
This one extract from the plans doesn't answer the question about why
"table access by index rowid batched" has appeared; nor does it explain the
total difference between the two executions because you also need to look
at the statistics collector at operation 6 and the impact that has on which
of operation 45/46 and 47/48 get chosen. The other point that then comes up
is that the order in which data appears from a hash join is (almost
certainly) different from the order it appears from a nested loop join -
which means that when you use the result of the join to probe other tables
by index the order of the driving data from one join may be roughly in line
with the indexes and data from the next table(s) in the join and benefit
from a lot of "self-induced" caching, while the other join might produce
data in what is effectively a randomised order resulting in "self-flushing"
and more random reads - and that might explain what happens at operations
60/61
What my observations suggest is a good argument for why you had such a
difference in performance, and that it had nothing to do with the batching
or not of the table access by rowid.
I may write this up in more detail as a blog some day, but I don't have
time right now.
Regards
Jonathan Lewis
On Fri, 8 Oct 2021 at 18:34, Pap <oracle.developer35@xxxxxxxxx> wrote:
It's fluctuating. I am now not able to reproduce the scenario for that
same small query for which I had posted here just before. And you are
correct , I was luckily having sql monitors saved. I am attaching those
here. If you see that, the main query was showing those rowid batched
operations when we have the optimizer_adaptive_reporting_only set as
default/false and was taking a long time to finish and also the first few
rows were also taking longer to get produced out of the query. But the
sample small query which i had posted a cursor plan for was just showing
the opposite behaviour. That is going for a rowid batched path when
optimizer_adaptive_reporting_only sets as true.
However, now I am seeing that same small query in both the cases
(irrespective of value of optimizer_adaptive_reporting_only) going for
'rowid batched' execution path. Not sure if it's just stats or
anything else influencing and I am seeing different things behaviour. Just
to note we have 'optimizer_adaptive_plans' set to true ,
'optimizer_adaptive_statistics' set to false. The only change we made was
moving ' optimizer_adaptive_reporting_only' from false to true.
And Jonathan when you said the optimizer_adaptive_reporting_only = true
will introduce 'statistics collector' operation, but if you see the
attached sql monitor for the main query, i am seeing 'statistics collector'
even when optimizer_adaptive_reporting_only is = false. Is that expected
behaviour?