pga_aggregate_targetでPGA？、_pga_max_sizeでPGA？ #5 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？ その５回目、

Oracle10g R1 (32bit)で、pga_aggregate_targetを40MBから一気に上限に近い4095GBに増加させたところで、global memory boundへの割当割合が変化した。

今回は、pga_aggregate_targetを1GBから16GBそして、後半では、_pga_max_sizeも増加させてどう変化するか確認してみることに。

_pga_max_sizeパラメータは隠しパラメータなのでOracle Supportの指示なしには変更しないパラメータ、遊ぶ場合は、趣味の環境等でやってね :)

あ、ところで、なんでPGAネタを始めたかって？　知りたいですか？　

実は、先日、インフラ担当（Oracleにはかなり詳しいと思われる）の方と話していて、pga_aggregate_targetの話題になったとき、その方が、「そーゆー、都市伝説みたいな話があるよね〜、キッパリ！」、みたいなノリで話していたので、私：「そんな事ないよー、都市伝説みたいじゃなくて都市伝説だよ（ウソウソ）、ほんととうにそう動いてるよ〜」、という話をしていて、どーしても書かなきゃ！、という気持ちになったのでした〜。それ以外に、第一回目でもちょっと書きましたが、10g r2ではちょいと変わったとか...いう話もあったし。（マニュアルにも書いてないし、Oracleに完全お任せでいいかというと、性能問題に絡んでくるちょいといやらしいとこなので）

余談はこのくらいにして、実験再開。

実際にソート処理をさせなくても、いままでの結果から、global memory boundの値が自動PGAメモリー管理では重要な意味があることは確認できたと思うので、毎回ソート処理を行わないで進めます。（適当な所で実際にソートしてみます！）

Oracle10g R1も9iと同様に、MIN(pga_aggregate_target * 0.05, _pga_max_size * 0.5)という条件で割り当てているのはほぼ確実なので、_pga_max_size=200MB (デフォルト）のままで、pga_aggregate_targetを1GB/2GB/4GB/8GB/16GBと増加させた場合、_pga_max_size * 0.5 = 100MBを下回るのは、pga_aggregate_targetに1GBを指定した場合のみなので、Oracleが気まぐれにglobal memory boundを設定しているのでなければ、global memory boundは、51.2BM/100MB/100MB/100MB/100MBという変化になるはず！

---

2010/5/8追記：
MIN(pga_aggregate_target * 0.05, _pga_max_size * 0.5)という表現よりSQLなら
LEAST(pga_aggregate_target * 0.05, _pga_max_size * 0.5)こっちの方がいいな次ぎからLEASTにする。

---

ということを脳のcacheにKEEPした上で、以下をご覧ください。

SYS> alter system set pga_aggregate_target=1g;

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

SYS> show parameter pga

NAME				     TYPE			       VALUE
------------------------------------ --------------------------------- ------------------------------
_pga_max_size			     big integer		       200M
pga_aggregate_target		     big integer		       1G

SYS> 
SYS> @pgastat

NAME						     size(MB)
-------------------------------------------------- ----------
aggregate PGA target parameter				 1024
aggregate PGA auto target			     908.4375
global memory bound				   51.1992188

SYS> 
SYS> 
SYS> alter system set pga_aggregate_target=2g;

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

SYS> 
SYS> show parameter pga

NAME				     TYPE			       VALUE
------------------------------------ --------------------------------- ------------------------------
_pga_max_size			     big integer		       200M
pga_aggregate_target		     big integer		       2G
SYS> 
SYS> @pgastat

NAME						     size(MB)
-------------------------------------------------- ----------
aggregate PGA target parameter				 2048
aggregate PGA auto target			    1830.2168
global memory bound					  100

SYS> 
SYS> alter system set pga_aggregate_target=4g;

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

SYS> show parameter pga

NAME				     TYPE			       VALUE
------------------------------------ --------------------------------- ------------------------------
_pga_max_size			     big integer		       200M
pga_aggregate_target		     big integer		       4G
SYS> 
SYS> @pgastat

NAME						     size(MB)
-------------------------------------------------- ----------
aggregate PGA target parameter				 4096
aggregate PGA auto target			   3673.33594
global memory bound					  100

SYS> 
SYS> alter system set pga_aggregate_target=8g;

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

SYS> show parameter pga

NAME				     TYPE			       VALUE
------------------------------------ --------------------------------- ------------------------------
_pga_max_size			     big integer		       200M
pga_aggregate_target		     big integer		       8G
SYS> 
SYS> @pgastat

NAME						     size(MB)
-------------------------------------------------- ----------
aggregate PGA target parameter				 8192
aggregate PGA auto target			   7359.69727
global memory bound					  100

SYS> 
SYS> alter system set pga_aggregate_target=16g;

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

SYS> show parameter pga

NAME				     TYPE			       VALUE
------------------------------------ --------------------------------- ------------------------------
_pga_max_size			     big integer		       200M
pga_aggregate_target		     big integer		       16G
SYS> 
SYS> @pgastat

NAME						     size(MB)
-------------------------------------------------- ----------
aggregate PGA target parameter				16384
aggregate PGA auto target			   14732.2793
global memory bound					  100

SYS> 
SYS> 

・・・中略・・・

SCOTT> @test2

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

やった〜！、予想通り！！！　:)

さらに念押し、pga_aggregate_target * 5%が　_pga_max_size * 50%　より小さくなるようにして、さらに変化を見る。

16GB * 5% = 819.2MB なので 820MB * 2 = 1640MB を _pga_max_sizeパラメータに設定すればいろいろと見えるよね。たぶん。

MacOS X版Oracle10g R1だけかもしれないけど？（未確認なので）、動的に変わるはず（と思っている）_pga_max_sizeがどうも静的なので、Oracle10g R1では　_pga_max_sizeを変更した場合は必ず、インスタンスを再起動しています。 (^^;;;;

_pga_max_sizeを1640MBに設定後、pga_aggregate_targetを1GB/2GB/4GB/8GB/16GBと増加させます。Oracleが気まぐれでglobal memory boundを設定していなければ、global memory boundの値は、51.2MB/102.4MB/204.8MB/409.6MB/819.2MBという変化を見せてくれるはず。

SYS> alter system set "_pga_max_size"=1640m;

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

SYS> alter system set pga_aggregate_target=1g;

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

SYS> shutdown
データベースがクローズされました。
データベースがディスマウントされました。
ORACLEインスタンスがシャットダウンされました。
SYS> startup
ORACLEインスタンスが起動しました。

Total System Global Area  293601280 bytes
Fixed Size		     778888 bytes
Variable Size		  116137336 bytes
Database Buffers	  176160768 bytes
Redo Buffers		     524288 bytes
データベースがマウントされました。
データベースがオープンされました。
SYS> 
SYS> show parameter pga

NAME				     TYPE			       VALUE
------------------------------------ --------------------------------- ------------------------------
_pga_max_size			     big integer		       1640M
pga_aggregate_target		     big integer		       1G
SYS> 
SYS> @pgastat

NAME						     size(MB)
-------------------------------------------------- ----------
aggregate PGA target parameter				 1024
aggregate PGA auto target			   907.892578
global memory bound				   51.1992188

SYS> alter system set pga_aggregate_target=2g;

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

SYS> show parameter pga

NAME				     TYPE			       VALUE
------------------------------------ --------------------------------- ------------------------------
_pga_max_size			     big integer		       1640M
pga_aggregate_target		     big integer		       2G
SYS> 
SYS> @pgastat

NAME						     size(MB)
-------------------------------------------------- ----------
aggregate PGA target parameter				 2048
aggregate PGA auto target			   1829.30273
global memory bound				   102.399414

SYS> 
SYS> alter system set pga_aggregate_target=4g;

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

SYS> show parameter pga

NAME				     TYPE			       VALUE
------------------------------------ --------------------------------- ------------------------------
_pga_max_size			     big integer		       1640M
pga_aggregate_target		     big integer		       4G
SYS> 
SYS> @pgastat

NAME						     size(MB)
-------------------------------------------------- ----------
aggregate PGA target parameter				 4096
aggregate PGA auto target			    3672.4834
global memory bound				   204.799805

SYS> 
SYS> alter system set pga_aggregate_target=8g;

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

SYS> show parameter pga

NAME				     TYPE			       VALUE
------------------------------------ --------------------------------- ------------------------------
_pga_max_size			     big integer		       1640M
pga_aggregate_target		     big integer		       8G
SYS> 
SYS> @pgastat

NAME						     size(MB)
-------------------------------------------------- ----------
aggregate PGA target parameter				 8192
aggregate PGA auto target			   7358.87988
global memory bound				   409.599609

SYS> 
SYS> alter system set pga_aggregate_target=16g;

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

SYS> show parameter pga

NAME				     TYPE			       VALUE
------------------------------------ --------------------------------- ------------------------------
_pga_max_size			     big integer		       1640M
pga_aggregate_target		     big integer		       16G
SYS> 
SYS> @pgastat

NAME						     size(MB)
-------------------------------------------------- ----------
aggregate PGA target parameter				16384
aggregate PGA auto target			   14731.6289
global memory bound				   819.199219

SYS> 

データ量を倍にして、実際にソートしてみると。。。700MB以上のPGAを使ってくれました〜パチパチ。

SCOTT> 
SCOTT> begin for i in 1..100000 loop insert into code values(lpad('#',4000,'#')); end loop; commit; end;
  2  /

PL/SQLプロシージャが正常に完了しました。

SCOTT> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'CODE',degree=>2,estimate_percent=>100);

PL/SQLプロシージャが正常に完了しました。

SCOTT> alter system flush shared_pool;

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

SCOTT> @test3

SQL_TEXT				    OPERATION_TYPE       last_mem(MB) LAST_TEMPSEG_SIZE MAX_TEMPSEG_SIZE
------------------------------------------- -------------------- ------------ ----------------- ----------------
select * from code where rownum <= 20000 SORT		       781.26
0 order by code


SCOTT> 

もうここまででもOracle10g R1での検証は十分かと思いますが、さらに念押しで確認してみますよ〜（趣味の世界w) 次回へつづく。

---

前回までのあらすじ・・・
・pga_aggregate_targetでPGA？、_pga_max_sizeでPGA？ #1
・pga_aggregate_targetでPGA？、_pga_max_sizeでPGA？ #2
・pga_aggregate_targetでPGA？、_pga_max_sizeでPGA？ #3
・pga_aggregate_targetでPGA？、_pga_max_sizeでPGA？ #4