[20171115]关于逻辑读的疑问.txt

[20171115]关于逻辑读的疑问.txt

--//有网友指出[20150209]为什么少1个逻辑读.txt,链接:http://blog.itpub.net/267265/viewspace-1430902/
--//如何验证是这样操作的.

1.环境:

SCOTT@test> @ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SCOTT@test> create table t3 as select rownum id,'test' data from dual connect by level<=100;
Table created.

SCOTT@test> alter table t3 minimize records_per_block;
Table altered.

--这样就实现每块100条记录.

SCOTT@test> insert into t3  select 100+rownum id,'test' data from dual connect by level<=400;
400 rows created.

SCOTT@test> commit ;
Commit complete.

--这样总共插入100条记录. 正好占满一个1个extents.(我使用assm,前面有L1,L2,以及段头),剩下仅仅5块.

SCOTT@test> select * from dba_extents where owner=user and segment_name='T3';
OWNER  SEGMENT_NAME  SEGMENT_TYPE  TABLESPACE_NAME  EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------ ------------- ------------- ---------------- --------- ---------- ---------- ---------- ---------- ------------
SCOTT  T3            TABLE         USERS                    0          4        536      65536          8            4

--//分析表略.
--//首先说明为什么这样?
1.首先提取1条作为1个逻辑读,我感觉像是形成输出模板.
2.逻辑读不会跨块,我在源链接没有说明这点,vage的书上有说明.

--如果我设置array=98呢?
--读1,98,1 ,98,2, 96,4, 94,6, 92,8,这样在加上块头1个逻辑读(我使用11g的版本),应该是12个逻辑读.

--//以上计算存在错误,应该是:(感谢网友指正)
--//读1,98,1 ,97,3, 95,5, 93,7, 91,9,这样在加上块头1个逻辑读,应该是12个逻辑读.

2.验证问题:
SCOTT@book> alter session set statistics_level=all ;
Session altered.

select * from t3;
SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  8xs59s0kcpn57, child number 1
-------------------------------------
select * from t3
Plan hash value: 4161002650
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |     4 (100)|          |    500 |00:00:00.01 |      12 |
|   1 |  TABLE ACCESS FULL| T3   |      1 |    500 |  4500 |     4   (0)| 00:00:01 |    500 |00:00:00.01 |      12 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T3@SEL$1

--//网友另外的问题是如何验证确实是这样的情况呢? 使用10046跟踪,仅仅看到fetch的数量.
set array 98
@ &r/10046on 12
select * from t3;
@ &r/10046off

--//查看跟踪文件:
=====================
PARSING IN CURSOR #139637584924536 len=17 dep=0 uid=83 oct=3 lid=83 tim=1510707143940419 hv=539881914 ad='7c3af540' sqlid='7pm8xmnh2vwdu'
select * from t3
END OF STMT
PARSE #139637584924536:c=4000,e=4035,p=0,cr=8,cu=0,mis=1,r=0,dep=0,og=1,plh=4161002650,tim=1510707143940418
EXEC #139637584924536:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4161002650,tim=1510707143940533
WAIT #139637584924536: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1510707143940613
FETCH #139637584924536:c=999,e=46,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=4161002650,tim=1510707143940707
WAIT #139637584924536: nam='SQL*Net message from client' ela= 312 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1510707143941086
WAIT #139637584924536: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1510707143941153
FETCH #139637584924536:c=0,e=76,p=0,cr=1,cu=0,mis=0,r=98,dep=0,og=1,plh=4161002650,tim=1510707143941213
WAIT #139637584924536: nam='SQL*Net message from client' ela= 2462 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1510707143943711
WAIT #139637584924536: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1510707143943830
FETCH #139637584924536:c=0,e=78,p=0,cr=2,cu=0,mis=0,r=98,dep=0,og=1,plh=4161002650,tim=1510707143943895
WAIT #139637584924536: nam='SQL*Net message from client' ela= 2395 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1510707143946326
WAIT #139637584924536: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1510707143946395
FETCH #139637584924536:c=0,e=76,p=0,cr=2,cu=0,mis=0,r=98,dep=0,og=1,plh=4161002650,tim=1510707143946459
WAIT #139637584924536: nam='SQL*Net message from client' ela= 3601 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1510707143950095
WAIT #139637584924536: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1510707143950157
FETCH #139637584924536:c=0,e=73,p=0,cr=2,cu=0,mis=0,r=98,dep=0,og=1,plh=4161002650,tim=1510707143950218
WAIT #139637584924536: nam='SQL*Net message from client' ela= 3598 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1510707143953880
WAIT #139637584924536: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1510707143953940
FETCH #139637584924536:c=1000,e=72,p=0,cr=2,cu=0,mis=0,r=98,dep=0,og=1,plh=4161002650,tim=1510707143954000
WAIT #139637584924536: nam='SQL*Net message from client' ela= 3607 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1510707143957641
WAIT #139637584924536: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1510707143957701
FETCH #139637584924536:c=0,e=55,p=0,cr=1,cu=0,mis=0,r=9,dep=0,og=1,plh=4161002650,tim=1510707143957744
STAT #139637584924536 id=1 cnt=500 pid=0 pos=1 obj=90670 op='TABLE ACCESS FULL T3 (cr=13 pr=0 pw=0 time=36 us cost=4 size=9500 card=500)'

*** 2017-11-15 08:52:31.840
WAIT #139637584924536: nam='SQL*Net message from client' ela= 7882935 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1510707151840765
CLOSE #139637584924536:c=0,e=21,dep=0,type=0,tim=1510707151840950
=====================

$ grep "FETCH #139637584924536" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_63061.trc
FETCH #139637584924536:c=999,e=46,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=4161002650,tim=1510707143940707
                                                      ~~~
FETCH #139637584924536:c=0,e=76,p=0,cr=1,cu=0,mis=0,r=98,dep=0,og=1,plh=4161002650,tim=1510707143941213
FETCH #139637584924536:c=0,e=78,p=0,cr=2,cu=0,mis=0,r=98,dep=0,og=1,plh=4161002650,tim=1510707143943895
FETCH #139637584924536:c=0,e=76,p=0,cr=2,cu=0,mis=0,r=98,dep=0,og=1,plh=4161002650,tim=1510707143946459
FETCH #139637584924536:c=0,e=73,p=0,cr=2,cu=0,mis=0,r=98,dep=0,og=1,plh=4161002650,tim=1510707143950218
FETCH #139637584924536:c=1000,e=72,p=0,cr=2,cu=0,mis=0,r=98,dep=0,og=1,plh=4161002650,tim=1510707143954000
FETCH #139637584924536:c=0,e=55,p=0,cr=1,cu=0,mis=0,r=9,dep=0,og=1,plh=4161002650,tim=1510707143957744

--//注意看r=..,从这里仅仅看出第一次提取1行(看下划线)

SCOTT@book> set autot traceonly
SCOTT@book> select * from t3;
500 rows selected.
Execution Plan
---------------------------
Plan hash value: 4161002650
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   500 |  4500 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T3   |   500 |  4500 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
       8458  bytes sent via SQL*Net to client
        574  bytes received via SQL*Net from client
          7  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        500  rows processed

--//从SQL*Net roundtrips to/from client=7  仅仅看出网络往返的次数.与fetch的次数对应的.

--//如何查看呢?我仅仅知道10200事件,可以做到.其它不是很清楚.做一个验证看看:

$ oerr ora 10200
10200, 00000, "consistent read buffer status"
// *Cause:
// *Action:

set array 98
ALTER SESSION SET EVENTS '10200 TRACE NAME CONTEXT FOREVER, LEVEL 1';
select  *  from t3;
ALTER SESSION SET EVENTS '10200 TRACE NAME CONTEXT OFF';

SCOTT@book> @ &r/pp
TRACEFILE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_63220.trc

$ grep "started" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_63220.trc
ktrget2(): started for block  <0x0004 : 0x01000223> objd: 0x0001622e
ktrget2(): started for block  <0x0004 : 0x01000223> objd: 0x0001622e
ktrget2(): started for block  <0x0004 : 0x01000223> objd: 0x0001622e
ktrget2(): started for block  <0x0004 : 0x01000224> objd: 0x0001622e
ktrget2(): started for block  <0x0004 : 0x01000224> objd: 0x0001622e
ktrget2(): started for block  <0x0004 : 0x01000225> objd: 0x0001622e
ktrget2(): started for block  <0x0004 : 0x01000225> objd: 0x0001622e
ktrget2(): started for block  <0x0004 : 0x01000226> objd: 0x0001622e
ktrget2(): started for block  <0x0004 : 0x01000226> objd: 0x0001622e
ktrget2(): started for block  <0x0004 : 0x01000227> objd: 0x0001622e
ktrget2(): started for block  <0x0004 : 0x01000227> objd: 0x0001622e
--//读1,98,1 ,97,3, 95,5, 93,7, 91,9,这样在加上块头1个逻辑读,应该是12个逻辑读.
--//但是仅仅看出读取块的顺序.

--//实际上还有1个简单的算法:
记录数量/array_size+block的数量.

SCOTT@book> set array 50
SCOTT@book> set autot traceonly
500 rows selected.
Execution Plan
---------------------------
Plan hash value: 4161002650
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   500 |  4500 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T3   |   500 |  4500 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
       9202  bytes sent via SQL*Net to client
        618  bytes received via SQL*Net from client
         11  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        500  rows processed
SCOTT@book> set autot off

--// 500/50+8 = 18 (与实际16相差2),块数量越多结果越接近.

时间： 2024-07-31 09:58:58

[20171115]关于逻辑读的疑问.txt的相关文章

[20150504]奇怪的逻辑读union or.txt

[20150504]奇怪的逻辑读union or.txt --在优化一个项目时遇到一个奇怪的情况,自己做例子测试看看. SCOTT@test> @ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------

[20120605]逻辑读那个高.txt

1.建立测试环境: SQL> select * from v$version ; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production CORE 

[20150309]逻辑读产生CBC Latch的解析.txt

[20150309]逻辑读产生Cache Buffer Chain(简称CBC) Latch的解析.txt --参考链接http://blog.csdn.net/guoyjoe/article/details/8585391,自己也做1次. 逻辑读的过程 1.Oracle以每个块的文件号.块号和类型做HASH运算,得到HASH值.根据HASH值,到HASH表中取出指定块的内存地址 2.获取CBC Latch(实验的重点测试部分) 3.根据HASH值,搜索CBC链表 4.根据DBA找到BH(Buf

[20150320]逻辑读增加.txt

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

ORACLE 物理读 逻辑读 一致性读 当前模式读总结浅析

     在ORACLE数据库中有物理读(Physical Reads).逻辑读(Logical Reads).一致性读(Consistant Get).当前模式读(DB Block Gets)等诸多概念,如果不理解或混淆这些概念的话,对你深入理解一些知识无疑是一个障碍,但是这些概念确实挺让让人犯晕的.下面我们总结.学习一下这方面的知识点.捋一捋他们的关系和特点,希望对你有所帮助.   物理读(Physical Reads)   从磁盘读取数据块到内存的操作叫物理读,当SGA里的高速缓存(Cac

[20171130]关于rman备份疑问.txt

[20171130]关于rman备份疑问.txt --//前面测试太乱,重新做一些rman as copy相关测试. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- --------------------------------------------------------------

数据读取的逻辑读简单解析：关于BUFFER CACHE

数据读取之逻辑读简单解析--BUFFER CACHE   关于consistent read--一致性读--Logical read-逻辑读-current read当前读--物理读,详见:http://blog.csdn.net/haibusuanyun/article/details/11489091 一.实验数据准备--查出一条数据的ROWID,及FILE_ID,BLOCK_ID等信息 BYS@ bys3>select rowid,test.* from test where rownum

buffer cache实验9:从buffer caceh中读取数据块解析-从逻辑读到物理读

先来张大图: 所用SQL语句: BYS@ ocm1>select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,deptno from bys.test;     FILE#     BLOCK#     DEPTNO ---------- ---------- ----------         4        391         10 就以上图为例,文字描述

逻辑读产生Cache Buffer Chain(简称CBC) Latch的解析

测试环境:版本11gR2 SQL> select * from v$version where rownum=1; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production 一.逻辑读的过程  1.Oracle以每个块的文件号.