[20170308]直接路径读特例.txt

[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'就没有这种情况.

时间： 2024-09-20 17:24:15

[20170308]直接路径读特例.txt的相关文章

[20170810]直接路径读特例2.txt

[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 ----------

[20171120]11G关闭直接路径读.txt

[20171120]11G关闭直接路径读.txt --//今天做filesystemio_options参数测试时,遇到一个关于直接路径读的问题. --//如果看以前的博客介绍,设置"_serial_direct_read"=never或者events '10949 trace name context forever';就可以关闭直接路径读. --//我的测试在11GR2下set events '10949 trace name context forever';不行. --//通过

[20150122]buffer busy waits特例.txt

[20150122]buffer busy waits特例.txt --oracle 里有句名言:读不阻塞写,写阻塞读. --如果读读模式,是否会出现buffer busy waits呢?通过例子来说明: 1.建立测试环境: SCOTT@test> @ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- --------------------

unicode编码-C++编程，文件路径保存在txt文件里，如果删除实现按路径删除

问题描述 C++编程,文件路径保存在txt文件里,如果删除实现按路径删除 开发环境 vs2010 扫描C盘的tmp文件扫描后把文件路径保存在txt文件里如果删除实现按路径删除 txt是unicode编码的, #include "stdafx.h" #include #include #include #include #include using namespace std; #define STR_SAVETEMPFILEINFO_FILENAME L"C:alltemp.

C++基础问题 读一个txt中大写字母的数量

问题描述 C++基础问题 读一个txt中大写字母的数量 读一个txt中大写字母的数量 请问为什么是return i-1 还有就是a.txt文件设置为AAA时是输出大写为3 当设置为AAAc时就变成2了 求助! #include #include using namespace std; int num1(char f[100]) { fstream file; file.open(f,ios::in); char ch; int i=0; while(!file.eof()) { file.ge

[20150320]逻辑读增加.txt

[20150320]逻辑读增加.txt --昨天在看awr报表发现一个奇怪的现象,一个小表在全表扫描时逻辑读增加许多. --有一个小表逻辑读很高比平时高,一开始以为是高水位的问题,检查发现不是.后来才发现一开发在做维护后,在执行select for -- upate查看,然后离开办别的事情,忘记提交或者回滚.导致其它用户执行时访问回滚段,导致逻辑读增加.我kill后恢复正常. --我做一个简单的例子来说明: 1.session 1: SCOTT@test> select * from emp f

[20170308]再谈hugepages.txt

[20170308]再谈hugepages.txt --//节前跟别人的交流,不幸被我言中.当时是去年5月份的事前,系统迁移,我去观摩,我感觉很奇怪的是内存128G的机器,不知道为什么不 --//应用hugepages,让我吃惊对方并不知道.因为毕竟是别的系统,别人如何做我无权过问.我当时只是提醒如果连接用户数量大,按照他们 --//当时的是设置,有可能会出现内存不足的情况. --//我在链接http://blog.itpub.net/267265/viewspace-2128811/的测试中:

DropdownList怎样和.txt文件绑定？

问题描述 急呀!在线等! 解决方案 解决方案二:自己抢沙发!解决方案三:没看懂你的意思啊是不是输入text的值在绑定?????????..还是有个text的文件在与其绑定??----------------前者好做后者就要做输入输出的io流做处理解决方案四:具体说一下子???????解决方案五:你可以把把txt中的数据读入到datatable中,然后循环添加进去.读txt的时候,你可以用readline一行一行的读.解决方案六:引用4楼xbfitliu的回复: 你可以把把txt中的数据读入到da

Java读取txt文件和写入txt文件的简单实例_java

写Java程序时经常碰到要读如txt或写入txt文件的情况,但是由于要定义好多变量,经常记不住,每次都要查,特此整理一下,简单易用,方便好懂! package edu.thu.keyword.test; import java.io.File; import java.io.InputStreamReader; import java.io.BufferedReader; import java.io.BufferedWriter; import java.io.FileInputStream;