[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),块数量越多结果越接近.
