Re: question on table access by index rowid batched

THere is a bit of confusion or I didn`t understood properly.

You mentioned initially that optimizer_adaptive_reporting_only was set to TRUE and you could see no batching was used. Then in your example you are making optimizer_adaptive_reporting_only to FALSE and we can see no batching is used

În vin., 8 oct. 2021 la 13:43, Jonathan Lewis <jlewisoracle_at_gmail.com> a scris:

> What do the two plans look like if you use the 'adaptive' option for the
> format. ('Advanced' doesn't show you everything).
>
> I can't reproduce the effect - but that's partly because there's not
> enough information available, and maybe because I'm on 19.11, or maybe
> because I've got adaptive_plans enabled anyway.
>
> Can you create a complete reproducible example - viz: create table, create
> indexes, then the two calls to explain that show the difference.
> Format=>'outline projection adaptive' should be sufficient.
>
> There is a POSSIBLE argument why the plans should operate the table access
> differently as the adaptive reporting is switched on and off.
>
> When adaptive reporting is in place then adaptive plans get "statistics
> collector" operations which accumulate and count data for a while
> (typically to see if the plan should swtich between NLJ and HJ). This means
> that there's a line in the plan which is "blocking" and there are some
> operations that Oracle will not choose to perform unless there's a blocking
> operation further up the plan - so the choice between batching and not
> batching may depend on the presence or absence of the statistics
> collector. (And it's POSSIBLE that in this particular example it's
> happening because of a simple code reuse principle rather than because of
> an explicit decision by a programmer that it's appropriate here.)
>
> Regards
> Jonathan Lewis
>
>
> On Fri, 8 Oct 2021 at 07:05, Pap <oracle.developer35_at_gmail.com> wrote:
>
>> Able to replicate the behavior with a small query as below . Its(rowid
>> batching) is really changing with the setting of
>> optimizer_adaptive_reporting_only parameter. But still unable to figure out
>> how?
>>
>> As per Oracle doc , "*With this setting, the information required for an
>> adaptive optimization is gathered, but no action is taken to change the
>> plan*", so why is it influencing the plan in our case? The default value
>> of the parameter is false, so is it advisable to turn it to TRUE and its
>> not having any negative impact?
>>
>>
>> https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/OPTIMIZER_ADAPTIVE_REPORTING_ONLY.html#GUID-8DD128F9-4891-4061-9B2D-9D45315D44FB
>>
>>
>> ************ Test case*************
>> alter session set optimizer_adaptive_reporting_only=true;
>>
>> explain plan for
>> SELECT TRIM ( SUBSTR (descr, 1, INSTR (descr, ',') - 1)) AS msi,
>> TRIM ( SUBSTR (descr, INSTR (descr, ',') + 1)) AS msv
>> FROM USER1.tab1
>> WHERE name = 'XXXX';
>>
>>
>> select plan_table_output
>> from table(dbms_xplan.display('plan_table',null,'ADVANCED'));
>>
>> Plan hash value: 3274520851
>>
>>
>> ---------------------------------------------------------------------------------------------------
>> | Id | Operation | Name | Rows | Bytes
>> | Cost (%CPU)| Time |
>>
>> ---------------------------------------------------------------------------------------------------
>> | 0 | SELECT STATEMENT | | 15 | 615
>> | 3 (0)| 00:00:01 |
>> | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| tab1 | 15 | 615
>> | 3 (0)| 00:00:01 |
>> |* 2 | INDEX RANGE SCAN | tab1_pk | 15 |
>> | 1 (0)| 00:00:01 |
>>
>> ---------------------------------------------------------------------------------------------------
>>
>> Query Block Name / Object Alias (identified by operation id):
>> -------------------------------------------------------------
>>
>> 1 - SEL$1 / tab1_at_SEL$1
>> 2 - SEL$1 / tab1_at_SEL$1
>>
>> Outline Data
>> -------------
>>
>> /*+
>> BEGIN_OUTLINE_DATA
>> BATCH_TABLE_ACCESS_BY_ROWID(_at_"SEL$1" "tab1"_at_"SEL$1")
>> INDEX_RS_ASC(_at_"SEL$1" "tab1"_at_"SEL$1" ("tab1"."NAME" "tab1"."CODE"))
>> OUTLINE_LEAF(_at_"SEL$1")
>> ALL_ROWS
>> DB_VERSION('19.1.0')
>> OPTIMIZER_FEATURES_ENABLE('19.1.0')
>> IGNORE_OPTIM_EMBEDDED_HINTS
>> END_OUTLINE_DATA
>> */
>>
>> Predicate Information (identified by operation id):
>> ---------------------------------------------------
>>
>> 2 - access("NAME"='XXXX')
>>
>> Column Projection Information (identified by operation id):
>> -----------------------------------------------------------
>>
>> 1 - "descr"[VARCHAR2,60]
>> 2 - "tab1".ROWID[ROWID,10]
>>
>> Query Block Registry:
>> ---------------------
>>
>> <q o="2"
>> f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[tab1]]></t><s><![CDATA[SEL$1]]
>> ></s></h></f></q>
>>
>>
>>
>> *************************** Setting to False******************
>>
>> alter session set optimizer_adaptive_reporting_only=false;
>>
>>
>> explain plan for
>> SELECT TRIM ( SUBSTR (descr, 1, INSTR (descr, ',') - 1)) AS msi,
>> TRIM ( SUBSTR (descr, INSTR (descr, ',') + 1)) AS msv
>> FROM USER1.tab1
>> WHERE name = 'XXXX';
>>
>>
>>
>> select plan_table_output
>> from table(dbms_xplan.display('plan_table',null,'ADVANCED'));
>>
>>
>> -------------------------------------------------------------------------------------------------------------
>>
>> Plan hash value: 2347410815
>>
>>
>> -------------------------------------------------------------------------------------------
>> | Id | Operation | Name | Rows | Bytes | Cost
>> (%CPU)| Time |
>>
>> -------------------------------------------------------------------------------------------
>> | 0 | SELECT STATEMENT | | 15 | 615 | 3
>> (0)| 00:00:01 |
>> | 1 | TABLE ACCESS BY INDEX ROWID| tab1 | 15 | 615 | 3
>> (0)| 00:00:01 |
>> |* 2 | INDEX RANGE SCAN | tab1_pk | 15 | | 1
>> (0)| 00:00:01 |
>>
>> -------------------------------------------------------------------------------------------
>>
>> Query Block Name / Object Alias (identified by operation id):
>> -------------------------------------------------------------
>>
>> 1 - SEL$1 / tab1_at_SEL$1
>> 2 - SEL$1 / tab1_at_SEL$1
>>
>> Outline Data
>> -------------
>>
>> /*+
>> BEGIN_OUTLINE_DATA
>> INDEX_RS_ASC(_at_"SEL$1" "tab1"_at_"SEL$1" ("tab1"."NAME" "tab1"."CODE"))
>> OUTLINE_LEAF(_at_"SEL$1")
>> ALL_ROWS
>> OPT_PARAM('_optimizer_batch_table_access_by_rowid' 'false')
>> DB_VERSION('19.1.0')
>> OPTIMIZER_FEATURES_ENABLE('19.1.0')
>> IGNORE_OPTIM_EMBEDDED_HINTS
>> END_OUTLINE_DATA
>> */
>>
>> Predicate Information (identified by operation id):
>> ---------------------------------------------------
>>
>> 2 - access("NAME"='XXXX')
>>
>> Column Projection Information (identified by operation id):
>> -----------------------------------------------------------
>>
>> 1 - "descr"[VARCHAR2,60]
>> 2 - "tab1".ROWID[ROWID,10]
>>
>> Query Block Registry:
>> ---------------------
>>
>> <q o="2"
>> f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[tab1]]></t><s><![CDATA
>> [SEL$1]]></s></h></f></q>
>>
>> On Fri, Oct 8, 2021 at 1:04 AM Pap <oracle.developer35_at_gmail.com> wrote:
>>
>>> 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-l