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 ,Ɔ') 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,Ɔ'))
 
 
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('%&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
設定_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 ,Ɔ') 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,Ɔ'))
 
 
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 = ཇ.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
這裡可以看出來,在調整optimizer_features_enable參數後,會直接影響某些資料庫相關的最佳化器參數,例如:_optimizer_batch_table_access_by_rowid

 

聯絡：手機(13429648788) QQ(107644445)
連結：http://www.xifenfei.com/5894.html
作者：惜分飛