Re: question on table access by index rowid batched

 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