Oracle 繫結變數 詳解

 

之前整理過一篇有關繫結變數的文章，不太詳細，重新補充一下。

         Oracle 繫結變數

         http://blog.csdn.net/tianlesoftware/archive/2009/10/17/4678335.aspx

 

 

一．繫結變數

         bind variable： A variable in a SQL statement that must be replaced with a valid value, or the address of a value, in order for the statement to successfully execute.

 

變數繫結是OLTP系統中一個非常值得關注的技術。良好的變數繫結會使OLTP系統資料庫中的SQL 執行速度飛快，記憶體效率極高；不使用繫結變數可能會使OLTP 資料庫不堪重負，資源被SQL解析嚴重耗盡，系統執行緩慢。

 

         當一個使用者與資料庫建立連線後，會向資料庫發出操作請求，即向資料庫送過去SQL語句。 Oracle 在接收到這些SQL後，會先對這個SQL做一個hash 函式運算，得到一個Hash值，然後到共享池中尋找是否有和這個hash 值匹配的SQL存在。 如果找到了，Oracle將直接使用已經存在的SQL 的執行計劃去執行當前的SQL，然後將結果返回給使用者。 如果在共享池中沒有找到相同Hash 值的SQL，oracle 會認為這是一條新的SQL。 會進行解析。

 

 

Oracle 解析的步驟如下：

（1）       語法解析

（2）       語義解析

（3）       生成執行計劃，這裡分軟解析和硬解析。硬解析是非常耗資源的。

（4）       SQL的執行

 

關於SQL的解析，詳見Blog：

         Oracle SQL的硬解析和軟解析

         http://blog.csdn.net/tianlesoftware/archive/2010/04/08/5458896.aspx

 

瞭解了SQL 的執行過程，在來看一些繫結變數，繫結變數的本質就是本來需要做Oracle 硬解析的SQL 變成軟解析，以減少ORACLE 花費在SQL解析上的時間和資源。

 

加入有兩條SQL:    

         Select salary from user where name=’A’;

         Select salary from user where name=’B’;

 

如果沒有用繫結變數，那麼這2條SQL 會被解析2次，因為他們的謂詞部分不一樣。 如果我們用了繫結變數，如：

         Select salary from user where name=:X;

 

這時，之前的2條SQL就變成了一種SQL， Oracle 只需要對每一種SQL做一次硬解析，之後類似的SQL 都使用這條SQL產生的執行計劃，這樣就可以大大降低資料庫花費在SQL解析上的資源開銷。 這種效果當SQL執行的越多，就越明顯。

 

         簡單的說，繫結變數就是拿一個變數來代替謂詞常量，讓Oracle每次對使用者發來的SQL做hash 運算時，運算出的結果都是同樣的Hash值，於是將所有的使用者發來的SQL看作是同一個SQL來物件。

 

 

二． OLAP 和OLTP 系統中的繫結變數

         OLAP 和 OLTP 系統是有很大差異的。 他們之間的區別，詳細參考Blog：

         Oracle OLAP 與 OLTP 介紹

         http://blog.csdn.net/tianlesoftware/archive/2010/08/08/5794844.aspx

 

在OLTP系統中，我們可以使用繫結變數是因為在OLTP中，SQL語句大多是比較簡單或者操作的結果集都很小。當一個表上建立了索引，那麼這種極小結果集的操作使用索引最合適，並且幾乎所有的SQL的執行計劃的索引都會被選擇，因為這種情況下，索引可能只需要掃描幾個資料塊就可以定位到資料，而全表掃描將會相當耗資源。 因此，這種情況下，即使每個使用者的謂詞條件不一樣，執行計劃也是一樣的，就是都用索引來訪問資料，基本不會出現全表掃描的情況。 在這種執行計劃幾乎唯一的情況下，使用繫結變數來代替謂詞常量，是合適的。

 

在OLAP系統中，SQL的操作就複雜很多，OLAP資料庫上大多數時候執行的一些報表SQL，這些SQL經常會用到聚合查詢（如：group by），而且結果集也是非常龐大，在這種情況下，索引並不是必然的選擇，甚至有時候全表掃描的效能會更優於索引，即使相同的SQL，如果謂詞不同，執行計劃都可能不同。

 

 

對於OLAP系統中的繫結變數，有以下原則：

（1）       OLAP 系統完全沒有必要繫結變數，那樣只會帶來負面的影響，比如導致SQL選擇錯誤的執行，這個代價有時是災難性的；讓Oracle對每條SQL做硬分析，確切的知道謂詞條件的值，這對執行計劃的選擇至關重要，這樣做的原因是，在OLAP系統中，SQL硬分析的代價是可以忽略的，系統的資源基本上是用於做大的SQL查詢，和查詢比起來，SQL解析消耗的資源顯得微不足道。所以得到一個最優的執行計劃就非常重要。

（2）       在OLAP系統中，讓Oracle確切地知道謂詞的數值至關重要，它直接決定了SQL執行計劃的選擇，這樣做的方式就是不要繫結變數。

（3）       在OLAP系統中，表，索引的分析顯得直觀重要，因為它是Oracle 為SQL做出正確的執行計劃的資訊的來源和依據，所以需要建立一套能夠滿足系統需求的物件分析的執行Job。

 

 

三．Bind peaking

        

先看一段官網的說明：

         The query optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. This feature enables the optimizer to determine the selectivity of any WHERE clause condition as if literals have been used instead of bind variables.

To ensure the optimal choice of cursor for a given bind value, Oracle Database uses bind-aware cursor matching. The system monitors the data access performed by the query over time, depending on the bind values. If bind peeking takes place, and if the database uses a histogram to compute selectivity of the predicate containing the bind variable, then the database marks the cursor as bind-sensitive.

Whenever the database determines that a cursor produces significantly different data access patterns depending on the bind values, the database marks this cursor as bind-aware. Oracle Database switches to bind-aware cursor matching to select the cursor for this statement. When bind-aware cursor matching is enabled, the database selects plans based on the bind value and the optimizer estimate of its selectivity. With bind-aware cursor matching, a SQL statement with user-defined bind variable can have multiple execution plans, depending on the bind values.

When bind variables appear in a SQL statement, the database assumes that cursor sharing is intended and that different invocations use the same execution plan. If different invocations of the cursor significantly benefit from different execution plans, then bind-aware cursor matching is required. Bind peeking does not work for all clients, but a specific set of clients.

 

From：http://download.oracle.com/docs/cd/E11882_01/server.112/e10821/optimops.htm#PFGRF94588

 

Bind Peeking是Oracle 9i中引入的新特性，它的作用就是在SQL語句硬分析的時候，檢視一下當前SQL謂詞的值，以便生成最佳的執行計劃。 而在oracle 9i之前的版本中，Oracle 只根據統計資訊來做出執行計劃。

 

要注意的是，Bind Peeking只發生在硬分析的時候，即SQL被第一次執行的時候，之後的變數將不會在做peeking。我們可以看出，Bind peeking並不能最終解決不同謂詞導致選擇不同執行計劃的問題，它只能讓SQL第一次執行的時候，執行計劃選擇更加準確，並不能幫助OLAP系統解決繫結變數導致執行計劃選擇錯誤的問題。這也是OLAP不應該使用繫結變數的一個原因。

 

 

 

總結：

         對於OLTP系統，相同的SQL重複頻率非常高，如果優化器反覆解析SQL，必然會極大的消耗系統資源，另外，OLTP系統使用者請求的結果集都非常小，所以基本上都考慮使用索引。 Bind Peeking 在第一次獲得了一個正確的執行計劃之後，後續的所有SQL都按照這個執行計劃來執行，這樣就極大的改善了系統的效能。

 

         對於OLAP系統，SQL執行計劃和謂詞關係極大，謂詞值不同，可能執行計劃就不同，如果採用相同的執行計劃，SQL的執行效率必然很低。另外，一個OLAP系統資料庫每天執行的SQL數量遠遠比OLTP少，並且SQL重複頻率也遠遠低於OLTP系統，在這種條件下，SQL解析花費的代價和SQL執行花費的代價相比，解析的代價可以完全忽略。

 

所以，對於OLAP系統，不需要繫結變數，如果使用可能導致執行計劃選擇錯誤。 並且，如果用了繫結變數，Bind Peeking也只能保證第一條硬分析SQL能正確的選擇執行計劃，如果後面的謂詞改變，很可能還是會選擇錯誤的執行計劃。 因此在OLAP系統中，不建議使用繫結變數。