联系：手机/微信(+86 17813235971) QQ(107644445)
标题：Oracle 12C TABLE ACCESS BY INDEX ROWID BATCHED
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
从Oracle 12C开始执行计划中可能会出现TABLE ACCESS BY INDEX ROWID BATCHED,官方的解释：TABLE ACCESS BY INDEX ROWID BATCHED：means that the database retrieves a few rowids from the index, and then attempts to access rows in block order to improve the clustering and reduce the number of times that the database must access a block.主要意思:对于一个块中多个rowid,通过批量减少访问快的次数.而作为12.1的新特性,数据库是通过_optimizer_batch_table_access_by_rowid来控制的
数据库版本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执行计划
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 ,'0') 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<2)
3 - access("BILL_ID"=LTRIM(:B1,'0'))
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参数为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('%¶m%')
7 order by name
8 /
Enter value for param: _optimizer_batch_table_access_by_rowid
old 6: and upper(a.ksppinm) LIKE upper('%¶m%')
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
设置_optimizer_batch_table_access_by_rowid为false,执行计划由TABLE ACCESS BY INDEX ROWID BATCHED变为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 ,'0') 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<2)
3 - access("BILL_ID"=LTRIM(:B1,'0'))
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修改为11.2之后,_optimizer_batch_table_access_by_rowid会联锁变为fasle
SQL> alter session set optimizer_features_enable = '11.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('%¶m%')
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('%¶m%')
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
这里可以看出来,在调整optimizer_features_enable参数后,会直接影响某些数据库相关的优化器参数,例如:_optimizer_batch_table_access_by_rowid