Insight Technology, Inc

インサイトテクノロジー

Japanese | English

株式会社インサイトテクノロジー  発行
http://www.insight-tec.com

\_〜…〜…〜…〜…〜…〜…〜…〜…〜…〜…〜…〜…〜…〜…〜…〜_/
^^\_                                               2003.10.24 _/^^
^^^^\_      海の見える開発室  −解き放てmind!−            _/^^^^
^^^^^^\_                                          Vol.006 _/^^^^^^
^^^^^^^^\_〜‥〜‥〜‥〜‥〜‥〜‥〜‥〜‥〜‥〜‥〜‥〜_/^^^^^^^^

≪目次≫
■ディベロッパーX  〜開発者たちの挑戦〜
■どっぷり開発生活  −Shared PoolとLarge Poolのチューニング−
■PI Knowledge Center
■開発フリートーク
■お知らせ・・・
■編集者より

___________________________________
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
■ディベロッパーX  〜開発者たちの挑戦〜
___________________________________
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
前回はログバッファのチューニングについて調べてみました。log_bufferのサ
イズが適切でないと、パフォーマンスに与える影響が大きいことから、
Performance Insightがどのような監視をしているのかを知りました。

Oracleのパフォーマンスに影響を与えてるものをいろいろ調べ始めたら、、、
はふぅ〜余計わかんなくなってきたぁ・・・。Aの値を変更するとBに影響して、
Bの値を変更するにはCやDを調べないと簡単に変更できなくて、CとDを調べるに
はEの値がFの条件の時とGの条件の時とって・・・・えっ?Eってなに???

もう机の上が資料だらけ!どーしよっ!!


⊂●⊃⊂●⊃⊂●⊃⊂●⊃⊂●⊃⊂●⊃⊂●⊃⊂●⊃⊂●⊃⊂●⊃⊂●⊃⊂●⊃

〔DV〕この前の共有プールの資料まとまってる?

〔KA〕はい。ええっと。。おかしいな。机の上に置いたんだけどな。

〔DV〕こりゃフラグメンテーションが発生してるなー(笑)

〔KA〕フラグメンテーション???

〔DV〕つまり散らかってるってことだ。
      机の上を共有プールとして説明しようか。
      共有プールの役目はなんだったかな?

〔KA〕データディクショナリのデータ、ストアドプロシージャやパッケージ、
      翻訳済みのSQL文を再利用するために貯えておくってことですよね。

〔DV〕つまり、机が大きければ大きいほどたくさんのSQL文を貯めることがで
      きる。だけどあまりにも大きすぎると頻繁に利用されるSQL文もなかな
      か見つからないし、新規のSQL文のための空きスペースを見つけるのも
      大変だ。再利用されないSQL文は捨てて、適度な大きさの机の上で作業
      することが理想的だ。第一そんな大きな机を買うのはもったいないだろ。

〔KA〕共有プールも整理整頓が大切なんですね!

〔DV〕そうだ。
      それじゃあ、どうやったら共有プールを整理整頓できるかについて説明
      しよう。

___________________________________
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
■どっぷり開発生活  −Shared PoolとLarge Poolのチューニング−
___________________________________
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
┏─────────────────────────────────┓
┃総合評価欄                                                        ┃
┗─────────────────────────────────┛
------------->> (1)Shared Poolが大きすぎます  <<----------------------
バインド変数を使っていないSQL文が存在するため、Shared Pool内でSql Area
の占める割合が増えています。
大きく設定しすぎたshared_pool_sizeはパフォーマンスを悪化させています。

------------->> (2)Shared Poolが小さすぎます  <<----------------------
shared_pool_sizeが不足しています。
large_pool_sizeが不足しています。

------------->> (3)Large Poolで問題が起きています <<-----------------
large_pool_sizeで指定した領域は使用されませんでした。
large_pool_sizeで指定した領域は最大でnn%しか使用されませんでした。
----------------------------------------------------------------------

┏─────────────────────────────────┓
┃Shared Poolがパフォーマンスに影響を与える条件                     ┃
┗─────────────────────────────────┛
以下のような条件になるとShared Poolがパフォーマンスに影響を与えます。
1.バインド変数を使っていないなどの条件で再利用できないSQLが増えつづけ
  ている。
2.1の状態にもかかわらずShared Poolを100MB以上の大きさに設定している。

上記のような現象はv$sgastatのshared poolの使用率が多くなっていく事でも
観察できます:

SQL> select 'Shared Pool Used%' p1
             , round((1-sum(decode(s.name,'free memory',s.bytes,0))/sum(s.bytes))*100,2) p1_value
             ,'Used KB' p2, round((sum(s.bytes)-sum(decode(s.name,'free memory',s.bytes,0)))/1024) p2_value
       from v$sgastat s
      where s.pool='shared pool';

P1                  P1_VALUE P2        P2_VALUE		
----------------- ---------- ------- ----------
Shared Pool Used%      34.46 Used KB      45165

またチャンク数が多くなっていく事でフラグメンテーションが進んでいること
も確認できます:

SQL> select count(*) chnk_cnt from sys.x_$ksmsp;

 CHUNK_CNT
----------
     14308

ここで、ミスジャッジしやすいのは、使用率が高い(上記の例だと使用率は
34.46%と低いですが)、だからshared_pool_sizeが足りない、増やそう!
と繰り返すうちに、shared_pool_sizeを1GBなどと設定してしまうケースです。

┏─────────────────────────────────┓
┃Oracleのバージョンによる違い                                      ┃
┗─────────────────────────────────┛
「Shared Poolの使用率が高いのは、
       バインド変数を使っていない再利用されないSQL文が多いためです。」
Oracle8.1.6以前では、このメッセージは「SQL文を書き換えなさい!
もしそれができないのであればflush shared_poolを定期的に実行しなさい!」
という意味でした。
Oracle8.1.6でサポートされたcursor_sharingパラメータは、SQL文の書き換え
なしでこの問題をクリアしてくれるというとても便利なものです。
ただし、いくつかのバグがあるので、実際はOracle8.1.7.3あるいはOracle9.2
以上(9.0ではバグがある)である必要があります。

┏─────────────────────────────────┓
┃Shared Pool関連で問題があるサイトのパターン                       ┃
┗─────────────────────────────────┛
(1)使用率が80%以上と高く、パッケージ・プロシージャのロード回数が高い
   ☆チューニング無頓着型☆
     ・小さすぎる
     ・Large Poolを設定していない
(2)使用率が80〜90%ぐらいでshared_pool_sizeが300MB以上
   ☆ヒット率や使用率でチューニングをしてきた、空回り型☆
     ・バインド変数を使用していない大量使い捨てSQL文がある
     ・日に日に使用率が高まっている
         [8.1.7.3 or 9.2以上]
           cursor_sharing force あるいは similarで改善できる
         [8.1.6以下、あるいは9.0]
           flush shared_poolのスケジューリングで改善(回避)
     ・バインド変数が問題ではない場合はLarge Poolを設定していない
(3)使用率は50%を上回った事がない
   ☆ベンダー任せ、無駄でもハード投資型☆
     ・(メモリも安くなったけど)空きが128MBを越えていたらアドバイスが必要

パフォーマンス悪化が著しいのは(2)の空回り型です。

┏─────────────────────────────────┓
┃Large Pool関連で問題があるサイトのパターン                        ┃
┗─────────────────────────────────┛
Large PoolはShared Poolと密接な関係にあるので、Large Poolについても調査
します。しかし、Large PoolはShared Poolと同じような判断ができません。
なぜならば、使っていないからといって減らしてしまうと、たまに使うパラレ
ルクエリーがABORTしたりするからです。
そこで、このサイトでLarge Pool不足が起きたことがないか?を調べてから、
どうしてもメモリが足りない場合だけ解放するようにします。

Large Poolの使用率も調べておきます。

SQL> select 'Large Pool Used%' p1
             ,round((sum(decode(name,'free memory',0,bytes))/nvl(sum(bytes),0))*100,2) p1_value
             ,'Used KB' p2
             ,round(sum(decode(name,'free memory',0,bytes))/1024) p2_value
       from v$sgastat
      where pool='large pool';

┏─────────────────────────────────┓
┃PI5.2詳細メッセージ                                               ┃
┗─────────────────────────────────┛
◆使用率が80%以上 or チャンク数15万以上、
  日に日に使用率が高まっている or ヒット率が95%未満の場合:
  MSG:IST-02201:
      Shared Poolの使用率が高くなっています。
  MSG:IST-02209:
      Shared Poolのフラグメンテーションが高くなっています。

◆バインド変数を使っていないSQL文が大量発生していないかの判定:
  SQL> SELECT COUNT(HASH_VALUE) COPIES, SUBSTRB(SQL_TEXT,1,instr(upper(sql_text),'WHERE')-1)
         FROM V$SQLAREA
        where upper(ltrim(sql_text)) like '% WHERE %'
              and PARSING_SCHEMA_ID > 0
              having COUNT(HASH_VALUE) > 100
              GROUP BY SUBSTRB(SQL_TEXT,1,instr(upper(sql_text),'WHERE')-1) ORDER BY COPIES DESC

  COPIES SUBSTRB(SQL_TEXT,1,INSTR(UPPER(SQL_TEXT),'WHERE')-1)
  ------ ----------------------------------------------------
    1222 select c1,c2 from ttt

  ≪トップ10のSQL文は10行づつ表示≫
  SQL> select sql_text from v$sqlarea where sql_text like 'select c1,c2 from ttt%' and rownum<11;

  SQL_TEXT
  -----------------------------------
  select c1,c2 from ttt where c1 = 0
  select c1,c2 from ttt where c1 = 1
  select c1,c2 from ttt where c1 = 10
  select c1,c2 from ttt where c1 = 11
  select c1,c2 from ttt where c1 = 12
  select c1,c2 from ttt where c1 = 13
  select c1,c2 from ttt where c1 = 14
  select c1,c2 from ttt where c1 = 15
  select c1,c2 from ttt where c1 = 16
  select c1,c2 from ttt where c1 = 17

  a.Oracle8.1.6以下 or Oracle9.0は、必ずチェック
    MSG:IST-02202:
        cursor_sharingパラメータを設定する事によりバインド変数を使っ
        ていないため、再利用されていないSQL文を減らす事ができます。
        イコール検索にのみ対応する:cursor_sharing=FORCE
        レンジ検索にも対応する必要がある:cursor_sharing=SIMILAR
  b.Oracle8.1.6以上 or Oracle9.2以上は、cursor_sharing=EXACTの時だけ
    チェック
    MSG:IST-02202:
        バインド変数を使う事によりShared Poolの使用率を減らす事ができます。
        バインド変数を使う変更ができない場合は以下のコマンドを定期的
        に実行してください。
        SQL> alter system flush shared_pool;

  PINHITRATIOは全体への影響度を示す指標なので、重要度はPINHITRATIOで
  決めることができる。
  また、「日に日に高まっている」というのが特定できない場合は、
  PINHITRATIO < 95で代用する事もできる。
  SQL> select NAMESPACE, PINHITRATIO from V$LIBRARYCACHE
        where namespace = 'SQL AREA' and PINS > 10000; 
      (PINSが10000以下であればあまり使われていないので対象外にする)

  PINHITRATIO<80 or チャンク数>30万 ⇒ 重要度1
  PINHITRATIO<80 or チャンク数>25万 ⇒ 重要度2
  PINHITRATIO<85 or チャンク数>20万 ⇒ 重要度3
  PINHITRATIO<90 or チャンク数>15万 ⇒ 重要度4
  PINHITRATIO<95  ⇒ 重要度4

◆shared_pool_sizeが100MB未満で、使用率が80%以上あり、
  パッケージ・プロシージャのロード回数が高い場合:
  SQL> select NAMESPACE, (RELOADS-INVALIDATIONS) RELOADS
         from V$LIBRARYCACHE
        where namespace = 'BODY';

  NAMESPACE          RELOADS
  --------------- ----------
  BODY                     3<- 100を超えていたらロード回数が多い

  a.Large Poolを設定していない、
    パラレルクエリーを使っている or MTSを使っている
    MSG:IST-02201:
        Shared Poolの使用率が高くなっています。
        Large Poolを設定する事によりShared Poolの使用率を下げる事ができます。
  b.Large Poolを設定している or 上記以外
    MSG:IST-02201:
        Shared Poolの使用率が高くなっています。
        Shared Poolを増やしてください。

  ※パラレルクエリーを使っているか:v$pq_sysstat.dfo_trees > 0
    MTSを使っているか             :v$mts.maximum_connections > 0

◆shared_pool_sizeが300MB以上で、使用率が80%以上あり、
  日に日に使用率が高まっている場合:
  基本的には300MB未満のときと同じだが、バインド変数を使っていない事が問
  題であるという結論に達したら、アドバイスするメッセージが増える。
  MSG:IST-02201:
      shared_pool_sizeが大きすぎると返って負荷が掛かる場合があります。
      減らす事を検討してください。

◆shared_pool_sizeが300MB以上で、Freeが128MBを超えている
    MSG:IST-02203:
        Shared Pool FreeがnnnMBを超えています。
        MEMORY_BUSYの時は、重要度2

◆large_pool_sizeが不足
    MSG:IST-02204:
        large_pool_sizeが不足しています
        Large Poolの使用率が最高で95%を上回りました
        Large Poolが不足すると、代わりにShared Poolの領域が割り当てられます
        よって頻繁なLarge Poolの不足はShared Poolのフラグメントを発生させ、
        レスポンスの低下を招きます

◆LOAD数の多いパッケージはKEEPする事により負荷を減らす事が可能
    MSG:IST-02208:
        共有プールへの固定(PIN)が望ましいパッケージ一覧

  SQL> select     OWNER,
    2     NAME||' - '||TYPE object
    3     ,EXECUTIONS, SHARABLE_MEM
    4     ,LOADS, LOCKS, KEPT
    5  from       v$db_object_cache
    6  where      EXECUTIONS > 100
    7  and        type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE','TRIGGER')
    9* order      by owner, object;

  OWNER      OBJECT                                   EXECUTIONS SHARABLE_MEM      LOADS      LOCKS KEP
  ---------- ---------------------------------------- ---------- ------------ ---------- ---------- ---
  SYS        DATABASE_NAME - FUNCTION                      37228         9573          1          0 NO
  SYS        DBMS_ASYNCRPC_PUSH - PACKAGE                  18612         2822          1          0 NO
  SYS        DBMS_ASYNCRPC_PUSH - PACKAGE BODY             18612        18366          1          0 NO
  SYS        DBMS_DEFER_INTERNAL_SYS - PACKAGE             18612         4967          1          0 NO
  SYS        DBMS_DEFER_INTERNAL_SYS - PACKAGE BODY        18612        23455          1          0 NO
  SYS        DBMS_DEFER_QUERY_UTL - PACKAGE                18612         6520          1          0 NO
  SYS        DBMS_DEFER_QUERY_UTL - PACKAGE BODY           18612        16920          1          0 NO
  SYS        DBMS_DEFER_SYS - PACKAGE                      18612         6342          1          0 NO
  SYS        DBMS_DEFER_SYS - PACKAGE BODY                 18612        58570          1          0 NO
  SYS        DBMS_DEFER_SYS_PART1 - PACKAGE                18612         2460          1          0 NO
  SYS        DBMS_DEFER_SYS_PART1 - PACKAGE BODY           18612        23772          1          0 NO
  〜後続行略〜

___________________________________
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
■PI Knowledge Center
___________________________________
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
●共有プール領域●
<KnowledgeCenter:keyword="SHARED_POOL_AREA,共有プール,ディクショナリ,
  SQL_AREA">
パフォーマンス上の重要な構成に、SGA内に配置された"共有プール"があります。
ここには、ディクショナリ等と伴に、SQLの翻訳情報や、実行形式のSQLが格納
されます。ユーザーにより発行されたSQLは、共有プール中にあるSQL AREA内
のSQLと比較され、既に実行したことのあるSQLであればSQL情報は再利用され
ます。

●共有プール領域の使用率●
<KnowledgeCenter:keyword=Buffer_Space,Shared_Pool_Usage,
  Shared Pool Usage">
Shared_Pool_Usage共有プール領域の使用量パーセンテージ
使用量パーセンテージは100%に近づけば近づくほどフルになっているという事
になります。あまりに大きく設定しすぎてしまうと反ってパフォーマンスには
マイナスになることがあります。100%フルになった状況でもORA-04031が報告さ
れていない限り緊急性は高くありません。
バインド変数を使用していないSQL文が大量に発行されると、この値は急上昇
します。そのような場合は共有プールを大きくすると反ってパフォーマンスを
悪くしてしまう場合があります。Buffer_Space(SQL_Area_Hit)のヒット率を評
価してください。
*500MB以上などの大きな設定をする場合には注意が必要です。
*しきい値は使用率(0-100%)で指定してください。
*デフォルトのしきい値はAUTOが設定されています。
Used KB
現在の使用Kiro Bytes
init.oraで指定したshared_pool_sizeの内、実際に使用されている量が表示さ
れます。 

●共有プールのチューニング●
<KnowledgeCenter:keyword="SHARED_POOL,共有プール,共有SQL,PL/SQL,
  ライブラリ・キャッシュ,データ・ディクショナリ・キャッシュ,
SHARED_POOL_SIZE,ORA-4031,LRU">
共有プールには、共有SQLやPL/SQL(これらが格納されているキャッシュをライ
ブラリ・キャッシュと呼びます)、データ・ディクショナリ・キャッシュ、
データベースに対する セッション情報などが格納されています。
その大きさは、初期設定パラメータ・ファイルの SHARED_POOL_SIZEで決めら
れます。もし、ORA-4031エラー(共有プール領域が足りません)が 度々起きる
ようならば、そのサイズを増やさなければなりません。SQL文は共有プールに
保持され、長い時間アクセスされないとLRU (Least Recently Used)アルゴリ
ズムにより再利用領域に回されます。データベースにとっては、SQL文の翻訳
回数は、少ない方が負荷も減ります。また、翻訳に掛かる時間は、チューニン
グによって減らすことはできない問題でもあります。

●Largeプール領域●
<KnowledgeCenter:keyword="LARGE_POOL,LARGE_POOL_SIZE,SGA,SHARED_POOL,
  フラグメント">
Oracle8からサポートされたLARGE_POOLにはマルチスレッド・サーバー環境で
接続された セッションのソート領域も含まれます。LARGE_POOL_SIZEを設定し
SGA内にLARGE_POOL領域を 作成することにより共有プール領域(SHARED_POOL)
のフラグメントを誘発するようなワーク コンテンツを区別することが可能と
なりパフォーマンス向上につながります。 

●Largeプール領域の使用率●
<KnowledgeCenter:keyword="Buffer_Space,Large_Pool_Usage,
  Large Pool Usage">
Large_Pool_UsageLargeプール領域の使用量パーセンテージ:
マルチスレッドサーバ接続数が多い場合には十分な大きさが必要です。中途半
端な大きさを設定するぐらいならば共有プール領域だけを十分に取って運用す
るほうが安全です。
この領域が足りなくなるとORA-04031が発生します。
パラレル・クエリーを使うサイトではパフォーマンスに影響しますので十分な
大きさが必要です。
*しきい値は使用率(0-100%)で指定してください。
*デフォルトのしきい値はAUTOが設定されています。
Used KB
現在の使用Kiro Bytes
init.oraで指定したlarge_pool_sizeの内、実際に使用されている量が表示さ
れます。 

___________________________________
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
■開発フリートーク                                   〔by 〕 涌 牧子
___________________________________
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
初めましてInsight@蜘蛛の巣(Web)デザイナーです。
開発者の側に身を置かせてもらい、Insight-tec.comという名の巣を毎日広げた
り作り直したりしております。

製品に関して、どうやって開発しているのか?どうやって使うのか?など、
全くの素人状態なので、開発側から「そうじゃないんだ!こう伝えて欲しい」
といった要望、アドバイスを受ける事がよーくあります。熱意は理解できるも
のの、それをユーザーに上手く伝えるにはどうしたらいいか?と悩む事もあり
ます。が、これからもこの立場を生かし、「開発魂」と「新鮮な情報」を、
ユーザーの立場に立って皆様に届け、他にはないサイト作りを心掛けていきた
いと考えています。

10月17日、Performance Insight5.2リリースと同時に製品ページを一新しまし
た。
続いて新コンサルティングサービス、「Au-DBit+(オーデビット・プラス)」
もスタートしますので、続々とコンテンツが充実してまいります。
今後のInsight-tec.comにどうぞご期待ください!

___________________________________
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
■お知らせ・・・
___________________________________
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
<ご意見・ご感想をお寄せください>
メルマガに関するご意見・ご感想をお送りください。

___________________________________
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
■編集者より
___________________________________
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
先週、テレビで紅葉が綺麗ですね!というニュースを見て、早々箱根へドライ
ブに出かけたんです。渋滞してるかな?人ばっかりかな?と心配してたんです
けど、、、紅葉はどこ???って感じで、山はまだ緑でした〜。くぅ。。。
来週再チャレンジしてみようかと思ってます!   by KA

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
登録・解除は以下のURLで行うことができます。
http://www.insight-tec.com/html/mailmagazine/d_mail.html
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
<海の見える開発室  −解き放てmind!−>
発行/編集:株式会社インサイトテクノロジー
http://www.insight-tec.com

本メールマガジンに掲載された記事を許可なく転載することを禁じます。
Copyright(c) 2003, Insight Technology, Inc.,  All Rights Reserved.
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

 

 メールマガジン解除