ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20130910]12C执行计划的TABLE ACCESS BY INDEX ROWID BATCHED(补充).txt

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

原创 Linux操作系统 作者:lfree 时间:2013-09-12 08:57:20 0 删除 编辑
[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许多东西不了解,那位知道给出答案!





来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-772596/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2599
  • 访问量
    6371435