oracle是如何進行全表掃描的

space6212發表於2019-05-30

我們都知道,全表掃描需要掃描HWM下的所有block,那麼,
1、全表掃描時是按照什麼順序掃描block的?
2、一次IO能否跨越一個extent?
3、全表掃描是否一定對應著db file scattered read等待事件?

我們透過試驗一一解答這些問題。


--首先構造一個表,讓資料部分處於資料檔案的高階,一部分處於資料檔案的低端
SQL> create table t1(a char(2000));

Table created

SQL> insert into t1 select '1' from dual connect by rownum<10000;

9999 rows inserted

SQL> commit;

Commit complete

SQL> drop table t;

Table dropped

SQL> insert into t1 select '1' from dual connect by rownum<40000;

39999 rows inserted

SQL> commit;

Commit complete

--檢視extent的情況
SQL> select extent_id,file_id,block_id,blocks from dba_extents where segment_name='T1' and owner='SUK' order by 1;

EXTENT_ID FILE_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
0 6 1345 8
1 6 1353 8
2 6 1361 8
3 6 1369 8
4 6 1377 8
5 6 1385 8
6 6 1393 8
7 6 1401 8
8 6 1409 8
9 7 8329 8
10 7 8337 8
11 7 8345 8
12 7 8353 8
13 7 8361 8
14 7 8369 8
15 7 8377 8
16 8 8201 128
17 6 8201 128
18 7 8457 128
...............
41 6 9225 128
42 7 1545 128
48 7 1801 128
49 8 1545 128
...............
78 7 3081 128
79 8 2953 1024
...............
86 6 5001 1024
87 7 5257 1024

--透過10046跟蹤一下全表掃
SQL> @d:sqlgettrace

TRACE_FILE_NAME
--------------------------------------------------------------------------------
e:oracleadminsukudumpsuk_ora_1208.trc

SQL> alter system dump datafile 6 block 1345;

System altered

--設定每次查詢IO最多讀取的block個數(不是4的倍數)
SQL> alter session set db_file_multiblock_read_count=18;

Session altered

--清空buffer cache,這是為了更好的觀察全表掃操作
SQL> alter session set events 'immediate trace name flush_cache level 1';

Session altered

SQL> alter session set events '10046 trace name context forever,level 12';

Session altered

SQL> select count(1) from t1;

COUNT(1)
----------
49998

SQL> alter session set events '10046 trace name context off';

Session altered




--觀察trace檔案
因為顯示問題,這裡只節選了部分內容
EXEC #2:c=0,e=800,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=3140574044
WAIT #2: nam='SQL*Net message to client' ela= 3 p1=1413697536 p2=1 p3=0
WAIT #2: nam='SQL*Net message from client' ela= 679 p1=1413697536 p2=1 p3=0
WAIT #2: nam='db file sequential read' ela= 9118 p1=6 p2=1345 p3=1
WAIT #2: nam='db file scattered read' ela= 694 p1=6 p2=1346 p3=7
--p1=6 p2=1346可知,oracle從檔案6的第1346個資料塊開始進行全表掃描,這個塊在表t1的第一個extent上
--表t1的第一個extent的第一個block是1345,但這個block並比記錄表的實際資料,而是記錄其所在extent的一些資訊,所以oracle在全表掃描的時候跳過這個block。
WAIT #2: nam='db file scattered read' ela= 788 p1=6 p2=1353 p3=8
WAIT #2: nam='db file scattered read' ela= 692 p1=6 p2=1361 p3=8
WAIT #2: nam='db file scattered read' ela= 692 p1=6 p2=1369 p3=8
WAIT #2: nam='db file scattered read' ela= 818 p1=6 p2=1377 p3=8
WAIT #2: nam='db file scattered read' ela= 688 p1=6 p2=1385 p3=8
WAIT #2: nam='db file scattered read' ela= 691 p1=6 p2=1393 p3=8
WAIT #2: nam='db file scattered read' ela= 651 p1=6 p2=1401 p3=8
........
WAIT #2: nam='db file scattered read' ela= 9917 p1=8 p2=1289 p3=18
WAIT #2: nam='db file scattered read' ela= 1566 p1=8 p2=1307 p3=18
WAIT #2: nam='db file scattered read' ela= 2887 p1=8 p2=1325 p3=18
WAIT #2: nam='db file scattered read' ela= 1596 p1=8 p2=1343 p3=18
WAIT #2: nam='db file scattered read' ela= 1584 p1=8 p2=1361 p3=18
WAIT #2: nam='db file scattered read' ela= 1620 p1=8 p2=1379 p3=18
WAIT #2: nam='db file scattered read' ela= 1628 p1=8 p2=1397 p3=18
WAIT #2: nam='db file scattered read' ela= 260 p1=8 p2=1415 p3=2
--mod(128,18)=2,且相鄰空間也是t1的extent,但oracle在extent的最後一次IO還是隻讀取了2個block,說明oracle的一次IO並不能跨越extent。
........
WAIT #2: nam='db file scattered read' ela= 11180 p1=8 p2=1417 p3=18
WAIT #2: nam='db file scattered read' ela= 1851 p1=8 p2=1435 p3=18
WAIT #2: nam='db file scattered read' ela= 1495 p1=8 p2=1453 p3=18
WAIT #2: nam='db file scattered read' ela= 1476 p1=8 p2=1471 p3=18
WAIT #2: nam='db file scattered read' ela= 2790 p1=8 p2=1489 p3=18
WAIT #2: nam='db file scattered read' ela= 1578 p1=8 p2=1507 p3=18
WAIT #2: nam='db file scattered read' ela= 1576 p1=8 p2=1525 p3=18

.......
WAIT #2: nam='db file scattered read' ela= 13550 p1=6 p2=5001 p3=18
WAIT #2: nam='db file scattered read' ela= 1582 p1=6 p2=5019 p3=18
WAIT #2: nam='db file scattered read' ela= 3037 p1=6 p2=5037 p3=18
WAIT #2: nam='db file scattered read' ela= 1511 p1=6 p2=5055 p3=18
WAIT #2: nam='db file scattered read' ela= 1461 p1=6 p2=5073 p3=18
WAIT #2: nam='db file scattered read' ela= 1612 p1=6 p2=5091 p3=18
WAIT #2: nam='db file scattered read' ela= 1660 p1=6 p2=5109 p3=18
WAIT #2: nam='db file scattered read' ela= 3243 p1=6 p2=5127 p3=18
WAIT #2: nam='db file scattered read' ela= 1484 p1=6 p2=5145 p3=18
WAIT #2: nam='db file scattered read' ela= 1458 p1=6 p2=5163 p3=18
WAIT #2: nam='db file scattered read' ela= 1448 p1=6 p2=5181 p3=18
WAIT #2: nam='db file scattered read' ela= 1755 p1=6 p2=5199 p3=18
WAIT #2: nam='db file scattered read' ela= 2655 p1=6 p2=5217 p3=18
WAIT #2: nam='db file scattered read' ela= 1496 p1=6 p2=5235 p3=18
WAIT #2: nam='db file scattered read' ela= 1556 p1=6 p2=5253 p3=18
WAIT #2: nam='db file scattered read' ela= 1677 p1=6 p2=5271 p3=18
WAIT #2: nam='db file scattered read' ela= 1529 p1=6 p2=5289 p3=18
WAIT #2: nam='db file scattered read' ela= 2678 p1=6 p2=5307 p3=18
WAIT #2: nam='db file scattered read' ela= 1642 p1=6 p2=5325 p3=18
WAIT #2: nam='db file scattered read' ela= 1536 p1=6 p2=5343 p3=18
WAIT #2: nam='db file scattered read' ela= 1616 p1=6 p2=5361 p3=18
WAIT #2: nam='db file scattered read' ela= 2891 p1=6 p2=5379 p3=18
WAIT #2: nam='db file scattered read' ela= 1787 p1=6 p2=5397 p3=18
WAIT #2: nam='db file scattered read' ela= 1585 p1=6 p2=5415 p3=18
WAIT #2: nam='db file scattered read' ela= 1569 p1=6 p2=5433 p3=18
WAIT #2: nam='db file scattered read' ela= 1474 p1=6 p2=5451 p3=18
WAIT #2: nam='db file scattered read' ela= 2805 p1=6 p2=5469 p3=18
WAIT #2: nam='db file scattered read' ela= 1647 p1=6 p2=5487 p3=18
WAIT #2: nam='db file scattered read' ela= 1566 p1=6 p2=5505 p3=18
WAIT #2: nam='db file scattered read' ela= 1587 p1=6 p2=5523 p3=18
WAIT #2: nam='db file scattered read' ela= 1749 p1=6 p2=5541 p3=18
WAIT #2: nam='db file scattered read' ela= 2846 p1=6 p2=5559 p3=18
WAIT #2: nam='db file scattered read' ela= 1562 p1=6 p2=5577 p3=18
WAIT #2: nam='db file scattered read' ela= 1472 p1=6 p2=5595 p3=18
WAIT #2: nam='db file scattered read' ela= 1535 p1=6 p2=5613 p3=18
WAIT #2: nam='db file scattered read' ela= 2864 p1=6 p2=5631 p3=18
WAIT #2: nam='db file scattered read' ela= 1621 p1=6 p2=5649 p3=18
WAIT #2: nam='db file scattered read' ela= 1608 p1=6 p2=5667 p3=18
WAIT #2: nam='db file scattered read' ela= 1790 p1=6 p2=5685 p3=18
WAIT #2: nam='db file scattered read' ela= 1553 p1=6 p2=5703 p3=18
WAIT #2: nam='db file scattered read' ela= 2554 p1=6 p2=5721 p3=18
WAIT #2: nam='db file scattered read' ela= 1612 p1=6 p2=5739 p3=18
WAIT #2: nam='db file scattered read' ela= 1631 p1=6 p2=5757 p3=18
WAIT #2: nam='db file scattered read' ela= 1610 p1=6 p2=5775 p3=18
WAIT #2: nam='db file scattered read' ela= 1590 p1=6 p2=5793 p3=18
WAIT #2: nam='db file scattered read' ela= 2898 p1=6 p2=5811 p3=18
WAIT #2: nam='db file scattered read' ela= 1621 p1=6 p2=5829 p3=18
WAIT #2: nam='db file scattered read' ela= 1568 p1=6 p2=5847 p3=18
WAIT #2: nam='db file scattered read' ela= 1607 p1=6 p2=5865 p3=18
WAIT #2: nam='db file scattered read' ela= 1623 p1=6 p2=5883 p3=18
WAIT #2: nam='db file scattered read' ela= 2901 p1=6 p2=5901 p3=18
WAIT #2: nam='db file scattered read' ela= 1588 p1=6 p2=5919 p3=18
WAIT #2: nam='db file scattered read' ela= 1627 p1=6 p2=5937 p3=18
WAIT #2: nam='db file scattered read' ela= 1632 p1=6 p2=5955 p3=18
WAIT #2: nam='db file scattered read' ela= 2900 p1=6 p2=5973 p3=18
WAIT #2: nam='db file scattered read' ela= 1531 p1=6 p2=5991 p3=18
WAIT #2: nam='db file scattered read' ela= 1385 p1=6 p2=6009 p3=16
--mod(1024,18)=16
.......
WAIT #2: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0
FETCH #2:c=312500,e=3037398,p=16669,cr=16679,cu=0,mis=0,r=1,dep=0,og=4,tim=3143614465
WAIT #2: nam='SQL*Net message from client' ela= 5091 p1=1413697536 p2=1 p3=0

從上面的trace可以得到如下結論:全表掃描時
1、oracle從表的第一個extent的第2個block開始讀取block
2、一次IO的讀取的最多block數由db_file_multiblock_read_count決定
3、一次IO不能跨越一個extent,即使空間是相鄰的。當讀取到extent的末端時,一次IO可能會不足db_file_multiblock_read_count設定值

接著看另一個問題,全表掃描是否就意味著db file scattered read等待呢?

suk@TEST1> select count(1) from t1


執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T1'




統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
6676 consistent gets
0 physical reads

可以看到都是邏輯讀,物理讀是0
對這次查詢進行trace跟蹤:
=====================
PARSING IN CURSOR #2 len=25 dep=0 uid=28 oct=3 lid=28 tim=18446744071181756059 hv=2178900350 ad='66546ef0'
select count(1) from t1
END OF STMT
PARSE #2:c=0,e=422,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=18446744071181756053
BINDS #2:
EXEC #2:c=0,e=840,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=18446744071181760837
WAIT #2: nam='SQL*Net message to client' ela= 3 p1=1413697536 p2=1 p3=0
WAIT #2: nam='SQL*Net message from client' ela= 617 p1=1413697536 p2=1 p3=0
WAIT #2: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0
FETCH #2:c=0,e=20715,p=0,cr=6677,cu=0,mis=0,r=1,dep=0,og=4,tim=18446744071181784695
WAIT #2: nam='SQL*Net message from client' ela= 6187 p1=1413697536 p2=1 p3=0
=====================

可以看到,這次的全表掃描沒有db file scattered read等待。這是因為db file scattered read表示從資料檔案裡讀取block,也就是會產生物理讀。
如果表的block都已經在記憶體中了,那就不會發生物理讀,也就沒有db file scattered read等待了。
如果只有部分block在記憶體中,那發生db file scattered read等待的次數也比全是物理讀的要少許多。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/231499/viewspace-63855/,如需轉載,請註明出處,否則將追究法律責任。

相關文章