pga_aggregate_targetでPGA？、_pga_max_sizeでPGA？ #3 Tweet

2010/11/25追記
実は_pga_max_sizeの扱いがOracle10g R2以降ひっそりと変更されていた！という事実。Season2という後付けのタイトルで後日談を書いておきましたので次いでに見ておくとちょっとは幸せになるかも…
pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? Season2 #1

pga_aggregate_targetでPGA？、_pga_max_sizeでPGA？ その３回目、

まずは、pga_aggregate_targetパラメータを変更し、PGAがどのように利用されるかみていく。10MBからはじめ、徐々に増加させてみる。
（なお、Oracleのバックグラウンドプロセス以外のセッションは以後のテストで接続している１セッションのみ。同時に複数のセッションがあると自動PGAメモリー管理が働きすぎて困るのでw）

SYS>
SYS> alter system set pga_aggregate_target=10m;

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

SYS> show parameter pga

NAME				     TYPE			       VALUE
------------------------------------ --------------------------------- ------------------------------
_pga_max_size			     big integer		       200M
pga_aggregate_target		     big integer		       10M

SYS> 
SYS> @pgastat

NAME						     size(MB)
-------------------------------------------------- ----------
aggregate PGA target parameter				   10
aggregate PGA auto target				    4
global memory bound					   .5

SYS> 

前述の情報を見ただけでもわかる方はもう気づいたと思いますが、実際にソート処理を行わせてPGAのサイズなどもみておきますね。（その方が面白いしw)

SCOTT> --test #1 _pga_max_size=200m(default) / pga_aggregate_target=10m
SCOTT> @test2

SQL_TEXT				 OPERATION_TYPE       last_mem(MB) LAST_TEMPSEG_SIZE MAX_TEMPSEG_SIZE
---------------------------------------- -------------------- ------------ ----------------- ----------------
select * from code where rownum <= 10000 SORT			       .44	   409993216	    409993216
0 order by code

SCOTT> 

前述の結果から見ると、ソート処理は、0.44MBのPGAを利用していることが確認でき、そのサイズは、global memory bound以下のサイズであり、global memory boundは、pga_aggregate_targetパラメータで指定したサイズ（10MB）の5%である0.5MBであることがわかる。

では次ぎに、20MBに増加させて試してみると…

SYS> alter system flush shared_pool;

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

SYS> alter system set pga_aggregate_target=20m;

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

SYS> show parameter pga

NAME				     TYPE			       VALUE
------------------------------------ --------------------------------- ------------------------------
_pga_max_size			     big integer		       200M
pga_aggregate_target		     big integer		       20M

SYS> 
SYS> @pgastat

NAME						     size(MB)
-------------------------------------------------- ----------
aggregate PGA target parameter				   20
aggregate PGA auto target			   4.36816406
global memory bound					    1

・・・中略・・・

SCOTT> 
SCOTT> -- test #2 _pga_max_size=200m(default) / pga_aggregate_target=20m
SCOTT> @test2

SQL_TEXT				 OPERATION_TYPE       last_mem(MB) LAST_TEMPSEG_SIZE MAX_TEMPSEG_SIZE
---------------------------------------- -------------------- ------------ ----------------- ----------------
select * from code where rownum <= 10000 SORT			       .97	   409993216	    409993216
0 order by code

SCOTT> 

pga_aggregate_targetパラメータが10MBの場合と同様に、pga_aggregate_targetの5%がglobal memory boundのサイズとして調整され、ソート処理を行わせてもその範囲以下を上限としてメモリソート＋DISKソートが行われていることがわかる。

Oracle10g R1は、Oracle9i R2のまでと同じ割当方法だよね〜。

ここまでに利用したスクリプトは以下。（今回使った分ね。）
・test2.sql

set term off
select * from code where rownum <= 100000 order by code
/
set term on
@show_workarea2

・show_workarea2.sql

col sql_text for a40
col operation_type for a20
select
  sql_text
 ,operation_type
 ,round(last_memory_used/1024/1024,2) as "last_mem(MB)"
 ,last_tempseg_size
 ,max_tempseg_size
from 
  v$sql vs join v$sql_workarea vsw
  on vs.sql_id = vsw.sql_id
where
  sql_text='select * from code where rownum <= 100000 order by code'
/

ということで、次回へつづく。 :)

---

前回までのあらすじ・・・

・pga_aggregate_targetでPGA？、_pga_max_sizeでPGA？ #1
・pga_aggregate_targetでPGA？、_pga_max_sizeでPGA？ #2