Re: question on table access by index rowid batched

Thank You Laurentiu.

And the bug seems to be affecting only <12.2 and we are on 19.9, not sure if it's still impacting. But yes it seems from the bug description, there is a relation between 'rowid batched access' and 'optimizer_adaptive_reporting_only' param value.

As I mentioned, with your below highlighted setup(which was the default setup only) the main search query(with first_rows mode) was running longer with 'rowid batched' operation in many of the index access path and we had then set 'optimizer_adaptive_reporting_only' to true and that changed the rowid batched access to rowid access for few of the access and the query performed better now. So now we are wondering if we should keep it like that. or we should rather keep the 'optimizer_adaptive_reporting_only' to false i.e default and set the underscore parameter '_optimizer_batch_table_access_by_rowid' to 'false'? And is there any downside of keeping the 'optimizer_adaptive_reporting_only' to true?

*alter session set optimizer_adaptive_reporting_only=false;* *alter session set "_optimizer_batch_table_access_by_rowid "=TRUE;*

On Fri, Oct 8, 2021 at 2:10 PM Laurentiu Oprea <laurentiu.oprea06_at_gmail.com> wrote:

> 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