[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> @ ver1PORT_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 0SCOTT@test01p> show arrayarraysize 200SCOTT@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 SYSAAAaKPAAJAAAAaWAAP SYSAAAaKPAAJAAAARtAAd SYSAAAaKPAAJAAAAK5AAw SYSAAAaKPAAJAAAAbGAAV SYSAAAaKPAAJAAAAIFAAK SYSAAAaKPAAJAAAASIAAB SYSAAAaKPAAJAAAAMIAAQ SYSAAAaKPAAJAAAAOMAAp SYS9 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_rowidNAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE---------------------------------------- ---------------------------------------- ------------- ------------- ------------_optimizer_batch_table_access_by_rowid enable table access by ROWID IO batching TRUE TRUE TRUESCOTT@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 SYSAAAaKPAAJAAAAaWAAP SYSAAAaKPAAJAAAARtAAd SYSAAAaKPAAJAAAAK5AAw SYSAAAaKPAAJAAAAbGAAV SYSAAAaKPAAJAAAAIFAAK SYSAAAaKPAAJAAAASIAAB SYSAAAaKPAAJAAAAMIAAQ SYSAAAaKPAAJAAAAOMAAp SYS9 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 10Plan 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$1Predicate 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 12select 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 10END OF STMTPARSE #180365864:c=62400,e=231012,p=15,cr=141,cu=0,mis=1,r=0,dep=0,og=1,plh=2683697726,tim=1785413704EXEC #180365864:c=0,e=45,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2683697726,tim=1785413932WAIT #180365864: nam='SQL*Net message to client' ela= 7 driver id=1413697536 #bytes=1 p3=0 obj#=665 tim=1785414043WAIT #180365864: nam='db file sequential read' ela= 14145 file#=9 block#=363 blocks=1 obj#=107152 tim=1785428269WAIT #180365864: nam='db file sequential read' ela= 315 file#=9 block#=364 blocks=1 obj#=107152 tim=1785428826WAIT #180365864: nam='db file sequential read' ela= 5921 file#=9 block#=1595 blocks=1 obj#=107151 tim=1785434811FETCH #180365864:c=0,e=20917,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=2683697726,tim=1785435009WAIT #180365864: nam='SQL*Net message from client' ela= 594 driver id=1413697536 #bytes=1 p3=0 obj#=107151 tim=1785435681WAIT #180365864: nam='db file sequential read' ela= 7635 file#=9 block#=1686 blocks=1 obj#=107151 tim=1785443416WAIT #180365864: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=107151 tim=1785443628WAIT #180365864: nam='db file sequential read' ela= 17772 file#=9 block#=1133 blocks=1 obj#=107151 tim=1785461472WAIT #180365864: nam='db file sequential read' ela= 6360 file#=9 block#=697 blocks=1 obj#=107151 tim=1785468019WAIT #180365864: nam='db file sequential read' ela= 276 file#=9 block#=1734 blocks=1 obj#=107151 tim=1785468533WAIT #180365864: nam='db file sequential read' ela= 6200 file#=9 block#=517 blocks=1 obj#=107151 tim=1785474873WAIT #180365864: nam='db file sequential read' ela= 10777 file#=9 block#=1160 blocks=1 obj#=107151 tim=1785485815WAIT #180365864: nam='db file sequential read' ela= 11727 file#=9 block#=776 blocks=1 obj#=107151 tim=1785497741WAIT #180365864: nam='db file sequential read' ela= 11447 file#=9 block#=908 blocks=1 obj#=107151 tim=1785509403FETCH #180365864:c=0,e=73927,p=8,cr=9,cu=0,mis=0,r=8,dep=0,og=1,plh=2683697726,tim=1785509674STAT #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.875WAIT #180365864: nam='SQL*Net message from client' ela= 3228967 driver id=1413697536 #bytes=1 p3=0 obj#=107151 tim=1788738943CLOSE #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_ROWIDNAME 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 0NO_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 '' outlinePLAN_TABLE_OUTPUT-------------------------------------SQL_ID 9c0n73wph7xax, child number 0-------------------------------------select /*+ NO_BATCH_TABLE_ACCESS_BY_ROWID */ rowid ,owner from t whereobject_id between 1 and 10Plan 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$1Outline 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 twhere object_id between 1 and 10Plan 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$1Predicate 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 520Plan 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 520Plan 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 300SCOTT@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 200SCOTT@test01p> show arrayarraysize 200SCOTT@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 |------------------------------------------------------------------------------------------------------------------------------------------------