InsightTechnology 旧ブログ

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

今回も検証生活～基礎編～です。
pga_aggregate_targetはインスタンス内で使用可能なPGAの総量を指定します。
では指定したサイズを1プロセスが消費し尽くすことができるのでしょうか？
それともリミッターのようなものはあるのでしょうか？
pga_aggregate_targetの値を動的に変更してみて、1プロセスがソート処理に
使用するメモリ領域の上限を検証してみます。

□環境

OS:Red Hat Linux release 7.1
Oracle:9.2.0.1

□自動PGA管理に設定

SQL> alter system set pga_aggregate_target=10M;

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

SQL> alter system set workarea_size_policy=auto;

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

□ソート処理を実行:pga_aggregate_target = 10M

SQL> set timing on
SQL> select distinct * from code where rownum<1000000;

経過: 00:01:45.43

▽sql作業領域の情報を見る

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 where rownum<1000000';

SQL_TEXT                                         OPERATION_TYPE
------------------------------------------------ ----------------
select distinct * from code where rownum<1000000 GROUP BY (SORT)

POLICY LAST_MEMORY_USED/1024/1024   LAST_EXECUTION LAST_TEMPSEG_SIZE
------ ---------------------------- -------------- -----------------
AUTO   .5                           61 PASSES      26214400

LAST＿MEMORY_USED列に注目してください。
pga_aggregate_target=10Mと設定していますが、10M全てを1プロセスにて消費
してしまうのではないようです。
以下、徐々にpga_aggregate_targetの値を大きくしていきます。

□pga_aggregate_target = 20Mに変更

SQL> alter system set pga_aggregate_target=20M;

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

SQL> select distinct * from code where rownum<1000000;

経過: 00:01:04.61

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 where rownum<1000000';

SQL_TEXT                                         OPERATION_TYPE
------------------------------------------------ ----------------
select distinct * from code where rownum<1000000 GROUP BY (SORT)

POLICY LAST_MEMORY_USED/1024/1024   LAST_EXECUTION LAST_TEMPSEG_SIZE
------ ---------------------------- -------------- -----------------
AUTO   1.09570313                   4 PASSES       25165824

□pga_aggregate_target = 30Mに変更

SQL> alter system set pga_aggregate_target=30M;

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

SQL> select distinct * from code where rownum<1000000;

経過: 00:00:58.25

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 where rownum<1000000';

SQL_TEXT                                         OPERATION_TYPE
------------------------------------------------ ----------------
select distinct * from code where rownum<1000000 GROUP BY (SORT)

POLICY LAST_MEMORY_USED/1024/1024   LAST_EXECUTION LAST_TEMPSEG_SIZE
------ ---------------------------- -------------- -----------------
AUTO   1.5                          2 PASSES       25165824

□pga_aggregate_target = 40Mに変更

SQL> alter system set pga_aggregate_target=40M;

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

SQL> select distinct * from code where rownum<1000000;

経過: 00:00:56.92

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 where rownum<1000000';

SQL_TEXT                                         OPERATION_TYPE
------------------------------------------------ ----------------
select distinct * from code where rownum<1000000 GROUP BY (SORT)

POLICY LAST_MEMORY_USED/1024/1024   LAST_EXECUTION LAST_TEMPSEG_SIZE
------ ---------------------------- -------------- -----------------
AUTO   2                            1 PASSES       25165824

□pga_aggregate_target = ほぼ最大値(4095G)に変更

SQL> alter system set pga_aggregate_target=4095G;

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

SQL> select distinct * from code where rownum<1000000;

経過: 00:00:51.81

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 where rownum<1000000';

SQL_TEXT                                         OPERATION_TYPE
------------------------------------------------ ----------------
select distinct * from code where rownum<1000000 GROUP BY (SORT)

POLICY LAST_MEMORY_USED/1024/1024   LAST_EXECUTION LAST_TEMPSEG_SIZE
------ ---------------------------- -------------- -----------------
AUTO   35.75                        OPTIMAL

□結論
pga_aggregate_targetの設定値を全て消費してしまうのではなく、
pga_aggregate_target * 5% を上限としてメモリを使用することができるよう
です。
この結果は以下のSQL文の結果からも推察できます。

SQL> select name,value/1024/1024 from v$pgastat
where name in ('aggregate PGA target parameter','global memory bound');

NAME                           VALUE/1024/1024
------------------------------ ------------------
aggregate PGA target parameter 10
global memory bound            .5

マニュアルを参照すると、global memory boundは -自動モードで実行される
作業領域の最大サイズ-と記述されています。
この値を見るとaggregate PGA target parameter:10(M) * 5% = 0.5(M)となっ
ています。

どうして、設定値分を1プロセスで消費できないのでしょうか？
それはオラクルの仕様だからと言ってしまえば簡単ですが、1プロセスが消費
し尽くしてしまうと、それ以外のプロセスはディスクソートとなってしまい、
全体としてみた時のスループットがダウンしてしまうからでしょう。

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

┏ QUESTION ┓
？★ここで問題ですっ！どうするのでしょう？★？
┗ ┛
正解者の中から抽選で当メルマガの本を差し上げます。
下記のアドレスまで奮ってご応募くださいっ！ (TI)

回答は次週！
今週はここまで。

以上、花粉警報発令中の茅ヶ崎にて