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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ORACLE全表掃描查詢Oracle
- oracle 全表掃描,索引範圍掃描與塊的理解Oracle索引
- oracle優化：避免全表掃描Oracle優化
- Oracle中存取資料掃描Table及索引的方式(全表掃描，索引掃描等)Oracle索引
- 優化Oracle with全表掃描的問題優化Oracle
- (轉)索引掃描還是全表掃描（Index Scan Or Full Table Scan）索引Index
- 轉)索引掃描還是全表掃描（Index Scan Or Full Table Scan）索引Index
- MySQL中的全表掃描和索引樹掃描MySql索引
- 優化Oracle with全表掃描的問題(二)優化Oracle
- delete 刪除資料 全表掃描還是掃描所有塊的測試delete
- 優化全表掃描優化
- delete 與全表掃描delete
- 查詢全表掃描的sqlSQL
- 使用索引快速全掃描(Index FFS)避免全表掃描的若干場景索引Index
- oracle實驗記錄（分割槽全表掃描（全區掃描） FTS 時候的成本計算）Oracle
- 在Linux中，什麼是埠掃描？如何使用工具如nmap進行埠掃描？Linux
- 24_Oracle資料庫全表掃描詳解(四)_全表掃描生產最佳化案例三則Oracle資料庫
- 抓取全表掃描的表，篩選和分析
- oracle sql tuning 8--優化全表掃描OracleSQL優化
- 查詢全表掃描語句
- 索引全掃描和索引快速全掃描的區別索引
- Oracle 11g全表掃描以Direct Path Read方式執行Oracle
- 23_Oracle資料庫全表掃描詳解(三)Oracle資料庫
- 22_Oracle資料庫全表掃描詳解(二)Oracle資料庫
- 21_Oracle資料庫全表掃描詳解(一)Oracle資料庫
- oracle實驗記錄 （全表掃描COST計算方法）Oracle
- 一條全表掃描sql語句的分析SQL
- Oracle JDBC驅動使用setDate()、setTimestamp()導致全表掃描OracleJDBC
- Greenplum儲存過程使用分割槽表將進行全表掃描儲存過程
- 使用全表掃描快取大表的相關問題快取
- 有索引卻走全表掃描的實驗分析索引
- noworkload下全表掃描cost的計算
- 【MySQL】全索引掃描的bugMySql索引
- 隱形轉換導致全表掃描案例
- 全表掃描的cost 與 索引掃描Cost的比較 – 無直方圖（10.1.0.3以後）索引直方圖
- 關於分割槽表中的全partition掃描問題
- 累計的力量，delete全表掃描導致程式執行時間過長。delete
- 【TUNE_ORACLE】列出NL（NESTED LOOPS）被驅動表走了全表掃描的SQL參考OracleOOPSQL