株式会社インサイトテクノロジー 発行
http://www.insight-tec.com
┏┏┏┏┏━━━━━━━━━━━━━━━━━━━━━━━━…・・ ┏━
┏┏┏┏┛ 2004.03.31 ┏┛┛
┏┏┏┛ ☆おら！オラ！Oracle -どっぷり検証生活-★ ┏┛┛┛
┏┏┛ ┏┛┛┛┛
┏┛・・…━━━━━━━━━━━━━━━━Vol.194━…・・┏┛┛┛┛┛
◇目次◇
■Oracle検証生活・・・新・ソートに関する検証 その９
■お知らせ・・・○ＱＡについて
■編集者より
◎バックナンバーココから！！
http://www.insight-tec.com/?http://www.insight-tec.com/html/mailmagazine/mail_back/mail_back_index.html
┏─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━┓
●【 Oracle 検証生活 】 ●
┗─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━┛
＜新・ソートに関する検証 その９＞
ペンネーム グリーンペペ
さて、先週に引き続き”共有サーバー接続時のORA-4031エラーを回避せよ”
の回答です。
■解決策その３
〜sort_area_retained_sizeを設定する〜
共有サーバー接続時のメモリソート領域は、以下の公式により所定の領域に
割り当てられます。
sort_area_size - sort_area_retained_size = ＰＧＡ
sort_area_retained_size = ＳＧＡ
では早速、検証してみましょう。
▽環境
OS:Red Hat Linux release 7.1
Oracle:9.2.0.1
▽設定値の確認
SQL> select name,value,isdefault from v$parameter
where name in ('workarea_size_policy','pga_aggregate_target')
or name like 'sort%';
NAME VALUE ISDEFAULT
------------------------------ ------------------------- ---------
sort_area_size 104857600 FALSE
sort_area_retained_size 0 TRUE
pga_aggregate_target 4294967296000 FALSE
workarea_size_policy AUTO TRUE
sort_area_size = 100MB,sort_area_retained_size = 0
となっています。公式に当てはめると、
sort_area_size - sort_area_retained_size = PGAに割当てられるメモリソート領域
100MB - 0 = 100MB
となり、SGAに割り当てられる領域はないはずです。
では、実際に集計処理を実行してみましょう。
▽セッション情報の確認
SQL> select sid,serial#,server from v$session
where username = 'XPRT';
SID SERIAL# SERVER
---------- ---------- ------------------
10 2105 SHARED
共有サーバー接続になっています。
▽集計処理の実行
SQL> select id,sum(cost) from code group by id;
ORA-04031: 共有メモリーの8192バイトを割当てできません("shared pool",
"unknown object","sort subheap","sort key")
おかしいです。
先ほどの公式によって得られた情報だと全てのメモリソート領域はＰＧＡに割
り当てられるはずなのに、エラーメッセージに出力されているshared poolは
ＳＧＡなので矛盾してます。と悩んでたら、マニュアルに以下の記述がありま
した。
以下、Oracle9iデータベース・リファレンス,リリース9.2より転載↓
注意: V$PARAMETER 動的パフォーマンス・ビューに反映されるデフォルト値
は0です。ただし、明示的にこのパラメータを設定していない場合、実際には
SORT_AREA_SIZE パラメータ値が使用されます。
ということはメモリソート領域は、sort_area_sizeに設定したサイズ全てが
ＳＧＡに割り当てられようとしたようです。では、sort_area_retained_size
を明示的に設定したらどうなるのか、検証してみましょう。
とその前に空きメモリがない状況なので、これ以上ＰＧＡが割り当てられると
ページングが起きてしまいます。
事前に大きくしすぎたshared_pool_sizeを小さくしておきましょう。
▽sort_area_sizeを小さくする
SQL> alter system set shared_pool_size=200M;
システムが変更されました。
▽sort_area_retained_size = 0 の設定
SQL> alter session set sort_area_retained_size=0;
セッションが変更されました。
SQL> select id,sum(cost) from code group by id;
ORA-04031: 共有メモリーの8192バイトを割当てできません("shared pool",
"unknown object","sort subheap","sort key")
”０”に設定してもだめなようです。
▽sort_area_retained_size = 1 の設定
SQL> alter session set sort_area_retained_size=1;
セッションが変更されました。
SQL> select id,sum(cost) from code group by id;
1000000行が選択されました。
集計処理は問題なく、完了しました。
■解決策その４
〜sort_area__sizeを設定する〜
最後の解決策です。
空きメモリがないようなので、ディスクソートで集計処理するように
sort_area_size を小さく設定してみましょう。
▽sort_area_sizeの設定
SQL> alter session set sort_area_size=1;
セッションが変更されました。
SQL> select id,sum(cost) from code group by id;
1000000行が選択されました。
集計処理は問題なく、完了しました。
さて、読者の皆様。今回の問題の手ごたえは如何でしたか？
難しかったですか？簡単でしたか？
感想・質問など寄せてくれると、大変励みになります。
今号で、新・ソートに関する検証シリーズはおしまいです。
また、皆様に有益なメルマガを提供できるよう、グリーンペペは旅に出てきます。
それでは！
お花見スポットはどこですか？茅ヶ崎にて。
◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆
一緒に検証してみませんか？
http://www.insight-tec.com/?http://www.insight-tec.com/html/company/com_career.html
◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆
┏─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━┓
●【 ＱＡについて 】 ●
┗─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━┛
＜皆様からのＱＡを受付けております＞
皆様のＱＡにはできるだけ、お答えしたいと思っています。
すべてのＱＡにお答えすることはできないかもしれませんが、
適宜メルマガ内でとりあげていく予定ですので、是非ＱＡを下記アドレスま
でお寄せください。
┏─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━┓
●【 編集者より 】 ●
┗─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━┛
週末に友達に会ったときに婚約したと報告をされたんですぅ。また去年、結
婚して７月にパパになる友達も・・。ん〜、春が近づいてきているのは嬉し
いことなのですが、私の春はいつくるのかと考えるとまだまだ先は長そうで
すぅ。 by TI
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
登録・解除は以下のURLで行うことができます。
http://www.insight-tec.com/jp/html/ora3/ora3.html
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
＜おら！オラ！Oracle−どっぷり検証生活−＞
発行／編集：株式会社インサイトテクノロジー
http://www.insight-tec.com
マガジンID：0000030093
本メールマガジンに掲載された記事を許可なく転載することを禁じます。
Copyright(c) 1995-2004, Insight Technology, Inc., All Rights Reserved.
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Insight Technology, Inc
Japanese | English