【調優篇基本原理】優化器相關引數配置

看看影響優化器的一些引數(本例的資料庫為dedicated server)：

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 ALL_ROWS

optimizer_secure_view_merging boolean TRUE

optimizer_mode是優化器模式，10g預設是all_rows，也可以設定為其他引數：

SQL> alter session set optimizer_mode = 'asd';

ERROR:

ORA-00096: invalid value asd 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

從這裡的提示可以看出優化模式的選擇值。一般來講，需要獲取所有記錄更重要，應該吧引數設定為all_rows，很多應用都是這麼設定的，包括olap和oltp，一些對相應時間的要求非常高的系統，可能會修改這個值，使用first_rows_n這裡的n根據具體應用來選擇。

optimizer_dynamic_sampling

這個值的設定範圍是0-10，如果optimizer_features_enable設定為10.0.0或者以上，預設為2，9.2.0則為1，9.0.1或者以下則為0，可以在系統級別和會話級別來修改這個引數。

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，則在任何情況下允許檢視合併。

SQL> show parameter cursor_sharing

NAME TYPE VALUE

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

cursor_sharing string EXACT

SQL>

cursor_sharing

這個引數用來設定sql如何來使用繫結變數，有三個值：SIMILAR, EXACT, FORCE，預設是EXACT，一般來說不會去更改這個設定，即使shared pool hit miss較大，也應該首先去思考是否需要優化應用。這個引數的測試見：

http://space.itpub.net/16179598/viewspace-627268

SQL> show parameters pga_aggregate_target;

NAME TYPE VALUE

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

pga_aggregate_target big integer 187M

SQL>

10g的pga_aggregate_target預設設定為sga_target 的1/3，這個引數指定了pga能達到的期望值，我們知道pga主要用來為連線的使用者儲存會話的相關內容，其中比較重要的是pga用來做排序操作，例如一些基於排序的操作：order by ,group by ,rollup和開窗函式(window functions)，hash-join，bitmap merge，bitmap create，還有一些使用bulk或者load來寫buffer的操作等。

有兩個檢視來幫助調整這個引數：v$pga_target_advice和v$pga_target_advice_histogram

通過檢視檢視v$pgastat可以看到pga的一些屬性或者狀態值。

Oracle推薦的pga_aggregate_target：

Oltp：（系統實體記憶體大小*80）*20%

Dss：（系統實體記憶體大小*80）*50%

下面來看看有關pga的一些專用區域大小的具體設定：

SQL> show parameter area_size

NAME TYPE VALUE

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

bitmap_merge_area_size integer 1048576

create_bitmap_area_size integer 8388608

hash_area_size integer 131072

sort_area_size integer 65536

workarea_size_policy string AUTO

SQL>

workarea_size_policy

對於10g來說，這個引數預設為AUTO，則必須要設定pga_aggregate_target的值，且必須至少大於10M，如果設定為0，即讓資料自動調整的話，會出現下列錯誤：

SQL> alter system set pga_aggregate_target = 0 scope=both;

alter system set pga_aggregate_target = 0 scope=both

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-00093: pga_aggregate_target must be between 10M and 4096G-1

sort_area_size

排序區大小，設定oracle一次排序使用的最大記憶體數量，這個引數設定對排序效能比較重要。

SQL> select name,value

2 from v$sysstat a where a.NAME

3 like '%sort%';

NAME VALUE

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

sorts (memory) 1019434

sorts (disk) 2

sorts (rows) 161842945

可以檢視磁碟排序和記憶體排序的比率來考慮是否設定了較為合理的排序區大小。

hash_area_size

這個引數設定單個會話的hash記憶體空間大小，可以在例項和會話級修改，預設值是sort_area_size的兩倍，上面的數值也可以看出來。對於10g來說dedicated server的hash area是從pga中分配的，而multi-threaded server的hash area是從uga中分配的，如果設定了PGA_AGGRATE_TARGET引數，則將自動管理hsah area大小。

create_bitmap_area_size

指定為建立點陣圖索引而分配的的記憶體量，10g預設是8m

bitmap_merge_area_size

bitmap合併區大小，在使用bitmap index時，pga中會用一塊區域來進行點陣圖索引排序和與點陣圖合併，預設的大小是1M

workarea_size_policy

10g 預設值是AUTO，意味著使用pga_aggregate_target來管理PGA記憶體，同時*_AREA_SIZE的設定全部失效。

另外如果是shared server那麼會話記憶體是共享的，永久區在sga中，而dml/ddl和dedicated server一樣都在pga中。

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

【調優篇基本原理】優化器相關引數配置

相關文章