解析Oracle資料掃描 Oracle SQL查詢最佳化 資料讀取方式

解析Oracle資料掃描

——Oracle SQL查詢最佳化

（1）

資料讀取方式

1、 Oracle資料讀取操作：

1.1資料讀取操作類型：

        在任何的資料庫中，當我們發起一個SQL語句進行資料查詢時，SQL執行引擎為了能夠返回我們需要的資料，都會進行資料掃描。這個掃描可能直接發生在特定的記憶體空間中，也可能發生在存放資料的磁碟上。但無論如何歸結起來，資料掃描的類型大致可以分為兩種，即順序掃描和隨機讀取。

       順序掃描主要發生在大範圍資料讀取時，而隨機掃描主要會發生在定位單條記錄資料時。而且順序掃描發生時通常會伴隨磁碟I/O的發生，也就是說從記憶體中直接讀取資料時通常不會發生資料順序掃描；但是隨機讀取卻可能發生在記憶體資料讀取時也可能發生在磁碟資料讀取時。當SQL執行資料讀取時，資料能夠從資料緩衝區中命中時，此時通常就會發生記憶體隨機資料讀取；但是當通過索引訪問資料，而資料又沒有緩衝在資料緩衝區中時，此時就會通過索引回表去讀取索引對應的資料，此時就會發生磁碟隨機I/O讀取。

       通常如果資料讀取數量不大，無論是資料掃描還是隨機讀取，資料讀取效能都不會太差，都能夠很快返回資料。但是隨著讀取資料範圍的擴大，那麼隨機讀取就會表現出對資料讀取效能的極大損害。如果發生的隨機讀取都是記憶體資料讀取，那麼這種損害的表現還不是馬上就會顯現，但是隨著時間的推移以及資料讀取範圍的不斷擴大，那麼即便是記憶體資料讀取也會表現出效能的急劇下降。因為資料在記憶體中，是被複雜的資料結構來管理的，在記憶體中操作這些資料時不可避免的要使用各種複雜的資料結構的操作演算法，同時可能還要伴隨著各種並發控制策略，因此大資料量的記憶體隨機讀取代價其實是不菲的。但是上述情況通常是不常見的，更加常見的是另外一種情況，那就是發生大量的隨機磁碟I/O讀取，通常這種情況會發生在，讀取資料的索引選擇性太差或者索引的聚簇因子過高等原因造成的，這種情況一旦出現那麼對系統的整體效能造成的傷害會非常大。因為隨機I/O所讀寫的資料都是一些不連續的資料，因此要進行大量的隨機I/O，那麼不可避免會造成物理磁碟不停的進行資料讀寫尋道，這非常可能形成系統整體的I/O瓶頸。在Oracle中如果出現了由於大量隨機I/O造成的資料讀取效能問題，通常會伴隨有較高的諸如buffer_busy_wait、db_sequence_read等待事件時間。因此如果涉及較大範圍的資料讀取，最好能夠驅使資料庫的執行引擎不要進行隨機I/O讀取，而進行資料掃描讀取，也就是說在資料範圍較大讀取發生時，應該驅使資料庫出現連續I/O操作，而不要出現不連續I/O操作，因為掃描操作其實是一種連續I/O操作。

       掃描操作也分為兩種情況，即全部範圍掃描和局部範圍掃描。所謂全部範圍掃描，是指一次性掃描滿足“驅動查詢條件”範圍的所有資料，然後在掃描的結果基礎上應用查詢的過濾條件進行資料過濾進而得到最終查詢結果；局部範圍掃描是指在進行資料掃描時，不會一次性掃描滿足“驅動查詢條件”範圍的所有資料，而是只掃描滿足驅動查詢條件一定範圍的資料，這個掃描範圍遠遠小於整體資料範圍，在Oracle中稱為批量數組Array_Size，當掃描的資料足以填滿批量數組時，查詢就立刻返回查詢結果，而且在使用者再次提出資料瀏覽要求之前停止查詢的執行。

由此可見局部範圍掃描能使掃描的資料量在很大程度上得到縮減，並能快速的返回查詢結果，提高查詢應用的響應性。局部範圍掃描並不是真正的縮減了查詢結果資料的範圍，而是利用了大多數使用者並不需要一次性瀏覽所有資料的特點，因此只返回部分資料供使用者查看。因此在一個具有大資料量而又要求系統具有很高響應性，同時大多數查詢操作涉及較大範圍資料的應用系統中，應該更多的引導資料庫的執行引擎進行資料局部範圍掃描，來提升資料查詢的效能和整體系統的響應性。

        全部範圍掃描和局部範圍掃描的處理過程如所示：

 

全域資料掃描

 

局部資料掃描

全部範圍掃描，首先掃描符合驅動查詢條件全部範圍的資料，然後在該範圍內應用過濾條件，並將過濾之後的資料儲存在臨時的儲存空間中（可能位於記憶體也可能位於外存），然後對臨時資料進行二次資料加工，之後填充批量數組，數組填充滿後返回結果並結束查詢直到下次請求出現。

在局部範圍掃描中，也是先按照驅動查詢條件掃描資料，與此同時通過應用過濾查詢條件，對資料進行過濾，並且將滿足條件的行直接填充到批量數組中，數組被填充滿之後返回結果並終止查詢。因此無論查詢資料的範圍有多大，局部範圍掃描都會比較快速返回查詢結果。

1.2局部範圍掃描的條件：

        在有的情況下如果能夠將SQL的執行方式從全域範圍掃描轉變成局部範圍掃描，那麼查詢的執行速度就可能得到大幅度的提高。但是並不是所有的SQL都可以直接按照局部範圍掃描的方式進行，因此要求我們應該知道SQL查詢直接進行範圍掃描的條件，同時還要知道如何將不能直接進行局部範圍掃描的SQL查詢，引導為進行局部範圍掃描的方法。

什麼樣的查詢可以直接使用局部範圍掃描呢？通常來說就是通過具有較高選擇性的過濾條件，進行的資料範圍掃描（range scan），同時查詢的結果不需要在見到所有範圍內的資料後才能得出結果的情況下，SQL執行最佳化器都可以引導執行引擎使用局部範圍掃描。這些操作包括：通過主鍵讀取資料、通過唯一索引讀取資料、以及通過各種索引（具有較高的選擇性）進行資料掃描，這些操作都可以直接使用局部範圍掃描進行資料讀取。

        相反那些需要讀取範圍內全部資料的操作，無法直接使用局部範圍掃描，這些操作都是運行在資料全域掃描模式下的。這些操作包括：分組函數（count,sum,min,max等）、引發排序的操作（order by,group by,distinct等）、使用Having進行分組過濾的查詢、集合操作（union、minus、intersect等）、歸併串連操作merge join、引發磁碟排序的hash join(non
in memory hash join)。通常如果你在某條SQL語句的執行計畫中發現了”sort”執行計畫，那麼通常就可以斷定這個語句中存在非局部掃描，可能是整條語句無法局部掃描，也可能是該域中的某個部分無法進行局部掃描（如：子查詢或者表串連操作），這時一種語句最佳化方式就是看一看能否將非局部掃描部分，通過引導方法引導成局部資料掃描（具體的引導方法，在後面相關部分論述）。

       這裡還要針對集合操作強調一下，集合操作中大部分無法進行局部範圍掃描，但是有一個例外就是union all操作，因為union all操作不需要對結果進行排重，因此也就省去了對資料的整體擷取或排序，所以該操作可以按照局部範圍掃描的方式進行。

1.3最佳化器模式對局部範圍掃描的影響：

       對於某個SQL語句，即使邏輯上可以實現局部範圍掃描，也會隨著最佳化器模式的不同而存在一定差異。在RBO或者CBO的FIRST_ROWS模式下，基本上是按照局部範圍掃描來制定執行計畫；在CBO的ALL_ROWS模式下，基本上是按照全域範圍掃描來制定執行計畫。RBO模式是一種過時的最佳化器技術，當今只會在某些極其特殊的場合來使用它來發揮一些其特殊的作用，因此我們在此對它不做討論。

    CBO的FIRST_ROWS模式所追求的是SQL操作部分範圍最佳化；ALL_ROWS追求的是SQL操作整體範圍最佳化，即追求“Best Throughput”。通過上述對資料局部範圍掃描的描述，那麼我們是不是就要將最佳化器設定為FIRST_ROWS模式而後萬事大吉呢？事實上並不是這樣。

       根據當今主流伺服器硬體平台處理能力的發展和資料庫系統等支撐軟體的技術發展，以及系統架構高可用特性的追求，ALL_ROWS模式是比較符合發展趨勢的最佳化器技術，因為它是追求整體最優，追求整個系統的效能成本的平衡。我想這也是為什麼Oracle10G之後，將ALL_ROWS設定為最佳化器預設模式的一個重要原因。

那麼我們正確的最佳化器設定策略應該是什麼呢？我認為正確的最佳化器設定策略應該是使用ALL_ROWS模式，然後在最佳化器結果無法令人滿意的必要情況下，有的放矢的利用最佳化器引導技術（如：Hints、SQL語句調整重寫等），來誘導最佳化器來採用正確的資料掃描方式，進行資料處理以便獲得令我們滿意的結果。