Secrets of Oracle9i Database

セルフ・チューニングOracle9i Database: Oracle SGA

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

Oracle Database 10gでは、これまで以上にDBAの定型業務が自動化されています。 ここで説明するOracle9i Databaseの自己管理方法は、新規リリースの機能の一部をエミュレートしています。

このドキュメントに付属するサンプル・コードをダウンロードしてください(リスト1〜7)。

DBAのセルフ・チューニングの試みがより高度化するにつれて、より多くの Oracleメトリックもセルフ・チューニング方式になる可能性があります。 Oracle Database 10g では、これまでにないほどセルフ・チューニング機能が多彩になっています。

たとえば、Oracle Database 10g の動的メモリー割当て機能により、セルフ・チューニングOracle SGAを作成できます。 デモンストレーションにより、このドキュメントでは、Oracle 9i DatabaseのOracle インスタンスを調べる方法について説明し、サーバー上およびデータベース内の処理要求に応じて、sort_area_sizeまたはpga_aggregate_targetlarge_pool_size, sga_max_sizeおよびdb_cache_sizeのメモリー領域を調整します。 ここで説明する方法は、ある期間にわたってメモリー領域を監視し、システム・リソース使用のシグネチャを開発するためのStatspackの使用に基づいています。

現在の処理上のニーズに応じて、Oracle9i Databaseを自動再構成するためのインテリジェント・メカニズムの作成方法も説明し、サンプル・コードを提供します。それによって、Oracle Database 10g 機能を実際にエミュレートする独自の自動化スクリプトを記述できます。たとえば、頻繁に使用される小さいセグメントを自動識別し、完全キャッシュのためにそのセグメントをKEEPプールに割り当てるスクリプトを示します。 (注意(重要): このエミュレーションは、外部動作のみにかかわり、新規リリースの内部実装は反映していません。) データベースはそれぞれ異なるため、これらのスクリプトは、わかりやすくするために意図的に短縮し、単純化してあります。 したがって、その例を拡張し、各自の環境に適したカスタム自動化スクリプトを書く必要があります。

自動セルフ・チューニングを有効に活用できるショップは、次のようなショップです。

  • バイモダル・システム — オンライン・トランザクション処理(OLTP)とデータ・ウェアハウス処理モードが交互に扱うシステムにとっては、特にセルフ・チューニングRAM領域が有効です。
  • 32ビット・ショップ — 32ビットのサーバーを稼働させているショップは、RAM領域のサイズ(最大約1.7GBのサイズ)の制約を受けます。 これらのショップの場合、RAMリソースを最も効果的に使用することは特に重要です。

非常に大きいdb_cache_sizeを持つことにはマイナス点もあることに注意してください。. データへの直接アクセスは、ハッシュ法を使用して行われますが、データベースは、時々、RAMキャッシュのすべてのブロックを調べる必要があります。

  • 高失効化付きシステム — プログラムが切捨て表を発行する場合、一時表を使用する場合、または大きいデータ・パージを実行する場合、Oracle では、常に、db_cache_sizeのすべてのブロックを一掃して使用済みブロックを除去する必要があります。 このアプローチは、10g Bを上回るdb_cache_sizeを持つシステムでは過剰なオーバーヘッドを生じることがあります。
  • 高更新システム — データベース・ライター(DBWR)プロセスは、非同期書込みを実行する際、db_cache_sizeのすべてのブロックを一掃する必要があります。 大きなdb_cache_sizeを持つことにより、データベース・ライターに過剰な作業が発生することがあります。

先ず、セルフ・チューニング・データベースの作成の原理を再確認します。

セルフ・チューニングの原理

Oracleインスタンスを再構成するための最も一般的な方法の1つは、dbms_job、UNIX cronなどの外部スケジューラで起動するスクリプトを使用する方法です。 簡単な事例を説明するため、昼間はOLTPモード、夜間はデータ・ウェアハウス・モードで動作するデータベースを考えてみてください。 この型のデータベースの場合、Oracleインスタンス内で行われている処理の型に最適の構成に合せてインスタンスSGAメモリーを再構成するようジョブをスケジュール設定できます。

リスト1には、意思決定支援処理用にOracleを再構成する場合に使用するUNIXスクリプトがあります。 データ・ウェアハウス・アクティビティに対処するための、shared_pool, db_cache_sizeおよびpga_aggregate_targetの構成の重要な変更に注意してください。 このスクリプトは、毎晩午後6時に起動されるdbms_jobでをスケジュール設定しています。

リスト1には、セルフ・チューニングOracle Database 10g の基盤を確立するalter systemコマンドがあります。 RAMは高価なOracleサーバー・リソースであり、DBAは、サーバー上のRAMリソースをに完全に割り当てる責任があります。 利用されていないRAMは、高価なハードウェア・リソースを無駄にしていることになります。

完全に割り当てられている場合でも、RAMの過剰割当ては無駄になります。 たとえば、200mだけが必要な場合にshared_pool_size=400mを割り当てることは効率的ではありません。RAMは、db_cache_sizeなどのSGAの別の領域で使用できるためです。

RAMの再構成の概要を説明するため、十分に割り当てられずデータ・バッファのヒット率が悪い16Kのデータ・バッファおよび過剰割当てされてデータ・バッファのヒット率が良い32Kのデータ・バッファを使った次の例を考えてみてください(図1参照)。

Over-allocated and under-allocated RAM regions
図1: 過剰割当ておよび割当て不十分のRAM領域

alter systemコマンドを使用し、最も必要なところにRAMを再割り当てるために、データ・バッファ間でRAMを調整できます(図2参照)。

Dynamic re-allocation of RAM in Oracle10g
図2: RAMの動的再割当て

動的SQL、dbms_jobおよびシェル・スクリプトをはじめとした多くの型のOracleスクリプトでalter systemコマンドを使用できます。 リスト2は、RAMキャッシュ・サイズを調整するシンプルなSQL*Plusスクリプトです。このスクリプトでは、キャッシュの名前とサイズの入力が要求され、RAM領域のサイズ調整のために、適切なalter systemコマンドが発行されます。 出力は次のとおりです。

SQL> @dyn_sga

Enter cache to decrease: shared_pool_size
Enter cache to increase: db_cache_size
Enter amount to change: 1048576

alter system set shared_pool_size = 49283072;             
System altered.                      

alter system set db_cache_size = 17825792;       

System altered. 

Oracle Database 10g でRAM領域がどのように容易に変更されるかがわまりました。次にRAM領域の自動調整を起動するためのルールをいくつか調べてみます。

動的再構成をトリガーする時期

スクリプトを監視しているインスタンスが過剰ストレス状態のRAM領域を示すたびに、どの領域からRAMを流用するかを選択する必要があります。 表1は、SGAの3つの主要な領域の動的メモリー変更をトリガーするしきい値条件のサンプルを示します。 それぞれのシステムは異なるため、個別のニーズに応じてこれらのしきい値を調整する場合もあります。 たとえば、多くのショップは、複数のブロック・サイズを実装し、db_32k_cache_size(索引テーブル・スペース用)、db_keep_cache_size(頻繁に参照される小さいオブジェクト用)などに対し個別のRAM領域があります。

データベースのニーズは、実行中のSQLのニーズに応じて常に変わることに留意することが重要です。午前9時の最適SGAは、午後3時には最適になるとはかぎりません。 処理特性の変更を見るには、Statspackレポートを実行してOracle変更RAMストレージが必要な時間を正確に特定します。 v$db_cache_advice, v$pga_target_advice, v$java_pool_adviceおよびv$db_shared_pool_adviceユーティリティを実行して、RAM領域のサイズ変更から得られる効果の限度を調べることもできます。

自動動的SGA再構成の一般的なアプローチの1つは、傾向を識別する方法です。 処理特性が変わる時期を予測する場合は、Statspackを使用でき、暫定的なSGAの変更を起動するために、dbms_jobパッケージまたは動的SQLを使用できます。 次に、傾向をベースにしたアプローチを検討します。

システム・シグネチャの開発

傾向をベースにした再構成の共通アプローチでは、Statspack履歴データを使用して予測がつく傾向を開発し、これらの傾向を使用してシグネチャをベースにしたデータベースを変更します。

このアプローチは、組立に必要なときに工場に部品が供給されるかんばん方式の生産に類似しています。 Oracle Database 10gにより、DBAは処理ニーズを予想でき、適切な市場介入を定期的にスケジュール設定できます。それにより、SGAリソースは、処理変更に合せてかんばん方式で確実に供給できます。

セルフ・チューニングOracleのメモリー領域は、いくつかのOracleパラメータの値を変更する必要があります。 データベースのあらゆる面の構成を管理するOracle Database 10gのパラメータは250以上ありますが、自動化されたOracle SGAチューニングにとって重要なパラメータは少数です。

  • db_cache_size db_cache_size は、Oracle SGAのデータベース・ブロック・バッファの数を決め、Oracleメモリーにとって最も重要な単一パラメータを表します。
  • db_keep_cache_size — このデータ・バッファ・プールは、Oracle8idb_block_buffersサブプールでしたが、Oracle9i Databaseから別個のRAM領域になりました。
  • db_nn_cache_size — Oracle Database 10gには、別個のデータ・バッファ・プールがあるため、データの分離、異なるI/O特性を持つオブジェクトの分離に使用できます。
  • shared_pool_sizeshared_pool_size は、SQL領域およびデータ・ディクショナリ・キャッシングをはじめとしたシステムの全ユーザーが共有するプールを定義します。
  • pga_aggregate_target pga_aggregate_target は、システム全体のソーティングおよびハッシュ結合に予約されたRAM領域を定義します。

ご覧のとおり、Oracleデータベースの健全性の最も重要な基準に正確に合せることは簡単ではありません。 ライブラリ・キャッシュ内の傾向を調べて開始し、shared_pool_sizeを自動調整する方法を決めましょう。

Oracle Database 10g アドバイザリ・ユーティリティの使用

Oracle Database 10g には、RAM領域サイズの変更から正確に変更を予測する完全アドバイザリ・ユーティリティがあります。 Oracle Database 10g アドバイザリ・ユーティリティには次のようなものがあります。

  • Shared pool advice — v$shared_pool_advice
  • PGA target advice — v$pga_target_advice
  • Data cache advice — v$db_cache_advice
  • Java Pool advice — v$java_pool_advice

これらのユーティリティは、セルフ・チューニングの変更が正当とされることを確実にするための優れた方法です。 次の項では、アドバイザリ・ユーティリティの起動および解釈の方法を示します。問題なく出力を解釈できる場合、自動化スクリプトを記述し、アドバイスを生成し、出力を解釈し、RAM領域のサイズを自動的に変更できます。

共有プール・アドバイス・ユーティリティ

このアドバイザリ機能は、Oracle9i Database Release 2では拡張され、v$shared_pool_adviceと呼ばれる新しいアドバイスを含みます。今後のリリースではすべてのSGA RAM領域にまで拡張される予定です。

Oracle9i Database Release 2以降、v$shared_pool_adviceビューにより、現在値の10%から現在値の200%に共有プールのサイズを変更した場合の、SQL解析の差異の限度が表示されます。

共有プール・アドバイス・ユーティリティの構成は非常に簡単です。 このユーティリティをインストールすると、シンプルなスクリプトを実行でき、v$shared_pool_adviceビューを検索し、様々なshared_poolサイズに対するSQL解析の変更の限度を調べることができます。 次のスクリプトからの出力は、shared_pool_size パラメータの動的増加または減少から変更を知らせます。

-- ************************************************
-- Display shared pool advice
-- ************************************************

set lines  100
set pages  999

column 	c1	heading 'Pool |Size(M)'
column 	c2	heading 'Size|Factor'
column 	c3	heading 'Est|LC(M)  '
column 	c4	heading 'Est LC|Mem. Obj.'

column 	c5	heading 'Est|Time|Saved|(sec)'
column 	c6	heading 'Est|Parse|Saved|Factor'
column	c7	heading 'Est|Object Hits'   format 999,999,999


SELECT
   shared_pool_size_for_estimate	c1,
   shared_pool_size_factor		c2,
   estd_lc_size			c3,
   estd_lc_memory_objects		c4,
   estd_lc_time_saved		c5,
   estd_lc_time_saved_factor	c6,
   estd_lc_memory_object_hits	c7
FROM 
   v$shared_pool_advice;

                                                   Est        Est
                                                  Time      Parse
     Pool        Size        Est     Est LC      Saved      Saved          Est
   Size(M)     Factor    LC(M)    Mem. Obj.      (sec)     Factor  Object Hits
---------- ---------- ---------- ---------- ---------- ---------- ------------
        48         .5         48      20839    1459645          1  135,756,032

        64      .6667         63      28140    1459645          1  135,756,101
        80      .8333         78      35447    1459645          1  135,756,149
        96          1         93      43028    1459645          1  135,756,253
       112     1.1667        100      46755    1459646          1  135,756,842
       128     1.3333        100      46755    1459646          1  135,756,842
       144        1.5        100      46755    1459646          1  135,756,842
       160     1.6667        100      46755    1459646          1  135,756,842
       176     1.8333        100      46755    1459646          1  135,756,842
       192          2        100      46755    1459646          1  135,756,842

ここでは、現行サイズの50%から現行サイズの200%までの範囲の共有プールの統計が表示されます。 これらの統計からshared_pool_sizeの適正サイズがわかります。 自動化alter systemコマンドでSGA領域のサイズを自動化している場合、この出力を作成し、その結果を解析するためにプログラムを書くことは、共有プールとライブラリ・キャッシュに常に十分なRAMがあることを確保するために有効な方法です。 次に、ある期間にわたって共有プールの使用量を追跡できる方法を調べ、共有プールの調整が必要な時期を予測できる「シグネチャ」を開発します。

Statspackを使用し、リスト3に表示すとおり、ライブラリ・キャッシュのミス率が規定レベル以下に下がった時期のリストを作成できます。 次の出力は、毎日午前9時から午前10時までの共有プールのRAMが繰り返し不足していることを示します。

 

                        Cache   Misses    Library Cache
Yr. Mo Dy  Hr.  execs   While Executing   Miss Ratio

--------------- ------- --------------- -------------
2001-12-11 10    10,338          6,443        .64
2001-12-12 10   182,477         88,136        .43
2001-12-14 10   190,707        101,832        .56
2001-12-16 10    72,803         45,932        .62

この例では、DBAは、不足した期間中、shared_pool_size に対し追加RAMをスケジュール設定する必要があります。

pga_aggregate_targetシグニチャの開発

Oracle Database 10g の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使用量に関するインスタンス・レベルのサマリー統計および自動メモリー・マネージャを表示します。 即座に概要を把握できるよう、シンプルな問合せは、OracleDatabase 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使用量が確保されます。

次のページへ

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