direct path read/write temp

実際の自動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_SIZE*1024=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_mem*80%)*20%
• DSS ： PGA_AGGREGATE_TARGET=(total_mem*80%)*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;