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

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

前回までで、自動PGA管理に設定した場合、1プロセスがソートなどの作業領域
として使用できるメモリ最大値はpga_aggregate_target * 5%であることを検
証しました。

では、20プロセス以上が同時にソート処理を行った場合にはPGA領域はどうな
るのか(pga_aggregate_target設定値内になるように自動調整されるのか)
検証してみます。

□環境

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

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

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

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

□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
---------- ------------------------------
        20                       18.78125

□同時に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
---------- ---------------------------
        30                  47.2087221

pga_used_mem列の総計に注目してください。
pga_aggregate_targetに設定した50MBの範囲内でPGAが使用されています。
pga_used_mem列がv$sql_workarea_active.ACTUAL_MEM_USED列の総計よりも
大きい理由は2つあります。

1.PGAにはソート領域だけではなく、セッション情報、カーソル状態、スタッ
  ク領域の各コンポーネントで構成されているため
2.オラクルのバックグラウンドプロセスが立ち上がっているため

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

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

299999行が選択されました。

経過: 00:01:37.05

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 * from code where rownum<300000 order by 1';

SQL_TEXT                                           OPERATION_TYPE
-------------------------------------------------- ----------------
select * from code where rownum<300000 order by 1  GROUP BY (SORT)

POLICY   LAST_MEMORY_USED/1024/1024   LAST_EXECUTION  LAST_TEMPSEG_SIZE
-------- ---------------------------- --------------- -------------------
AUTO     2.296875                     1 PASS          7340032

LAST_MEMORY_USED列からソート領域は約2.3MB、pga_aggregte_targetの約4.6%
が使用されたことがわかります。

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

次に同時ソート処理を倍の40プロセスに増やしてみます。

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

  COUNT(*) SUM(ACTUAL_MEM_USED)/1024/1024
---------- ------------------------------
        40                     8.51660156

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

  COUNT(*) SUM(PGA_USED_MEM)/1024/1024
---------- ---------------------------
        50                   37.687932

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

299999行が選択されました。

経過: 00:03:44.83
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 * from code where rownum<300000 order by 1';

SQL_TEXT                                           OPERATION_TYPE
-------------------------------------------------- ----------------
select * from code where rownum<300000 order by 1  GROUP BY (SORT)

POLICY   LAST_MEMORY_USED/1024/1024   LAST_EXECUTION  LAST_TEMPSEG_SIZE
-------- ---------------------------- --------------- -------------------
AUTO     .53125                       17 PASSES       8388608

LAST_MEMORY_USED列からソート領域は約544KB、pga_aggregte_targetの約1%ほ
どしか使用されていません。
LAST_EXECUTION列からもマルチパスが発生していることが確認され、SQL文の
実行時間も2.3倍以上を要しています。
1プロセスで使用されるソート作業領域が自動でチューニングされた(平等に割
り当てられた)結果です。

□■まとめ

自動PGA管理にしているからといって、データベース管理者はチューニングの
業務から開放されるわけではありません。
ソート処理がどれくらいのメモリを必要とするのか？
同時プロセス数はどれくらいなのか？
同時ソート処理はどれくらいなのか？
を把握し、初期化パラメタの設定値を適正な値へと導く作業が必要なことはな
んら変わりがないようです。

次回は更に同時ソート処理を増やした場合、自動PGA管理はどういった動作を
するのかを検証します。

以上、国際化がススム茅ヶ崎にて

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