幾條Oracle資料庫開發的原則歸納（下）

 

上篇中，我們主要介紹了進行Oracle資料庫開發應該關注的幾條原則問題，本篇繼續討論這個話題。

 

4、The Trap In Your Where Condition and Order/Group clause

 

我們進行的日常資料庫開發，主要集中在DML操作型別，以select/update/insert/delete為核心。在這些型別操作，我們最需要關注的並不是select的結果集合列表，也不是insert/update的具體數值，而是定位操作物件的where條件和進行大規模計算操作的group/order。

 

Where條件的作用是讓Oracle可以定位到我們需要進行檢索處理的記錄。Where條件的書寫起到兩個層面的作用，其一是描述了結果集合屬性，另一個是間接影響到Oracle定位資料的方式，也就是執行計劃。

 

SQL是一種描述性語言，我們只需要描述需要的資料屬性就可以了。但是也就是這個特性，往往會讓我們書寫出很糟糕的SQL。同樣的結果集合，不同的描述方式（SQL where條件），執行效果和執行計劃可能會千差萬別。

 

寫好where條件的規則技巧有很多，比如in用exists替換、is null不選擇等等，每種技巧都是基於特定的應用場景和內部背景。這裡列舉一個對條件列不要輕易處理的例子。

 

如果我們在where條件中書寫一些表示式，通常Oracle在SQL預處理前就會將表示式進行處理。但是，對於攜帶資料列的條件表示式，這種改寫變化是不會越過等號的。下面進行試驗。

 

 

SQL> create table t as select * from dba_objects;

Table created

 

SQL> create index idx_t_id on t(object_id);

Index created

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

 

 

選擇在資料表t的object_id列新增索引。兩個SQL含義等價，但是執行計劃完全不同。

 

 

SQL> explain plan for select * from t where object_id=999+1;

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 514881935

--------------------------------------------------------------------------------

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Ti

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |          |     1 |    93 |     2   (0)| 00

|   1 |  TABLE ACCESS BY INDEX ROWID| T        |     1 |    93 |     2   (0)| 00

|*  2 |   INDEX RANGE SCAN          | IDX_T_ID |     1 |       |     1   (0)| 00

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OBJECT_ID"=1000)

14 rows selected

 

SQL> explain plan for select * from t where object_id-1=999;

Explained

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |   513 | 47709 |   160   (3)| 00:00:02 |

|*  1 |  TABLE ACCESS FULL| T    |   513 | 47709 |   160   (3)| 00:00:02 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("OBJECT_ID"-1=999)

 

13 rows selected

 

 

相同的含義SQL，只是進行簡單的算術移向操作，就有如此大的執行計劃成本差異。在第一個SQL中，Oracle自動將運算完成後走索引路徑。而第二個SQL因為減一操作在object_id同側，視為將object_id處理的表示式。表示式是不會選擇普通索引路徑的，只有建立特定的函式索引。

 

此類問題還可以引申到對列進行函式處理或者表示式處理，這樣的SQL語句非常容易出現在where條件中。作為一名開發人員，應該儘可能的消除這樣的SQL語句，增加SQL最佳化的機率。

 

Group by/order也是同樣的問題。在Oracle中，分組操作和排序操作是相當“昂貴”的。當進行group by/order的時候，對應的資料集合需要存放在PGA的專門區域中進行。這種操作消耗PGA甚至臨時Temp表空間空間，同時也會消耗一定的CPU資源。所以，如果沒有明確的需求，我們儘量少用這兩種型別操作。

 

5、Hard Parse VS Soft Parse

 

Oracle開發人員平時聽到最多的資料庫SQL技巧恐怕就是繫結變數SQL的書寫了。使用繫結變數的原因簡單的說，就是為了增加SQL共享遊標的共享機率，減少硬解析hard parse。

 

對Oracle來說，記憶體的快取cache技術是貫穿在整個體系框架中的。當一個新的SQL語句輸入時，要經歷語法、語義和許可權等一系列檢查，之後要進行parse過程。如果在SGA的Library Cache中沒有能找到，就會自己生成該SQL的執行計劃和共享遊標，這個過程要消耗SGA空間和CPU成本，同時還會帶來一定數量的library Cache Latch和Library Cache Pin。進行執行計劃生成之後，該SQL以shared cursor的形式快取在library cache中，等待再次被使用。這個過程被稱為hard parse，硬解析。

 

當library cache中存在該SQL的執行計劃時，另一次SQL呼叫輸入。如果新SQL與原來的SQL字面值和其他一些引數相同，就存在遊標共享的可能。這樣，新SQL不需要進行SGA空間分配和執行計劃生成，會使用原有的執行計劃。這個過程我們稱之為Soft Parse。

 

無論是Oracle自身的SQL和PL/SQL，還是Java/.NET的介面語言，都存在使用繫結變數的介面API。使用繫結變數可以增加SQL出現soft parse的機率，增加資料庫並行性。

 

最後，我們談一下繫結變數的適用環境。並不是什麼樣的場景使用繫結變數都是沒有問題的，在OLTP這類事務併發和事務密集型的系統中，使用繫結變數會提高系統整體併發能力。但是在OLAP和DSS類的系統中，一個SQL執行次數很少，但是執行時間很長，這樣場景下使用繫結變數的意義就不大。

 

此外，使用繫結變數存在出現bind peeking的可能性，這方面的效能抖動問題也不能忽視。

 

 

6、Prioritize your Use Cases for Tunning

 

最後說說效能最佳化。系統從業務需求分析、設計、開發到投產運維，效能分析最佳化是貫穿整個生命週期的。效能分析最佳化手段越是往前規劃，我們可以使用的最佳化選擇手段就越多，價效比就越好。傳統意義上的SQL調優，都是談在投產運維階段進行的DBA運維調優。在運維階段進行的手段很有限，而且收效往往很低。

 

在開發階段，我們進行最佳化的方式主要是SQL改寫和索引選擇。大多數效能最佳化手段都是需要付出額外的成本。比如索引，建立索引的確可以獲取很好的select效率，但是另一方面要付出update/insert/delete成本，而且索引本身也是要有空間佔用和管理成本。所以，我們追求的最佳化，實際上就是最優價效比的最佳化。

 

 

那麼，面對諸多的需求場景，我們如何選擇呢？筆者以為：所謂關鍵用例確定架構，關鍵用例同樣決定最佳化策略方向。我們面對的需求不可能是相同優先順序別的，對使用者而言，必然存在輕重緩急。我們開發系統的目標是實現使用者的願景，實現使用者目標的最大化。但是，使用者的目標實現是不可能完全實現。在“質量-工期-成本”三角形的控制下，必然有需求會被裁減。我們追求的目標就是將優先的最佳化資源分配給儘可能高優先順序別的用例需求中。

 

舉一個例子，兩個資料列都有加索引最佳化的需求，但是資源限制下只能加一個索引。一個用例是在介面上顯示系統引數，這個介面對應SQL如果是全表掃描，要多消耗5秒鐘。但是該用例很少有人用，每年只會開啟一次。另一個用例是每日的Daily Job，每天都會執行成百上千次的SQL。經過詳細分析，用例的優先順序別立刻可以看出來了，最佳化方案自然也就出來了。

 

 

7、結論

 

本系列集中介紹了開發階段我們需要關注的技術和原則，大部分的技巧是思路和指導原則，希望對讀者有所幫助。