Re: question on table access by index rowid batched

Hello,

It is probable that your situation might be linked with: Bug 22445503 Wrong results / suboptimal plan with nested loop batching over parallel hash distribution and adaptive plans

You can try out of curiosity:
alter session set optimizer_adaptive_reporting_only=false; alter session set "_optimizer_batch_table_access_by_rowid "=TRUE;

And see what is happening.

În vin., 8 oct. 2021 la 09:05, Pap <oracle.developer35_at_gmail.com> a scris:

> 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