[20180319]直接路徑讀特例12c.txt
[20180319]直接路徑讀特例12c.txt
--//昨天的測試突然想起以前遇到的直接路徑讀特例,在12c重複測試看看.
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> 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.
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
--//分析.
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.
2.測試:
SCOTT@test01p> alter system flush buffer_cache ;
System altered.
SCOTT@test01p> alter session set statistics_level=all;
Session altered.
SCOTT@test01p> select rowid ,owner,object_id from t where rowid between 'AAAaKPAAJAAAAY7AAE' and 'AAAaKPAAJAAAAY7AAF';
ROWID OWNER OBJECT_ID
------------------ -------------------- ----------
AAAaKPAAJAAAAY7AAE SYS 2
AAAaKPAAJAAAAY7AAF SYS 10575
Plan hash value: 280204748
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 421 (100)| | 2 |00:00:00.07 | 5 | 2 |
|* 1 | TABLE ACCESS BY ROWID RANGE| T | 1 | 225 | 5175 | 421 (1)| 00:00:01 | 2 |00:00:00.07 | 5 | 2 |
---------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 280204748
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 421 (100)| | 2 |00:00:00.01 | 5 | 1 |
|* 1 | TABLE ACCESS BY ROWID RANGE| T | 1 | 225 | 5175 | 421 (1)| 00:00:01 | 2 |00:00:00.01 | 5 | 1 |
---------------------------------------------------------------------------------------------------------------------------------------
--//多次執行總存在一個物理讀.
SCOTT@test01p> @ viewsess 'physical reads direct%'
NAME STATISTIC# VALUE SID
---------------------------------------------------------------------- ---------- ------ ----------
physical reads direct 91 4 242
physical reads direct temporary tablespace 104 0 242
physical reads direct (lob) 170 0 242
physical reads direct for securefile flashback block new 181 0 242
SCOTT@test01p> select rowid ,owner,object_id from t where rowid between 'AAAaKPAAJAAAAY7AAE' and 'AAAaKPAAJAAAAY7AAF';
ROWID OWNER OBJECT_ID
------------------ -------------------- ----------
AAAaKPAAJAAAAY7AAE SYS 2
AAAaKPAAJAAAAY7AAF SYS 10575
SCOTT@test01p> @ viewsess 'physical reads direct%'
NAME STATISTIC# VALUE SID
---------------------------------------------------------------------- ---------- ------ ----------
physical reads direct 91 5 242
physical reads direct temporary tablespace 104 0 242
physical reads direct (lob) 170 0 242
physical reads direct for securefile flashback block new 181 0 242
3.不過如果where rowid between 'AAAaKPAAJAAAAY7AAE' and 'AAAaKPAAJAAAAY7AAE';
SCOTT@test01p> select rowid ,owner,object_id from t where rowid between 'AAAaKPAAJAAAAY7AAE' and 'AAAaKPAAJAAAAY7AAE';
ROWID OWNER OBJECT_ID
------------------ -------------------- ----------
AAAaKPAAJAAAAY7AAE SYS 2
SCOTT@test01p> @ viewsess 'physical reads direct%'
NAME STATISTIC# VALUE SID
---------------------------------------------------------------------- ---------- ------ ----------
physical reads direct 91 5 242
physical reads direct temporary tablespace 104 0 242
physical reads direct (lob) 170 0 242
physical reads direct for securefile flashback block new 181 0 242
--//physical reads direct沒有增加.
SCOTT@test01p> select file#,block#,status from v$bh where OBJD=107151 and STATUS<>'free';
FILE# BLOCK# STATUS
---------- ---------- --------------------
9 186 xcur
9 1595 xcur
SCOTT@test01p> @ rowid AAAaKPAAJAAAAY7AAE
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
107151 9 1595 4 0x240063B 9,1595 alter system dump datafile 9 block 1595
--//rowid='AAAaKPAAJAAAAY7AAE'已經進入資料快取.
SCOTT@test01p> select header_file,header_block from dba_segments where owner=user and segment_name='T';
HEADER_FILE HEADER_BLOCK
----------- ------------
9 186
--//dba =9 , 186 對應 段頭.
--//我一直不明白為什麼oracle要這樣設計.12c與11g的測試一樣.
--//還有1點不明白的地方就是:
select rowid ,owner,object_id from t where rowid between 'AAAaKPAAJAAAASIAAB' and 'AAAaKPAAJAAAASIAAC';
Plan hash value: 280204748
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 421 (100)| | 2 |00:00:00.04 | 5 | 1 |
|* 1 | TABLE ACCESS BY ROWID RANGE| T | 1 | 2 | 46 | 421 (1)| 00:00:01 | 2 |00:00:00.04 | 5 | 1 |
---------------------------------------------------------------------------------------------------------------------------------------
select rowid ,owner,object_id from t where rowid between 'AAAaKPAAJAAAASIAAB' and 'AAAaKPAAJAAAASIAAB';
Plan hash value: 3207308387
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1 (100)| | 1 |00:00:00.01 | 1 |
| 1 | TABLE ACCESS BY USER ROWID| T | 1 | 1 | 23 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 |
-----------------------------------------------------------------------------------------------------------------------------
--//為什麼前者邏輯讀是5,而後者為1.
ALTER SESSION SET EVENTS '10200 TRACE NAME CONTEXT FOREVER, LEVEL 1';
select rowid ,owner,object_id from t where rowid between 'AAAaKPAAJAAAASIAAB' and 'AAAaKPAAJAAAASIAAC';
ALTER SESSION SET EVENTS '10200 TRACE NAME CONTEXT off';
D:\tools\rlwrap>grep "started" D:\app\oracle\diag\rdbms\test\test\trace\test_ora_5280.trc
grep "started" D:\app\oracle\diag\rdbms\test\test\trace\test_ora_5280.trc
ktrget3(): started for block <0x0003 : 0x02400488> objd: 0x0001a28f
ktrget3(): started for block <0x0003 : 0x02400488> objd: 0x0001a28f
--//轉儲內容:
ktrget3(): started for block <0x0003 : 0x02400488> objd: 0x0001a28f
env [0x0000000002197E9C]: (scn: 0x0000.019be656 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 statement num=0 parent xid: 0x0000.000.00000000 st-scn: 0x0000.00000000 hi-scn: 0x0000.00000000 ma-scn: 0x0000.019be619 flg: 0x00000661)
ktrgcm(): completed for block <0x0003 : 0x02400488> objd: 0x0001a28f
ktrget3(): completed for block <0x0003 : 0x02400488> objd: 0x0001a28f
ktrget3(): started for block <0x0003 : 0x02400488> objd: 0x0001a28f
env [0x0000000002197E9C]: (scn: 0x0000.019be656 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 statement num=0 parent xid: 0x0000.000.00000000 st-scn: 0x0000.00000000 hi-scn: 0x0000.00000000 ma-scn: 0x0000.019be619 flg: 0x00000660)
ktrgcm(): completed for block <0x0003 : 0x02400488> objd: 0x0001a28f
ktrget3(): completed for block <0x0003 : 0x02400488> objd: 0x0001a28f
SCOTT@test01p> @ 10046on 12
old 1: alter session set events '10046 trace name context forever, level &1'
new 1: alter session set events '10046 trace name context forever, level 12'
Session altered.
SCOTT@test01p> select rowid ,owner,object_id from t where rowid between 'AAAaKPAAJAAAASIAAB' and 'AAAaKPAAJAAAASIAAC';
ROWID OWNER OBJECT_ID
------------------ -------------------- ----------
AAAaKPAAJAAAASIAAB SYS 8
AAAaKPAAJAAAASIAAC SYS 10174
SCOTT@test01p> @ 10046off
Session altered.
=====================
PARSING IN CURSOR #35276568 len=102 dep=0 uid=109 oct=3 lid=109 tim=5262812718 hv=3900635955 ad='7ff13b72e40' sqlid='c5tpurvn7xytm'
select rowid ,owner,object_id from t where rowid between 'AAAaKPAAJAAAASIAAB' and 'AAAaKPAAJAAAASIAAC'
END OF STMT
PARSE #35276568:c=15600,e=5133,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=280204748,tim=5262812715
EXEC #35276568:c=0,e=78,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=280204748,tim=5262813350
WAIT #35276568: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=5262813588
WAIT #35276568: nam='Disk file operations I/O' ela= 598 FileOperation=2 fileno=9 filetype=2 obj#=107151 tim=5262814790
WAIT #35276568: nam='direct path read' ela= 40173 file number=9 first dba=1160 block cnt=1 obj#=107151 tim=5262855268
FETCH #35276568:c=0,e=41975,p=1,cr=4,cu=0,mis=0,r=1,dep=0,og=1,plh=280204748,tim=5262855697
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~=> CR=4,為什麼?
WAIT #35276568: nam='SQL*Net message from client' ela= 2441 driver id=1413697536 #bytes=1 p3=0 obj#=107151 tim=5262858326
WAIT #35276568: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=107151 tim=5262858682
FETCH #35276568:c=0,e=332,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=280204748,tim=5262858968
STAT #35276568 id=1 cnt=2 pid=0 pos=1 obj=107151 op='TABLE ACCESS BY ROWID RANGE T (cr=5 pr=1 pw=0 time=41932 us cost=421 size=5175 card=225)'
*** 2018-03-19 21:34:02.984
WAIT #35276568: nam='SQL*Net message from client' ela= 4953864 driver id=1413697536 #bytes=1 p3=0 obj#=107151 tim=5267820265
CLOSE #35276568:c=0,e=32,dep=0,type=0,tim=5267820714
=====================
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2152011/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 產生直接路徑讀的N種情況
- [20180316]共享服務模式和直接路徑讀.txt模式
- Windows 直接訪問 WSL2 路徑並直接進行讀寫操作，許可權不足解決方法Windows
- winddows路徑過長無法讀寫
- JavaWeb中讀取【專案路徑下檔案】的路徑問題：this.getServletContext().getRealPath()JavaWebServletContext
- Windows程式讀取不了中文路徑問題Windows
- 【Django】檔案讀取時路徑問題Django
- Linux檔案的路徑定位-相對路徑和絕對路徑Linux
- 從物理讀突增的複雜分析路徑談起
- Excel 讀取圖片並獲取儲存路徑Excel
- canvas 路徑與子路徑Canvas
- HTML絕對路徑與相對路徑HTML
- [20181007]Scalable sequences oracle database 12c.txtOracleDatabase
- UNC路徑
- FileUpload路徑
- Python最常用的讀取指定路徑檔案的方法！Python
- Java 專案讀取 resource 資原始檔路徑問題Java
- css的繼承性及特例CSS繼承
- nodejs路徑處理方法和絕對路徑NodeJS
- Facebook：解讀網路時代移動遊戲玩家的消費路徑遊戲
- 讀《我和Labview》3.5-3.6路徑和資料平化View
- canvas路徑與子路徑詳解Canvas
- 05_Linux相對路徑和絕對路徑Linux
- 【MFC】BROWSEINFO設定路徑，支援記憶上次路徑
- 深入解析Java絕對路徑與相對路徑Java
- 絕對路徑和相對路徑的區別，
- 修改桌面路徑
- 尤拉路徑
- 哈密頓路徑
- Flutter——路徑裁剪Flutter
- 跟隨路徑
- 路徑問題
- CentOS 個人目錄下中文路徑轉英文路徑CentOS
- 路徑規劃: 淺談路徑規劃演算法演算法
- [20180319]windows批處理檔案大小比較.txtWindows
- python如何將相對路徑轉換為絕對路徑？Python
- C++運算子優先順序特例C++
- java找不到路徑Java