DB2中SORTHEAP、SHEAPTHRES_SHR和SHEAPTHRES引數的用法(轉自飛天)

在DB2實踐中，我們經常發現很多使用者對排序的幾個引數使用不當，造成效能的嚴重下降。本文詳細介紹這幾個引數的使用原理，並介紹在新版本中v9.1的一些變化，希望對大家有所幫助。

1） DB2 v8

當 需要對資料行進行順序讀取(order)時，比如order by\group by\distinct等，如果沒有索引滿足要求，或者最佳化器認為索引的代價更高時，就需要掃描資料表並進行排序。DB2裡，存在兩種型別的排序：私有排 序（private sort）和共享排序(shared sort)。我們知道，DB2的記憶體模型包括：例項級共享記憶體、資料庫級共享記憶體、應用共享記憶體和代理記憶體。其中，私有排序就發生在代理記憶體（agent private memory)；而共享排序發生在資料庫共享記憶體。

對於私有排序，例項配置引數sheapthres指定了某個給定時間可為所有私有排序分配的軟記憶體限制(soft limit)。當為私有排序分配的記憶體達到了此限制，新請求的私有排序的記憶體大小分配將會減少。同時，在db2diag.log日誌裡會給出提示：

當 設定了intra-partition分割槽內並行或者connection contentrator(連線集中器，當MAX_CONNECTIONS>MAX_COORDAGENTS時)時，DB2可能選擇會共享排序。如 果執行共享排序，那麼排序發生在資料庫共享記憶體，可供分配的共享排序大小由sheapthres_shr資料庫引數來控制，這個值是硬限制(hard limit)。當達到此限制後，請求排序的新的應用會收到SQL0955(reason code 2)錯誤，只有當消耗的共享排序值低於此限制後才允許新應用請求共享排序。對於共享排序，當資料庫啟用或第一次連線時就會分配sheapthres_shr引數指定的資料庫共享記憶體。

當發現排序次數太多或者有排序溢位的時候，需要檢查是否在表上建立了合適的索引。通常大的表掃描會導致的大量的排序，嚴重影響效能。

intra-partition parallelism：指的是在一個單分割槽資料庫中把一個操作分成多個部分，然後同時執行。比較典型的有index creation,complex sql query,load等，透過enable intra-partition，可以充分利用SMP的多處理器的處理能力。

2）DB2 v9.1的變化

在DB2 v9.1中，sheapthres_shr引數是一個軟限制。需要時，排序記憶體堆可使用其他未使用的資料庫共享記憶體。當例項級引數sheapthres設 置為0時，僅適用資料庫共享記憶體進行共享排序，排序記憶體分配受sheapthres_shr配置引數值的約束。

而且，僅當資料庫管理器配置引數 sheapthres 設定為 0 時，才允許自動調整 sheapthres_shr和sortheap。在DB2 V8 中，無論如何設定 sheapthres 引數值，只有 SMP 環境中(intra_partition=yes)的排序或執行可由多個代理程式訪問的集中器的排序才會消耗共享記憶體。在 DB2 V9.1 中，如果將sheapthres資料庫管理器配置引數設定為 0，則所有排序都將使用共享記憶體。如果將 sheapthres 資料庫管理器配置引數設定為大於 0 的值，則只有 SMP 環境中的排序或執行可由多個代理程式訪問的集中器的排序將會消耗共享記憶體，其餘進行私有排序。

3）使用建議

- 透過get snapshot for sorts on 進行監控鎖的使用 （需先開啟開關 update monitor switches using sort on)

- sheapthres的值依賴於sortheap的大小和應用的併發執行數，應用的併發執行數可以透過get snapshot for database來監控。考慮到單個應用需要多次排序，sheapthres可設為：sortheap*併發數*2

- 減少排序次數，建立合適的索引

- 需要對大表進行排序時，適當增大sortheap和sheapthres引數值

- 對於v9.1，建議用預設值。（dbm cfg: sheapthres=0 ; db cfg: sheapthres_shr=automatic, sortheap=automatic