Re: question on table access by index rowid batched

  It's fluctuating. I am now not able to reproduce the scenario for that same small query for which I had posted here just before. And you are correct , I was luckily having sql monitors saved. I am attaching those here. If you see that, the main query was showing those rowid batched operations when we have the optimizer_adaptive_reporting_only set as default/false and was taking a long time to finish and also the first few rows were also taking longer to get produced out of the query. But the sample small query which i had posted a cursor plan for was just showing the opposite behaviour. That is going for a rowid batched path when optimizer_adaptive_reporting_only sets as true.

However, now I am seeing that same small query in both the cases (irrespective of value of optimizer_adaptive_reporting_only) going for 'rowid batched' execution path. Not sure if it's just stats or anything else influencing and I am seeing different things behaviour. Just to note we have 'optimizer_adaptive_plans' set to true , 'optimizer_adaptive_statistics' set to false. The only change we made was moving ' optimizer_adaptive_reporting_only' from false to true.

And Jonathan when you said the optimizer_adaptive_reporting_only = true will introduce 'statistics collector' operation, but if you see the attached sql monitor for the main query, i am seeing 'statistics collector' even when optimizer_adaptive_reporting_only is = false. Is that expected behaviour?

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

> 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




text/plain attachment: list_sql_monitor_compare.txt