oracle是如何進行全表掃描的
我們都知道，全表掃描需要掃描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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 全表掃描和全索引掃描索引
- MySQL中的全表掃描和索引樹掃描MySql索引
- 全表掃描和全索引掃描繼續（PG-TiDB）索引TiDB
- 24_Oracle資料庫全表掃描詳解(四)_全表掃描生產最佳化案例三則Oracle資料庫
- 在Linux中，什麼是埠掃描？如何使用工具如nmap進行埠掃描？Linux
- 23_Oracle資料庫全表掃描詳解(三)Oracle資料庫
- 22_Oracle資料庫全表掃描詳解(二)Oracle資料庫
- 21_Oracle資料庫全表掃描詳解(一)Oracle資料庫
- 在Linux中，如何進行安全漏洞掃描？Linux
- 【Oracle】 索引的掃描方式Oracle索引
- 【TUNE_ORACLE】列出NL（NESTED LOOPS）被驅動表走了全表掃描的SQL參考OracleOOPSQL
- 對上次的自動掃描進行改造
- [20210220]全索引掃描快速索引掃描的邏輯讀.txt索引
- 論Springboot下如何進行包掃描Spring Boot
- [20210219]全表掃描邏輯讀問題.txt
- PostgreSQL DBA(55) - MVCC#8(對全表掃描的影響)SQLMVCC#
- 索引掃描可能不如全表掃描的場景的理解__純粹資料量而言，不涉及CLUSTERING_FACTOR索引
- 什麼是漏洞掃描?漏洞掃描功能有哪些?
- 關係型資料庫全表掃描分片詳解資料庫
- [20190815]索引快速全掃描的成本.txt索引
- 怎麼解決因全表掃描帶來的 Buffer Pool 汙染
- 掃描行為分析
- 理解資料庫掃描方法-利用掃描方法對資料儲存進行優化資料庫優化
- win10系統掃描器提示掃描不到掃描器如何解決Win10
- 掃描器的存在、奧普 掃描器
- Win10系統下如何使用印表機進行掃描檔案Win10
- IDEA如何進行阿里巴巴編碼規約掃描並匯出報告Idea阿里
- win10怎麼關閉安全掃描 win10正在執行安全掃描如何關閉Win10
- 電腦執行twincat2掃描ethercat裝置並進行控制
- AWVS掃描器掃描web漏洞操作Web
- 如何使用evilscan 掃描網路
- oracle 一個or語句因欄位資料分佈不均並缺少直方圖引起的全表掃描優化分析Oracle直方圖優化
- 專案owner看這裡，MaxCompute全表掃描新功能，給你“失誤”的機會
- 如何減少 Hyperf 框架的掃描時間框架
- 通過Python掃描程式碼關鍵字並進行預警Python
- 掃描器
- 掃描王 for Mac專業圖片掃描工具Mac
- win10掃描器在哪開啟 win10如何啟動掃描器Win10