ITPub博客

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

[20130909]12C执行计划的TABLE ACCESS BY INDEX ROWID BATCHED.txt

原创 Linux操作系统 作者:lfree 时间:2013-09-09 08:57:36 0 删除 编辑
[20130909]12C执行计划的TABLE ACCESS BY INDEX ROWID BATCHED.txt

在写[20130809]12c Clustering Factor.txt,链接

提到执行计划里面存在TABLE ACCESS BY INDEX ROWID BATCHED,这里的BATCHED表示什么? 自己不是很清楚。

既然多了一个batched,一定与原来的不同,具体含义是什么呢?做一些简单的探究:

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> select * from t where id=42;
        ID NAME
---------- --------------------
        42 xxxxxxxxxxxxxxxxxxxx

SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  dvv86knmasnpw, child number 0
-------------------------------------
select * from t where id=42

Plan hash value: 1149237570

--------------------------------------------------------------------
| Id  | Operation                   | Name   | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |        |     1 (100)|
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |      1 |     1   (0)|
|*  2 |   INDEX UNIQUE SCAN         | I_T_ID |      1 |     0   (0)|
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=42)

--执行计划使用TABLE ACCESS BY INDEX ROWID。

SCOTT@test01p> select * from t where id between 1 and 3 ;
        ID NAME
---------- --------------------
         1 aaaaaaaaaaaaaaaaaaaa
         2 bbbbbbbbbbbbbbbbbbbb
         3 cccccccccccccccccccc

SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  38raj6puvxubp, child number 0
-------------------------------------
select * from t where id between 1 and 3
Plan hash value: 3446268138
----------------------------------------------------------------------------
| Id  | Operation                           | Name   | E-Rows | Cost (%CPU)|
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |        |     2 (100)|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T      |      3 |     2   (0)|
|*  2 |   INDEX RANGE SCAN                  | I_T_ID |      3 |     1   (0)|
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID">=1 AND "ID"<=3)
--执行计划使用TABLE ACCESS BY INDEX ROWID BATCHED。 对比以上看看两者的差别到底在那里呢?视乎做范围扫描时,会出现BATCHED。

看看consistent gets:
set autot traceonly 
select * from t where id=42;
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        483  bytes sent via SQL*Net to client
        533  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
--consistent gets=2,如果存在recursive calls,可以执行多次消除影响。

--很明显,索引很小,level=0,就是一个索引的根节点。select * from t where id=42;
--读索引的根节点,读数据块,执行完成(如果建立的索引非唯一,这样会多读1块,大家可另行测试).

set autot traceonly 
select * from t where id between 1 and 3 ;
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        735  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)
          3  rows processed

--如果按照以前操作,读索引的根节点,读id=1的数据块,再读索引的根节点,读id=2的数据块,
--再读索引的根节点,读id=3的数据块,这样应该至少5个逻辑读。
--而实际的consistent gets是多少呢?4个。

--oracle实际上是如何操作的呢?可以猜测一下,oracle先读索引的根节点,确定id=1,id=2,id=3的数据块,然后再
--读取相应的数据块.由于id=1,2,3分别在2个块中,这样要读取3次,这样加起来就是4个逻辑读。好像有点问题???
--注意id=2,3在一个块中。
--这个就是batched的含义,我以前学习oracle的时候,一直以为oracle就应该这样操作的,实际上不是,这个我以前
--无法很好理解oracle的consistent gets。

3.在做一个极端的测试就很清楚了:
SCOTT@test01p> show array
arraysize 200

SCOTT@test01p> Select * from t where id between 4 and 100 ;
        ID NAME
---------- --------------------
         4 xxxxxxxxxxxxxxxxxxxx
....
       100 xxxxxxxxxxxxxxxxxxxx

97 rows selected.

SCOTT@test01p> @dpc '' ' '
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  0z6wka791vxz2, child number 0
-------------------------------------
Select * from t where id between 4 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      |     98 |     3   (0)|
|*  2 |   INDEX RANGE SCAN                  | I_T_ID |     98 |     1   (0)|
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

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

--注意我没有使用hint,执行计划扫描大部分数据,因为id between 4 and 100的一个数据块中。
SCOTT@test01p> select distinct DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) from t where id between 4 and  100;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
                                 183
--按照以前执行计划正常应该选择全表扫描,而现在执行计划选择的是INDEX RANGE SCAN。

SCOTT@test01p> set autot traceonly
SCOTT@test01p> Select * from t where id between 4 and 100 ;
97 rows selected.

Execution Plan
---------------------------
Plan hash value: 3446268138
----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |    98 |  2352 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T      |    98 |  2352 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | I_T_ID |    98 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID">=4 AND "ID"<=100)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       3461  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)
         97  rows processed
 
--可以发现consistent gets=4,这个4如何得到的呢?
--读取索引根节点,确定id between 4 and 100的要读取的数据块,仅仅在1个块中,应该是2而不是4啊?

--实际上再再极端的测试:
SCOTT@test01p> Select * from t where id between 1 and 100 ;
100 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3446268138
----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |   100 |  2400 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| 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
--执行计划扫描全部数据,选择的还是索引扫描,consistent gets=4,注意这个是颠覆性的,如果数据的聚集因子很小,
--在12c下即使扫描很大的范围,选择的执行计划可能是INDEX RANGE SCAN + TABLE ACCESS BY INDEX ROWID BATCHED.

3.做一个10046 跟踪看看:
SCOTT@test01p> alter system flush BUFFER_CACHE;
System altered.

SCOTT@test01p> @10046on 12
Session altered.

SCOTT@test01p> Select * from t where id between 1 and 100 ;
...
SCOTT@test01p> @10046off
Session altered.

=====================
PARSING IN CURSOR #213308544 len=43 dep=0 uid=109 ct=3 lid=109 tim=3272937076 hv=510903843 ad='7ff577ab250' sqlid='cc80t2cg77jj3'
Select * from t where id between 1 and 100
END OF STMT
PARSE #213308544:c=0,e=3840,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=3446268138,tim=3272937074
EXEC #213308544:c=0,e=77,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3446268138,tim=3272937332
WAIT #213308544: nam='SQL*Net message to client' ela= 11 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=3272937493
WAIT #213308544: nam='db file sequential read' ela= 30835 file#=9 block#=187 blocks=1 obj#=93288 tim=3272968472
WAIT #213308544: nam='db file sequential read' ela= 8764 file#=9 block#=181 blocks=1 obj#=93287 tim=3272977579
FETCH #213308544:c=0,e=40152,p=2,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=3446268138,tim=3272977730
WAIT #213308544: nam='SQL*Net message from client' ela= 618 driver id=1413697536 #bytes=1 p3=0 obj#=93287 tim=3272978490
WAIT #213308544: nam='db file sequential read' ela= 473 file#=9 block#=183 blocks=1 obj#=93287 tim=3272979246
WAIT #213308544: nam='SQL*Net message to client' ela= 7 driver id=1413697536 #bytes=1 p3=0 obj#=93287 tim=3272979385
FETCH #213308544:c=0,e=893,p=1,cr=2,cu=0,mis=0,r=99,dep=0,og=1,plh=3446268138,tim=3272979596
STAT #213308544 id=1 cnt=100 pid=0 pos=1 bj=93287 p='TABLE ACCESS BY INDEX ROWID BATCHED T (cr=4 pr=3 pw=0 time=40138 us cost=3 size=2400 card=100)'
STAT #213308544 id=2 cnt=100 pid=1 pos=1 bj=93288 p='INDEX RANGE SCAN I_T_ID (cr=2 pr=1 pw=0 time=33856 us cost=1 size=0 card=100)'

*** 2013-09-08 21:09:30.900
WAIT #213308544: nam='SQL*Net message from client' ela= 25387691 driver id=1413697536 #bytes=1 p3=0 obj#=93287 tim=3298367575
CLOSE #213308544:c=0,e=37,dep=0,type=0,tim=3298367930
=====================
--如果看db file sequential read读取的块3次,
--而STAT #213308544 id=2 cnt=100 pid=1 pos=1 bj=93288 p='INDEX RANGE SCAN I_T_ID (cr=2 pr=1 pw=0 time=33856 us cost=1 size=0 card=100)'
--指示cr=2,也就是讲索引读取2次。

SCOTT@test01p> select object_id,data_object_id,object_name from dba_objects where object_name in('I_T_ID','T') and wner=user;
 OBJECT_ID DATA_OBJECT_ID OBJECT_NAME
---------- -------------- ------------
     93287          93287 T
     93288          93288 I_T_ID

SCOTT@test01p> select segment_name,segment_type,header_file,header_block,bytes,blocks from dba_segments where segment_name in('I_T_ID','T') and wner=user;
SEGMENT_NAME         SEGMENT_TYPE       HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS
-------------------- ------------------ ----------- ------------ ---------- ----------
I_T_ID               INDEX                        9          186      65536          8
T                    TABLE                        9          178      65536          8

--视乎这种方式索引读取2次,即使是访问1个数据块也要读取2次,许多东西概念还是不是很清楚。但是执行计划的TABLE ACCESS BY
--INDEX ROWID BATCHED的大概意思应该差不多。

总结:
注意12c这种执行计划的变化,即使出现扫描很大的范围,如果CF因子很小,执行计划选择的是 INDEX RANGE SCAN ,而不是全表扫描。

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

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

注册时间:2008-01-03

  • 博文量
    2457
  • 访问量
    6261314