[20130910]12C執行計劃的TABLE ACCESS BY INDEX ROWID BATCHED(補充).txt

[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許多東西不瞭解，那位知道給出答案！