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

<新・ソートに関する検証 その5>
ペンネーム グリーンペペ

今回は同時プロセス数100にして作業領域がpga_aggregate_target設定値内に
なるように自動調整されるのか検証してみます。

□■テストパターン3
同時ソート処理100プロセスでのテスト

□環境

OS:Red Hat Linux release 7.1
Oracle:9.2.0.1

□pga_aggregate_target=50MBでテスト

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

VALUE/1024/1024
---------------
             50

□弊社で開発されたオープンソースの開発言語”sqeel”で作成したプログラム
により同時に100セッションでソート処理を行う

SQL> select count(*) from v$session where program='sqeel.exe';

  COUNT(*)
----------
       100

□v$sql_workarea_active.actual_mem_used列からソート処理で使用されている
メモリ領域の総計を見る

SQL> select count(*),sum(ACTUAL_MEM_USED)/1024/1024
from v$sql_workarea_active;

  COUNT(*) SUM(ACTUAL_MEM_USED)/1024/1024
---------- ------------------------------
       100                     24.9804688

□同時にv$process.pga_used_mem列からPGAメモリ領域の総計を見る

SSQL> select count(*),sum(pga_used_mem)/1024/1024 from v$process;

  COUNT(*) SUM(PGA_USED_MEM)/1024/1024
---------- ---------------------------
       110                  73.0665865

pga_used_mem列の総計に注目してください。
pga_aggregate_targetに設定した50MBを超えてしまっています。

□PGA メモリー使用統計を見る

PGAメモリー使用統計ビューからもpga_aggregate_targetの設定値よりも多く
のPGAメモリーが使用されていることが確認できます。

SQL> select * from v$pgastat;

NAME                                                    VALUE UNIT
---------------------------------------- -------------------- ------------
aggregate PGA target parameter                       52428800 bytes
aggregate PGA auto target                             4194304 bytes
global memory bound                                    131072 bytes
total PGA inuse                                      89151488 bytes
total PGA allocated                                 164722688 bytes
maximum PGA allocated                               168338432 bytes
total freeable PGA memory                              327680 bytes
PGA memory freed back to OS                       27360231424 bytes
total PGA used for auto workareas                    30837760 bytes
maximum PGA used for auto workareas                  79933440 bytes
total PGA used for manual workareas                         0 bytes
maximum PGA used for manual workareas                       0 bytes
over allocation count                                   16461
bytes processed                                     963551232 bytes
extra bytes read/written                            316532736 bytes
cache hit percentage                                       75 percent

total PGA inuse行に注目してください。
現在約85MBのPGAメモリーが使用中であることが確認できます。
このようにpga_aggregate_targetの設定値が少なすぎるためにPGAメモリーが
過剰使用された累積回数がover allocation count行にて確認できます。

□SQL文の結果を見る
ソート実行したSQL文の結果を見てみます。

SQL> select * from code where rownum<300000 order by 1;

299999行が選択されました。

経過: 00:24:24.38

SQL> select sql_text,operation_type,policy,last_memory_used/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 * from code where rownum<300000 order by 1';

SQL_TEXT                                           OPERATION_TYPE 
-------------------------------------------------- ----------------
select * from code where rownum<300000 order by 1  SORT

POLICY   LAST_MEMORY_USED/1024   LAST_EXECUTION   LAST_TEMPSEG_SIZE
-------- ----------------------- ---------------- -------------------
AUTO     561                     108 PASSES       11534336

LAST_MEMORY_USED列からメモリ作業領域は 561KBが使用されています。
pga_aggregate_targetの約1%が使用されたことになります。
実行時間は20プロセスの時が1分37秒要していたので
(おら!オラ!Oracle -どっぷり検証生活- Vol.189参照)
約15倍を要しています。

□■まとめ
自動PGA管理にしているからといって、メモリスワップおよびページングが全
く発生しなくなるわけではありません。
プロセス数が多いとpga_aggregate_targetに指定した値だけでは足りずにPGA
メモリーが過剰使用される場合があることが今回の検証によって判明しました。

以上