Re: question on table access by index rowid batched

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

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