Re: question on table access by index rowid batched
Date: Sat, 9 Oct 2021 12:19:00 +0100
Message-ID: <CAGtsp8mOX+kVxhzCj+WBcia6Wj-6TN5kXpScZZFO8M2sQJ-d7w_at_mail.gmail.com>
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
With reporting only = FALSE
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
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 | 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 | | | |
| | | . |
| 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
| | | | | . |
| 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 | . | | |
| 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_at_gmail.com> 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? > > >
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Oct 09 2021 - 13:19:00 CEST