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

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

今回は実践編です。
実例を元とした検証を行います。

□背景

あるシステムにおいて、同一処理ロジックを行っているにも関わらず、ある特
定のクライアントのみ、実行時間が倍以上かかってしまっています。
調査を進めていくとソート処理を行っているSQL文が悪さをしているらしいこ
とが判明しました。
早速、ソート関連の設定を確認してみます。

□環境

OS:Red Hat Linux release 7.1
Oracle:9.2.0.1

□問題点の切り分け　その１□
＠初期化パラメタの確認

SQL> select name,value from v$parameter
     where name in ('workarea_size_policy','pga_aggregate_target')
     or name like 'sort%';

NAME                      VALUE
------------------------- -------------------------
sort_area_size            65536
sort_area_retained_size   65536
pga_aggregate_target      4294967296000
workarea_size_policy      AUTO

自動PGA管理の設定がされています。
PGAターゲットメモリも充分な大きさが設定されています。
次に実際にパフォーマンスが遅いクライアントでソート処理を実行してみて
比較してみます。

□問題点の切り分け　その２□
＠セッション間でのソート処理性能比較

セッションＡ：

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

経過: 00:03:210.22

セッションＢ：

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

経過: 00:01:42.88

セッションＡがパフォーマンスが悪いクライアントから実行した結果です。
同一SQL文なのにも関わらすセッションＢと比較すると倍近く実行時間を要し
ています。セッション情報も確認してみます。

□問題点の切り分け　その３□
＠セッション情報の確認

※関連する重要な情報のみ取得しています。

SQL> select sid,serial#,server from v$session
     where username = 'XPRT';

       SID    SERIAL# SERVER
---------- ---------- ------------------
        15        640 SHARED     <= セッションＡ
        12        706 DEDICATED  <= セッションＢ

server列に注目してください。
セッションＡはSHARED、つまり共有サーバー(MTS)接続になっています。
一方、セッションＢはDEDICATED、専用サーバー接続になっています。

共有サーバー接続の場合のソートはＰＧＡではなく、ＳＧＡを使用して実行さ
れます。

共有サーバー(MTS)接続と専用サーバー接続の詳細は
こちらのバックナンバーをご覧下さい。
↓↓
https://old.insight-tec.com/mailmagazine/ora3/vol037.html

共有サーバー接続はCPUやメモリなどのシステムリソースが足りない場合に専
用サーバー接続の場合にセッション別に立ち上がるサーバープロセスを共有し
ましょうというアーキテクチャーです。
システムリソースを節約するのが目的ですのでセッション別にソート作業領域
をＰＧＡメモリに割り当てるのではなく、共有プールやラージプールといった
事前に割り当てられたメモリ領域(ＳＧＡ)上にてソート処理が行われます。

ということは共有サーバー接続の場合は、自動ＰＧＡ管理の設定値
(pga_aggregate_target)でもってメモリ割り当てが行われるのではなく、
sort_area_sizeの設定値がＳＧＡ上に割り当てられるのです。
これは、v$sql_workareaを参照することで確認できます。

□問題点の切り分け　その４□
＠v$sql_workarea(sqlによって使用される作業領域情報ビュー)の確認

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 like 'select * from code where rownum<2000000 order by 1%';

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

POLICY   LAST_MEMORY_USED/1024   LAST_EXECUTION   LAST_TEMPSEG_SIZE
-------- ----------------------- ---------------- -------------------
MANUAL   73                      462 PASSES       70254592   <= セッションＡ
AUTO     73608                   OPTIMAL                     <= セッションＢ

policy列を見るとセッションＡは MANUAL になっており、自動PGA管理が適用
されずsort_area_sizeの値が適用されたことがわかります。
sort_area_sizeは 65536 にしか設定されていないのでマルチパズが発生し、
ディスクソートが行われたことが分かります。

今回のトラブル例ではCPU、メモリなどのシステムリソースは充分に余裕があ
りましたので、全ての接続方式を専用サーバー接続になるように初期化パラメ
タを変更し、自動PGA管理の設定値が適用されるように対応しました。

今週はここまで！！

春よコイ！茅ヶ崎にて。

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