317TABLE ACCESS BY INDEX ROWID BATCHED2
[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 |
------------------------------------------------------------------------------------------------------------------------------------------------
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2151975/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 317TABLE ACCESS BY INDEX ROWID BATCHED3IndexBAT
- 0317TABLE ACCESS BY INDEX ROWID BATCHEDIndexBAT
- 【ROWID】Oracle rowid說明Oracle
- MySQL中的_rowidMySql
- oracle rowid詳解Oracle
- [20191011]拆分rowid 2.txt
- [20191012]組成rowid.txt
- mysql 5.7 _rowid虛列之一MySql
- 淺談Rowid中的行號
- 【TUNE_ORACLE】ROWID切片SQL參考OracleSQL
- Oracle redo解析之-4、rowid的計算Oracle Redo
- 基於ROWID更新的物化檢視測試
- AP(Access Pointer)
- svn access to forBiddenORB
- JAVA使用accessJava
- Index of /virtualboxIndex
- PostgreSQL：INDEXSQLIndex
- Extract rows from a CORRUPT table creating ROWID from DBA_EXTENTS
- oracle invisible index與unusable index的區別OracleIndex
- SQL Access Advisor(zt)SQL
- Java連線AccessJava
- Configuring Harbor with HTTPS AccessHTTP
- Python, pandas: how to sort dataframe by index// Merge two dataframes by indexPythonIndex
- Django Error: [WinError 10013] An attempt was made to access a socket in a way forbidden by its access permissionsDjangoErrorORB
- create index .. onlineIndex
- index.jspIndexJS
- null與indexNullIndex
- PostgreSQL DBA(119) - pgAdmin(LIMIT：Index Scan vs Bitmap Index Scan)SQLMITIndex
- 小程式報錯Invoke event bindViewTap in page: pages/index/indexViewIndex
- Artificial Intelligence Index：2018年AI Index報告出爐IntelIndexAI
- Thrift RPC新增access logRPC
- ACCESS 模擬雪花ID
- TypeScript 之 Indexed Access TypesTypeScriptIndex
- access偏移注入原理
- Access建立表/新建表
- Elasticsearch之索引模板index template與索引別名index aliasElasticsearch索引Index
- Index of /debian-cd/Index
- enable_index_filterIndexFilter