[20180317]12c TABLE ACCESS BY INDEX ROWID BATCHED2.txt

news/2024/7/7 20:06:27

[20180317]12c TABLE ACCESS BY INDEX ROWID BATCHED2.txt

--//简单探究12c TABLE ACCESS BY INDEX ROWID BATCHED特性.
--//当使用12c时,执行计划出现TABLE ACCESS BY INDEX ROWID BATCHED,做一些探究.
--//本文主要探究如何使用提示或者隐含参数控制这种特性.

1.环境:

SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

SCOTT@test01p> show array
arraysize 200

SCOTT@test01p> create table t as select * from all_objects order by  DBMS_RANDOM.random;
Table created.

SCOTT@test01p> create index i_t_object_id on t(object_id);
Index created.

--//分析.
execute sys.dbms_stats.gather_table_stats ( OwnName => nvl('',user),TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false)

SCOTT@test01p> select rowid ,owner from t where object_id  between  1 and 10;
ROWID              OWNER
------------------ --------------------
AAAaKPAAJAAAAY7AAE SYS
AAAaKPAAJAAAAaWAAP SYS
AAAaKPAAJAAAARtAAd SYS
AAAaKPAAJAAAAK5AAw SYS
AAAaKPAAJAAAAbGAAV SYS
AAAaKPAAJAAAAIFAAK SYS
AAAaKPAAJAAAASIAAB SYS
AAAaKPAAJAAAAMIAAQ SYS
AAAaKPAAJAAAAOMAAp SYS
9 rows selected.

SCOTT@test01p> select data_object_id,object_id from dba_objects where owner=user and object_name='T';
DATA_OBJECT_ID  OBJECT_ID
-------------- ----------
        107151     107151

--//看看那些参数可以控制取消TABLE ACCESS BY INDEX ROWID BATCHED特性.

2.测试:
--//通过隐含参数_optimizer_batch_table_access_by_rowid可以改变执行计划.
SYS@test> @ hide _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 statistics_level=all;
Session altered.

SCOTT@test01p> select rowid ,owner from t where object_id  between  1 and 10;
ROWID              OWNER
------------------ -----
AAAaKPAAJAAAAY7AAE SYS
AAAaKPAAJAAAAaWAAP SYS
AAAaKPAAJAAAARtAAd SYS
AAAaKPAAJAAAAK5AAw SYS
AAAaKPAAJAAAAbGAAV SYS
AAAaKPAAJAAAAIFAAK SYS
AAAaKPAAJAAAASIAAB SYS
AAAaKPAAJAAAAMIAAQ SYS
AAAaKPAAJAAAAOMAAp SYS
9 rows selected.

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  anscphj6zbgpn, child number 0
-------------------------------------
select rowid ,owner from t where object_id  between  1 and 10
Plan hash value: 2683697726
---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |      1 |        |       |    10 (100)|          |      9 |00:00:00.01 |      12 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T             |      1 |      8 |   184 |    10   (0)| 00:00:01 |      9 |00:00:00.01 |      12 |
|*  2 |   INDEX RANGE SCAN          | I_T_OBJECT_ID |      1 |      8 |       |     2   (0)| 00:00:01 |      9 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1
   2 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID">=1 AND "OBJECT_ID"<=10)

--//现在执行使用TABLE ACCESS BY INDEX ROWID.顺便测试10046事件的跟踪情况.

SCOTT@test01p> alter system flush buffer_cache ;
System altered.

@ 10046on 12
select rowid ,owner from t where object_id  between  1 and 10;
@ 10046off

--//转储文件:
=====================
PARSING IN CURSOR #180365864 len=61 dep=0 uid=109 oct=3 lid=109 tim=1785413706 hv=1307950772 ad='7ff1292e768' sqlid='anscphj6zbgpn'
select rowid ,owner from t where object_id  between  1 and 10
END OF STMT
PARSE #180365864:c=62400,e=231012,p=15,cr=141,cu=0,mis=1,r=0,dep=0,og=1,plh=2683697726,tim=1785413704
EXEC #180365864:c=0,e=45,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2683697726,tim=1785413932
WAIT #180365864: nam='SQL*Net message to client' ela= 7 driver id=1413697536 #bytes=1 p3=0 obj#=665 tim=1785414043
WAIT #180365864: nam='db file sequential read' ela= 14145 file#=9 block#=363 blocks=1 obj#=107152 tim=1785428269
WAIT #180365864: nam='db file sequential read' ela= 315 file#=9 block#=364 blocks=1 obj#=107152 tim=1785428826
WAIT #180365864: nam='db file sequential read' ela= 5921 file#=9 block#=1595 blocks=1 obj#=107151 tim=1785434811
FETCH #180365864:c=0,e=20917,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=2683697726,tim=1785435009
WAIT #180365864: nam='SQL*Net message from client' ela= 594 driver id=1413697536 #bytes=1 p3=0 obj#=107151 tim=1785435681
WAIT #180365864: nam='db file sequential read' ela= 7635 file#=9 block#=1686 blocks=1 obj#=107151 tim=1785443416
WAIT #180365864: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=107151 tim=1785443628
WAIT #180365864: nam='db file sequential read' ela= 17772 file#=9 block#=1133 blocks=1 obj#=107151 tim=1785461472
WAIT #180365864: nam='db file sequential read' ela= 6360 file#=9 block#=697 blocks=1 obj#=107151 tim=1785468019
WAIT #180365864: nam='db file sequential read' ela= 276 file#=9 block#=1734 blocks=1 obj#=107151 tim=1785468533
WAIT #180365864: nam='db file sequential read' ela= 6200 file#=9 block#=517 blocks=1 obj#=107151 tim=1785474873
WAIT #180365864: nam='db file sequential read' ela= 10777 file#=9 block#=1160 blocks=1 obj#=107151 tim=1785485815
WAIT #180365864: nam='db file sequential read' ela= 11727 file#=9 block#=776 blocks=1 obj#=107151 tim=1785497741
WAIT #180365864: nam='db file sequential read' ela= 11447 file#=9 block#=908 blocks=1 obj#=107151 tim=1785509403
FETCH #180365864:c=0,e=73927,p=8,cr=9,cu=0,mis=0,r=8,dep=0,og=1,plh=2683697726,tim=1785509674
STAT #180365864 id=1 cnt=9 pid=0 pos=1 obj=107151 op='TABLE ACCESS BY INDEX ROWID T (cr=12 pr=11 pw=0 time=94758 us cost=10 size=184 card=8)'
STAT #180365864 id=2 cnt=9 pid=1 pos=1 obj=107152 op='INDEX RANGE SCAN I_T_OBJECT_ID (cr=3 pr=2 pw=0 time=14819 us cost=2 size=0 card=8)'

*** 2018-03-17 19:56:06.875
WAIT #180365864: nam='SQL*Net message from client' ela= 3228967 driver id=1413697536 #bytes=1 p3=0 obj#=107151 tim=1788738943
CLOSE #180365864:c=0,e=11,dep=0,type=0,tim=1788739114
=====================

--//可以看到IO的等待事件全是'db file sequential read'.没有'db file parallel read'.

3.测试二:
--//使用NO_BATCH_TABLE_ACCESS_BY_ROWID提示.
SCOTT@test01p> @ sqlhint BATCH_TABLE_ACCESS_BY_ROWID
NAME                           SQL_FEATURE     CLASS                       INVERSE                        TARGET_LEVEL PROPERTY VERSION  VERSION_OUTLINE CON_ID
------------------------------ --------------- --------------------------- ------------------------------ ------------ -------- -------- --------------- ------
BATCH_TABLE_ACCESS_BY_ROWID    QKSFM_EXECUTION BATCH_TABLE_ACCESS_BY_ROWID NO_BATCH_TABLE_ACCESS_BY_ROWID            4      272 12.1.0.1 12.1.0.1             0
NO_BATCH_TABLE_ACCESS_BY_ROWID QKSFM_EXECUTION BATCH_TABLE_ACCESS_BY_ROWID BATCH_TABLE_ACCESS_BY_ROWID               4      272 12.1.0.1 12.1.0.1             0

---//重新登录,取消参数隐含参数_optimizer_batch_table_access_by_rowid设置,执行如下:

SCOTT@test01p> select /*+ NO_BATCH_TABLE_ACCESS_BY_ROWID */ rowid ,owner from t where object_id  between  1 and 10;

SCOTT@test01p> @ dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  9c0n73wph7xax, child number 0
-------------------------------------
select /*+ NO_BATCH_TABLE_ACCESS_BY_ROWID */ rowid ,owner from t where
object_id  between  1 and 10
Plan hash value: 2044526593
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |      1 |        |       |    10 (100)|          |      9 |00:00:00.01 |      12 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T             |      1 |      8 |   184 |    10   (0)| 00:00:01 |      9 |00:00:00.01 |      12 |
|*  2 |   INDEX RANGE SCAN                  | I_T_OBJECT_ID |      1 |      8 |       |     2   (0)| 00:00:01 |      9 |00:00:00.01 |       3 |
-----------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / T@SEL$1
   2 - SEL$1 / T@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
      DB_VERSION('12.1.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."OBJECT_ID"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   2 - access("OBJECT_ID">=1 AND "OBJECT_ID"<=10)
--//可以发现这样使用提示并不生效,实际上还是TABLE ACCESS BY INDEX ROWID BATCHED.不过看Outline Data就知道提示的方法,要写成如下:

select /*+ NO_BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T"@"SEL$1") */ rowid ,owner from t where object_id  between  1 and 10;
select /*+ NO_BATCH_TABLE_ACCESS_BY_ROWID(t) */ rowid ,owner from t where object_id  between  1 and 10;

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  7y2913wgp3t1b, child number 0
-------------------------------------
select /*+ NO_BATCH_TABLE_ACCESS_BY_ROWID(t) */ rowid ,owner from t
where object_id  between  1 and 10

Plan hash value: 2683697726

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |      1 |        |       |    10 (100)|          |      9 |00:00:00.01 |      12 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T             |      1 |      8 |   184 |    10   (0)| 00:00:01 |      9 |00:00:00.01 |      12 |
|*  2 |   INDEX RANGE SCAN          | I_T_OBJECT_ID |      1 |      8 |       |     2   (0)| 00:00:01 |      9 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / T@SEL$1
   2 - SEL$1 / T@SEL$1

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

   2 - access("OBJECT_ID">=1 AND "OBJECT_ID"<=10)

--//执行计划变为TABLE ACCESS BY INDEX ROWID.

--//顺便更正我以前一个错误的观点,一直以为执行计划选择TABLE ACCESS BY INDEX ROWID BATCHED逻辑读会减少,实际上不会变化,
--//因为输出的结果集顺序没有发生变化.可以看到最后的逻辑读都是一样的12.
--//我以前错误的理解确定读取扫描的数据块,然后输出满足条件的结果.实际上还是按照索引的顺序读取键值以及rowid,再读取数据块.
--//TABLE ACCESS BY INDEX ROWID BATCHED仅仅变成了db file parallel read,而且读取的数据块是按照顺序读取,并且是不连续的块,
--//最大127块(当然数据块不在缓存的情况下).

--//在看看如下例子:

SCOTT@test01p> select /*+ index(t)  */ rowid ,owner,object_id from t where object_id  between  1 and 520;
..

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  53r6yurs7jnk8, child number 0
-------------------------------------
select /*+ index(t)  */ rowid ,owner,object_id from t where object_id between  1 and 520
Plan hash value: 2044526593
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |      1 |        |       |   437 (100)|          |    474 |00:00:00.01 |     479 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T             |      1 |    435 | 10005 |   437   (0)| 00:00:01 |    474 |00:00:00.01 |     479 |
|*  2 |   INDEX RANGE SCAN                  | I_T_OBJECT_ID |      1 |    435 |       |     2   (0)| 00:00:01 |    474 |00:00:00.01 |       5 |
-----------------------------------------------------------------------------------------------------------------------------------------------

SCOTT@test01p> select /*+ index(t) NO_BATCH_TABLE_ACCESS_BY_ROWID(t) */ rowid ,owner,object_id from t where object_id  between  1 and 520;
...
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  3kf824wz4rdk5, child number 0
-------------------------------------
select /*+ index(t) NO_BATCH_TABLE_ACCESS_BY_ROWID(t) */ rowid ,owner,object_id from t where object_id  between  1 and 520
Plan hash value: 2683697726
---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |      1 |        |       |   437 (100)|          |    474 |00:00:00.01 |     479 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T             |      1 |    435 | 10005 |   437   (0)| 00:00:01 |    474 |00:00:00.01 |     479 |
|*  2 |   INDEX RANGE SCAN          | I_T_OBJECT_ID |      1 |    435 |       |     2   (0)| 00:00:01 |    474 |00:00:00.01 |       5 |
---------------------------------------------------------------------------------------------------------------------------------------

--//可以发现逻辑读479.两种情况都是一样的.只有参数array以及索引的群集因子才可能改变逻辑读的数量.这里的数据太离散,改变array效果不会太大.

SCOTT@test01p> set array 300
SCOTT@test01p> select /*+ index(t)  */ rowid ,owner,object_id from t where object_id  between  1 and 520;
..

Plan hash value: 2044526593
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |      1 |        |       |   437 (100)|          |    474 |00:00:00.01 |     478 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T             |      1 |    435 | 10005 |   437   (0)| 00:00:01 |    474 |00:00:00.01 |     478 |
|*  2 |   INDEX RANGE SCAN                  | I_T_OBJECT_ID |      1 |    435 |       |     2   (0)| 00:00:01 |    474 |00:00:00.01 |       4 |
-----------------------------------------------------------------------------------------------------------------------------------------------

--//仅仅减少1个.而且在INDEX RANGE SCAN操作,减少到4个.

--//重新建立表按照object_id导入:
SCOTT@test01p> create table tx as select * from t order by object_id;
Table created.

SCOTT@test01p> create index i_tx_object_id on tx(object_id);
Index created.

--//分析略.
SCOTT@test01p> set array 200
SCOTT@test01p> show array
arraysize 200

SCOTT@test01p> select /*+ index(t)  */ rowid ,owner,object_id from tx where object_id  between  1 and 520;

Plan hash value: 475430699
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |      1 |        |       |    10 (100)|          |    474 |00:00:00.01 |      15 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TX             |      1 |    435 | 10005 |    10   (0)| 00:00:01 |    474 |00:00:00.01 |      15 |
|*  2 |   INDEX RANGE SCAN                  | I_TX_OBJECT_ID |      1 |    435 |       |     2   (0)| 00:00:01 |    474 |00:00:00.01 |       5 |
------------------------------------------------------------------------------------------------------------------------------------------------

转载于:https://www.cnblogs.com/lfree/p/8599115.html


http://lihuaxi.xjx100.cn/news/237306.html

相关文章

解析equals(Object obj)和compareTo(T obj)

解析equals(Object obj)和compareTo(T obj)

背景&#xff1a;最近在研究静态扫描的东西&#xff0c;遇到一个规则&#xff1a;"equals(Object obj)" should be overridden along with the "compareTo(T obj)" method 然后就想深度扒一扒equals和compareTo有什么区别 1.java.lang.Object是所有类的父类…
阅读更多...
区块链技术名词简介

区块链技术名词简介

链客&#xff0c;专为开发者而生&#xff0c;有问必答&#xff01; 此文章来自区块链技术社区&#xff0c;未经允许拒绝转载。 零知识证明 零知识证明指证明者能在不向验证者提供任何有用的信息下&#xff0c;使验证者相信某个论断是正确的。零知识证明实质是一种涉及两方或更…
阅读更多...
RPC-原理及RPC实例分析

RPC-原理及RPC实例分析

还有就是&#xff1a;RPC支持的BIO,NIO的理解 (1)BIO: Blocking IO;同步阻塞&#xff1b; (2)NIO:Non-Blocking IO&#xff0c; 同步非阻塞; 参考&#xff1a;IO多路复用,同步&#xff0c;异步&#xff0c;阻塞和非阻塞 区别 在学校期间大家都写过不少程序&#xff0c;比如写个…
阅读更多...
C++基础技术简介

C++基础技术简介

链客&#xff0c;专为开发者而生&#xff0c;有问必答&#xff01; 此文章来自区块链技术社区&#xff0c;未经允许拒绝转载。 容器 容器用于存储数据元素&#xff0c;是由长度可变的同类型的元素构建成的序列。 Vector&#xff1a;用于快速定位任意元素及主要在元素序列的尾…
阅读更多...
客户旅程_编程如何找到我的：21岁开发人员的7年旅程

客户旅程_编程如何找到我的：21岁开发人员的7年旅程

客户旅程Ive read some amazing stories about peoples coding journeys here, and I was interested to share my own as well. Before starting out with anything about my journey, let me introduce myself quickly here, and then well go into flashback mode. 我在这里…
阅读更多...
usermod

usermod

功能说明&#xff1a;用于修改系统已经存在的用户账号信息。 参数选项&#xff1a;-c comment 修改用户password文件中用户说明栏&#xff0c;同useradd -c功能。-d home_dir 修改用户每次登入时所使用的家目录&#xff0c;同useradd -d功能。-e expired_date 修改用户终止日期…
阅读更多...
D3.js系列——初步使用、选择元素与绑定数据

D3.js系列——初步使用、选择元素与绑定数据

D3 的全称是&#xff08;Data-Driven Documents&#xff09;&#xff0c;顾名思义可以知道是一个被数据驱动的文档。听名字有点抽象&#xff0c;说简单一点&#xff0c;其实就是一个 JavaScript 的函数库&#xff0c;使用它主要是用来做数据可视化的。 D3 提供了各种简单易用的…
阅读更多...
开坑序言

开坑序言

保持了多年的博客习惯&#xff0c;在进入工作后却慢慢的被荒废了。其实&#xff0c;在大学期间就有意识的通过博客来归纳总结一些学习中认为比较重要的知识技能。只是当时限于知识面的束缚&#xff0c;总结的都是一些比较基础浅薄的东西。   在经历了将近一年的工作后&#x…
阅读更多...
最新文章

Copyright @ 2022~2023