Mac De Oracle なんですが、Windows(32bit)でのOracleな話 #3 Tweet
では本題。
実際に発生した問題は、自動PGA管理下の環境でもpga_aggregate_targetで指定したサイズ以上のサイズになってしまうというもの。
おら！オラ！Oracle -どっぷり検証生活 - 新・ソートに関する検証 その５では、Linux環境のことが書かれているのだが、私が遭遇したのはWindowsの32bit環境つまり、3GBスイッチを利用しなければ、oracle.exeは2GBまでしかメモリを利用できない環境での話だった。（SGAや全PGAのサイズなどすべての合計メモリサイズが、2GBを超えることはできない環境）
その影響でOracleに接続エラーできない状態がかなりの頻度で発生していた。（oracle.exeのメモリサイズがユーザモードで利用可能なサイズを超えたため、新たな専用サーバーを起動できない状態になっていた。接続できないのも当然なのだが。。。）
参考として、以下に私が遭遇した状況に類似している例をネット上で見つけたので、ついでに載せておく。
注）Oracle8iのバグ絡みの話のようなのでOracle10gには該当しないようだ。また、私が遭遇した事例では、crashは発生していないが、専用サーバーが起動できず、全く接続できない状態に陥っていた。
http://dba.ipbhost.com/index.php?showtopic=806
さて、本題に戻ると、
Windows 32bit環境では、oracle.exeのメモリサイズは、通常は2GBまでしか利用できない。
例えば、単純に同時セッションすが増加し、PGAのオーバーアロケーションが発生し、且つ、2GBを超えてアロケーションを試みた場合、Oracleのエラーと共に、前述のURLで示したようなskgpspawn関連のエラーメッセージがアラートログファイルに記録される。
まあ、ここまでなら、単純に、メモリが足りないのだから仕方ないということで同時セッション数を制限したり、場合によっては、もう一台サーバーを立て、例えば１００あったセッションを５０セッションづつに振り分ければどうか？ その他64bit版や linuxへの移行ということも話題に上がり始めたのも不思議ではなかった。。。
ただ、その場合、Oracleのライセンスや新規サーバーなどのH/W購入、ついでに、そのサーバーの維持管理などのコストも増加してしまう。。。
最終手段としては、それでも仕方ないか！？ と私も考えていたのだが、、
その前にアプリケーションが利用するメモリサイズ（詰りPGAサイズ）が適切にチューニングされているのか？ 妥当だと思えるサイズなのか？ という点を調べておきたかった。（ある検索処理が重い、遅いということが、確認済みであったこともその理由だったのだが。。。。）
早速、statspackや、v$ビューなどをモニタリングして調査したところ、PGAサイズの大きなセッションが多数存在することは確認できた。各PGAサイズが大きく、セッション数の多い時に発生するのが今回の問題の特徴なのでその結果自体には驚きはなかった。
さらに細かく調べると、PGAサイズが大きくなるセッションで必ず実行される類似したSQL文が見つかったのである。
とまあ、このような状況だった。（これ以上はあまり詳しく書けないのでこの程度で、ご勘弁を）
ところで、pga（実際にはCGA部分が増加していたのだが）が大きくなるそのSQL文とは、どんなSQL文だったのか？
以下に示すSQL文は実際のものとは大きく異なるのだが、”雰囲気” が伝わるだろうと思われるSQL文の一例である。（尚、実際に発生した問題の状況に近づけるため、NO_USE_HASHヒントを付けて実行している。）
select /*+ NO_USE_HASH */
empno,ename
from
emp
where
empno = any ( select /*+ NO_USE_HASH */
mgr
from
emp
where
empno = ( select /*+ NO_USE_HASH */
mgr
from
emp
where
empno = ( select /*+ NO_USE_HASH */
mgr
from
emp
where
empno = ( select /*+ NO_USE_HASH */
mgr
from
emp
where
empno = ( select /*+ NO_USE_HASH */
mgr
from
emp
where
empno = ( select /*+ NO_USE_HASH */
mgr
from
emp
where
empno = 7904
)
)
)
)
)
)
/
＃このSQLを見た瞬間、頭の中で 「が〜〜〜〜〜ん」という文字が浮かんだり、聞こえたのは私だけではないはず・・・
上記のSQL文は、Oracleではおなじみ、Scottユーザのemp表を問い合わせて、社員ツリーの下位から最上位の社員名を取得するものだのだが、見ての通り、Oracleに慣れた方であれば、間違いなく、階層問い合せを利用する場面で、動的SQL文を利用し、階層の深さに応じた副問い合せを生成して実行していたのである。。。この例では階層の深さはそれほどでもないのだが、実際のシステムでは、数十階層を超えるものも存在していたのである。
ちなみに、emp表は以下のようになっている。（改めて載せる必要も無いかもしれないが、念のため）
尚、以下のクラス図は、最近お気に入りの Visual Paradigm for UMLを利用している。
ということで、話は簡単、階層問い合せに変更してください！ ということで解決を見たのである。 上記のSQL文と同じ結果を取得する階層問い合せは、以下のようになるだろう。
SCOTT> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80-12-17 800 20
7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30
7521 WARD SALESMAN 7698 81-02-22 1250 500 30
7566 JONES MANAGER 7839 81-04-02 2975 20
7654 MARTIN SALESMAN 7698 81-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 81-05-01 2850 30
7782 CLARK MANAGER 7839 81-06-09 2450 10
7788 SCOTT ANALYST 7566 87-04-19 3000 20
7839 KING PRESIDENT 81-11-17 5000 10
7844 TURNER SALESMAN 7698 81-09-08 1500 0 30
7876 ADAMS CLERK 7788 87-05-23 1100 20
7900 JAMES CLERK 7698 81-12-03 950 30
7902 FORD ANALYST 7566 81-12-03 3000 20
7934 MILLER CLERK 7782 82-01-23 1300 10
7901 SADE 7876
7903 ALEX 7901
7904 DISCUS 7903
7905 JEAN 7904
7906 Duke 7905
7907 Wendy 7906
20行が選択されました。
SCOTT>
select
empno,ename
from
emp
where
connect_by_isleaf = 1
start with empno = 7904
connect by prior mgr = empno
/
この対策だけで新しいサーバーやOracleのライセンスを購入する必要するのない状況までメモリ状況が改善したのである。一件落着！。
めでたし、めでたし。。。
以下は、上記２つのSQL文を実行し利用されるPGAサイズやUGAサイズを比較したものである。尚、各SQL文が初めて実行された場合のPGAとUGAサイズである。
問題となったSQL文は、PGA内のCGAが多量に消費されていた点が興味深い。（尚、実際に発生した問題の状況に近づけるため、NO_USE_HASHヒントを付けて実行している。）
また、副問い合せのネストの深さが７層程度であっても、同じ結果を返す階層問い合せのPGAサイズの２倍以上までPGAサイズが膨らむ点にも着目してもらいたい。
● 各SQL文の実行前のPGA,UGAサイズ
SYS> l
1 select
2 ses.sid,
3 sname.name,
4 sstat.value
5 from
6 v$sesstat sstat
7 join v$statname sname
8 on sstat.statistic# = sname.statistic#
9 join v$session ses
10 on sstat.sid = ses.sid
11 where
12 ses.username = 'SCOTT' and
13 (sname.name like '%uga%' or sname.name like '%pga%')
14 order by
15* ses.sid
SYS> /
SID NAME VALUE
---------- ------------------------------ ----------
152 session uga memory 152180
152 session uga memory max 152180
152 session pga memory 498252
152 session pga memory max 498252
156 session uga memory 152180
156 session uga memory max 152180
156 session pga memory 498252
156 session pga memory max 498252
8行が選択されました。
● 問題のSQL文（副問合せをネストさせている文）をSID=152のセッションで実行した直後のPGAサイズ
SYS> /
SID NAME VALUE
---------- ------------------------------ ----------
152 session uga memory 152180
152 session uga memory max 217644
152 session pga memory 1153612
152 session pga memory max 1219148
156 session uga memory 152180
156 session uga memory max 152180
156 session pga memory 498252
156 session pga memory max 498252
8行が選択されました。
● 解決案（階層問合せ）をSID=156のセッションで実行した直後のPGAサイズ
SID=152のセッションの半分のサイズで済んでいる。
SYS> /
SID NAME VALUE
---------- ------------------------------ ----------
152 session uga memory 152180
152 session uga memory max 217644
152 session pga memory 1153612
152 session pga memory max 1219148
156 session uga memory 152180
156 session uga memory max 152180
156 session pga memory 563788
156 session pga memory max 563788
8行が選択されました。
SYS>
| 固定リンク | 0
コメント