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

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

□さて、まずは前回のオラクルクイズの回答から、

Question:
夜間バッチに大きなソート処理が実行されている。
夜間帯は通常ほとんどバッチプロセス以外は実行されない。
pga_aggregate_targetの5%しか1プロセスにソートエリアがメモリに割り当て
られないのでは、ディスクソートとなってしまい処理の遅延が発生してしまう。
pga_aggregate_targetを大きくするほどメモリに余裕はない。
なんとかならないのか？

Answer:
大量ソートのバッチトランザクションには以下の呪文をつけて下さい。

alter session set workarea_size_policy = MANUAL;
alter session set sort_area_size = バイト数;

これでトランザクション別に任意に大きなソートサイズを割り当てることがで
きます。簡単でしたか？
正解者の方には弊社の本をプレゼントいたします。

□では少しおさらいです。
前回の検証生活では以下のようにpga_aggregate_targetを変更し、1プロセス
に割り当てられるメモリ領域はpga_aggregate_targetの5%であることを検証し
ました。。

□環境

OS:Red Hat Linux release 7.1
Oracle:9.2.0.1

SQL> select name,value/1024/1024/1024 from v$parameter where name='pga_aggregate_target';

NAME                                VALUE/1024/1024/1024
----------------------------------- --------------------
pga_aggregate_target                                4095

SQL> select distinct * from code;

5422055行が選択されました。

経過: 00:08:49.21

SQL> select sql_text,operation_type,policy,last_memory_used/1024/1024,
last_execution,last_tempseg_size from v$sql l,v$sql_workarea a
where l.hash_value=a.hash_value
and sql_text='select distinct * from code'

SQL_TEXT                     OPERATION_TYPE    POLICY
---------------------------- ----------------- --------
select distinct * from code  GROUP BY (SORT)   AUTO

LAST_MEMORY_USED/1024/1024   LAST_EXECUTION   LAST_TEMPSEG_SIZE
---------------------------- ---------------- -------------------
87.265625                    1 PASS           132120576

おかしいです!!

前回の検証ではpga_aggregate_targetの5%が、１プロセスに割り当てられるメ
モリソート領域であることを全国１万人の読者にお伝えしたばかりなのに。。。
pga_aggregate_taget=4095G * 5% = 204.75G なのですが、
SQL文の結果を見ると(last_memory_used列)約87MBしかメモリが割り当てられ
てません。
last_execution列を見ると1 PASS となっているのでディスクソートが発生し
ています。

このままでは嘘つきになってしまいます。。。

これはOracle内部的に別の閾値があるに違いないです。
ということで、隠しパラメータを見てみましょう。

SQL> select i.ksppinm name , v.ksppstvl value
     from x$ksppi i, x$ksppcv v
     where i.indx = v.indx
     and i.ksppinm like '_pga%';

NAME                 VALUE
-------------------- --------------------
_pga_max_size        209715200

むむ!!
この_pga_max_sizeパラメータってのが怪しそうです。
とりあえず10倍くらいに変更してみます。

SQL> alter system set "_pga_max_size"=2G;

システムが変更されました。

再び同じSQL文を実行してみます。

SQL> select distinct * from code;

5422055行が選択されました。

経過: 00:07:22.42

SQL> select sql_text,operation_type,policy,last_memory_used/1024/1024,
last_execution,last_tempseg_size from v$sql l,v$sql_workarea a
where l.hash_value=a.hash_value
and sql_text='select distinct * from code';

SQL_TEXT                     OPERATION_TYPE    POLICY
---------------------------- ----------------- --------
select distinct * from code  GROUP BY (SORT)   AUTO

LAST_MEMORY_USED/1024/1024   LAST_EXECUTION   LAST_TEMPSEG_SIZE
---------------------------- ---------------- -------------------
189.33593                    OPTIMAL

今回は_pga_max_size=209715200の時よりも多くメモリが割り当てられたため
(LAST_MEMORY_USED=約189MB)、メモリ内でソートが完了したようです。
(last_execution=OPTIMAL)

それでは次に_pga_max_sizeを小さく(100MB)してみます。

SQL> alter system set "_pga_max_size"=100M;

システムが変更されました。

SQL> select distinct * from code;

5422055行が選択されました。

経過: 00:08:07.72

SQL> select sql_text,operation_type,policy,last_memory_used/1024/1024,
last_execution,last_tempseg_size from v$sql l,v$sql_workarea a
where l.hash_value=a.hash_value
and sql_text='select distinct * from code';

SQL_TEXT                     OPERATION_TYPE    POLICY
---------------------------- ----------------- --------
select distinct * from code  GROUP BY (SORT)   AUTO

LAST_MEMORY_USED/1024/1024   LAST_EXECUTION   LAST_TEMPSEG_SIZE
---------------------------- ---------------- -------------------
49.2421875                   1 PASS           132120576

LAST_MEMORY_USED=約49MBとなりました。_pga_max_size(100MB)の約半分です。

□まとめ
自動PGA管理の場合、１プロセスが使用できるメモリーソート領域は
pga_aggregate_targetの5%、もしくは隠しパラメタ_pga_max_sizeの半分が上
限である。

今週はここまで。

以上、春真近の茅ヶ崎にて

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