Temp落ち #8 - 自動PGA管理でパラメータ上設定可能な最大サイズは？（実際に利用可能なサイズとは限りませんが。意味深） 12.2.0.1版 Tweet

Previously on Mac De Oracle

自動PGA管理下では、SQL Work Areaサイズは、pga_aggregate_targetのサイズに応じて変化し、最大1GBまで調整され、それのサイズ以上のソートやハッシュ結合は、もれなく、"Temp落ち" する。
手動PGA管理下では、最大2GBまで指定できました。。自動だと1GBまでなのか。。。と残念がる声が、昔は海だった方面から聞こえた気がしましたが、たぶん、気のせいw

というところまででした。

---

今日は、自動PGA管理下ではそれが最大なのか？　のか？　自己責任で試してみることにしますw

まずはこれまでの復習。

自動PGA管理で、SQL Work Areaサイズの算出に関わるパラメータは以下の通り

pga_aggregate_target = 10MB 〜 4TB - 1
_pga_max_size = 200MB 〜　2GB
（Oracleが動的に値を調整している_pga_max_sizeパラメータへユーザーが値を設定してしまうとOracleの自動調整が無効化され、設定した値で固定されてしまうので注意が必要です。）

私の観測範囲だとおおよそ以下のように変化します。最近は大量のメモリーを搭載したサーバーが多いので、pga_aggregate_targetが10GB以上という状況も普通になってきたので、自動PGA管理下のSQL Work Areaサイズは最大1GBとざっくり丸暗記しても困ることは思いますw（ちょっと乱暴かw）

pga_aggregate_target = 10MB〜10GB - 1 :

_pga_max_size = 200MB 〜　2047MB
GREATEST(pga_aggregate_target*0.2 ,200MB)

pga_aggregate_target = 10GB以上〜4TB-1 :

_pga_max_size = 2GB
LEAST(pga_aggregate_target*0.2 ,2GB)

_pga_max_sizeはpga_aggregate_targetの値に応じて動的に変化し、それらを元に _smm_max_size が算出される

LEAST(pga_aggregate_target * 0.2, _pga_max_size * 0.5)

pga_aggregate_target = 10MB〜10GB-1
_pga_max_size = 200MB〜2047MB
_smm_max_size = 2MB〜1023MB


pga_aggregate_target = 10GB〜4TB-1
_pga_max_size = 2048MB
_smm_max_size = 1024MB

_smm_max_sizeは、v$pgastatのglobal memory boundだろうということは確からしいということまでは確認しました。



いままでの結果をまとめると
PGAのSQL Work Areaサイズの最大サイズを示す global memory boundは、pga_aggregate_targetが10MB〜10GB-1までは2MB〜1023MBで調整され、pga_aggregate_targetが10GB〜4TB-1では、1024MB (1GB) で固定されている、というのが、Oracle 10GR2〜12cR2まで動きであることは間違いなさそう:)

自動PGA管理下でSQL Work Areaを1GBを超えるサイズにするにはどのパラメータをどのような値に設定すればよいか。。。
その鍵を握るパラメータは

_pga_max_size

(いまいちピンとこないという方は、過去のエントリーを参照していただけるとスッキリすると思います。）

ただ、冒頭にも書きましたが（昔それでハマったw）、（Oracleが動的に値を調整している_pga_max_sizeパラメータへユーザーが値を設定してしまうとOracleの自動調整が無効化され、設定した値で固定されてしまうので注意が必要です。）
という点はお忘れなく。また、今後も同仕様のままである保証はなく、世界中のOracle使いの方達が調べ上げた結果、現状はこんな感じ。
という状況だと認識しておいたほうが無難だと思っています。

とはいえ、実際どこまで設定可能なのか？　　1GBを以上使えるのか？　知りたいですよね...

長い前置きはこれぐらいにして、
実際にSQL Work Areaサイズに対応したメモリーが実際にどの程度割り当てられるのかということの確認は後回しですが、 パラメータの上では、どこまで設定できるのかというところを確認しました。

なお、
以下のエラーメッセージから、_pga_max_sizeに設定可能な値は、pga_aggregate_targetと同様（Big Integer）、10MB〜4TB-1までであると思われます。

orcl12c@SYS> show parameter _pga_max_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_pga_max_size                        big integer 2G

orcl12c@SYS> alter system set "_pga_max_size" = 4T scope=memory;
alter system set "_pga_max_size" = 4T scope=memory
*
行1でエラーが発生しました。:
ORA-02097: 指定した値が無効なので、パラメータを変更できません。 ORA-00093:
_pga_max_sizeは、10Mから4096G-1の間に設定する必要があります。

ということで、
_pga_max_sizeを1GB/10GB/100GB/1TB/4T-1のそれぞれに設定したうえで、Temp落ち #6 - pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? 12.2.0.1版 で利用したスクリプトを利用し、global memory boundやその他パラメータの変化を確認しました。

ログの例

orcl12c@SYS> alter system set "_pga_max_size" = 10m scope=both;

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

orcl12c@SYS> show parameter _pga_max_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_pga_max_size                        big integer 10m


orcl12c@SYS> @dotest_auto_workarea
<< 10m >>
接続されました。
  1* alter system set pga_aggregate_target=&1 scope=spfile
旧   1: alter system set pga_aggregate_target=&1 scope=spfile
新   1: alter system set pga_aggregate_target=10m scope=spfile

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

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

Total System Global Area 2147483648 bytes
Fixed Size                  8794848 bytes
Variable Size             603983136 bytes
Database Buffers         1526726656 bytes
Redo Buffers                7979008 bytes
データベースがマウントされました。
データベースがオープンされました。
+++ initial parameters +++

KSPPINM                                        KSPPSTVL                       KSPPSTDF
---------------------------------------------- ------------------------------ ------------------------------
__pga_aggregate_target                         16777216                       FALSE

・・・中略・・・

pga_aggregate_limit                            0                              FALSE
pga_aggregate_target                           10485760                       FALSE

50行が選択されました。

/proc/sys/vm/max_map_count

65530

+++ v$pgastat +++

NAME                                                                  VALUE UNIT             CON_ID
---------------------------------------------------------------- ---------- ------------ ----------
aggregate PGA target parameter                                           10 MB                    0
aggregate PGA auto target                                                 4 MB                    0
global memory bound                                                       2 MB                    0

・・・中略・・・

結果は以下のグラフと表にまとめたとおり。
_pga_max_size=4TB-1、pga_aggregate_target=4TB-1の時のglobal memory bound (v$pgastat) = _smm_max_size が最大となり、約839GBとなることがわかりました。
パラメータ上は839GB程度まで増加しますが、ほんとうにPGAがそんなサイズまで利用可能なのでしょうか？....（怪しいです。答え、知ってるんですけどねw。。

ということで、

次回は実際に、そんなに使えんのかよ！　という実験をしてみたいと思います :)

つづく。

---

10gR2(64bit)のころのままなので、以下のシリーズも合わせて読んでおくといいですよ：）
・Mac De Oracle なんですが、Windows(32bit)でのOracleな話
・Mac De Oracle なんですが、Windows(32bit)でのOracleな話 #2
・Mac De Oracle なんですが、Windows(32bit)でのOracleな話 #3
・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
・pga_aggregate_targetでPGA？、_pga_max_sizeでPGA？ #5
・pga_aggregate_targetでPGA？、_pga_max_sizeでPGA？ #6
・pga_aggregate_targetでPGA？、_pga_max_sizeでPGA？ #7
・pga_aggregate_targetでPGA？、_pga_max_sizeでPGA？ #8
・pga_aggregate_targetでPGA？、_pga_max_sizeでPGA？ #9
・pga_aggregate_targetでPGA？、_pga_max_sizeでPGA？ #10
・pga_aggregate_targetでPGA？、_pga_max_sizeでPGA？ #11
・pga_aggregate_targetでPGA？、_pga_max_sizeでPGA？ #12
・pga_aggregate_targetでPGA？、_pga_max_sizeでPGA？ #13
・pga_aggregate_targetでPGA？、_pga_max_sizeでPGA？ #14
・pga_aggregate_targetでPGA？、_pga_max_sizeでPGA？ #15
・pga_aggregate_targetでPGA？、_pga_max_sizeでPGA？ #16
・pga_aggregate_targetでPGA？、_pga_max_sizeでPGA？ #17
・pga_aggregate_targetでPGA？、_pga_max_sizeでPGA？ #18
・pga_aggregate_targetでPGA？、_pga_max_sizeでPGA？ #19
・_pga_max_sizeってOracle11gではどうなったっけ？　という確認。
・_pga_max_sizeってOracle11gではどうなったっけ？　という確認。シーズン２
・pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? Season2 #1
・pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? Season2 #2
・pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? Season2 #3
・pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? Season2 #4
・pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? Season2 #5
・pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? Season2 #6
・pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? Season2 #7
・pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? Season2 #8

---

・Temp落ち #1 - "Temp落ち" って？
・Temp落ち #2 - PGA (Program Global Area)
・Temp落ち #3 - 手動PGA管理で作業領域として指定可能な最大サイズ
・Temp落ち #4 - 手動PGA管理で作業領域として指定可能な最大サイズ de Temp落ちの確認
・Temp落ち #5 - pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? 12.2.0.1版 （その前に少し脱線）
・Temp落ち #6 - pga_aggregate_targetでPGA?、_pga_max_sizeでPGA? 12.2.0.1版
・Temp落ち #7 - 自動PGA管理で到達可能な最大サイズ de Temp落ちの確認 12.2.0.1版