[20170810]直接路径读特例2.txt
--//以前在11.2.0.4下测试,出现直接路径读的一种特殊情况,就是使用rowid访问数据块(使用between).
--//参考链接:http://blog.itpub.net/267265/viewspace-2134894/
--//在11.2.0.3下测试看看是否也存在类似的情况.
1.环境:
SCOTT@zzzzzz> select * from v$version where rownum<=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@zzzzzz> alter session set statistics_level=all ;
Session altered.
SCOTT@zzzzzz> select rowid,dept.* from dept;
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AAASZFAAEAAAACHAAA 10 ACCOUNTING NEW YORK
AAASZFAAEAAAACHAAB 20 RESEARCH DALLAS
AAASZFAAEAAAACHAAC 30 SALES CHICAGO
AAASZFAAEAAAACHAAD 40 OPERATIONS BOSTON
SCOTT@192.168.xx.y:1521/zzzzzz> @ &r/rowid AAASZFAAEAAAACHAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
75333 4 135 0 0x1000087 4,135 alter system dump datafile 4 block 135 ;
SCOTT@192.168.xx.y:1521/zzzzzz> select rowid,dept.* from dept where rowid between 'AAASZFAAEAAAACHAAA' and 'AAASZFAAEAAAACHAAB';
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AAASZFAAEAAAACHAAA 10 ACCOUNTING NEW YORK
AAASZFAAEAAAACHAAB 20 RESEARCH DALLAS
SCOTT@192.168.xx.y:1521/zzzzzz> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 2y8kxkn5u7jxd, child number 0
-------------------------------------
select rowid,dept.* from dept where rowid between 'AAASZFAAEAAAACHAAA'
and 'AAASZFAAEAAAACHAAB'
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>='AAASZFAAEAAAACHAAA' AND ROWID<='AAASZFAAEAAAACHAAB')
--//因为现在数据缓存了,不会出现直接路径读的情况.
2.刷新数据缓存看看.
SYS@192.168.xx.y:1521/zzzzzz> alter system flush BUFFER_CACHE;
System altered.
SYS@192.168.xx.y:1521/zzzzzz> @ &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
---------------- ---------- ---------- ---------- -------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
00000000BA5BDF10 4 135 1 data block free 0 0 0 0 0 0 0000000080796000 DEPT
SCOTT@192.168.xx.y:1521/zzzzzz> select rowid,dept.* from dept where rowid between 'AAASZFAAEAAAACHAAA' and 'AAASZFAAEAAAACHAAB';
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AAASZFAAEAAAACHAAA 10 ACCOUNTING NEW YORK
AAASZFAAEAAAACHAAB 20 RESEARCH DALLAS
SCOTT@192.168.xx.y:1521/zzzzzz> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 2y8kxkn5u7jxd, child number 0
-------------------------------------
select rowid,dept.* from dept where rowid between 'AAASZFAAEAAAACHAAA'
and 'AAASZFAAEAAAACHAAB'
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>='AAASZFAAEAAAACHAAA' AND ROWID<='AAASZFAAEAAAACHAAB')
--//再次执行检查执行计划,可以发现还是存在物理读.
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@192.168.xx.y:1521/zzzzzz> @ &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
---------------- ---------- ---------- ---------- -------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
00000000BA5BDF10 4 135 1 data block free 0 0 0 0 0 0 0000000080796000 DEPT
--//可以发现如果使用rowid访问数据块(使用between).如果数据库不在数据缓存,使用的是直接路径读.
3.测试直接访问rowid的情况呢?
SCOTT@192.168.xx.y:1521/zzzzzz> select rowid,dept.* from dept where rowid between 'AAASZFAAEAAAACHAAA' and 'AAASZFAAEAAAACHAAA';
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AAASZFAAEAAAACHAAA 10 ACCOUNTING NEW YORK
SCOTT@192.168.xx.y:1521/zzzzzz> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 8fc4qx5d8khks, child number 0
-------------------------------------
select rowid,dept.* from dept where rowid between 'AAASZFAAEAAAACHAAA'
and 'AAASZFAAEAAAACHAAA'
Plan hash value: 3453257278
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1 (100)| | 1 |00:00:00.01 | 1 | 1 |
| 1 | TABLE ACCESS BY USER ROWID| DEPT | 1 | 1 | 20 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 | 1 |
--------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / DEPT@SEL$1
SYS@192.168.xx.y:1521/zzzzzz> @ &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
---------------- ---------- ---------- ---------- -------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
00000000BA5BDF10 4 135 1 data block xcur 1 0 0 0 0 0 00000000A03FA000 DEPT
00000000BA5BDF10 4 135 1 data block free 0 0 0 0 0 0 0000000080796000 DEPT
--//相应的数据块进入相应的数据缓存.
--//再次执行如下:
select rowid,dept.* from dept where rowid between 'AAASZFAAEAAAACHAAA' and 'AAASZFAAEAAAACHAAB';
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 |
---------------------------------------------------------------------------------------------------------------------------------------
--//奇怪依次存在直接路径读.很奇怪为什么现在还是是使用直接路径读.难道块头不在数据缓存的原因吗?
SCOTT@192.168.xx.y:1521/zzzzzz> select * from dba_extents where owner=user and segment_name='DEPT';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ -------------------- ------------------------------ ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
SCOTT DEPT TABLE USERS 0 4 128 65536 8 4
SCOTT@192.168.xx.y:1521/zzzzzz> select HEADER_FILE, HEADER_BLOCK from dba_segments where owner=user and segment_name='DEPT';
HEADER_FILE HEADER_BLOCK
----------- ------------
4 130
--//数据段头 4,130.
SYS@192.168.xx.y:1521/zzzzzz> @ &r/bh 4 128
no rows selected
SYS@192.168.xx.y:1521/zzzzzz> @ &r/bh 4 129
no rows selected
SYS@192.168.xx.y:1521/zzzzzz> @ &r/bh 4 130
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
---------------- ---------- ---------- ---------- -------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
00000000BA539DB8 4 130 4 segment header xcur 10 0 0 0 0 0 000000006EDE8000 DEPT
00000000BA539DB8 4 130 4 segment header free 0 0 0 0 0 0 0000000087E3A000 DEPT
--//dba=4,128 4,129没有缓存.
--//做一个全表扫描,再测试.
SCOTT@192.168.xx.y:1521/zzzzzz> select rowid,dept.* from dept ;
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AAASZFAAEAAAACHAAA 10 ACCOUNTING NEW YORK
AAASZFAAEAAAACHAAB 20 RESEARCH DALLAS
AAASZFAAEAAAACHAAC 30 SALES CHICAGO
AAASZFAAEAAAACHAAD 40 OPERATIONS BOSTON
SCOTT@192.168.xx.y:1521/zzzzzz> select rowid,dept.* from dept where rowid between 'AAASZFAAEAAAACHAAA' and 'AAASZFAAEAAAACHAAB';
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AAASZFAAEAAAACHAAA 10 ACCOUNTING NEW YORK
AAASZFAAEAAAACHAAB 20 RESEARCH DALLAS
SCOTT@192.168.xx.y:1521/zzzzzz> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 2y8kxkn5u7jxd, child number 0
-------------------------------------
select rowid,dept.* from dept where rowid between 'AAASZFAAEAAAACHAAA' and 'AAASZFAAEAAAACHAAB'
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>='AAASZFAAEAAAACHAAA' AND ROWID<='AAASZFAAEAAAACHAAB')
--//可以发现这次直接路径读消失.
总结:
还是不清楚什么情况下会采用直接路径读,视乎和缓存的数据块有关.因为在没有全表扫描前dba= 4,131 到 4,135 不在缓存中.
--//加入加载这些块到数据缓存,是否就不出现直接路径读呢?
SYS@192.168.xx.y:1521/zzzzzz> alter system flush BUFFER_CACHE;
System altered.
SCOTT@192.168.xx.y:1521/zzzzzz> select rowid,dept.* from dept where rowid = dbms_rowid.ROWID_CREATE(1,75333,4,131,0);
no rows selected
select rowid,dept.* from dept where rowid between 'AAASZFAAEAAAACHAAA' and 'AAASZFAAEAAAACHAAB'
SCOTT@192.168.xx.y:1521/zzzzzz> select rowid,dept.* from dept where rowid = dbms_rowid.ROWID_CREATE(1,75333,4,132,0);
no rows selected
select rowid,dept.* from dept where rowid between 'AAASZFAAEAAAACHAAA' and 'AAASZFAAEAAAACHAAB'
SCOTT@192.168.xx.y:1521/zzzzzz> select rowid,dept.* from dept where rowid = dbms_rowid.ROWID_CREATE(1,75333,4,133,0);
no rows selected
select rowid,dept.* from dept where rowid between 'AAASZFAAEAAAACHAAA' and 'AAASZFAAEAAAACHAAB'
--当加载4,133时,再次执行select rowid,dept.* from dept where rowid between 'AAASZFAAEAAAACHAAA' and 'AAASZFAAEAAAACHAAB';
--执行计划变成:
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 |
------------------------------------------------------------------------------------------------------------------------------
--//直接路径读消失.
SYS@192.168.xx.y:1521/zzzzzz> @ &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
---------------- ---------- ---------- ---------- -------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
00000000BA5BDF10 4 135 1 data block xcur 1 0 0 0 0 0 000000006AD56000 DEPT
00000000BA5BDF10 4 135 1 data block free 0 0 0 0 0 0 00000000A03FA000
00000000BA5BDF10 4 135 1 data block free 0 0 0 0 0 0 0000000080796000
--//我估计加载一定数量的数据块到数据缓存(我这里测试加载3个数据块),执行计划才发生变化.