[20130910]12C执行计划的TABLE ACCESS BY INDEX ROWID BATCHED(补充).txt

链接
http://space.itpub.net/267265/viewspace-772371

写了12c下在范围扫描时可能出现的TABLE ACCESS BY INDEX ROWID BATCHED，这是一种新的执行方式，能够
提高执行效率，特别在数据聚集很好的情况下。

既然是12c的一个特性应该有一个参数关闭这个特性。重复前面的例子：

1.建立测试环境:

SCOTT@test01p> @ver

BANNER                                                                               CON_ID

-------------------------------------------------------------------------------- ----------

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

SCOTT@test01p> create table t (id number,name varchar2(20));
Table created.

--打开3个session，分别执行如下：
-session 1:
insert into t values (1,lpad('a',20,'a'));
commit ;

-session 2:
insert into t  values (2,lpad('b',20,'b'));
commit ;

-session 3:
insert into t  values (3,lpad('c',20,'c'));
commit ;

insert into t  select rownum+3 id ,lpad('x',20,'x') name from dual connect by level <=97;
commit ;

--这样操作可以导致id=1在一个数据块id=2,3在另外的数据块。

SCOTT@test01p> select rowid ,t.* from t where id between 1 and 3;

ROWID                      ID NAME

------------------ ---------- --------------------

AAAWxnAAJAAAAC1AAA          1 aaaaaaaaaaaaaaaaaaaa

AAAWxnAAJAAAAC3AAA          2 bbbbbbbbbbbbbbbbbbbb

AAAWxnAAJAAAAC3AAB          3 cccccccccccccccccccc

COTT@test01p> @lookup_rowid AAAWxnAAJAAAAC1AAA

   OBJECT       FILE      BLOCK        ROW DBA

--------- ---------- ---------- ---------- --------------------

    93287          9        181          0 9,181

COTT@test01p> @lookup_rowid AAAWxnAAJAAAAC3AAA

   OBJECT       FILE      BLOCK        ROW DBA

--------- ---------- ---------- ---------- --------------------

    93287          9        183          0 9,183

SCOTT@test01p> create unique index i_t_id on t(id);
Index created.

--分析表
SCOTT@test01p> execute dbms_stats.gather_table_stats(user,'t');
PL/SQL procedure successfully completed.

2.测试

SCOTT@test01p> @dpc '' ''

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  3uy89r3c5z5yy, child number 0

-------------------------------------

select * from t where id between 1 and 100

Plan hash value: 3446268138

----------------------------------------------------------------------------

| Id  | Operation                           | Name   | E-Rows | Cost (%CPU)|

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT                    |        |        |     3 (100)|

|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T      |    100 |     3   (0)|

|*  2 |   INDEX RANGE SCAN                  | I_T_ID |    100 |     1   (0)|

----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("ID">=1 AND "ID"<=100)

--即使扫描全部数据，选择的也是使用索引。

SYS@test01p> @hide _optimizer_batch_table_access_by_rowid

old  10:  and lower(a.ksppinm) like lower('%&1%')

new  10:  and lower(a.ksppinm) like lower('%_optimizer_batch_table_access_by_rowid%')

NAME                                     DESCRIPTION                                DEFAULT_VALUE  SESSION_VALUE  SYSTEM_VALUE

---------------------------------------- ------------------------------------------ -------------- -------------- ------------

_optimizer_batch_table_access_by_rowid   enable table access by ROWID IO batching   TRUE           TRUE           TRUE

SCOTT@test01p> alter session set "_optimizer_batch_table_access_by_rowid"=false;

Session altered.

SCOTT@test01p> @dpc '' ''

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  3uy89r3c5z5yy, child number 1

-------------------------------------

select * from t where id between 1 and 100

Plan hash value: 4153437776

--------------------------------------------------------------------

| Id  | Operation                   | Name   | E-Rows | Cost (%CPU)|

--------------------------------------------------------------------

|   0 | SELECT STATEMENT            |        |        |     3 (100)|

|   1 |  TABLE ACCESS BY INDEX ROWID| T      |    100 |     3   (0)|

|*  2 |   INDEX RANGE SCAN          | I_T_ID |    100 |     1   (0)|

--------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("ID">=1 AND "ID"<=100)

--回到原来的模式，奇怪的是为什么选择的还是INDEX RANGE SCAN+ TABLE ACCESS BY INDEX ROWID扫描呢？
--正常应该选择全表扫描。

SCOTT@test01p> set autot traceonly

SCOTT@test01p> select * from t where id between 1 and 100;

100 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 4153437776

--------------------------------------------------------------------------------------

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |        |   100 |  2400 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T      |   100 |  2400 |     3   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | I_T_ID |   100 |       |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("ID">=1 AND "ID"<=100)

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          4  consistent gets

          0  physical reads

          0  redo size

       3548  bytes sent via SQL*Net to client

        544  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

        100  rows processed

--奇怪逻辑读还是4.难道oracle改进了什么？能力如此，oracle许多东西不了解，那位知道给出答案！