[20170308]直接路径读特例.txt
--//昨天测试一些sql语句时,无意中发现出现直接路径读的一种特殊情况,就是使用rowid访问数据块(使用between).而且还发现一些我无
--//法解析的情况.通过例子说明:
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> select rowid,dept.* from dept;
ROWID DEPTNO DNAME LOC
------------------ ------------ -------------- -------------
AAAVRCAAEAAAACHAAA 10 ACCOUNTING NEW YORK
AAAVRCAAEAAAACHAAB 20 RESEARCH DALLAS
AAAVRCAAEAAAACHAAC 30 SALES CHICAGO
AAAVRCAAEAAAACHAAD 40 OPERATIONS BOSTON
SCOTT@book> @ &r/rowid AAAVRCAAEAAAACHAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
------------ ------------ ------------ ------------ -------------------- -------------------- ----------------------------------------
87106 4 135 0 0x1000087 4,135 alter system dump datafile 4 block 135 ;
SCOTT@book> alter session set statistics_level=all;
Session altered.
SCOTT@book> select rowid,dept.* from dept where rowid between 'AAAVRCAAEAAAACHAAA' and 'AAAVRCAAEAAAACHAAB';
ROWID DEPTNO DNAME LOC
------------------ ------------ -------------- -------------
AAAVRCAAEAAAACHAAA 10 ACCOUNTING NEW YORK
AAAVRCAAEAAAACHAAB 20 RESEARCH DALLAS
SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 5pang9x5by7p5, child number 0
-------------------------------------
select rowid,dept.* from dept where rowid between 'AAAVRCAAEAAAACHAAA' and 'AAAVRCAAEAAAACHAAB'
Plan hash value: 4056682038
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 2 |00:00:00.01 | 3 |
|* 1 | TABLE ACCESS BY ROWID RANGE| DEPT | 1 | 1 | 20 | 3 (0)| 00:00:01 | 2 |00:00:00.01 | 3 |
------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / DEPT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access(ROWID>='AAAVRCAAEAAAACHAAA' AND ROWID<='AAAVRCAAEAAAACHAAB')
--//当前3个逻辑读是正确的,我不知道为什么?安装我的理解应该是2.但是如果该块不再数据缓存.就会出现物理读的情况.
2.测试:
--//首先刷新数据缓存.
SCOTT@book> alter system flush buffer_cache;
System altered.
SCOTT@book> alter system flush buffer_cache;
System altered.
SCOTT@book> select rowid,dept.* from dept where rowid between 'AAAVRCAAEAAAACHAAA' and 'AAAVRCAAEAAAACHAAB';
ROWID DEPTNO DNAME LOC
------------------ ------------ -------------- -------------
AAAVRCAAEAAAACHAAA 10 ACCOUNTING NEW YORK
AAAVRCAAEAAAACHAAB 20 RESEARCH DALLAS
SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 5pang9x5by7p5, child number 0
-------------------------------------
select rowid,dept.* from dept where rowid between 'AAAVRCAAEAAAACHAAA'
and 'AAAVRCAAEAAAACHAAB'
Plan hash value: 4056682038
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 2 |00:00:00.01 | 3 | 2 |
|* 1 | TABLE ACCESS BY ROWID RANGE| DEPT | 1 | 1 | 20 | 3 (0)| 00:00:01 | 2 |00:00:00.01 | 3 | 2 |
---------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / DEPT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access(ROWID>='AAAVRCAAEAAAACHAAA' AND ROWID<='AAAVRCAAEAAAACHAAB')
--//reads=2
--//以后再次执行,都会存在一个物理读(除非该块进入数据缓存).
Plan hash value: 4056682038
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 2 |00:00:00.01 | 3 | 1 |
|* 1 | TABLE ACCESS BY ROWID RANGE| DEPT | 1 | 1 | 20 | 3 (0)| 00:00:01 | 2 |00:00:00.01 | 3 | 1 |
---------------------------------------------------------------------------------------------------------------------------------------
SYS@book> @ &r/bh 4 135
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
0000000084A58490 4 135 1 data block free 0 0 0 0 0 0 000000006C16A000 DEPT
0000000084A58490 4 135 1 data block free 0 0 0 0 0 0 0000000066FB8000 DEPT
--//当前没有缓存.
3.跟踪分析:
SCOTT@book> @ &r/10046on 12
Session altered.
SCOTT@book> select rowid,dept.* from dept where rowid between 'AAAVRCAAEAAAACHAAA' and 'AAAVRCAAEAAAACHAAB';
ROWID DEPTNO DNAME LOC
------------------ ------------ -------------- -------------
AAAVRCAAEAAAACHAAA 10 ACCOUNTING NEW YORK
AAAVRCAAEAAAACHAAB 20 RESEARCH DALLAS
SCOTT@book> @ &r/10046off
Session altered.
--//查看跟踪
=====================
PARSING IN CURSOR #140416991265296 len=95 dep=0 uid=83 oct=3 lid=83 tim=1488934054181670 hv=1254039205 ad='7d9cd7b8' sqlid='5pang9x5by7p5'
select rowid,dept.* from dept where rowid between 'AAAVRCAAEAAAACHAAA' and 'AAAVRCAAEAAAACHAAB'
END OF STMT
PARSE #140416991265296:c=0,e=27,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4056682038,tim=1488934054181668
EXEC #140416991265296:c=0,e=30,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4056682038,tim=1488934054181800
WAIT #140416991265296: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=87106 tim=1488934054181878
WAIT #140416991265296: nam='direct path read' ela= 18 file number=4 first dba=135 block cnt=1 obj#=87106 tim=1488934054182309
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FETCH #140416991265296:c=1000,e=484,p=1,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=4056682038,tim=1488934054182446
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~=> 不明白为什么cr=2
WAIT #140416991265296: nam='SQL*Net message from client' ela= 325 driver id=1650815232 #bytes=1 p3=0 obj#=87106 tim=1488934054182847
WAIT #140416991265296: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=87106 tim=1488934054182919
FETCH #140416991265296:c=0,e=57,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=4056682038,tim=1488934054182960
STAT #140416991265296 id=1 cnt=2 pid=0 pos=1 obj=87106 op='TABLE ACCESS BY ROWID RANGE DEPT (cr=3 pr=1 pw=0 time=490 us cost=3 size=20 card=1)'
*** 2017-03-08 08:47:36.854
WAIT #140416991265296: nam='SQL*Net message from client' ela= 2671223 driver id=1650815232 #bytes=1 p3=0 obj#=87106 tim=1488934056854277
CLOSE #140416991265296:c=0,e=15,dep=0,type=3,tim=1488934056854384
=====================
4.最后1点:
--//一直不明白为什么逻辑读是3,哪位知道???
--//使用rowid='AAAVRCAAEAAAACHAAA'就没有这种情况.
