本文共 9890 字，大约阅读时间需要 32 分钟。
[20171115]关于逻辑读的疑问.txt
--//有网友指出[20150209]为什么少1个逻辑读.txt,链接:
--//如何验证是这样操作的.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 ProductionSCOTT@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),块数量越多结果越接近.
转载地址：http://ecoel.baihongyu.com/