【12c新特性】12cR1 ROWID IO Batching特性

在介绍12cR1的这个优化器特性之前,我们先来看如下的例子:

 

SQL> create table sample nologging tablespace users as select rownum t1  from dual  connect by level<=900000;  

Table created.  

SQL> alter table sample add t2 number;

Table altered.

update sample set t2=dbms_random.value(1,999999);

900000 rows updated.

SQL> commit;
Commit complete.

SQL> create index ind_t1 on sample(t1) nologging tablespace users;
Index created.

SQL> create index ind_t2 on sample(t2) nologging tablespace users;
Index created.

SQL> exec dbms_stats.gather_table_stats(USER,'SAMPLE',cascade=>TRUE);
PL/SQL procedure successfully completed.

SQL> select blocks,NUM_ROWS from dba_tables where table_name='SAMPLE';

    BLOCKS   NUM_ROWS
---------- ----------
      9107     902319

SQL> select CLUSTERING_FACTOR,LEAF_BLOCKS,DISTINCT_KEYS,index_name from dba_indexes where table_name='SAMPLE';

CLUSTERING_FACTOR LEAF_BLOCKS DISTINCT_KEYS INDEX_NAME
----------------- ----------- ------------- ------------------------------
             1370        2004        900000 IND_T1
           899317        4148        900000 IND_T2

alter session set events '10046 trace name context forever,level 12';

set autotrace traceonly;

alter system flush buffer_cache;

alter session set "_optimizer_batch_table_access_by_rowid"=true;

 select /*+ index(sample ind_t2) */ * from sample where t2 between 1 and 999997;

 select /*+ index(sample ind_t2) */ *
from
 sample where t2 between 1 and 999997

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch    60001      4.68       8.56      12754    1810330          0      899999
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    60003      4.68       8.56      12754    1810330          0      899999

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
    899999     899999     899999  TABLE ACCESS BY INDEX ROWID BATCHED SAMPLE (cr=1810330 pr=12754 pw=0 time=20413784 us cost=903657 size=24300000 card=900000)
    899999     899999     899999   INDEX RANGE SCAN IND_T2 (cr=63873 pr=4150 pw=0 time=4655140 us cost=4155 size=0 card=900000)(object id 92322)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                   60001        0.00          0.32
  Disk file operations I/O                        1        0.00          0.00
  db file sequential read                     11388        0.00          1.70
  SQL*Net message from client                 60001        0.00          8.95
  db file parallel read                         197        0.00          0.00

 alter system flush buffer_cache;

alter session set "_optimizer_batch_table_access_by_rowid"=false;

 select /*+ index(sample ind_t2) */ * from sample where t2 between 1 and 999997;

 select /*+ index(sample ind_t2) */ *
from
 sample where t2 between 1 and 999997

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch    60001      4.70       8.82      12754    1810333          0      899999
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    60003      4.70       8.82      12754    1810333          0      899999

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
    899999     899999     899999  TABLE ACCESS BY INDEX ROWID SAMPLE (cr=1810333 pr=12754 pw=0 time=25464232 us cost=903657 size=24300000 card=900000)
    899999     899999     899999   INDEX RANGE SCAN IND_T2 (cr=63874 pr=4150 pw=0 time=4404956 us cost=4155 size=0 card=900000)(object id 92322)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                   60001        0.00          0.32
  db file sequential read                     12754        0.00          1.85
  SQL*Net message from client                 60001        0.00          8.95

 

 

我们看到了一个陌生的operation “ TABLE ACCESS BY INDEX ROWID BATCHED” 注意 这个Batched是 之前的版本没有的。

 

须知 TABLE ACCESS BY ROWID 这种常见操作是从 子数据集合(例如INDEX中)获得必要的ROWID, 以便在表上定位到对应的行fetch对应数据。 若该行不在Buffer Cache中,则该 Table Access by ROWID的数据集合需要等待必要的IO完成才能处理下一个ROWID。 在很多场景中IO延迟在这里成为重要的瓶颈, 由于不管是RANGE SCAN、FULL SCAN还是Access By Rowid默认均使用DB FILE SEQUENTIAL READ所以如果访问的数据恰巧不在内存里+ 它要Fetch大量的数据行则 往往其整体相应速度和逻辑读要多于全表扫描。

 

常见在以下三种场景中多需要Table Access by Rowid的数据源访问:

  1. Index Range SCan
  2. Bitmap index plan
  3. Nested Loop Join

 

所以Oracle开发人员想到了要使用prefetch预读取数据源来提升性能,通过遍历ROWID以找出那些需要完成的IO操作并prefetch其数据源,将那些数据块预先读入。这里的实现上应当是通过buffer 驱动数据源哪里获得的ROWID,之后通过遍历这些 ROWID对应的的找到需要做物理读的数据块,并使用向量Io操作(例如上文中的db file parallel read)来prefetch这些数据块到buffer cache中,这样TABLE ACCESS By ROWID的访问就可以保证必要的块(主要是表块)均在buffer cache中。

使用此Batching Io特性可以有效减少IO延迟造成的性能损耗,但并不是任何场景都有效。由于实际能buffer的ROWID是有限的,而且是在不知道哪些ROWID对应需要IO哪些不需要的情况下全部都复制到buffer中,所以如果buffer的所有ROWID对应只需要少量的IO,则该IO Batching特性带来的性能改善将最小化。 亦或者遇到的ROWID对应的数据块全部在内存在 一点Io都不需要,则这种prefetch数据的行为有画蛇添足之嫌,反倒会徒增CPU时间片。

 

目前控制该特性的 优化器参数为_ optimizer_batch_table_access_by_rowid,该参数2个选项 TRUE /FALSE负责控制是否启用Table access by ROWID IO batching。

 

还可以通过 BATCH_TABLE_ACCESS_BY_ROWID和 NO_BATCH_TABLE_ACCESS_BY_ROWID 2个HINT来控制是否启用该特性, HINT的优先级高于参数optimizer_batch_table_access_by_rowid。不过目前在12.1.0.1.0上测试该HINT仍有一些问题。

 

 

 

SQL> select * from V$VERSION where rownum=1;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

  1* select name from v$SQL_HINT where name like '%BATCH%'

NAME
----------------------------------------------------------------
NLJ_BATCHING
NO_NLJ_BATCHING
BATCH_TABLE_ACCESS_BY_ROWID
NO_BATCH_TABLE_ACCESS_BY_ROWID

SQL> alter session set "_optimizer_batch_table_access_by_rowid"=true;

Session altered.

SQL>   select /*+     index(sample ind_t2)  NO_BATCH_TABLE_ACCESS_BY_ROWID */ * from sample where t2 between 1 and 999997;

899999 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3882332507

----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |   900K|    23M|   903K  (1)| 00:00:36 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| SAMPLE |   900K|    23M|   903K  (1)| 00:00:36 |
|*  2 |   INDEX RANGE SCAN                  | IND_T2 |   900K|       |  4155   (1)| 00:00:01 |
----------------------------------------------------------------------------------------------

 

  1. Pingback: Oracle Database 12c新特性汇总页面 – ORACLE数据库数据恢复、性能优化、故障诊断来问问MACLEAN

  2. Hello,Just add the alias to hint NO_BATCH_TABLE_ACCESS_BY_ROWID and hint work as expected:select /*+ index(sample ind_t2) NO_BATCH_TABLE_ACCESS_BY_ROWID(SAMPLE) */ * from sample where t2 between 1 and 999997;