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@SEL$1
2 - SEL$1 / tab1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "tab1"@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "tab1"@"SEL$1" ("tab1"."NAME" "tab1"."CODE"))
OUTLINE_LEAF(@"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@SEL$1
2 - SEL$1 / tab1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@"SEL$1" "tab1"@"SEL$1" ("tab1"."NAME" "tab1"."CODE"))
OUTLINE_LEAF(@"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@xxxxxxxxx> 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
