direct path read/write temp

概要

　ソート作業のため、一時領域を使っている場合にはdirect path read temp、direct path write tempイベントにて待機をします。このような待機イベントは10g以降に細分化されたイベントで、9iまではdirect path read、direct path write待機に観察されていました。ソートセグメントからのダイレクトI/Oは、ソートのために割り当てたPGAメモリーより大きい場合に発生します。従って、ソート作業時にdirect path read temp、direct path write temp待機が広範囲に現れる場合は、ソート作業用のメモリーを追加で割り当てることにより解消できます。

アプリケーションレイヤー

　ソートが必要なSQL文が最適なのかを調べてみます。必要以上のソート作業はCPU、PGA領域の無駄使い、過度なディスクI/Oを招きます。UNIONとUNION ALLの性能差でも分かるように不要なソート作業を減らすことだけでも多くの問題を解決することができます。

Oracleメモリーレイヤー

　PGAはソート、ハッシュジョイン、ビットマップ演算などを実行するためにメモリー領域を持ってます。
　8iまではSORT_AREA_SIZE、HASH_AREA_SIZE、BITMAP_MERGE_AREA_SIZE、CREATE_BITMAP_AREA_SIZEのパラメータを利用して管理者が直接に個別サイズを調整しました。ただし、9i以降に追加された自動PGAメモリー管理機能を活用すると、このような各メモリサイズを動的に管理することができます。当機能はPGAメモリーをセッションごとに適切に割り当てます。

　プロセスに割り当てられたメモリ以上のソート作業が、ディスク上で1回行われた場合、One pass sort、2回以上行われた場合は、Multi pass sortと言います。Multi pass sortが発生する場合は、一時領域へのソート作業によって、direct path read temp、direct path write temp待機が発生することになります。万一、該当の待機が広範囲に発生する場合は自動PGAメモリー管理の値（PGA_AGGREGATE_TARGET)を適切に調整して問題を解決できます。

　PGA_AGGREGATE_TARGETを設定するときの注意点として、サーバプロセスが実際に使用可能なメモリーに制限があることです。つまり、PGA_AGGREGATE_TARGETの一部分だけ個別のセッションに使われます。PGA_AGGREGATE_TARGETによって個別プロセスに割り当てれる最大のメモリー領域のサイズが算定され、その値は_SMM_MAX_SIZEに保存されます。(KB)

　 _SMM_PX_MAX_SIZEはパラレルクエリー実行時、全体のスレーブセッションが使用可能な最大のメモリーのサイズです。
　V$SESSTATビューで「session pga memory max」の値を参照するとセッションが実際に使った最大のメモリー領域を確認できます。

待機パラメータと待機時間

待機パラメータ

　direct path read/write temp待機イベントの待機パラメータは以下の通りです。

• P1 ： 絶対ファイル番号
• P2 ： 開始ブロック番号
• P3 ： ブロック数

待機時間

　I/O関連イベントのため、タイムアウトが発生しないのでI/Oが完了されるまでの所要時間を表します。

豆知識

自動PGA メモリー管理とダイレクト I/O

　実際の自動PGAメモリー管理を設定した場合、個別セッションのPGA値がどうやって指定されるか確認してみましょう。

SQL> alter system set pga_aggregate_target = 200M;

SQL> @show_param max_size
_smm_max_size (Kbyte単位)
 40960
 
_smm_px_max_size
102400

-- 索引を生成します。索引の生成時に内部のソート作業が発生します。
SQL> create index pdml_idx on pdml_test(id);

SQL> @sesstat
stat_name 値を入力してください。: 'session pga memory max'
==> 44920200

　_SMM_MAX_SIZE1024=41943040でsession pga memory max統計値とほとんど同一となることが分かります。たとえシステム全体のPGA作業空間が200Mでも、個別プロセスが実際に使用できるメモリーは40Mに制限します。もし、自動PGAメモリー管理をそのまま使いながら特定セッションに限り作業空間を大きくしたいなら、該当セッションのPGA作業空間だけを変更すればできます。つまり、alter session set workarea_size_policy=manualに変更した後、alter session set sort_area_size=…を利用して必要な大きさの値を与えるとできます。Oracleチューニングガイドでは以下のようにPGA_AGGREGATE_TARGETを設定することをお勧めしています。

• OLTP ： PGA_AGGREGATE_TARGET=(total_mem80%)20%
• DSS ： PGA_AGGREGATE_TARGET=(total_mem80%)*50%

　OSレベルで20%ほどのメモリーを使用すると仮定して、OLTPシステムの場合には余裕なメモリーの20%位、DSSシステムはソート作業が多いので50%位を使用しなさいという意味です。上の計算法が絶対的なことではありませんが、メモリー量の算定は基本政策で活用することができます。一つ注意することは不要に多量のメモリーを割り当てるのは正しくないということです。特に物理的メモリーのサイズを超過するようにPGAメモリーのサイズを割り当てるとページアウト/ページインが発生する確率が高くなります。ページング現象が頻繁に発生する場合は、システムの性能が大きく低下することもあります。

　自動PGAメモリー管理のパラメータ値を適切に変更する場合、ダイレクトI/O待機がどのぐらい減るのかテストしてみましょう。

-- PGA 全体のサイズを10Mに変更
SQL> alter system set pga_aggregate_target = 10M;
 
-- ソート作業の実行
SQL> create index pdml_idx on pdml_test(id);
Index created.
Elapsed: 00:00:40.07
 
SQL> @my_sess_event
EVENT					TOTAL_		WAITS TIME_WAITED
-----------------------------------  -----------  --------------------------
SQL*Net message from client		22		3251
direct path read temp			4593		7
log file sync				2		4
direct path write temp			684		1
SQL*Net message to client		23		0
direct path write			4		0
events in waitclass Other		1		0
 
-- PGA全体のサイズを1Gに大きくして再実行します。 '
SQL> alter system set PGA_AGGREGATE_TARGET = 1G;
SQL> create index pdml_idx on pdml_test(id);
Index created.
Elapsed: 00:00:28.11
 
SQL> @my_sess_event
-- PGA_AGGREGATE_TARGET 値を大きくした結果 ダイレクトI/Oが消えました。
EVENT					TOTAL_WAITS	TIME_WAITED
------------------------------------  -----------------  ----------------
SQL*Net message from client		20		8268
log file switch completion		4		33
log file sync				2		8
SQL*Net message to client		21		0
events in waitclass Other		1		0
direct path write			4		0

　PGA_AGGREGATE_TARGETを適切に設定される場合、ダイレクトI/Oが消えて、これによるdirect path read temp、direct path write temp待機現象が完全に消えることになります。さらに性能も大きく改善されることが確認できます。

分析事例

自動PGAメモリー管理の整合性分析

　問題分析期間の間、特定SQL文が同時に複数のセッションから実行される場合、次のようにdirect path read/writeイベントが多く発生しました。一般的にセッションはSQL文を実行しながらソート、ハッシュジョイン処理時にセッションのPGAメモリーを割り当てて処理するようになります。今回の事例の場合、DBのpga_aggregate_targetパラメータ値が20Mと小さく設定されており、セッション当たり1Mしか割り当らず大部分の作業がPGAメモリーを活用できなかったため、一時セグメントで行われダイレクトI/Oの発生頻度が高く出ました。

　適当なpga_aggregate_targetパラメータ値を割り当てるために、v$pga_target_adviceビューを活用します。現在、パラメータ値は20Mですが、この値ではPGAメモリーのヒット率は60%以下でかなり低調です。

PGA空間を増加させて問題状況を解決しました。(20M→2048M)

alter system set pga_aggregate_target = 2048m scope = both;

パラメータの変更順序

1．pga_aggregate_targetパラメータを2GB程度に設定します。
2．_pga_max_sizeの値は現在の設定値(200MB)のままにしておきます。
3．_smm_max_sizeの値は100MBに自動設定されます。

_pga_max_size (200 MB) > 5% of pga_aggregate_target (100 MB) の場合には 
  pga_aggregate_target 設定値の5%に自動設定されます。( 2048 MB * 0.05 ) = 100 MB

4．_smm_px_max_sizeの値が614MBに自動設定されます。

_ pga_max_size (200 MB) > 5% of pga_aggregate_target (100 MB)の場合には
  pga_aggregate_target 設定値の30%に自動設定されます。(2048 MB * 0.3 ) = 614 MB

5．パラメータの値がDBに正しく適用されたか確認します。

パラメータの確認スクリプト

–pga_aggregate_target設定の確認スクリプト

select sid,name,value,display_value 
from v$spparameter
where name like '%pga%';

–_smm_max_size、_smm_px_max_size、_pga_max_size設定の確認スクリプト

select a.ksppinm, b.ksppstvl, a.ksppdesc
from x$ksppi a, x$ksppsv b
where a.indx = b.indx
 and a.inst_id = b.inst_id
 and a.ksppinm in ( '_smm_max_size','_smm_px_max_size','_pga_max_size')
order by ksppinm;