[20210219]全表掃描邏輯讀問題.txt
[20210219]全表掃描邏輯讀問題.txt
--//一般探究邏輯讀設定_trace_pin_time,或者設定10200事件.
--//設定_trace_pin_time有一個明顯的缺點就是在系統級設定,而且要重啟,這樣每個程式產生大量跟蹤資訊.
--//還有的一個因素就是對於唯一索引的執行計劃,一些pin會捕捉到.自己沒什麼事情測試看看,
--//主要出現一些小問題,自己無法理解.
1.環境:
SYS@book> @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
SYS@book> alter system set "_trace_pin_time"=1 scope=spfile;
System altered.
--//重啟資料庫.
SYS@book> @ hide _trace_pin
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
--------------- ------------------------------------ ------------- ------------- ------------ ----- ---------
_trace_pin_time trace how long a current pin is held FALSE 1 1 FALSE FALSE
2.測試:
--//測試前在別的會話執行select * from emp;多次,避免一些遞迴語句的執行.不然跟蹤輸出太亂.
SCOTT@book> @ 10046on 12
Session altered.
SCOTT@book> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
...
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
SCOTT@book> @ 10046off
Session altered.
3.檢查跟蹤檔案:
=====================
PARSING IN CURSOR #139634694966072 len=17 dep=0 uid=83 oct=3 lid=83 tim=1613695908737267 hv=1745700775 ad='7c3dc5f0' sqlid='a2dk8bdn0ujx7'
select * from emp
END OF STMT
PARSE #139634694966072:c=3000,e=2654,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=3956160932,tim=1613695908737263
EXEC #139634694966072:c=0,e=41,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3956160932,tim=1613695908737408
WAIT #139634694966072: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=407 tim=1613695908737476
pin ktewh26: kteinpscan dba 0x1000092:4 time 3681186314
pin kdswh11: kdst_fetch dba 0x1000093:1 time 3681186358
pin kdswh11: kdst_fetch dba 0x1000094:1 time 3681186382
pin kdswh11: kdst_fetch dba 0x1000095:1 time 3681186398
pin kdswh11: kdst_fetch dba 0x1000096:1 time 3681186413
pin kdswh11: kdst_fetch dba 0x1000097:1 time 3681186429
FETCH #139634694966072:c=0,e=161,p=0,cr=6,cu=0,mis=0,r=1,dep=0,og=1,plh=3956160932,tim=1613695908737681
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
WAIT #139634694966072: nam='SQL*Net message from client' ela= 557 driver id=1650815232 #bytes=1 p3=0 obj#=407 tim=1613695908738283
pin kdswh11: kdst_fetch dba 0x1000097:1 time 3681187130
WAIT #139634694966072: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=407 tim=1613695908738391
FETCH #139634694966072:c=0,e=89,p=0,cr=1,cu=0,mis=0,r=13,dep=0,og=1,plh=3956160932,tim=1613695908738440
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
STAT #139634694966072 id=1 cnt=14 pid=0 pos=1 obj=87108 op='TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=144 us cost=3 size=532 card=14)'
*** 2021-02-19 08:51:51.614
WAIT #139634694966072: nam='SQL*Net message from client' ela= 2876023 driver id=1650815232 #bytes=1 p3=0 obj#=407 tim=1613695911614606
CLOSE #139634694966072:c=0,e=10,dep=0,type=0,tim=1613695911614709
=====================
PARSING IN CURSOR #139634694966072 len=55 dep=0 uid=83 oct=42 lid=83 tim=1613695911614965 hv=2217940283 ad='0' sqlid='06nvwn223659v'
alter session set events '10046 trace name context off'
END OF STMT
PARSE #139634694966072:c=999,e=194,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=1613695911614964
EXEC #139634694966072:c=0,e=373,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=1613695911615415
$ grep ^pin /tmp/aa.txt
pin ktewh26: kteinpscan dba 0x1000092:4 time 3681186314
pin kdswh11: kdst_fetch dba 0x1000093:1 time 3681186358
pin kdswh11: kdst_fetch dba 0x1000094:1 time 3681186382
pin kdswh11: kdst_fetch dba 0x1000095:1 time 3681186398
pin kdswh11: kdst_fetch dba 0x1000096:1 time 3681186413
pin kdswh11: kdst_fetch dba 0x1000097:1 time 3681186429
pin kdswh11: kdst_fetch dba 0x1000097:1 time 3681187130
--//0x1000092 = set dba 4,146 = alter system dump datafile 4 block 146 = 16777362
--//0x1000097 = set dba 4,151 = alter system dump datafile 4 block 151 = 16777367
--//0x1000097 讀2次,注意看前面fetch存在2次,第1次fetch1條,第2次fetch 13次.共14條記錄返回.
--//中間4塊是空塊.
SCOTT@book> alter session set statistics_level = all;
Session altered.
SCOTT@book> select * from emp;
...
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID a2dk8bdn0ujx7, child number 2
-------------------------------------
select * from emp
Plan hash value: 3956160932
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 14 |00:00:00.01 | 7 |
| 1 | TABLE ACCESS FULL| EMP | 1 | 14 | 532 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 7 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMP@SEL$1
--//與前面的測試邏輯讀正好7個一致.有點奇怪的是與vaga書提到的不同,按照他的介紹段頭要讀2次.而實際上僅僅1次.這是我的疑問.
SCOTT@book> select rowid from emp where rownum=1;
ROWID
------------------
AAAVREAAEAAAACXAAA
SCOTT@book> @ rowid AAAVREAAEAAAACXAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
87108 4 151 0 0x1000097 4,151 alter system dump datafile 4 block 151 ;
SCOTT@book> select * from dba_segments where owner=user and segment_name='EMP'
2 @ prxx
==============================
OWNER : SCOTT
SEGMENT_NAME : EMP
PARTITION_NAME :
SEGMENT_TYPE : TABLE
SEGMENT_SUBTYPE : ASSM
TABLESPACE_NAME : USERS
HEADER_FILE : 4
HEADER_BLOCK : 146
BYTES : 65536
BLOCKS : 8
EXTENTS : 1
INITIAL_EXTENT : 65536
NEXT_EXTENT : 1048576
MIN_EXTENTS : 1
MAX_EXTENTS : 2147483645
MAX_SIZE : 2147483645
RETENTION :
MINRETENTION :
PCT_INCREASE :
FREELISTS :
FREELIST_GROUPS :
RELATIVE_FNO : 4
BUFFER_POOL : DEFAULT
FLASH_CACHE : DEFAULT
CELL_FLASH_CACHE : DEFAULT
PL/SQL procedure successfully completed.
--//段頭dba=4,146.與跟蹤看到的dba地址一致.
4.建立另外的表測試看看:
SCOTT@book> create table empx as select * from emp;
Table created.
SCOTT@book> @ 10046on 12
Session altered.
SCOTT@book> select /*+ full(empx) */ count(*) from empx;
COUNT(*)
----------
14
SCOTT@book> @ 10046off
Session altered.
=====================
PARSING IN CURSOR #139940786742280 len=43 dep=0 uid=83 oct=3 lid=83 tim=1613696616211174 hv=4266967087 ad='7e10b110' sqlid='36gz0zrz59h1g'
select /*+ full(empx) */ count(*) from empx
END OF STMT
PARSE #139940786742280:c=1999,e=1151,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=36332186,tim=1613696616211169
EXEC #139940786742280:c=0,e=37,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=36332186,tim=1613696616211315
WAIT #139940786742280: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1613696616211379
pin ktewh26: kteinpscan dba 0x1000222:4 time 93692910
pin kdswh11: kdst_fetch dba 0x1000223:1 time 93692951
FETCH #139940786742280:c=0,e=100,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=36332186,tim=1613696616211516
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
STAT #139940786742280 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=2 pr=0 pw=0 time=97 us)'
STAT #139940786742280 id=2 cnt=14 pid=1 pos=1 obj=90510 op='TABLE ACCESS FULL EMPX (cr=2 pr=0 pw=0 time=79 us cost=3 size=0 card=14)'
WAIT #139940786742280: nam='SQL*Net message from client' ela= 332 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1613696616212000
FETCH #139940786742280:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=36332186,tim=1613696616212063
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
WAIT #139940786742280: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1613696616212101
*** 2021-02-19 09:03:39.314
WAIT #139940786742280: nam='SQL*Net message from client' ela= 3102263 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1613696619314389
CLOSE #139940786742280:c=0,e=10,dep=0,type=0,tim=1613696619314489
=====================
--//0x1000222 = set dba 4,546 = alter system dump datefile 4 block 546 = 16777762
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 36gz0zrz59h1g, child number 2
-------------------------------------
select /*+ full(empx) */ count(*) from empx
Plan hash value: 36332186
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 (100)| | 1 |00:00:00.01 | 2 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:00.01 | 2 |
| 2 | TABLE ACCESS FULL| EMPX | 1 | 14 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 2 |
-------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / EMPX@SEL$1
--//2個邏輯讀.也就是不像vage測試那樣,讀段頭2次在全表掃描的情況下.為什麼呢?
--//如果你看連結%e5%86%8d%e8%ae%aeopen-cursor%e4%b8%8ebulk-collect.html的測試,可以發
--//現kteinpscan後跟著一個kteinmap.版本問題,還是oracle做了什麼改進.
pin ktewh26: kteinpscan dba 0x10a6202:4 time 1039048805
pin ktewh27: kteinmap dba 0x10a6202:4 time 1039048847
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
5.我使用gdb跟蹤程式,發現實際上還是呼叫kteinmap.
SCOTT@book> select * from emp;
(gdb) b kteinmap
Breakpoint 1 at 0xfc97f0
(gdb) c
Continuing.
Breakpoint 1, 0x0000000000fc97f0 in kteinmap ()
(gdb) info register
rax 0x0 0
rbx 0x0 0
rcx 0x0 0
rdx 0x0 0
rsi 0x85f7a080 2247598208
rdi 0x7f44073a92f0 139930155782896
rbp 0x7fff1225b300 0x7fff1225b300
rsp 0x7fff1225b300 0x7fff1225b300
r8 0x85f7a080 2247598208
r9 0x0 0
r10 0x85f7a020 2247598112
r11 0x10 16
r12 0x7f44073a92f0 139930155782896
r13 0x7f44073a95c0 139930155783616
r14 0x0 0
r15 0x0 0
rip 0xfc97f0 0xfc97f0 <kteinmap+4>
eflags 0x246 [ PF ZF IF ]
cs 0x33 51
ss 0x2b 43
ds 0x0 0
es 0x0 0
fs 0x0 0
gs 0x0 0
fctrl 0x27f 639
fstat 0x4020 16416
ftag 0xffff 65535
fiseg 0x0 0
fioff 0x5f47add 99908317
foseg 0x7fff 32767
fooff 0x1225bb28 304462632
fop 0x0 0
mxcsr 0x1fa0 [ PE IM DM ZM OM UM PM ]
(gdb) x /32x $rdi
0x7f44073a92f0: 0x00000004 0x01000092 0x00015444 0x00015444
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0x7f44073a9300: 0x003f0008 0x00001fe8 0x00000000 0x00000000
0x7f44073a9310: 0x00000000 0x00000000 0x00000000 0x00000000
0x7f44073a9320: 0x0000000a 0x00000000 0x0747b6d0 0x00007f44
0x7f44073a9330: 0x0747b690 0x00007f44 0x00000000 0x00000000
0x7f44073a9340: 0x00000000 0x00000000 0x00000000 0x00000000
0x7f44073a9350: 0x00000033 0x01000092 0x00000004 0x00000004
0x7f44073a9360: 0x01000093 0x00000005 0x01000090 0x00000008
--//$rdi記錄的地址偏移4個位元組記錄的是dba地址. 我嘗試建立大表發現還是可以跟蹤到kteinmap.繼續...
6.繼續測試:
SCOTT@book> create table t1 as select * from dba_objects where rownum<=600;
Table created.
SCOTT@book> select count(*),DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) from t1 group by DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) order by 2;
COUNT(*) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
---------- ------------------------------------
88 3483
83 3484
80 3485
78 3486
78 3487
80 3488
81 3489
32 3490
8 rows selected.
--//88+83+80+78+78 = 407,這樣僅僅使用1個extent.
SCOTT@book> create table t2 as select * from dba_objects where rownum<=407;
Table created.
SCOTT@book> create table t3 as select * from dba_objects where rownum<=408;
Table created.
--//分析表略.
> select count(*) from t2;
Plan hash value: 3321871023
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 (100)| | 1 |00:00:00.01 | 6 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:00.01 | 6 |
| 2 | TABLE ACCESS FULL| T2 | 1 | 407 | 4 (0)| 00:00:01 | 407 |00:00:00.01 | 6 |
-------------------------------------------------------------------------------------------------------------
*** 2021-02-19 16:03:37.760
pin ktewh26: kteinpscan dba 0x1000daa:4 time 3820405730
pin kdswh11: kdst_fetch dba 0x1000dab:1 time 3820405818
pin kdswh11: kdst_fetch dba 0x1000dac:1 time 3820405880
pin kdswh11: kdst_fetch dba 0x1000dad:1 time 3820405919
pin kdswh11: kdst_fetch dba 0x1000dae:1 time 3820405953
pin kdswh11: kdst_fetch dba 0x1000daf:1 time 3820405988
SCOTT@book> select count(*) from t3;
Plan hash value: 463314188
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 (100)| | 1 |00:00:00.01 | 8 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:00.01 | 8 |
| 2 | TABLE ACCESS FULL| T3 | 1 | 408 | 4 (0)| 00:00:01 | 408 |00:00:00.01 | 8 |
-------------------------------------------------------------------------------------------------------------
*** 2021-02-19 16:05:05.952
pin ktewh26: kteinpscan dba 0x1000db2:4 time 3908597185
pin ktewh27: kteinmap dba 0x1000db2:4 time 3908597257
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
pin kdswh11: kdst_fetch dba 0x1000db3:1 time 3908597290
pin kdswh11: kdst_fetch dba 0x1000db4:1 time 3908597334
pin kdswh11: kdst_fetch dba 0x1000db5:1 time 3908597372
pin kdswh11: kdst_fetch dba 0x1000db6:1 time 3908597412
pin kdswh11: kdst_fetch dba 0x1000db7:1 time 3908597456
pin kdswh11: kdst_fetch dba 0x1000db8:1 time 3908597505
--//你可以發現我僅僅增加1條記錄,邏輯讀增加2個.
--//0x1000db2 = set dba 4,3506 = alter system dump datafile 4 block 3506 = 16780722
SCOTT@book> alter system dump datafile 4 block 3506;
System altered.
Block dump from disk:
buffer tsn: 4 rdba: 0x01000db2 (4/3506)
scn: 0x0003.176c4279 seq: 0x01 flg: 0x04 tail: 0x42792301
frmt: 0x02 chkval: 0xd1dd type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F7083993200 to 0x00007F7083995200
7F7083993200 0000A223 01000DB2 176C4279 04010003 [#.......yBl.....]
7F7083993210 0000D1DD 00000000 00000000 00000000 [................]
7F7083993220 00000000 00000002 00000010 00000A9C [................]
7F7083993230 00000001 00000001 00000008 01000DB9 [................]
7F7083993240 00000000 00000001 00000000 00000009 [................]
7F7083993250 00000000 00000000 00000000 00000001 [................]
7F7083993260 00000001 00000008 01000DB9 00000000 [................]
7F7083993270 00000001 00000000 00000009 01000DB0 [................]
7F7083993280 01000DB0 00000000 00000000 00000000 [................]
7F7083993290 00000000 00000000 00000000 00000000 [................]
Repeat 3 times
7F70839932D0 00000001 00002000 00000000 00001434 [..... ......4...]
7F70839932E0 00000000 01000DB1 00000001 01000DB0 [................]
7F70839932F0 01000DB1 00000000 00000000 00000000 [................]
7F7083993300 00000000 00000000 00000002 00000000 [................]
7F7083993310 00016198 10000000 01000DB0 00000008 [.a..............]
7F7083993320 01000DB8 00000008 00000000 00000000 [................]
7F7083993330 00000000 00000000 00000000 00000000 [................]
Repeat 151 times
7F7083993CB0 01000DB0 01000DB3 01000DB0 01000DB8 [................]
7F7083993CC0 00000000 00000000 00000000 00000000 [................]
Repeat 151 times
7F7083994640 00000000 00000000 01000DB1 00000000 [................]
7F7083994650 00000000 00000000 00000000 00000000 [................]
Repeat 185 times
7F70839951F0 00000000 00000000 00000000 42792301 [.............#yB]
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 16
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x01000db9 ext#: 1 blk#: 1 ext size: 8
~~~~~~~~~~~~~~~~~~~~~~~~高水位標識.
#blocks in seg. hdr's freelists: 0
#blocks below: 9
mapblk 0x00000000 offset: 1
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x01000db9 ext#: 1 blk#: 1 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 9
mapblk 0x00000000 offset: 1
Level 1 BMB for High HWM block: 0x01000db0
Level 1 BMB for Low HWM block: 0x01000db0
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x01000db1
Last Level 1 BMB: 0x01000db0
Last Level II BMB: 0x01000db1
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 2 obj#: 90520 flag: 0x10000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x01000db0 length: 8
0x01000db8 length: 8
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x01000db0 Data dba: 0x01000db3
Extent 1 : L1 dba: 0x01000db0 Data dba: 0x01000db8
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x01000db1
End dump data blocks tsn: 4 file#: 4 minblk 3506 maxblk 3506
--//存在2個extent.
--//你可以發現在Auxillary Map僅僅1條Extent的情況下就不會將kteinmap計入邏輯讀的現象.
7.繼續測試呼叫kteinmap的情況:
create table t1 as select * from dba_objects ;
--//分析略.不然邏輯讀有一些增加.
SCOTT@book> alter session set "_serial_direct_read"=never;
Session altered.
--//注:主要我的表有點大,出現直接路徑讀,需要關閉直接路徑讀.不然僅僅看到僅僅2個邏輯讀.
*** 2021-02-19 16:16:43.303
pin ktewh26: kteinpscan dba 0x1000d9a:4 time 310980860
pin ktewh27: kteinmap dba 0x1000d9a:4 time 310980967
SCOTT@book> select count(*) from t1;
COUNT(*)
----------
87003
$ grep -C1 kteinmap /tmp/a1.txt
pin ktewh26: kteinpscan dba 0x1000d9a:4 time 436002887
pin ktewh27: kteinmap dba 0x1000d9a:4 time 436002967
pin kdswh11: kdst_fetch dba 0x1000d9b:1 time 436003159
--
pin kdswh11: kdst_fetch dba 0x1000dff:1 time 436006353
pin ktewh27: kteinmap dba 0x1000d9a:4 time 436006383
pin kdswh11: kdst_fetch dba 0x1000781:1 time 436006478
--
pin kdswh11: kdst_fetch dba 0x1000a7f:1 time 436028085
pin ktewh27: kteinmap dba 0x1000d9a:4 time 436028114
pin kdswh11: kdst_fetch dba 0x1000a82:1 time 436029189
--//出現3次.
--//0x1000d9a = set dba 4,3482 = alter system dump datafile 4 block 3482 = 16780698;
$ grep "^pin" /tmp/a1.txt |wc
1246 8722 68524
Plan hash value: 3724264953
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 347 (100)| | 1 |00:00:00.04 | 1246 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:00.04 | 1246 |
| 2 | TABLE ACCESS FULL| T1 | 1 | 87003 | 347 (1)| 00:00:05 | 87003 |00:00:00.03 | 1246 |
-------------------------------------------------------------------------------------------------------------
--//邏輯讀的數量1246一致.
SCOTT@book> alter system checkpoint;
System altered.
SCOTT@book> alter system dump datafile 4 block 3482;
System altered.
--//轉儲 Auxillary Map部分如下:
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x01000d98 Data dba: 0x01000d9b
Extent 1 : L1 dba: 0x01000d98 Data dba: 0x01000da0
Extent 2 : L1 dba: 0x01000dc0 Data dba: 0x01000dc1
Extent 3 : L1 dba: 0x01000dc0 Data dba: 0x01000dc8
Extent 4 : L1 dba: 0x01000dd0 Data dba: 0x01000dd1
Extent 5 : L1 dba: 0x01000dd0 Data dba: 0x01000dd8
Extent 6 : L1 dba: 0x01000de0 Data dba: 0x01000de1
Extent 7 : L1 dba: 0x01000de0 Data dba: 0x01000de8
Extent 8 : L1 dba: 0x01000df0 Data dba: 0x01000df1
Extent 9 : L1 dba: 0x01000df0 Data dba: 0x01000df8
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~=>讀到0x1000dff後出現kteinmap.
Extent 10 : L1 dba: 0x01000780 Data dba: 0x01000781
Extent 11 : L1 dba: 0x01000780 Data dba: 0x01000788
Extent 12 : L1 dba: 0x01000790 Data dba: 0x01000791
Extent 13 : L1 dba: 0x01000790 Data dba: 0x01000798
Extent 14 : L1 dba: 0x010007a0 Data dba: 0x010007a1
Extent 15 : L1 dba: 0x010007a0 Data dba: 0x010007a8
Extent 16 : L1 dba: 0x01000800 Data dba: 0x01000802
Extent 17 : L1 dba: 0x01000880 Data dba: 0x01000882
Extent 18 : L1 dba: 0x01000900 Data dba: 0x01000902
Extent 19 : L1 dba: 0x01000a00 Data dba: 0x01000a02
-------------------------------------------------=>讀到0x1000a7f後出現kteinmap.
Extent 20 : L1 dba: 0x01000a80 Data dba: 0x01000a82
Extent 21 : L1 dba: 0x01000b00 Data dba: 0x01000b02
Extent 22 : L1 dba: 0x01000b80 Data dba: 0x01000b82
Extent 23 : L1 dba: 0x01000c00 Data dba: 0x01000c02
Extent 24 : L1 dba: 0x01000c80 Data dba: 0x01000c82
--------------------------------------------------------
--//你可以簡單發現一個規律,1次呼叫kteinmap讀10個Extent.
總結:
1.當段頭的 Auxillary Map僅僅1個Extent時,全表掃描不會將出現kteinmap計入邏輯讀的情況.
2.如果段頭的 Auxillary Map 記錄的extent很多,每次呼叫kteinmap讀10個Extent.
3.我沒有測試索引全掃描以及索引快速全掃描的情況,有機會測試看看.
4.注意執行計劃存在INDEX UNIQUE SCAN,是檢測不到pin的情況的.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2757857/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20210220]全索引掃描快速索引掃描的邏輯讀.txt索引
- 全表掃描和全索引掃描索引
- [20190221]使用nmap掃描埠的問題.txt
- 全表掃描和全索引掃描繼續（PG-TiDB）索引TiDB
- MySQL中的全表掃描和索引樹掃描MySql索引
- [20190815]索引快速全掃描的成本.txt索引
- oracle是如何進行全表掃描的Oracle
- 24_Oracle資料庫全表掃描詳解(四)_全表掃描生產最佳化案例三則Oracle資料庫
- [20231124]奇怪的高邏輯讀4.txt
- [20230216]奇怪的高邏輯讀3.txt
- [20210224]fetch r=0算邏輯讀嗎.txt
- [20210220]gdb跟蹤邏輯讀2.txt
- [20210301]為什麼邏輯讀這麼多.txt
- Fotify掃描問題Dynamic Code Evaluation:Code Injection
- awvs -網站掃描問題求指導網站
- 關係型資料庫全表掃描分片詳解資料庫
- 23_Oracle資料庫全表掃描詳解(三)Oracle資料庫
- 22_Oracle資料庫全表掃描詳解(二)Oracle資料庫
- 21_Oracle資料庫全表掃描詳解(一)Oracle資料庫
- [20180425]為什麼走索引邏輯讀反而高.txt索引
- PostgreSQL DBA(55) - MVCC#8(對全表掃描的影響)SQLMVCC#
- [20180410]為什麼2個邏輯讀不一樣.txt
- 《金子塔原理》讀書筆記之解決問題的邏輯筆記
- 索引掃描可能不如全表掃描的場景的理解__純粹資料量而言，不涉及CLUSTERING_FACTOR索引
- 邏輯題
- 讀人工智慧全傳06邏輯程式設計人工智慧程式設計
- 怎麼解決因全表掃描帶來的 Buffer Pool 汙染
- [20200306]hash join會提前終止掃描嗎.txt
- AWVS掃描器掃描web漏洞操作Web
- oracle邏輯讀過程Oracle
- 【TUNE_ORACLE】列出NL（NESTED LOOPS）被驅動表走了全表掃描的SQL參考OracleOOPSQL
- 掃描器的存在、奧普 掃描器
- 三、邏輯迴歸logistic regression——分類問題邏輯迴歸
- win10系統掃描器提示掃描不到掃描器如何解決Win10
- 掃描器
- 《底層邏輯》讀後感
- 【SpringSecurity系列02】SpringSecurity 表單認證邏輯原始碼解讀SpringGse原始碼
- SonarQube系列-透過配置掃描分析範圍，聚焦關鍵問題