セルフ・チューニングOracle9i Database: Oracle SGA(続き)

Donald K. Burleson
2001年以来のOTNメンバー

パート1へ戻る

pga_aggregate_targetのシグネチャ開発

Oracle DatabaseのPGA領域は、ソート操作やSQLハッシュ結合の速度を支配するため、非常に重要です。 次の条件の1つでもあてはまる場合、pga_aggregate_targetパラメータを動的に変更することもできます。

  • v$sysstat統計の"estimated PGA memory for one-pass"の値がpga_aggregate_target,を超える場合、pga_aggregate_targetを増やします。
  • v$sysstat統計の"workarea executions —multipass"の値が1パーセントより大きい場合、データベースは追加RAMメモリーから利益を得る可能性があります。
  • PGAメモリーを過剰割当てすることもでき、pga_aggregate_targetの値を低減することが考えられます。その場合、v$sysstat行の"workarea executions—optimal"の値は常に100パーセントとなります。

v$pgastatビューは、PGA使用量に関するインスタンス・レベルのサマリー統計および自動メモリー・マネージャを表示します。 即座に概要を把握できるよう、シンプルな問合せは、Oracle Database 10g 接続に対する優れた全PGA使用量統計を提供します。

check_pga.sql

-- *************************************************************
-- Display detailed PGA statistics
--
-- *************************************************************
column name  format a30

column value format 999,999,999

select
   name, 
   value 
from
   v$pgastat
;

この問合せは次のように出力されます。


NAME                                                   VALUE     
------------------------------------------------------ ----------
aggregate PGA auto target                             736,052,224
global memory bound                                        21,200
total expected memory                                     141,144

total PGA inuse                                        22,234,736
total PGA allocated                                    55,327,872
maximum PGA allocated                                  23,970,624
total PGA used for auto workareas                         262,144
maximum PGA used for auto workareas                     7,333,032
total PGA used for manual workareas                             0
maximum PGA used for manual workareas                           0
estimated PGA memory for optimal                          141,395
maximum PGA memory for optimal                        500,123,520
estimated PGA memory for one-pass                         534,144
maximum PGA memory for one-pass                        52,123,520

v$pgastatからの前の表示には、次の重要な統計が表示されています。

  • Total PGA used for auto workareas — この統計は、自動メモリーモードで動作するすべての接続のRAM消費量を監視します。 Oracleでは必ずしもすべての内部プロセスに自動メモリー機能を使用することを認めていないことを覚えておいてください。 たとえば、JavaとPL/SQLは、RAM メモリーを割り当てますが、それは合計PGA統計で計数されません。 したがって、接続で使用されたメモリー量およびJavaとPL/SQLで消費されたRAMメモリーを表示するには、この値を割り当てられたPGA総計から引く必要があります。
  • Estimated PGA memory for optimal/one-pass — この統計では、最適モードですべてのタスク接続のRAM需要を実行するために必要なメモリー量が推定されます。 Oracle Database 10g でメモリーが不足している場合、DBAは、マルチパス操作を起動して、空いたRAMメモリーを検索します。 この統計は、Oracle Database 10g のRAM消費量の監視にとってきわめて重要であり、大部分のOracle DBAは、pga_aggregate_targetをこの値まで増やします。

Oracle Database 10g では、v$pga_target_adviceと呼ばれる新規アドバイザリ・ユーティリティを使用できます。 このユーティリティは、現在値の10% から200%の異なるサイズのpga_aggregate_targetに対し、最適のワンパスおよびマルチパスPGAの変更の限度を示します。

リスト4は、この新規ユーティリティを使用してサンプル問合せ表示します。出力のサンプルは次のとおりです。 ここでは、現在の処理に対するpga_aggregate_targetの過剰割当てが見られます。この領域からRAMを取得し、それを他の場所に割り当てることができます。


             Estimated   Estimated
 Target(M) Cache Hit % Over-Alloc.

---------- ----------- -----------
       113          73           0
       225          81           0
       450          92           0
       675         100           0
       900         100           0
      1080         100           0
      1260         100           0 <= current size
      1440         100           0
      1620         100           0
      1800         100           0
      2700         100           0
      3600         100           0
      5400         100           0
      7200         100           0

PGAメモリー不足を検出し(Statspack使用)、pga_aggregate_targetを動的に変更するためにジョブを書くための自動化された方法を簡単に作成でき、ソートおよびハッシュ結合に対し最適のRAM使用量が確保されます。

データ・バッファのシグネチャ開発

DBAは、実際にデータ・バッファのヒット率(DBHR)の変動が、測定された間隔の頻度と共に増大することに気が付くでしょう。 たとえば、Statspackは、1時間ごとの間隔で92パーセントのDBHRを報告しますが、図3に示されるとおり、2分間隔で比率がサンプリングされたときは、大きい変動を示します。

Statspackレポート、2分間隔

一般ガイドラインとして、ホストで使用可能なすべてのメモリーは、チューニングされるものとし、db_cache_size は、収穫逓減の点までRAMリソースを割り当てます(図4参照)。 その点では、追加バッファ・ブロックはバッファのヒット率を大幅に高めません。

収穫逓減の点まで割り当てられたRAMリソース

新規v$db_cache_adviceビューは、Oracle7に導入されたx$kcbrbhという旧ユーティリティに類似しています。それは、バッファのヒットの追跡に使用されていました。同様に、x$kcbcbhビューは、バッファーのミスの追跡に使用されていました。 データ・バッファのヒット率は、v$db_cache_adviceのものと類似したデータを提供できます。したがって、大部分のOracleチューニング専門家は、データ・バッファの有効性の監視に両ツールを使用できます。

リスト5のスクリプトは、v$db_cache_adviceユーティリティが使用可能になり、データベースが典型的結果を示すことができる程度に長時間稼動した場合の、キャッシュ・アドバイス機能の実行に使用できます。 このスクリプトを使用し、2k、4k、8k、16kおよび32kのデータ・バッファを含むすべてのバッファ・プールに対しキャッシュ・アドバイスを取得できます。

スクリプトからの出力は次のとおりです。 値は、現在値の10パーセントからdb_cache_sizeの現在値の2倍までの範囲であることに注意します。

                                Estd Phys    Estd Phys
 Cache Size (MB)      Buffers Read Factor        Reads
---------------- ------------ ----------- ------------
              30        3,802       18.70  192,317,943 <- 10% size

              60        7,604       12.83  131,949,536
              91       11,406        7.38   75,865,861
             121       15,208        4.97   51,111,658
             152       19,010        3.64   37,460,786
             182       22,812        2.50   25,668,196
             212       26,614        1.74   17,850,847
             243       30,416        1.33   13,720,149
             273       34,218        1.13   11,583,180
             304       38,020        1.00   10,282,475 Current Size
             334       41,822         .93    9,515,878
             364       45,624         .87    8,909,026
             395       49,426         .83    8,495,039
             424       53,228         .79    8,116,496
             456       57,030         .76    7,824,764
             486       60,832         .74    7,563,180
             517       64,634         .71    7,311,729
             547       68,436         .69    7,104,280
             577       72,238         .67    6,895,122
             608       76,040         .66    6,739,731 <- 2x size

図4で示されるとおり、データ・バッファの最適設定は、追加バッファの効果が減少する点です。 この最適点は時間経過とともに変化します。そのため、現在の処理ニーズに応じてデータ・バッファのサイズを変えられるよう、事前のSGA再構成が必要です。

傾向分析の場合、DBHRの変動は重要ではなく、データ・バッファの平均ヒット率は、2つの要素に従って生成できます。 曜日別の平均DBHRおよび時間別の平均DBHR

変化は、データ・バッファ内で急激に発生することに留意してください。長期分析により、データベース内の処理問題の傾向を示す情報が提供されることがあります。 ほとんどのOracleデータベースは、シグネチャと呼ばれる定期的処理スケジュールにリンクされたパターンを示します。

StatspackのDBHR時間平均スクリプトの出力は次のとおりです。 レポートは、6か月のデータ収集に基づき、日々の平均ヒット率を示します。 データベースのDBHRシグネチャは、このデータがスプレッドシートでプロットされた場合に明らかになります。



hr   BHR
-- -----
00   .94
01   .96
02   .91
03   .82
04   .80
05   .90
06   .94
07   .93
08   .96
09   .95
10   .84
12   .91
13   .96
14   .95
17   .97
18   .97
19   .95
20   .95
21   .99

22   .93
23   .94

データのプロットは、図5で示され、繰返し見られる興味深い傾向がいくつか存在します。

データのプロット

グラフで明らかなとおり、DBHRは、毎日午前3時、午前4時および午前10時に90パーセント以下に下がります。 この問題を解決するため、DBAは、毎日、RAMをdb_cache_sizeに追加するよう動的調整をスケジュール設定するとができます。

類似スクリプトは、リスト6に示されるとおり、曜日別の平均DBHRを生じます。スクリプトの出力は次のとおりです。

DOW         BHR
--------- -----
sunday      .91
monday      .98
tuesday     .93
wednesday   .91
thursday    .96
friday      .89
saturday    .92

このデータをスプレッドシートに貼り付け、 図6に示されるとおり、グラフ化できます。

データのグラフ

このレポートは、断続的または定期的バッファ・シグネチャの確認に有効です。また、グラフは、月曜日と金曜日にdb_cache_size を増やす必要性を明確に示しています。 その理由を理解するには、Statspackを使用して該当する曜日と他の曜日の違いを調査します。

次に、オブジェクトを自動的に識別し、完全RAMキャッシングのためにKEEPプールに割り当てる別のセルフ・チューニング方式について調べてみます。

KEEPプール割当ての自動化

Oracleマニュアルによると、「KEEPプールに入れるための適切なセグメントは、DEFAULTバッファ・プールのサイズより10%小さく、システムの全I/Oの1%以上を占めるセグメントです。」 データ・バッファのサイズの10%未満のセグメントを検索することは簡単ですが、Oracleには、セグメント・レベルでI/Oを追跡する直接メカニズムはありません。 1つの解決策は、個々のセグメントを分離された表領域に入れることです。その場合、Statspackは、合計I/Oを示しますが、この方法は、数百ものセグメントがある複雑なスキーマの場合は実用的ではありません。

KEEPの目的は完全キャッシングであるため、小さく、アクティビティの量の不均衡なオブジェクトを探す必要があります。 このガイドラインを使用し、次のようなオブジェクトをキャッシュすることを検討してみます。

  • データ・バッファの全サイズの10%を以上を消費するオブジェクト
  • すでに50%以上のオブジェクトがデータ・バッファ内に存在する(x$bh問合せに基づく)

これらのオブジェクトを識別するため、小さい表と全表スキャンを検索するデータベース内のすべてのSQLを説明することから開始します。 次に、データ・バッファ・キャッシュを繰返し調べ、RAMのブロックの80%を超えるオブジェクトを確認します。

KEEPプールの表および索引を識別するための最善の方法は、データ・バッファの現在のブロックを調べることです。 この問合せの場合、ルールは簡単です。 データ・バッファ内のデータ・ブロックの80%以上を持つオブジェクトは、おそらく完全にキャッシュされます。

値しない表または索引がこの基準を満たす見込みはほとんどありません。 バッファ内容は急速に変化するため、終日何度もこのスクリプトを起動する必要があります。

リスト7のスクリプトは、1時間ごとにdbms_jobを介して起動でき、KEEPプールの候補の監視を自動化できます。 候補を検索するたびに、DBAは、構文を実行し、新規オブジェクトに対応するようKEEP プールのサイズを調整します。 スクリプトからの出力は次のとおりです。



alter TABLE LIS.BOM_DELETE_SUB_ENTITIES storage (buffer_pool keep);
alter TABLE LIS.BOM_OPERATIONAL_ROUTINGS storage (buffer_pool keep);
alter INDEX LIS.CST_ITEM_COSTS_U1 storage (buffer_pool keep);
alter TABLE ISM3.FND_CONCT_PROGRAMS storage (buffer_pool keep);
alter TABLE ISM3.FND_CONCT_REQUESTS storage (buffer_pool keep);
alter TABLE IS.GL_JE_BATCHES storage (buffer_pool keep);
alter INDEX IS.GL_JE_BATCHES_U2 storage (buffer_pool keep);
alter TABLE IS.GL_JE_HEADERS storage (buffer_pool keep);

KEEPプールへの割当て用セグメントを識別した際、プールに割り当てられたセグメントの完全なキャッシュに必要なブロックを確保するために、db_keep_cache_sizeパラメータを調整する必要があります。

自動化アプローチには多くの例外があることは言うまでもありません。 たとえば、これらのスクリプトでは、表パーティションやその他のオブジェクト型は処理されません。 このスクリプトは、KEEPプールのキャッシング方針のフレームワークとしてのみ使用し、そのままでは起動しないでください。

今後のデータベースのセルフ・チューニング

自動チューニングを簡単にするOracle Database 10g の新機能は数多くありますが、最も直接的に関連する機能は、新しいdbms_advisor PL/SQL パッケージおよびJava pool advisory utilityです。

  • Java Pool Advisory — このユーティリティは、RAMフレームを追加する、またはjava_pool_sizeメモリー領域から削除する効果を予測する場合に有効です。
  • SQLAccess Advisor — 新規dbms_advisorパッケージのOracle Database 10g の新機能です。索引およびマテリアライズド・ビューの作成、メンテナンスおよび使用に関するアドバイスを提供します。
  • The tune_mview advisor utility — Oracle Database 10g dbms_advisorパッケージのもう1つの新規コンポーネントです。SQL問合せのハイパーチャージにどのマテリアライズド・ビューを使用できるかについての専門家のアドバイスを提供します。

Oracleが提供するalter system文などの強力なツールを活用して、DBAの定型業務を自動化してください。 それにより、Oracleデータベース管理のより複雑な分野を追求するための時間を確保できます。

Donald K. Burleson [don@burleson.cc] は、世界でもトップクラスのOracleデータベースの専門家の一人です。 同氏は、19冊の書籍の著者であり、全国向け雑誌で100以上の記事を発表し、主要なOracleデータベース定期刊行物であるOracle Internalsの編集長でもあります。  Burleson氏の最新の著書は、Rampant TechPressから出版された『 Creating a Self-Tuning Database』です。. 同氏のWebサイトは、http://www.dba-oracle.comhttp://www.remote-dba.net/です。

表1
RAM領域ストレス過剰な状態過剰割当て状態
Shared pool Library cache misses No misses
Data buffer cache Hit ratio < 90% Hit ratio > 95%
PGA aggregate High multi-pass exec 100% optimal executions


この文書はあくまでも参考資料であり、掲載されている情報は予告なしに変更されることがあります。 
万一、誤植などにお気づきの場合は、オラクル社までお知らせください。オラクル社は本書の内容に関していかなる保証もしません。また、本書の内容に関連したいかなる損害についても責任を負いかねます。
Copyright © 2003, Oracle Corporation. All Rights Reserved.
Oracle Corporation発行の「Self-Tuning Oracle9i Database: Oracle SGA 」の翻訳版です。