Oracle效能分析4：資料存取方法之全掃描

標籤：database   oracle   效能   資料庫   

SQL語句執行時，Oracle的最佳化器會根據統計資訊確定表的訪問方式，一般來說，有兩種基本的資料訪問方式：1）全掃描。在全掃描（全表掃描或者快速全索引掃描）中，多個塊被讀入到一個IO運算中。2）索引掃描。索引掃描首先掃描索引葉子塊以取得特定的行id（rowid），然後利用這些行id來訪問父表取得實際的行資料，訪問通過單塊讀取來完成。這裡主要講解全掃描方式，後面將介紹索引掃描。

使用全掃描

當對一個表進行全掃描時，會將表中所有資料區塊（block）取出並進行處理，篩選出合格資料。注意Oracle必須將整個資料區塊（block）中的資料讀到記憶體中，再取得合格資料。因此Oracle的最佳化器需要關心兩個資訊：擷取塊的數量和每個塊中捨棄的資料量。最佳化器將根據這兩個資訊來判斷是否使用全掃描，首先我們來看看擷取塊的數量怎麼影響最佳化器的選擇。

擷取塊的數量

總的來說，如果查詢需要取出表的大部分資料區塊，則應該採用全掃描。但由於很難評估查詢將取出的表的資料區塊的數量，因此在使用全掃描上存在很多這樣的“經驗法則”：當你的查詢會取出表中x%的資料行，則應該選擇全掃描。這些法則有一定的道理，但是並不準確，因為當取出的資料行較大時，自然取出的資料區塊也會較大，這時採用全掃描並沒有問題，但有時雖然取出的資料行較小，會取出的資料區塊也可能會較大，實際上這時也應該採用全掃描，但這些“經驗法則”則不再生效。我們看看下面具體的例子。
我們建立一個表T1：

create table t1 as select trunc((rownum - 1) / 100) id, rownum value  from dba_source where rownum <= 10000

然後為T1建立索引：

create index idx_t1_id on t1(id)

然後為T1收集統計資訊：

BEGIN  dbms_stats.gather_table_stats(user,                                't1',                                method_opt => 'FOR ALL COLUMNS SIZE 1',                                cascade    => TRUE);END;

然後我們執行查詢：

select * from t1 where id = 0

該查詢的執行計畫如下：

SELECT STATEMENT, GOAL = ALL_ROWS TABLE ACCESS BY INDEX ROWID  INDEX RANGE SCAN

該執行計畫使用了索引範圍掃描，由於符合條件id為0的資料在表中只有100行資料，而整個表有1萬行資料，查詢出的資料只佔整個資料的1%，因此我們認為這是一種合理的執行計畫。
接下來我們看下面的例子，建立一個表格T2：

create table t2 as select mod(rownum,100) id, rownum value  from dba_source where rownum <= 10000

同樣為T2建立索引：

create index idx_t2_id on t2(id)

然後為T2收集統計資訊：

BEGIN  dbms_stats.gather_table_stats(user,                                't2',                                method_opt => 'FOR ALL COLUMNS SIZE 1',                                cascade    => TRUE);END;

然後執行查詢：

select * from t2 where id = 0

該查詢的執行計畫如下：

SELECT STATEMENT, GOAL = ALL_ROWS TABLE ACCESS FULL

我們看到表的執行計畫變成的全表掃描，我們可以很容易的得到該查詢的結果任然是100條資料，占T2表總資料量的1%，如果我們進一步比較T2和T1的資料，會發現兩張表的id欄位完全一樣，那為什麼T1選擇的是索引掃描，而T2卻選擇了全表掃描呢？
要瞭解原因，我們需要從資料在資料區塊上的分布來分析，在T1表中，id欄位的分布如下：

0 0...0 0 1 1...1 1 2 2...2 2......88 88...88 88......99 99...99 99

而T2表中id欄位的分布如下：

0 1 2 3 ... 98 99 0 1 2 3 ... 98 99 ...... 0 1 2 3 ... 98 99

從這裡可以看出T1表中id為0的資料都集中在幾個資料區塊上，而T2表中id為0的資料則分布在很多不同的塊上，這樣導致T1的查詢只需要讀取很少塊就可以得到結果，因此使用了索引範圍掃描，而T2上的查詢則需要讀取大部分塊，因此最佳化器選擇了全表掃描。

捨棄

需要注意的是，全掃描的效率不僅取決於讀取的資料區塊個數，也取決於最終的結果集行數。從上面的例子中我們可以看到：當一個資料區塊被讀取後，查詢將根據過濾條件捨棄不合格資料。而這個捨棄的過程是需要耗費資源的，由於這個操作在記憶體中，因此耗費的將是CPU資源，而捨棄的資料量越大，耗費的CPU資源就越多。
因此，讀取的資料區塊的個數越多，捨棄的資料量越大，全掃描的成本（cost）就越高。
不難想象，當表的資料量不斷增大，捨棄的行的數量不斷增加，全掃描的成本不斷增加，最終可能導致最佳化器放棄全掃描，轉而選擇索引掃描。

多塊讀取方式

多掃描使用的是多塊讀取，即一個單獨的IO調用將會讀取多個塊，讀取的塊的數量是可變的，但有一個上限，通過db_file_multiblock_read_count參數指定，該參數通過下面的SQL查看：

select * from v$parameter where name = 'db_file_multiblock_read_count'

下面描述了Oracle在幾種情況下讀取的塊的數量：

 1）Oracle不得不讀取超過一定邊界範圍的資料區塊。
 在這種情況下，Oracle將會在一次調用中讀取直到邊界範圍的資料區塊，然後發起另一次調用來讀取剩下的塊。
 2）存在塊已經在記憶體中
 首先讀取那麼已經在記憶體中的塊，然後發起調用讀取剩下的塊，這意味著多塊讀取可能一次僅讀取一塊。例如，假定多塊讀取的上限是16，該次讀取的資料區塊編號為1-16，並且編號為偶數的塊已經在記憶體中，那麼在這裡例子中，將會有8次的單塊讀取調用來讀取奇數編號的塊。
 3）多塊讀取大小超過了作業系統限制
 這時取決於你作業系統，因此是可變的。

高水位線

所謂高水位線，就是表中最後一塊有資料寫入的資料區塊。需要注意的是即使幾乎所有資料行都被刪除了，並且一些塊實際上已經完全變為空白的了，高水位線還是保持不變。看下面的例子，當表建立並插入資料後：

而隨著後面資料的變化（刪除和修改），表中的資料變化為：

雖然很多儲存地區已經沒有資料，但高水位線任然保持不變。
那麼，高水位線對全掃描會造成什麼影響呢？
執行全掃描時，Oracle將一直讀取到位於表中高水位線的資料區塊，即使它們是空的，這就意味著許多實際上不需要讀取的資料區塊也被讀取了。

下面通過一個具體的執行個體來看，使用先前的表T2。

1）通過下面的語句判斷表所包含的資料區塊數量：

select blocks from user_segments where segment_name = 'T2'結果：24

2）確定表中有多少資料區塊包含資料；

select count(distinct(dbms_rowid.rowid_block_number(rowid))) block_ct from t2結果：17

3）執行下面的查詢，並查看trace資訊（trace資訊的擷取方面見Oracle效能分析1）

alter system flush buffer_cache;--清理緩衝select * from t2 where id = 0

trace資訊為：

call     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        1      0.00       0.00          0          0          0           0Execute      1      0.00       0.00          0          0          0           0Fetch        2      0.00       0.06         18         20          0         100------- ------  -------- ---------- ---------- ---------- ----------  ----------total        4      0.00       0.07         18         20          0         100Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: 5  Rows     Row Source Operation-------  ---------------------------------------------------    100  TABLE ACCESS FULL T2 (cr=20 pr=18 pw=0 time=35975 us)

查詢出100行資料，物理讀取的資料區塊數量（disk）為18，包括一個表頭資料區塊的讀取（只有17個資料區塊包含資料）。執行計畫使用了全表掃描。

4）執行刪除資料的操作

delete from  T2

5）重新擷取表包含的資料區塊數量

select blocks from user_segments where segment_name = 'T2'結果：24

6）擷取包含資料的資料區塊數量

select count(distinct(dbms_rowid.rowid_block_number(rowid))) block_ct from t2結果：0

7）執行查詢並查看trace資訊

alter system flush buffer_cache;--清理緩衝select * from t2 where id = 0

trace資訊為：

call     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        1      0.00       0.00          0          0          0           0Execute      1      0.00       0.00          0          0          0           0Fetch        1      0.00       0.21         18         20          0           0------- ------  -------- ---------- ---------- ---------- ----------  ----------total        3      0.00       0.22         18         20          0           0Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: 5  Rows     Row Source Operation-------  ---------------------------------------------------      0  TABLE ACCESS FULL T2 (cr=20 pr=18 pw=0 time=214806 us)

我們可以看到查詢出的行數是0，但任然物理讀取了18個資料區塊，執行計畫任然使用了全掃描。

修正高水位線

我們已經瞭解了高水位線給全掃描帶來的效能問題，下面介紹了幾種降低高水位線的方法。

使用truncate操作

truncate table_name

在刪除資料時盡量使用truncate操作，降低高水位線。

move操作

alter table table_name move

注意move操作需要使用額外的資料表空間儲存，會鎖住表，這樣其他並發的使用者在表上執行的DML語句會產生等待。move操作會影響到表上的索引，因此索引需要rebuild。

shrink操作

shrink space操作，不需要任何額外的空間，但是速度要比move慢上很多。shrink命令分為下面兩種：

1）只壓縮空間不調整水位線，在業務繁忙時可以執行

alter table table_name shrink space compact

compact操作通過一系列insert、delete操作，將資料盡量排列在段的前面。在這個過程中需要在表上加RX鎖，即只在需要移動的行上加鎖。但由於涉及到rowid的改變，因此需要enable row movement。

2)調整水位線  會產生鎖，可以在業務比較少的時候執行，oracle 會記住1步驟中的操作，只調整水位線

alter table big_table shrink space

使用新表

複製要保留的資料到暫存資料表t，drop原表，然後rename暫存資料表t為原表。

Oracle效能分析4：資料存取方法之全掃描