question on table access by index rowid batched
Date: Fri, 8 Oct 2021 01:04:58 +0530
Message-ID: <CAEjw_fjraKwNds2y+Ftzc5V0CjP_-SE0dVzaBEmvCcrpA4i=nQ_at_mail.gmail.com>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Oct 07 2021 - 21:34:58 CEST