【SQL 效能優化】引數設定

QL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production
SQL> show parameter optimizer_
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      10.2.0.4
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      CHOOSE
optimizer_secure_view_merging        boolean     TRUE
SQL>
---optimizer_features_enable
如果在升級資料庫後還想保持優化器在舊版本上的原有行為，可以設定optimizer_features_enable為舊版本號，不過建議儘快將應用程式調整為適合新版本資料庫。可以看看這個引數的取值：
SQL> alter session set optimizer_features_enable ='ddd';
ERROR:
ORA-00096: invalid value ddd for parameter optimizer_features_enable, must be from among
10.2.0.4.1, 10.2.0.4, 10.2.0.3, 10.2.0.2, 10.2.0.1, 10.1.0.5, 10.1.0.4, 10.1.0.3, 10.1.0,
9.2.0.8, 9.2.0, 9.0.1, 9.0.0, 8.1.7, 8.1.6, 8.1.5, 8.1.4, 8.1.3, 8.1.0, 8.0.7, 8.0.6,
8.0.5, 8.0.4, 8.0.3, 8.0.0
optimizer_features_enable 是動態的，可以在系統級和語句級修改。也可以在語句級使用提示修改

optimizer_features_enable(enable)
optimizer_features_enable('10.2.0.5')
注意：optimizer_features_enable 不僅能禁用新版本的特性也能禁用bug 修復。

--optimizer_mode
從這裡的提示可以看出優化模式的選擇值。
SQL> alter session set optimizer_mode ='dd';
ERROR:
ORA-00096: invalid value dd for parameter optimizer_mode, must be from among
first_rows_1000, first_rows_100, first_rows_10, first_rows_1, first_rows, all_rows,choose, rule
每個值的含義如下：
Rule:基於規則的方式,忽略CBO和統計資料並且完全基於基本資料字典資訊生成執行計劃。
Choose:允許ORACLE選擇最合適的優化器目標，預設的情況下Oracle用的便是這種方式。指的是當一個表或或索引有統計資訊，則走CBO的方式，如果表或索引沒統計資訊，那麼ORACLE將使用RULE模式
First Rows:基於成本的優化器模式，當一個表有統計資訊時，它將以最快的速度返回查詢的最先幾行，從總體上減少了響應時間，但是會造成總體查詢速度的下降或者是消耗更多的資源，通常會選擇索引掃描而不是全表掃描，所以這種模式最適用於線上系統，因為在這樣的系統中終端使用者希望以最快的速度看到一些結果
All Rows:基於成本的優化器模式，當一個表有統計資訊時，它將確保總體查詢時間最短，但是它可能在收到第一條記錄的操作上花費更長的時間。這種模式通常選擇全表掃描，所以這種模式最適用於批量查詢，沒有統計資訊則走RULE模式。
一般來講，
1 需要獲取所有記錄更重要，應該將引數設定為all_rows。比如報表系統和 OLAP，資料倉儲系統和快取資料的中間層元件中。
2 如果前幾行更重要或者對響應時間的要求非常高的系統，使用first_rows_n這裡的n可取(1，10，100，1000)。
  optimizer_mode 是動態的，可以在系統級和語句級設定。也可以使用提示(all_rows，first_rows(n))在語句級更改。

---optimizer_dynamic_sampling 動態取樣
優化器可以在語句解析階段進行資訊統計並動態收集。注意:動態資訊統計資訊既不儲存在資料字典也不在其他地方，真正重用它們的是共享遊標本身。該引數指定了動態取樣的的方式和時間。
1 如果optimizer_features_enable設定為10.0.0或者以上，預設為2，
2 9.2.0則為1，
3 9.0.1或者以下則為0，即禁用了動態取樣。
可以在系統級別和會話級別來修改這個引數。也可以通過提示dynamic_sampling()在語句級應用動態取樣。
1 設定為所有表設定取樣層級：dynamic_sampling(N)
2 為某個表指定取樣層級:dynamic_sampling(TNAME N)
層級  含義                                                                     資料塊的數量
0     禁止動態取樣                                                                0
1     對於沒有物件統計資訊的表在下面三種情況下使用動態取樣。                      32
      1 表沒有索引。2 是連線的一部分(包括子查詢和不可合併檢視)。3塊數多於取樣數。  
 
2      對於沒有物件統計資訊的表使用動態取樣。                                     64

3     對符合層級2標準的表已及已經使用了評估條件選擇性猜測的表使用動態取樣                             32

4     對符合層級3標準的表以及where子句中引用了兩個或更多欄位的表使用動態取樣。                  32
 
5     同等級4                                                                     64
6     同等級4                                                                     128
7     同等級4                                                                     256
8     同等級4                                                                     1024
9     同等級4                                                                     4096
10    同等級4                                                                      所有               

optimizer_index_caching
這個引數影響巢狀迴圈連線的探測索引的代價，0-100表示在使用巢狀迴圈或這in-list迭代時將索引快取在buffer cache的百分比。例如，設定為100，則優化器認為100%能在記憶體中找到索引資料，會按照這個設定來計算cost和選擇執行計劃。

optimizer_index_cost_adj

和optimizer_index_caching一樣，這個引數也是cbo用來計算cost的，這個引數可以用來調整使用索引的代價，預設值是100，範圍是1-10000，它表示索引掃描和全表掃描的比值。例如設定為10，意味著使用通過索引路徑訪問是正常通過索引路徑訪問的10%（oracle 10g performace tuning guide），也即可以設定索引參與計算代價的不同值。
optimizer_secure_view_merging
這個引數控制檢視合併，預設值是true，在不影響安全問題的情況下允許檢視合併，如果設定為false，則在任何情況下允許檢視合併。

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-673997/，如需轉載，請註明出處，否則將追究法律責任。