Oracle 12C execution Plan prompt table ACCESS by INDEX ROWID batched

A table access by index ROWID batched may appear in the start of an Oracle 12C execution plan, official explanation: Table access by index ROWID Batched:means that Databa SE retrieves a few rowids from the index, and then attempts to access rows in blocks order to improve the clustering and re Duce the number of times this database must access a block. Main meaning: For multiple rowid in a chunk, reduce access speed by batch. And as a new feature of 12.1, the database is passed through _optimiz Er_batch_table_access_by_rowid to control.

Database version 12.1
Sql> select * from V$version;

BANNER con_id
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition release 12.1.0.2.0-64bit Production 0
Pl/sql Release 12.1.0.2.0-production 0
CORE 12.1.0.2.0 Production 0
TNS for Ibm/aix RISC system/6000:version 12.1.0.2.0-production 0
Nlsrtl Version 12.1.0.2.0-production 0
TABLE ACCESS by INDEX ROWID batched Execution Plan
Sql> set Autot traceonly exp stat;
sql> var b1 number;
Sql> Set Lines 150
Sql> Set pages 10000
Sql> exec:b1:=18868701138;

Pl/sql procedure successfully completed.

Sql> SELECT brand_id from T_usertype_fullno WHERE bill_id= LTRIM (: B1, ɔ ') and RowNum < 2;


Execution Plan
----------------------------------------------------------
Plan Hash value:942613467

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------------------------------------
| 0 |                    SELECT STATEMENT |     |    1 |     15 | 2 (0) | 00:00:01 |
|* 1 |                    COUNT Stopkey |       |       |            |          | |
|   2 | TABLE ACCESS by INDEX ROWID batched|     Xifenfei |    1 |     15 | 2 (0) | 00:00:01 |
|* 3 | INDEX RANGE SCAN |     Ind_xifenfei |       1 |     | 2 (0) | 00:00:01 |
-----------------------------------------------------------------------------------------------------------

predicate information (identified by Operation ID):
---------------------------------------------------

1-filter (ROWNUM&LT;2)
3-access ("bill_id" =ltrim (: B1,ɔ '))


Statistics
----------------------------------------------------------
0 Recursive calls
0 db Block gets
5 Consistent gets
0 physical Reads
0 Redo Size
559 Bytes sent via sql*net to client
552 Bytes received via sql*net from client
2 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
1 rows processed
_optimizer_batch_table_access_by_rowid parameter is True
Sql> col name for A32
Sql> col value for A24
Col description for A70
Set Linesize 150
Select A.KSPPINM NAME,B.KSPPSTVL value,a.ksppdesc description
Sql> sql> sql> 2 from X$ksppi A,X$KSPPCV b
3 Where a.inst_id = USERENV (' Instance ')
4 and b.inst_id = USERENV (' Instance ')
5 and A.indx = B.indx
6 and Upper (A.KSPPINM) like Upper ('%&param% ')
7 Order BY name
8/
Enter value for param: _optimizer_batch_table_access_by_rowid
Old 6:and Upper (A.KSPPINM) like Upper ('%&param% ')
New 6:and Upper (A.KSPPINM) like Upper ('%_optimizer_batch_table_access_by_rowid% ')

NAME VALUE DESCRIPTION
------------------------------------- ------------------------ ----------------------------------------------
_optimizer_batch_table_access_by_rowid TRUE Enable table access by rowID IO batching
Set _optimizer_batch_table_access_by_rowid to False, the execution plan changes from table access by index ROWID batched to table access by index ROWID
Sql> set Autot traceonly exp stat;
sql> var b1 number;
Sql> Set Lines 150
Sql> Set pages 10000
Sql> exec:b1:=18868701138;

Pl/sql procedure successfully completed.

Sql> alter session Set "_optimizer_batch_table_access_by_rowid" =FALSE;

Session altered.

Sql> SELECT brand_id from Xifenfei WHERE bill_id= LTRIM (: B1, ɔ ') and RowNum < 2;


Execution Plan
----------------------------------------------------------
Plan Hash value:2797551150

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
---------------------------------------------------------------------------------------------------
| 0 |                    SELECT STATEMENT |     |    1 |     15 | 2 (0) | 00:00:01 |
|* 1 |                    COUNT Stopkey |       |       |            |          | |
|   2 | TABLE ACCESS by INDEX rowid|     Xifenfei |    1 |     15 | 2 (0) | 00:00:01 |
|* 3 | INDEX RANGE SCAN |     Ind_xifenfei |       1 |     | 2 (0) | 00:00:01 |
---------------------------------------------------------------------------------------------------

predicate information (identified by Operation ID):
---------------------------------------------------

1-filter (ROWNUM&LT;2)
3-access ("bill_id" =ltrim (: B1,ɔ '))


Statistics
----------------------------------------------------------
1 Recursive calls
0 db Block gets
5 Consistent gets
0 physical Reads
0 Redo Size
559 Bytes sent via sql*net to client
552 Bytes received via sql*net from client
2 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
1 rows processed
Optimizer_features_enable modified to 11.2, _optimizer_batch_table_access_by_rowid will be interlocked into Fasle
Sql> alter session set Optimizer_features_enable =ཇ.2.0.3 '

Session altered.

Sql> col name for A52
Col value for A24
Col description for A50
Set Linesize 150
Select A.KSPPINM NAME,B.KSPPSTVL value,a.ksppdesc description
From X$ksppi A,X$KSPPCV b
where a.inst_idsql> sql> sql> sql> 2 3 = USERENV (' Instance ')
and b.inst_id = USERENV (' Instance ')
and a.indx = B.indx
and Upper (A.KSPPINM) like Upper ('%&param% ')
Order BY name
4 5 6 7 8/
Enter value for param: _optimizer_batch_table_access_by
Old 6:and Upper (A.KSPPINM) like Upper ('%&param% ')
New 6:and Upper (A.KSPPINM) like Upper ('%_optimizer_batch_table_access_by% ')

NAME VALUE DESCRIPTION
------------------------------------------ -------------- -----------------------------------------
_optimizer_batch_table_access_by_rowid FALSE Enable table access by rowID IO batching
As you can see, adjusting the optimizer_features_enable parameters directly affects some database-related optimizer parameters, such as: _optimizer_batch_table_access_by_rowid

Contact: Mobile Phone (13429648788) QQ (107644445)
Link:http://www.xifenfei.com/5894.html
Author: Xi-FEI

Alibaba Cloud Hot Products
Elastic Compute Service (ECS)	Dedicated Host (DDH)	ApsaraDB RDS for MySQL (RDS)	ApsaraDB for PolarDB(PolarDB)	AnalyticDB for PostgreSQL (ADB for PG)
AnalyticDB for MySQL(ADB for MySQL)	Data Transmission Service (DTS)	Server Load Balancer (SLB)	Global Accelerator (GA)	Cloud Enterprise Network (CEN)
Object Storage Service (OSS)	Content Delivery Network (CDN)	Short Message Service (SMS)	Container Service for Kubernetes (ACK)	Data Lake Analytics (DLA)
ApsaraDB for Redis (Redis)	ApsaraDB for MongoDB (MongoDB)	NAT Gateway	VPN Gateway	Cloud Firewall
Anti-DDoS	Web Application Firewall (WAF)	Log Service	DataWorks	MaxCompute
Elastic MapReduce (EMR)	Elasticsearch