在介绍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的数据源访问:
- Index Range SCan
- Bitmap index plan
- 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 | ----------------------------------------------------------------------------------------------
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;
Hi Mikhail, you had given some interesting hint
感觉不是那么好这个功能,. 据说 从索引找到符合条件的ROWID,然后去表里提取数据,提取完了后接着顺着索引键找下个符合条件的ROWID. 当数据很大的时候,这个两个动作执行很频繁,虽然从数据库整体上消耗资源不多. 因为它是串行执行该两个动作. 索引是根据索引键值来排序的.并非根据ROWID来排序的.第一个ROWID和第10个ROWID 是属于同一个块中,当找到第10个ROWID时,或许第一个ROWID读取的块,已经被刷出了内存,它的算法不好, 好的算法是 把整个索引先扫描 完后再根据ROWID 排序分组 并且提取数据. 这样索引聚集因子和DB FILE SEQUENTIAL READ带来性能问题就解决了