新・ソートに関する検証　その１

＜新・ソートに関する検証　その１＞
ペンネーム　グリーンペペ

今回からソート処理に関する検証を行います。
9iからソート処理に関わる新しい機能が用意されました。
自動PGA管理です。

□自動PGA管理って何？
8iまでは、SORT_AREA_SIZEやHASH_AREA_SIZEなどの初期化パラメータでセッシ
ョン単位のメモリ使用量の上限を設定していました。
9iより以下の初期化パラメータにより、インスタンス単位でプロセスが使用す
るSQL作業領域であるPGA(プログラム・グローバル・エリア)メモリの使用量の
上限を設定できます。

PGA_AGGREGATE_TARGET
インスタンスで使用可能なPGAメモリの総使用量を指定。
インスタンスレベルでの動的変更可能。
設定できる値の範囲は10M～4096G-1バイト。

WORKAREA_SIZE_POLICY
SQL作業領域のサイズを自動チューニングするか、手動チューニングするかを指定。
AUTO：自動
MANUAL：手動

WORKAREA_SIZE_POLICYがMANUALの場合は、従来どおりSORT_AREA_SIZE、
HASH_AREA_SIZEなどのXXX_AREA_SIZEパラメータの値が適用されます。また、値が
AUTOの場合は自動チューニングが有効になり、XXX_AREA_SIZEパラメータの値は無視
されます。PGA_AGGREGATE_TARGETの値が設定されているときのWORKAREA_SIZE_POLICY
のデフォルトはAUTOになります。

□PGA_AGGREGATE_TARGET値の目安
一般的に以下の計算式を目安とします。
OLTP系システムの場合は、
PGA_AGGREGATE_TARGET=(実メモリの総計*80%)*20%
DSS系システムの場合は、
PGA_AGGREGATE_TARGET=(実メモリの総計*80%)*50%

DSS系の係数の方が大きいのはORDER BY,GROUP BYなどソートを伴った集計処理
が多いことを考慮しています。

SQL作業領域は、以下の3つに分けられます。

optimal   : ソートなどの処理が全てメモリ上で行なわれた場合のSQL作業領域
onepass   : 全ての処理をメモリ上で行なうことができず、最小限のディスク・
            アクセスが発生した場合のSQL作業領域
multipass : 使用できるSQL作業領域非常に小さく、多くの処理でディスク・ア
            クセスが発生した場合のSQL作業領域

そして、チューニングの目標値の目安は以下となります。
optimalの割合 >= 90%
multipassの割合 = 0%

ほとんどの処理がoptimalで実行され、onepassでの実行を少なくすることが目
標となります。

以下のv$sysstatを使用したSQL文より、PGAの使用状況をモニターできます。

select name
,value
,100*(value/
decode((select sum(value) from v$sysstat where name like 'workarea
exec%'),0,null,(select sum(value) from v$sysstat where name like 'workarea
exec%'))) pct
from v$sysstat
where name like 'workarea exec%'
/

NAME                             VALUE        PCT
----------------------------------------------------------------------
workarea executions - optimal    1529         100
workarea executions - onepass       0           0
workarea executions - multipass     0           0

multipassの割合が0%でない場合、またはonepassの割合が10%を超える場合に
は、PGA_AGGREGATE_TARGETの値を増やすことを検討してください。
逆にoptimalの割合が 100% の場合にはPGA_AGGREGATE_TARGETの値を減らして
も問題なさそうです。

今週はここまで。

雨まじり一転の茅ヶ崎にて

新・ソートに関する検証 その１