Hello Listers, we have one customer database on version 19.9. One of the
search queries which was running with first_rows optimizer mode was
running for ~10+minutes but giving the first few rows after ~2.5minutes.
But suddenly we see the same query is finishing in ~4minutes and
it's giving the first few rows almost instantly. We want to know the reason.
Looking into the execution path , we found that the execution path for the
slow one, was having 'table access by index rowid batched' in many of its
access paths throughout the plan whereas the fast execution path does
not have any, it was simple 'table access by index rowid'. The outlines
section for the fast execution also shows hints as
opt_param('optimizer_batch_table_access_by_rowid','false').
I have three questions:
1) If there is a known performance issue associated with the new 'rowid
batching' optimization feature(in 19.9 specifically) in conjunction with
first _rows mode and any workaround for that exists?
2) We were trying to understand what caused this feature change. and as
per the team the only change done was 'optimizer_adaptive_reporting_only'
has been changed to TRUE, so can this be anyway related to the 'rowid
batching' feature being turned off in this query? How can we get the cause?
3)Is there any downside(bad impact on performance) of setting
'optimizer_adaptive_reporting_only' to true?
Regards
Pap
