« pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? #4 | トップページ | pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? #6 »

2010年5月 8日 (土)

pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? #5

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? その5回目、

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

|

トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/106341/48300096

この記事へのトラックバック一覧です: pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? #5:

コメント

コメントを書く