Oracle Database 10gからOracle Database 11gへのアップグレード：オプティマイザ機能の詳細

(1)

Oracleホワイト・ペーパー 2009年11月

Oracle Database 10g から 11g へのアップ

グレード：オプティマイザ機能の詳細

(2)

はじめに

... 1

11g

でのオプティマイザおよび統計の新機能 ... 2

Init.ora

パラメータ ... 2

オプティマイザ統計と

DBMS_STATS

パッケージの変更 ... 3

自動統計収集ジョブ ... 9

SQL Plan Management ... 10

Adaptive Cursor

の共有（バインド・ピーキング） ... 14

SQL Test Case Builder ... 15

コストベースの新しい変換 ... 15

アップグレードに対する準備 ... 18

既存の実行計画の取得 ... 19

既存のオプティマイザ統計情報の取得 ... 20

アプリケーションのテスト ... 21

アップグレード前のチェックリスト ... 21

アップグレード後の作業

... 22

SQL Plan Management

への

10g

計画の移入 ... 22

SQL

計画ベースラインの使用の確認 ... 24

アップグレード後のオプティマイザ統計情報の処理 ... 25

アップグレード後のチェックリスト

... 26

パフォーマンスの低下した

SQL

文の修正 ... 27

結論 ... 31

(3)

はじめに

オプティマイザの目的は、問合せに対してもっとも効率的な実行計画を特定することにあります。

実行計画は、問合せの構造や収集したデータに関する統計情報に基づき、Oracleデータベースの機能を利用して決定されます。アップグレードの後でオプティマイザが生成する実行計画は、ほとんどのSQL文に対して同等またはそれ以上のパフォーマンスを発揮することが見込まれます。しかし、

一部のSQL文に対して、以前のリリースよりも新リリースのパフォーマンスが低くなる実行計画が生成される可能性があります。実行計画によるこれらのパフォーマンス低下の根本原因を探すことは、非常に困難な作業になる可能性があります。

このホワイト・ペーパーの目的は、オプティマイザを取り巻く不可解な事象を解決し、Oracle Database 10gからOracle Database 11gへのアップグレードに備えることにあります。本書では、オプティマイザの新機能を紹介するとともに、計画変更に関連して生じるパフォーマンス低下を回避するため、

アップグレードの前後で実施すべき手順について説明します。このホワイト・ペーパーは、次の 3 つのセクションに分かれています。

• はじめのセクションでは、統計管理を含む11gのオプティマイザ機能を紹介します。

• 2番目のセクションでは、アップグレードの前に実行すべき手順について説明します。

• 最後に、3番目のセクションではアップグレード後に実施すべき手順と、計画変更によって発生するパフォーマンス低下への対処方法について説明します。

(4)

11g でのオプティマイザおよび統計の新機能

Init.oraパラメータ

Oracle Database 11gでは、オプティマイザとその新機能を管理するいくつかの初期化パラメータが新

しく導入されています。ここからは、新しいパラメータの詳細について説明します。

OPTIMIZER_USE_INVISIBLE_INDEXES

Oracle Database 11gでは、非表示索引と呼ばれる新機能が導入されました。この機能を使用すると、

DBAは実行計画に影響を及ぼすことなく表に索引を作成できます。したがって、アプリケーション・

パフォーマンスへの影響はありません。オプティマイザは、表に対するアクセス・パスとして非表示索引を使用することはできません。この非表示索引を潜在的なアクセス・パスとしてテストするには、セッション内でOPTIMIZER_USE_INVISIBLE_INDEXESをTRUE（デフォルト値はFALSE）

に設定する必要があります。その後で、この索引を使用することでパフォーマンスが上がる可能性のあるSQL文を実行して、パフォーマンスをテストします。この索引が有効であると分かった場合、

表示索引として記録できます。有効でない場合は、アプリケーション・パフォーマンスに影響を与えることなくこの索引を削除できます。

OPTIMIZER_USE_PENDING_STATISTICS

従来のオプティマイザ統計は、収集されるとすぐに適切なディクショナリ表に公開（書込み）され、

オプティマイザによって使用されていました。Oracle Database 11gでは、オプティマイザ統計を収集しても、すぐに公開しないように設定できます。統計情報を通常のディクショナリ表ではなく保留表に格納することで、公開前のテストが可能になりました。これらの保留統計をテストするには、

alter sessionコマンドを使用して、OPTIMIZER_USE_PENDING_STATISTICSにTRUEを設定します。

その後で、新しい統計情報による影響を受けると考えられるSQL文を実行します。統計を公開する前にテストすることで、DBAは不完全な統計や不正確な統計によって実行計画が低下することを回避する機会が得られます。

保留統計に関して、詳しくは後述する新機能のセクションを参照してください。

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES

Oracle Database 11gでは、すべての計画変更がパフォーマンス向上につながるように、SQL Plan

Management（SPM）と呼ばれる新機能が導入されました。OPTIMIZER_CAPTURE_SQL_PLAN_

BASELINESをTRUE（デフォルト値はFALSE）に設定すると、システム上の繰返し可能なすべてのSQL 文に対してSQL計画ベースラインが自動的に取得されます。解析時に見つかった実行計画は、承認された計画としてSQL計画ベースラインに追加されます。

(5)

SPMに関して、詳しくは後述する新機能のセクションを参照してください。

OPTIMIZER_USE_SQL_PLAN_BASELINES

SPMに取得されたSQL文にはそれぞれ1つのSQL計画ベースラインがあり、その中に1つ以上の検証済み実行計画または既知の実行計画が含まれます。OPTIMIZER_USE_SQL_PLAN_BASELINESに TRUE（デフォルト）が設定されている場合、SQLのコンパイル時に別の計画が見つかっても、オプティマイザはこれらの既知の計画のみを使用します。これにより、計画が変更された場合は必ず、

パフォーマンスが向上することが確認されてから新しい計画が使用されるようになります。

新しいINIT.ORAパラメータのサマリー

パラメータ名 11gでのデフォルト値 OPTIMIZER_USE_INVISIBLE_INDEXES FALSE OPTIMIZER_USE_PENDING_STATISTICS FALSE OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES FALSE OPTIMIZER_USE_SQL_PLAN_BASELINES TRUE

オプティマイザ統計とDBMS_STATSパッケージの変更

統計を収集するための ANALYZE コマンドは、正式に廃止されました。代わりにDBMS_

STATS パッケージを使用してください。

オプティマイザ統計を収集および管理するための新しいPL/SQLパッケージとして、DBMS_STATSは Oracle8iで導入されました。DBMS_STATSは、統計情報を収集する方法としてオラクルが推奨している方法です。Oracle Database 11gではDBMS_STATSパッケージが拡張され、新しい種類の統計収集とデータ監視が可能になりました。また、自動統計収集ジョブも変更され、11gではデフォルトで有効化されています。

DBMS_STATSパッケージの新しいサブプログラム

DBMS_STATSプロシージャが使用するパラメータ値のプリファレンス設定

以前のリリースでは、DBMS_STATS.GATHER_*_STATSプロシージャが使用するパラメータのデフォルト値を変更するには、DBMS_STATS.SET_PARMプロシージャを使用する必要がありました。

この変更の有効範囲は、すべての後続処理に適用されていました。Oracle Database 11gでは、DBMS_

STATS.SET_PARAMプロシージャが廃止され、新しい一連のプロシージャで置き換えられました。

新しいプロシージャを使用すると、表、スキーマ、データベース、およびグローバル・レベルで各パラメータのプリファレンスを設定できます。これらの新しいプロシージャはDBMS_STATS.SET_

*_PREFSという名前であり、はるかにきめ細かいコントロールが可能になります。変更できるパラメータは、次のとおりです。

(6)

AUTOSTATS_TARGET（SET_GLOBAL_PREFSのみ）

CASCADE DEGREE

ESTIMATE_PERCENT METHOD_OPT NO_INVALIDATE GRANULARITY PUBLISH INCREMENTAL STALE_PERCENT

SET_TABLE_PREFSプロシージャを使用すると、指定した表に対してのみ、DBMS_STATS.GATHER_

*_STATSプロシージャが使用するパラメータのデフォルト値を変更できます。

SET_SCHEMA_PREFSプロシージャを使用すると、指定したスキーマ内に存在するすべての表に対して、DBMS_STATS.GATHER_*_STATSプロシージャが使用するパラメータのデフォルト値を変更できます。実際は、このプロシージャによって、指定されたスキーマ内の各表に対してSET_TABLE_

PREFSが呼び出されます。SET_TABLE_PREFSコールを使用しているため、このプロシージャを実行した後に作成された新規オブジェクトに対しては有効ではありません。新規オブジェクトに対しては、すべてのパラメータにグローバル・プリファレンスが適用されます。

SET_DATABASE_PREFSプロシージャを使用すると、データベース内のすべてのユーザー定義スキーマに対して、DBMS_STATS.GATHER_*_STATSプロシージャが使用するパラメータのデフォルト値を変更できます。実際は、このプロシージャによって、ユーザー定義スキーマ内の各表に対して SET_TABLE_PREFSが呼び出されます。SET_TABLE_PREFSコールを使用しているため、このプロシージャを実行した後に作成された新規オブジェクトに対しては有効ではありません。新規オブジェクトに対しては、すべてのパラメータにグローバル・プリファレンスが適用されます。また、ADD_SYS パラメータにTRUEを設定すると、Oracle所有のスキーマ（sys、systemなど）を対象に含めることもできます。

SET_GLOBAL_PREFSプロシージャを使用すると、既存の表プリファレンスがないすべてのデータベース・オブジェクトに対して、DBMS_STATS.GATHER_*_STATSプロシージャが使用するパラメータのデフォルト値を変更できます。表プリファレンスが設定されているか、またはGATHER_*_STATS コマンド内でパラメータが明示的に指定されている場合を除いて、すべてのパラメータのデフォルト値としてこのグローバル設定が使用されます。このプロシージャによる変更は、プロシージャを実行した後に作成されたすべての新規オブジェクトに対しても有効です。新規オブジェクトに対して、すべてのパラメータにグローバル・プリファレンスが適用されます。また、グローバル・プリファレンスを使用して、AUTOSTAT_TARGETと呼ばれる追加パラメータにデフォルト値を設定できます。この追加パラメータを使用すると、自動統計収集ジョブ（夜間のメンテナンス・ウィンドウで実行）の対象となるオブジェクトをコントロールできます。このパラメータに設定できる値は、

ALL、ORACLE、AUTOのいずれかであり、デフォルト値はAUTOです。

(7)

DBMS_STATS.GATHER*_STATSは、次の優先順位に従ってパラメータ値を決定します。コマンド内で明示的に指定されたパラメータ値は、すべてに優先します。コマンド内でパラメータが指定されない場合、表レベルのプリファレンスがチェックされます。表プリファレンスが設定されていない場合、グローバル・プリファレンスが使用されます。たとえば、SHスキーマ内のSALES表に対してヒストグラムの作成を無効化する場合、次のプロシージャを使用します。

BEGIN

DBMS_STATS.SET_TABLE_PREFS('SH','SALES','METHOD_OPT','FOR ALL COLUMN SIZE 1');

END;

表統計のコピー

パーティション化された表では、非常に多くの場合、新しい空のパーティションが表に追加されると、すぐにそこにデータがロードされ始めます。このパーティションに対する統計情報が収集される前に、新しくロードされたデータに対する問合せが実行されると、Oracleは問合せに対してカーディナリティ見積りを案分する必要があります。カーディナリティ見積りを案分した場合、最適でない計画が使用される可能性があります。DBMS_STATS.COPY_TABLE_STATSプロシージャを使用すると、表に含まれるその他のパーティションから新しいパーティションへ、統計情報をコピーできます。列統計情報（最小値、最大値、NDV、ヒストグラムなど）、パーティション統計情報（行数、ブロック数など）、およびローカル索引の統計情報がコピーされます。パーティション化された列の最小値と最大値は、新規パーティションの正しい値を反映するように調整されます。次の例では、SALES_Q3_2000 がもとのパーティションであり、SALES_Q4_2000 がターゲット・パーティションになります。

BEGIN

DBMS_STATS.COPY_TABLE_STATS ('SH','SALES','SALES_Q3_2000', 'SALES_Q4_2000', FORCE=>TRUE);

END;

統計情報の拡張

実際のデータでは、多くの場合、同じ表内の異なる列に格納されたデータ同士に関係または相関関係があります。たとえばCUSTOMERS表において、CUST_STATE_PROVICE列の値はCOUNTRY_ID列の値による影響を受けます。これは、カリフォルニア州が米国にしか存在しないためです。今までのオプティマイザには、これらの現実世界での関係を把握する方法が備わっておらず、同じ表の複数の列がSQL文のWHERE句で使用された場合、誤った選択が計算される可能性がありました。拡張統計を利用すると、これらの現実世界での関係をオプティマイザに知らせることができます。

列グループに対して統計情報を作成することで、SQL文のWHERE句で一緒に使用されている列に対して、より正確な選択ガイドラインがオプティマイザに提供されます。列グループ内のすべての列がSQL文で使用されている必要はなく、一部の列が使用されていれば、オプティマイザは拡張統計を使用できます。統計情報をまとめて収集したい列グループを定義するには、DBMS_STATS.CREATE_

EXTENDED_STATSを使用します。いったんグループが作成されたら、表に対する統計情報が収集される際に、自動的にこの列グループの統計情報も収集されます。

(8)

SELECT DBMS_STATS.CREATE_EXTENDED_STATS(null,'customers', '(country_id, cust_state_province)');

FROM dual;

列グループを作成すると、システムによって生成された名前を持つ新しい列がuser_tab_col_

statistics表に追加されます。この新しい列が列グループを表しています。

また、式の内部に列が埋め込まれたWHERE句条件のカーディナリティをオプティマイザが見積もることは難しいため、式（関数を含む）に対する拡張統計を作成することもできます。たとえばWHERE 句条件に、UPPER(LastName)=:B1が含まれることが多い場合、UPPER(LastName)に対して拡張統計を作成すると良いでしょう。

BEGIN

DBMS_STATS.GATHER_TABLE_STATS(null,'customers',method_opt =>

'for all columns size skewonly for columns(upper(cust_last_name))');

END;

パーティション化された表に対する増分統計

パーティション化された表に対する統計情報の収集は、表レベルでの収集とパーティション・レベルでの収集で構成されています。Oracle Database 11gより前のリリースでは、新規パーティションの追加や複数のパーティションにわたるデータ変更を行う場合、表レベルの統計を更新するには表全体をスキャンする必要がありました。パーティション化された表は通常非常に大きいため、表全体をスキャンするには極めて高いコストがかかる場合があります。しかし、Oracle Database 11gでは増分グローバル統計が導入されたことにより、この問題が解決されました。通常パーティション化された表では、新しいパーティションが追加されると、追加されたパーティションにデータがロードされます。パーティションにデータがすべてロードされると、パーティション・レベルの統計を収集し、グローバル統計を更新して新しいデータを反映させる必要があります。パーティション表の INCREMENTALプリファレンス値をTRUEに設定し、DBMS_STATSのGRANULARITYパラメータを AUTOに設定した場合、新しいパーティションの統計を収集したり、グローバル表の統計を更新したりする際、表全体ではなく追加または変更されたパーティションのみがスキャンされます。増分グローバル統計を使用するために必要な手順は、以下のとおりです。

BEGIN

DBMS_STATS.SET_TABLE_PREFS('SH','SALES','INCREMENTAL','TRUE');

END;

BEGIN

DBMS_STATS.GATHER_TABLE_STATS('SH','SALES');

END;

(9)

増分グローバル統計は、表の各パーティションのシノプシスを保存することにより機能します。シノプシスはパーティションとパーティションに含まれる列の統計メタデータです。各シノプシスは

SYSAUX表領域に保存され、約 200KBを消費します。各パーティションのシノプシスを集約するこ

とでグローバル統計が生成されるため、表レベルの統計情報を収集するために全表スキャンを実行する必要はありません（図 1 を参照してください）。表に新しいパーティションを追加した場合、

このパーティションの統計を収集するだけで良いのです。グローバル統計は、新しいパーティションのシノプシスと既存のパーティションのシノプシスを集約することにより自動更新されます。

図1. 増分グローバル統計

統計情報の比較

新しいアプリケーションまたはアプリケーション・モジュールを配置する場合、標準的手法として、

本番に移行する前にテスト環境でアプリケーションのテストとチューニングが実施されます。しかし、テストを実施した場合でも、本番とテスト・システムでアプリケーションのSQL文に対する実行計画が異なる可能性はあります。実行計画がシステム間（テストと本番）で異なるおもな原因は、

システムごとにオプティマイザの統計情報が異なることにあります。Oracle Database 10g Release 2では、DIFF_TABLE_STATS_*関数を使用して、2つの異なるソース間で表の統計情報を比較できます。

次に、比較できる統計情報の例をあげます。

• ユーザー統計表とディクショナリ内の現在の統計情報

• 1つのユーザー統計表に含まれ、statidを使用して識別可能な2つの統計情報

• 2つの異なるユーザー統計表

• 過去の2つの時点

(10)

• 現在の統計情報と過去の時点での統計情報

• 保留統計情報とディクショナリ内の現在の統計情報

• 保留統計情報とユーザー統計表

また、この関数では、依存しているオブジェクト（索引、列、パーティション）の統計情報も比較されます。2つのソース間での統計情報の差異が、指定されたしきい値を超える場合、両方のオブジェクトの統計情報が表示されます。しきい値は関数の引数として指定でき、デフォルト値は10%です。

最初のソースに相当する統計情報が差分の割合を計算するための基準になります。

次の例では、EMP表に対する現在のディクショナリ統計情報と、統計表TAB1におけるEMPの統計情報を比較します。SQL文を実行すると、レポートのような出力が画面に表示されます。

SQL> select report, maxdiffpct from

table(DBMS_STATS.DIFF_TABLE_STATS_IN_STATTAB('SCOTT','EMP','TAB1' ));

パーティション・レベルの統計のロック

以前のリリースでは、表またはスキーマに対する統計情報をロックすることができました。いったんロックされた統計情報は、ロックが解除されるまで変更されることはありません。Oracle Database 11gのDBMS_STATSパッケージでは、パーティション・レベルで統計情報をロックおよびロック解除するための2 つの新規プロシージャが提供されています。これらの新しいプロシージャを使用すると、さらにきめ細かいコントロールが可能になります。

BEGIN

DBMS_STATS.LOCK_PARTITION_STATS('SH','SALES', 'SALES_Q3_2000');

END;

保留統計

すでに説明したとおり、Oracle Database 11gではオプティマイザ統計を収集しても、すぐに公開しないように設定できます。保留統計を有効化するには、DBMS_STATS.SET_*_PREFSプロシージャを使用して、保留統計を作成するオブジェクトのPUBLISHパラメータ値をTRUE（デフォルト）から FALSEへ変更する必要があります。

BEGIN

DBMS_STATS.SET_TABLE_PREFS('SH','SALES','PUBLISH','FALSE');

END;

次に通常どおり、オブジェクトの統計情報を収集します。

BEGIN

DBMS_STATS.GATHER_TABLE_STATS('SH','SALES');

END;

これらのオブジェクトから収集された統計情報は、USER_*_PENDING_STATSというディクショナリ・ビューを使用して表示できます。alter sessionコマンドを使用してOPTIMIZER_USE_PENDING_

STATS初期化パラメータをTRUEに設定し、必要な問合せを実行すると、これらの統計情報をテストできます。保留統計のテスト結果に問題がない場合、新しいPUBLISH_PENDING_STATSプロシージャを使用すると、保留統計を公開できます。

(11)

BEGIN

DBMS_STATS.PUBLISH_PENDING_STATS('SH','SALES');

END;

新しいサンプリング・アルゴリズム

オプティマイザに正確な統計情報を提供することは非常に重要であり、従来は表に含まれるすべての行が処理された場合に、もっとも正確な統計情報が収集されていました。しかし、大規模な表で全体スキャンを実行して統計情報を収集するには非常に多くの時間がかかるため、ほとんどの顧客にとって通常は選択肢になりません。実際、ほとんどの顧客は、素早く実行できるサンプリングを使用して統計情報を収集しています。ただし、詳細なテストを実施しない限り、どのようなサンプル・サイズを使用すれば正確な統計情報を取得できるのかを把握することは困難です。以前に、オラクルはAUTO_SAMPLE_SIZEを導入することでこの問題への対処を試みました。AUTO_SAMPLE_

SIZEを使用した場合、適切な統計情報を取得できるサンプル・サイズが自動的に決定されます。しかしAUTO_SAMPLE_SIZEには欠点があり、データに著しい偏りがある場合、統計情報の計算には不適切なサンプル・サイズがAUTO_SAME_SIZEによって決定される可能性があります。Oracle Database 11gでは、サンプリング・アルゴリズムが完全に新しくなりました。ハッシュ・ベースの新しいアルゴリズムは、確定的な統計情報を提供します。この統計情報は 10%のサンプルと同じ速度で、正確な統計計算を実現します。この新しいアルゴリズムは、いずれかのDBMS_STATS.GATHER_*_STATS プロシージャ内でestimate_percentにAUTO_SAMPLE_SIZEが指定されている場合に使用されます。

自動統計収集ジョブ

統計情報が収集されていなかったり、古くなったりしたデータベース・オブジェクトに対しては、

事前定義されたメンテナンス・ウィンドウ（平日は午後10時から午前2時、週末は午前6時から午前2時）内にOracle AutoTaskが実行され、自動的にOracleによって統計情報が収集されます。

このAutoTaskは内部プロシージャDBMS_STATS.GATHER_DATABASE_STATS_JOB_PROCを呼び出すことで、オプティマイザ統計を収集します。このプロシージャは、DBMS_STATS.GATHER_

DATABASE_STATSプロシージャでGATHER AUTOオプションを使用した場合と非常に良く似た方法で処理されます。おもな違いは、統計情報を必要とするデータベース・オブジェクトに対して、Oracle が内部的に優先順位を付ける点であり、これによって統計情報の更新をもっとも必要とするオブジェクトが最初に処理されます。DBA_AUTOTASK_CLIENT_JOBビューを参照すると、自動統計収集ジョブの存在が確認できます。

SELECT client_name, status FROM DBA_AUTOTASK_TASK

WHERE client_name like 'auto optimizer %';

表の統計情報が古くなったと見なされるのは、STALE_PERCENT（デフォルトは10%）に指定された値を上回る割合の行が変更された（挿入、削除、更新の総数）場合です。すべてのオブジェクトに対するDMLアクティビティはOracleによって監視されており、SGAに記録されています。監視情報は定期的にディスクに書き込まれ、*_tab_modificationsビューを通じて公開されます。

(12)

SELECT TABLE_NAME, INSERTS, UPDATES, DELETES FROM USER_TAB_MODIFICATIONS;

またDBMS_STATS.FLUSH_MONITORING_INFOプロシージャを呼び出すと、手動でこのデータを書き込むことができます。

自動統計収集ジョブはDBMS_STATSプロシージャを実行する際、デフォルトのパラメータ値を使用します。これらのデフォルト値を変更するには、DBMS_STATS.SET_GLOBAL_PREFSプロシージャを使用します。変更した値は、'SYS'を含むすべてのスキーマに適用されることに注意してください。'STALE_PERCENT'を変更するには、次のように指定します。

BEGIN

DBMS_STATS.SET_GLOBAL_PREFS('STALE_PERCENT','5');

END;

十分に確立された統計収集プロシージャをすでに使用している場合や、何らかの理由で自動統計収集を完全に無効化する必要がある場合、もっとも直接的な方法は次のようにGATHER_STATS_JOBを無効化することです。

BEGIN

DBMS_AUTO_TASK_ADMIN.DISABLE(

client_name => 'auto optimizer stats collection', operation => NULL,

window_name => NULL);

END;

自動統計収集ジョブを、主要なアプリケーション・スキーマに対して無効化する場合でも、ディクショナリ表に対しては有効にしておくことを検討してください。これを実行するには、DBMS_STATS.

SET_GLOBAL_PREFSを使用して、AUTOSTATS_TARGETの値をAUTOからORACLEに変更します。

SQL Plan Management

実行計画の安定は、常にオプティマイザ分野における究極の目標であり、過去のリリースではストアド・アウトラインやSQL Profileなどの安定性を高めるための機能がいくつか導入されてきました。

しかしながらこれらの手法では、データ量が変化した場合にオプティマイザがより良い実行計画を探すことができませんでした。Oracle Database 11gでは、SQL Plan Managementが導入されたことで、

実行計画の安定に対して決定的な対応が施されました。

SQL Plan Managementは、実行計画の変更によるランタイム・パフォーマンスの低下を防止します。

これを実現するため、承認済みの（信頼できる）実行計画だけが使用されます。計画は後で追跡および評価され、新しい計画が承認済み計画のパフォーマンスを上回る場合にのみ検証済みとして承認されます。SQL Plan Managementには、以下の3つの主要コンポーネントがあります。

(13)

1. SQL計画ベースラインの取得関連するすべてのSQL文に対する承認済みの実行計画を示す

SQL計画ベースラインを作成します。SQL計画ベースラインは、SYSAUX表領域のSQL

Management Baseの計画履歴に保存されます。

2. SQL計画ベースラインの選択

SQL計画ベースラインを使用して、承認済みの実行計画だけがSQL文で使用されるようにし、

SQL文に対する履歴内のすべての新しい実行計画を未承認計画として追跡します。計画履歴は、検証済みの計画と未承認の計画で構成されます。未承認の計画とは、未検証である（新たに検索されたが、検証されていない）か、または拒否された（検証されたが、パフォーマンス不足が明らかになった）ものです。

3. SQL計画ベースラインの展開

計画履歴に含まれる、特定の文に対する未検証の実行計画を評価して、承認または拒否を設定します。

図2. 繰返し可能なSQL文のステートメント・ログと計画履歴で構成されるSQL Management Base

(14)

SQL計画ベースラインの取得

SPMを有効にするには、実行計画をSQL Management Baseにシードする必要があります。これが、各 SQL文のSQL計画ベースラインになります。SQL Management Baseを移入するには、以下の2つの方法があります。

• 自動取得

• バルク・ロード

自動取得 - "オンザフライ"

初期化パラメータのOPTIMIZER_CAPTURE_SQL_PLAN_BASELINESをTRUE（デフォルト値はFALSE）に設定すると、自動取得を有効化できます。自動取得が有効になっている場合、すべての繰返し可能なSQL文に対して、SPMリポジトリは自動的に移入されます。繰返し可能なSQL文を識別するため、

最初のコンパイル時に、オプティマイザは各SQL文のID（SQLシグネチャ）をステートメント・ログに格納します。SQL文を再処理（実行またはコンパイル）する際にそのIDがステートメント・ログに存在する場合、この文は繰返し可能な文であることを意味します。SQL計画履歴は文に対して作成され、SQLテキスト、アウトライン、バインド変数、コンパイル環境など、現在の実行計画を再現するためにオプティマイザによって使用される情報が含まれます。現在の実行計画は最初のSQL計画ベースラインとして追加され、この計画は承認済みとして記録されます。承認された計画だけが使用されるため、後になってこのSQL文に対する新しい計画が見つかった場合、その実行計画は計画履歴に追加され、検証対象として記録されます。この実行計画のパフォーマンスが、現在のSQL計画ベースラインから選択された計画のパフォーマンスを上回る場合のみ、この実行計画は承認済みとして記録されます。

"オンザフライ"

での計画の取得、カーソル・

キャッシュや SQL Tuning Setを使用したSPMへの計画のバルク・

ロード、または別のシステムからの計画のインポートが可能です。

バルク・ロード

実行計画のバルク・ロードは、データベースを以前のバージョンからOracle Database 11gにアップグレードする場合や新しいアプリケーションを配置する場合に特に有効です。バルク・ロードは、自動計画取得と連携するか、その代わりとして実行できます。バルク・ロードされた実行計画は自動的に使用され、新しいSQL計画ベースラインが作成されるか、または既存のSQL計画ベースラインに追加されます。この新しいSQL計画ベースラインは承認済みとして記録されます。SQL Management

Baseへのバルク・ロードを実行する方法には、次の4種類があります。

1. 特定のSQL Tuning Set（STS）に対して、実行計画を移入します。

2. ストアド・アウトラインから実行計画を移入します。

3. 現在カーソル・キャッシュに格納されている実行計画を使用します。

4. ステージング表から既存のSQL計画ベースラインをアンパックします。

アップグレード中のバルク・ロードの使用について、詳しくはアップグレードに対する準備のセクションを参照してください。

(15)

SQL計画ベースラインの選択

SQL文がコンパイルされるたびに、オプティマイザはまず従来のコストベースの検索方法を使用して、

最適なコスト計画を構築します。初期化パラメータのOPTIMIZER_USE_PLAN_BASELINESがTRUE

（デフォルト）に設定されている場合、コストベースの計画を実行する前に、オプティマイザはSQL 文の計画ベースラインから一致する計画を検索します。この検索はメモリ内で実行されるため、アプリケーションへのオーバーヘッドはほとんどありません。一致する計画がある場合、その計画を使用します。一致する計画がない場合、新しく生成された計画が計画履歴に追加されます。この計画がSQL計画ベースラインとして承認されるには、先に検証される必要があります。新しく生成された計画を実行する代わりに、オプティマイザはこのSQL文に対して承認された各計画のコストを見積もり、もっとも低コストのものを選択します（1つのSQL計画ベースラインには、特定の文に対して複数の検証済み計画と承認済み計画が含まれます）。ただし、索引の削除などのシステム変更によって、承認されたすべての計画が再作成できなくなった場合、オプティマイザは新しく生成されたコストベースの計画を使用します。

SQL計画ベースラインの展開

計画は、必要に応じて手作業で展開または検証できます。または、展開プロセスを実行するようにデータベース・ジョブをスケジュールできます。

SQL文に対する新しい計画がオプティマイザによって検出されると、この計画は未承認計画として計画履歴に追加され、承認済み計画となる前に検証が必要となります。Oracle Enterprise Managerまたはコマンドライン関数のDBMS_SPM.EVOLVE_SQL_PLAN_BASELINEを使用すると、SQL文の実行計画を展開できます。これらの方法のいずれかを使用する場合、次の3つの選択肢があります。

1. 既存のSQL計画ベースラインよりもパフォーマンスが優れている場合にのみ、計画を承認します。

2. パフォーマンスを検証せずに計画を承認します。

3. 新しい計画を展開せずに、パフォーマンスを比較してレポートを生成します。

オプション1 を選択すると、新しい計画のパフォーマンスが、選択された計画ベースラインより優れているかどうかが評価されます。優れている場合、新しい計画が承認された計画としてSQL計画ベースラインに追加されます。それ以外の場合、新しい計画は未承認計画のまま計画履歴に残りますが、現在のタイムスタンプでLAST_VERIFIED属性が更新されます。関数から返される書式設定済みテキスト・レポートには、関数によって実行されたアクションが含まれるだけでなく、新しい計画と元の計画のパフォーマンス統計が並んで表示されます。

SQL Plan Managementについて、詳しくはアップグレードに対する準備のセクションを参照してくだ

さい。

(16)

Adaptive Cursor の共有（バインド・ピーキング）

バインド・ピーキング機能はOracle9iで導入されました。バインド・ピーキングを使用すると、オプティマイザは最初にカーソルを呼び出す際にユーザー定義のバインド変数の値を読み込みます。この場合、オプティマイザはWHERE句条件にバインド変数ではなくリテラルが使用されているものと見なして選択を決定できるため、バインド変数を使用している文に対して生成される実行計画の品質が向上します。

Adaptive Cursorを共有すると、バインドを使用する1つの文に対して複数の実行計画を作成できます。

ただし、WHERE句のバインド変数で使用されている列のデータに偏りがある場合、このアプローチには問題がありました。列に含まれるデータに偏りがある場合、統計収集中にこの列に対してヒストグラムが作成されていると考えられます。オプティマイザがユーザー定義バインド変数の値を読み込んで計画を選択した場合、取り得るすべてのバインド変数値に対してこの計画が最適であるとは限りません。言い換えると、読み込まれたバインド変数値に対してこの計画は最適ですが、その他すべての値に対して最適であるとは言えません。

Oracle Database 11gではオプティマイザが拡張され、バインド変数を使用する1つのSQL文に対して複数の実行計画を使用できるようになりました。これにより、バインド変数に合わせた最適な実行計画が使用されます。

バインド変数の値によって最適な実行計画が異なるとオプティマイザが認識した場合、カーソルはバインド依存として記録されます。カーソルがバインド依存として記録されると、Oracleはさまざまなバインド値を使用してカーソルの動作を監視し、バインド変数ごとに別の計画を呼び出すかどうかを決定します。バインド変数を使用する列にはヒストグラムが作成されているため、通常カーソルはバインド依存として記録されます。ヒストグラムの存在はこの列のデータに偏りがあることを意味しているため、多くの場合、バインド変数の値ごとに異なる計画が呼び出されます。

後続の実行で異なるバインド値が使用される場合も、Oracleは最初に実行計画を共有できると想定しているため、同じ実行計画が使用されます。ただし、この新しいバインド値に対する実行統計情報が記録され、以前の値に対する実行統計情報と比較されます。新しいバインド値を使用した場合に、

問合せによって処理されるデータ量が大きく変わると判断した場合、Oracleはその動作を"調節"します。そのため、この問合せに対して常に同じ計画が使用される訳ではありません。このように、新しいバインド値に基づいて新しい計画が生成されると、カーソルはバインド認識として記録されます。

バインド認識カーソルはバインド変数を含む条件の選択に応じて、バインド値ごとに異なる計画を使用できます。カーソルがバインド認識モードに切り替わると、この文に対して生成されたもとのカーソルは破棄されることに注意してください。これは一時的なオーバーヘッドです。カーソルが共有不可能として記録されると（V$SQLのis_shareableが"N"に設定される）、このカーソルは今後使用されることはなく、カーソル・キャッシュの中で最初にエージングによって削除されます。

さらに別の新しいバインド値が使用されると、バインド値の選択が類似しているかどうかに基づいて、オプティマイザはふさわしいカーソルを探します。適切なカーソルが見つからない場合、新しくカーソルを作成します。新しいカーソルに対する計画が既存カーソルと同じである場合、カーソル・キャッシュ内の領域を節約するため 2 つのカーソルはマージされます。また、新しいバインドの選択を含めるため、このカーソルの選択範囲が拡大されます。これにより、共有不可能な状態に

(17)

SQL Test Case Builder

SQLの問題についてOracle Supportに問い合わせる必要が生じた場合、素早い解決を実現するためのもっとも重要な要素は、再現可能なテスト・ケースを入手することです。この作業はまた、もっとも時間がかかり、もっとも困難な作業になる可能性があります。Oracle Database 11gでは、SQL Test Case Builderという新規ツールが導入されました。このツールを利用すると、顧客はSQLの問題に関してできる限り多くの情報を収集してパッケージ化し、オラクルに送信できます。オラクルの開発者はこのパッケージを使用することで、別のOracleインスタンスを使用して単独でこの問題を再現できるようになるため、問題を迅速に解決できます。SQL Test Case Builderへアクセスするには、Oracle Enterprise ManagerまたはPL/SQLパッケージのDBMS_SQLDIAGを使用します。SQL Test Case Builder に関係したプロシージャは2つあります。DBMS_SQLDIAG.EXPORT_SQL_TESTCASEを使用すると、

特定のSQL文に対するテスト・ケースを特定のディレクトリにエクスポートでき、DBMS_SQLDIAG.

IMPORT_SQL_TESTCASEを使用すると、特定のSQLテスト・ケースを特定のディレクトリからインポートできます。

SQL Test Case Builderを使用するには、出力ファイルの宛先となるOSディレクトリを指すOracleディレクトリを作成します。

SQL> CREATE DIRECTORY EXPDP AS '/scratch/mcolgan/spm/tc';

次に、DBMS_SQLDIAG.EXPORT_SQL_TESTCASEパッケージを呼び出して、作成したディレクトリと当該SQL文のSQL_IDを渡します。また、テスト・ケースの名前を指定します（この例ではtcが使用されています）。

DECLARE tc clob;

BEGIN

DBMS_SQLDIAG.EXPORT_SQL_TESTCASE(

DIRECTORY=>'EXPDP', SQL_ID=>'aqa1r0ca84rs1', TESTCASE=>tc);

END;

EXPORT_SQL_TESTCASEを実行すると、複数のトレース・ファイル、ダンプ・ファイル、.sqlファイル、およびREADME.txtが、指定したディレクトリに作成されます。このディレクトリから 1 つの圧縮ファイルを作成し、Oracle Supportへアップロードしてください。セキュリティ上の理由から、

ユーザー・データがデフォルトでエクスポートされることはありません。ユーザー・データを含めるには、exportDataをTRUEに設定します。

コストベースの新しい変換

Oracleによって問合せが最適化される際、各種の高度なテクニックを使用してSQL文が変換されます。

この問合せの最適化フェーズの目的は、もとのSQL文を変換して、意味的に同等でありながらより効率的に処理できるSQL文を生成することにあります。Oracle Database 11gでは、いくつかの新しいコストベースの変換が導入されました。ここからは、3つの変換について説明します。

(18)

GROUP BYの使用

GROUP BYを使用した場合、オプティマイザは問合せを書き換えて、GROUP BY処理を一部の結合の前に実行することで、後続の結合に必要な行数を最小化します。たとえば、次のような問合せについて考えてみましょう。

SELECT p.prod_id, sum(s.quantity_sold) FROM Products p, Sales s

WHERE p.prod_id = s.prod_id GROUP BY p.prod_id;

Oracle Database 10gでは、この問合せに対して従来の実行計画が選択されており、ハッシュ結合の後

にGROUP BYが実行されていました。

Oracle Database 11gのオプティマイザはこの問合せを変換して、GROUP BY処理を結合の前に実行します。SQL文は次のように書き換えられます。

SELECT V.sumv, p.prod_id FROM Products p,

(SELECT sum(s.quantity_sold) as sumv, s.prod_id FROM Sales s

GROUP BY s.prod_id) V WHERE V.prod_id = p.prod_id

このSQL文から分かるとおり、GROUP BYはビュー（下の計画のVW_GBC_5）の内部で実行されるため、SALESとPRODUCTSとの間のハッシュ結合で処理する必要のある行数が削減されます。

(19)

結合条件プッシュダウン機能の拡張

以前のリリースでは、SQL文でビューVと表Tを結合する場合（結合条件T.x = V.yを使用）、TとVの結合に対してオプティマイザが使用できる結合方法にはハッシュ結合かソート・マージ結合の 2 種類しかありませんでした。

Oracle Database 10gで導入された結合条件のプッシュダウン変換を使用すると、オプティマイザは結合条件をビューの内部へ移動できます。この場合、結合T.x = V.yはT.x = T2.y（T2はビューV内の表であり、列yを含む）になります。これにより、T2.yに対して索引が作成されている場合、ネスト・

ループ結合を使用できる可能性が見えてきます。

Oracle Database 11gでは、結合条件のプッシュダウン機能が拡張され、GROUP BY、DISTINCT、ANTI JOIN、SEMI JOINが含まれるようになりました。次の問合せについて考えてみましょう。

SELECT p.prod_id, v1.row_count FROM products p,

(SELECT s.prod_id, count(*) row_count FROM sales s

WHERE s.quantity_sold BETWEEN 1 AND 47 GROUP BY s.prod_id) v1

WHERE p.supplier_id = 12

AND p.prod_id = v1.prod_id(+);

Oracle Database 10gでも結合条件のプッシュダウン機能は提供されていましたが、GROUP BYがあったためこれを使用できませんでした。10gでは、ビューv1 が評価された後にPRODUCTS表へのハッシュ結合が行われています。

しかし、11gでは結合条件のプッシュダウンが可能であるため、SALES表の索引を利用して、ハッシュ

結合の代わりにネスト・ループ結合を実行します。結合条件をプッシュダウンしたことで、新しい計画のコストは79から28に削減されました。

(20)

NULL認識型のアンチ結合

NOT INまたはNOT EXISTS副問合せを含むSQL文は、多くの場合、アンチ結合を含む問い合わせに書き換えられます。アンチ結合では、左側の表のキーが右側の表に存在しない場合、一致行が得られます。このような副問合せのネストを外すと、桁違いのパフォーマンス向上が実現されます。次に例を示します。

SELECT C.CUST_ID, C.CUST_FIRST_NAME, C.CUST_LAST_NAME FROM CUSTOMERS C

WHERE C.CUST_ID NOT IN (SELECT ST.CUST_ID

FROM SALES_TRANSACTIONS_EXT ST

WHERE ST.UNIT_PRICE < 15);

列C.CUST_IDまたはST.CUST_IDがnullable（NULL値を取り得る）である場合、Oracle Database 10g のオプティマイザでは、既存（通常）のアンチ結合を使用して副問合せのネストを外すことはできません。したがって、実行計画は事実上のデカルト積になります。

Oracle Database 11gでは、null-aware（NULL認識）アンチ結合と呼ばれる新しいタイプのアンチ結合が導入されました。NULL認識アンチ結合を使用すると、オプティマイザは副問合せのネストを外すことができるため、ネスト・ループ結合、ハッシュ結合、ソート・マージ結合を含む新しい結合方法を使用できるようになります。この例では、11gの計画はハッシュ結合を使用していますが、実行計画にはHASH JOIN RIGHT ANTI NAと表示されています。ここで、"NA"はNULL認識を意味します。計画コストは639から38まで削減されています。

アップグレードに対する準備

データベース・アップグレードはどんなDBAにとっても非常に手ごわい作業です。データベースのアップグレードに成功しても、さまざまなデータベース動作の変化という難関が待ち受けています。

潜在的な動作変化リストのトップに常に挙げられるのが、実行計画の変化です。これらの変化を素早く検出し、パフォーマンス低下の可能性のある実行計画を修正するには、アップグレードを開始する前に、以前の実行計画とオプティマイザ統計について十分に把握しておく必要があります。また、本番システムをアップグレードする前に、新規リリースでのアプリケーション・テストを実行する必要があります。

(21)

既存の実行計画の取得

11gではSQL Plan Managementが導入されたため、アップグレード後に実行計画が変わらないという新しい安全策が得られます。この安全策を最大限に活用するには、アップグレード前に既存の実行計画を取得して、SPMにシードする必要があります。

SQL Tuning Setの使用

Oracle Diagnostics Packに含まれるSQL Tuning Setを使用できる場合、既存の10g実行計画を取得するにはこれが一番簡単な方法です。STSは、1つ以上のSQL文とその実行統計、実行コンテキスト、および現在の実行計画を含むデータベース・オブジェクトです。Oracle Database 10g Release 1のSTSは

SQL文の実行計画を取得しないため、SPMに計画をシードすることはできません。計画を取得するの

は10g Release 2のSTSのみです。

はじめに、新規STSを作成する必要があります。実行するには、Oracle Enterprise Managerまたは DBMS_SQLTUNEパッケージを使用します。ここではDBMS_SQLTUNEを使用します。

BEGIN

SYS.DBMS_SQLTUNE.CREATE_SQLSET (

sqlset_name => 'SPM_STS', description => '10g plans');

END;

STSの作成が終わったら、次に移入を実行します。ワークロード・リポジトリや別のSTS、またはカーソル・キャッシュからSTSを移入できます。ここでは、SQL文とその実行計画をカーソル・キャッシュから取得します。このプロセスには2つの手順が含まれます。はじめに、指定したSQLをカーソル・

キャッシュから選択するREFカーソルを作成します（ここでは、SYS以外のすべてのSQL文）。次に、

このREFカーソルを使用してSTSを移入します。

DECLARE

stscur dbms_sqltune.sqlset_cursor;

BEGIN

OPEN stscur FOR SELECT VALUE(P)

FROM TABLE(dbms_sqltune.select_cursor_cache(

eparsing_schema_name <> eeSYSfff,

null, null, null, null, 1, null, 'ALL')) P;

-- populate the sqlset

dbms_sqltune.load_sqlset(sqlset_name => 'SPM_STS',

populate_cursor => stscur);

END;

ストアド・アウトラインの使用

SQL Tuning Setを使用できない場合は、ストアド・アウトラインを使用して既存の実行計画を取得で

きます。ストアド・アウトラインの取得方法は2種類あります。CREATE OUTLINEコマンドを使用して、1つのSQL文ごとに手動で作成する方法と、実行する各SQL文に対して自動的にストアド・アウトラインを作成する方法です。ストアド・アウトラインを自動生成するOracle機能を使用するには、

次の手順を実行します。

(22)

1. 新規セッションを開始し、次のコマンドを使用してストアド・アウトラインの自動取得を有効化すると、明示的に無効化するまでの間に解析されるすべてのSQL文に対して、ストアド・アウトラインが自動取得されます。

SQL > ALTER SYSTEM set CREATE_STORED_OUTLINES=OLDPLAN;

注：ストアド・アウトラインを作成するユーザーにCREATE ANY OUTLINE権限が付与されていることを確認してください。この権限がない場合、ストアド・アウトラインは取得されません。

2. 次に、アプリケーションを実行するか、または手動でSQL文を発行して、ワークロードを実行します。注：手動でSQL文を発行する場合、アプリケーションが使用する正確なSQLテキストを使用してください。アプリケーションでバインド変数が使用される場合、手動でもバインド変数を使用する必要があります。

3. 重要なSQL文の実行を完了したら、次のコマンドを発行して自動取得機能を無効化します。

SQL > ALTER SYSTEM set CREATE_STORED_OUTLINES=false;

4. 必要なストアド・アウトラインを取得したことを確認するには、次のSQL文を実行します。

SQL> SELECT name, sql_text, category FROM user_outlines;

注：すべてのストアド・アウトラインにOLDPLANカテゴリが含まれることを確認します。

5. 実際のストアド・アウトラインは、OUTLNスキーマ内に格納されています。アップグレードを実行する前に、バックアップとしてこのスキーマをエクスポートする必要があります。

exp outln/outln file=soutline.dmp owner=outln rows=y

注：インプレース・アップグレードを計画していない場合、STSまたはストアド・アウトラインを Oracle Database 11gシステムへ移行する必要があります。

既存のオプティマイザ統計情報の取得

アップグレードを実行する前に、現在のオプティマイザ統計情報のセットを取得しておく必要があります。アップグレードした後、システムが安定するまでの間はこの統計情報セットを使用します。

何らかの変更が発生した場合に診断しやすくするため、アップグレード中はできる限り変更を少なくする必要があります。統計情報はオプティマイザにもっとも大きな影響を与えるため、アップグレード中にこれらの情報が変わらないよう注意します。統計情報のバックアップを維持するための最善の方法は、DBMS_STATS.EXPORT_*_STATSを使用してオプティマイザ統計一式を統計表にエクスポートすることです。

はじめに、統計表を作成します。

BEGIN

DBMS_STATS.CREATE_STATS_TABLE('SYS','MY_STATS_TAB');

END;

/

(23)

次に、重要なスキーマの統計情報をエクスポートし、10gの統計情報を識別できるようなstats_idを指定します。

BEGIN

DBMS_STATS.EXPORT_SCHEMA_STATS(eSHf,'MY_STATS_TAB',f10g_statsf);

END;

/

最後に、万一の際のバックアップとして統計表をエクスポートします。

アプリケーションのテスト

アップグレードを実行する前に、Oracle Database 11gでアプリケーションを完全にテストすることが絶対不可欠です。テストを実施することで、アップグレード後に何が変わる可能性があるかという見通しが得られるとともに、安全策（SPM）をテストする機会が得られます。10gの実行計画を収集する際、重要なSQL文を見逃す可能性は常にあります。必要なものすべてを取得するため、完全なアプリケーション・テストを実行することが重要です。

アプリケーションで現在オプティマイザ・ヒントが使用されている場合、ヒントを使用せずにOracle

Database 11gでのアプリケーション・テストを実行することを推奨します。ヒントは通常、初期のバー

ジョンのオプティマイザで発生する制限や問題の回避策として、文やアプリケーションに追加されていました。最近のデータベース・リリースでは、ヒントを削除するとより良い実行計画が得られることが多くあります。ヒントなしでテストを実行するためのもっとも簡単な方法は、アンダースコア・パラメータの_OPTIMIZER_IGNORE_HINTSにTRUEを設定することです。

アップグレード前のチェックリスト

本番システムをOracle Database 11gへアップグレードする前に、将来的に比較対象が必要になった場合に使用できる明確なベースラインを確保するため、次の情報を収集および保管しておく必要があります。

1. 本番データベースからインスタンス全体のパフォーマンス統計情報を収集します（ピーク・ロード時）。インスタンス・レベルのパフォーマンス統計情報には、次が含まれます。

a. StatspackまたはAWRデータおよびレポート。1 時間ごとのレポートが少なくとも 7 日分必要です。AWRはデフォルトで1時間ごとにスナップショットを取得し、上位のSQL文に対する実行計画を自動的に取得します。これらのレポートは8 日間保存されます。ただし、STATISTICS_LEVELパラメータまたは保存レベルを変更している場合、少なくともアップグレードの 1週間前にはこれらをデフォルトに戻す必要があります。Statspackを使用している場合、セグメント統計や計画情報を収集できるように、レベル7のスナップショットを取得するように設定する必要があります。

b. CPU、メモリ、IOを含むOS統計情報（sar、vmstat、iostatなど）。

(24)

2. ベースラインを取得する際、ビジネス上重要なすべてのトランザクションに加えて、月末処理やよく使用される非定型問合せを実行していることを確認します。

3. Statspackスキーマの所有者であるPERFSTATをエクスポートし、エクスポート・ファイルをバッ

クアップとして保管します。AWRを使用している場合、このステップは必須です。

4. すべてのオプティマイザ統計情報を統計表にエクスポートし、バックアップとしてこの表をエクスポートします。

5. init.oraファイルのバックアップを作成します。

6. 現在の上位SQL文やアプリケーションで重要なSQL文を含む、主要な文に対する実行計画を取得します（詳しくは、記述の既存の実行計画の取得セクションを参照してください）。

アップグレード後の作業

ソフトウェア・アップグレードが完了したら、アプリケーションを再起動してユーザーがシステムを使用する前に、アップグレード前に取得した10gの実行計画をSQL Plan Managementに移入する必要があります。10gの実行計画を使用してSPMにシードすることで、アップグレード前と同じ実行計画がアプリケーションで引き続き使用されます。Oracle Database 11gで新しく検出された実行計画は、

この文に対する計画履歴に記録されますが、使用はされません。準備が整ったら、新しい計画を展開して検証し、10gの計画よりもパフォーマンスが高い計画だけを実装します。

SQL Plan Management への 10g 計画の移入

実行計画をSPMにバルク・ロードする方法には、次の4種類があります。

1. 特定のSQL Tuning Setに対して、実行計画を移入します 2. ストアド・アウトラインから実行計画を移入します

3. 現在カーソル・キャッシュに格納されている実行計画を使用します 4. ステージング表から既存のSQL計画ベースラインをアンパックします

計画をどこからバルク・ロードしたかに関係なく、各計画は自動的に承認され、新しいSQL計画ベースラインが作成されるか、または既存ベースラインに追加されます。アップグレード中は、最初の3 つのオプションを使用して計画をバルク・ロードする可能性が高いでしょう。ここからは、これら3 つのオプションについて詳しく説明していきます。ステージング表からSQL計画ベースラインをアンパックする方法について、詳しくは『Oracle Databaseパフォーマンス・チューニング・ガイド』の第 15章を参照してください。

(25)

SQL Tuning Setを使用したバルク・ロード

PL/SQLプロシージャのDBMS_SPM.LOAD_PLANS_FROM_SQLSET、またはOracle Enterprise Manager を使用して、STSからSPMへ実行計画をバルク・ロードできます。

SQL> Variable cnt number

SQL> execute :cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( -

sqlset_name => 'SPM_STS');

図3. STSによるSPMへのシードを使用したアップグレード

ストアド・アウトラインを使用したバルク・ロード

Oracle Database 11g Release 2では、DBMS_SPM.MIGRATE_STORED_OUTLINEまたはOracle Enterprise

Managerを使用して、1 つ以上のSQL文に対するストアド・アウトラインをSQL計画ベースラインに

移行できます。アウトライン名、SQLテキスト、またはアウトライン・カテゴリに基づいて移行するストアド・アウトラインを指定することも、システム内のすべてのストアド・アウトラインをSQL 計画ベースラインに移行することもできます。

SQL> variable report clob;

-- 名前で指定した1つのストアド・アウトラインを移行

SQL> exec :report:=DBMS_SPM.MIGRATE_STORED_OUTLINE(

attribute_name=>'OUTLINE_NAME', attribute_value => 'stmt01');

-- すべてのストアド・アウトラインを移行

SQL> exec :report:=DBMS_SPM.MIGRATE_STORED_OUTLINE(

attribute_name=>'ALL');

(26)

カーソル・キャッシュを使用したバルク・ロード

文に対する実行計画をカーソル・キャッシュから直接SPMにロードできます。またモジュール名、

スキーマ、またはSQL_IDにフィルタを適用すると、取得するSQL文またはSQL文のセットを特定できます。PL/SQLプロシージャのDBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHEまたはOracle

Enterprise Managerを使用して、計画をロードします。アップグレード前に一部、または全部の文に対

する計画を取得できなかった場合、カーソル・キャッシュから直接計画をロードする方法が極めて有効です。

OPTIMIZER_FEATURES_ENABLEパラメータの値をアップグレード前に使用していた 10gバージョンに設定すると、アップグレード前と同じ実行計画を回復できます。これらの10gバージョンの実行計画をカーソル・キャッシュから取得することで、OPTIMIZER_FEATURES_ENABLEを11gバージョンに設定する前に、SPMに10gバージョンの計画をシードできます。ここでは、10gで使用していたものと同じオプティマイザ統計を使用する必要があることに注意してください。すべての 10gバージョンの計画が取得されるまで、統計は再収集されません。

図4. カーソル・キャッシュを使用した10g計画の取得によるアップグレード

SQL計画ベースラインの使用の確認

アップグレード前の計画がSPMにシードされた後で実行計画のNoteセクションを参照すると、新しく作成されたSQL計画ベースラインが使用されていることを確認できます。SQL計画ベースラインが使用されている場合、Noteセクションに"SQL plan baseline XXXXX used for this statement"と表示されます。

(27)

SQL> explain plan for 2 SELECT *

3 FROM sh.sales

4 WHERE quantity_sold > 40 5 ORDER BY prod_id;

Explained.

SQL> SELECT * FROM table(dbms_xplan.display(null, null, 'basic +note'));

PLAN_TABLE_OUTPUT

--- Plan hash value:1421641795

---

| Id | Operation | Name | ---

| 0 | SELECT STATEMENT | |

| 1 | SORT ORDER BY | |

| 2 | PARTITION RANGE ALL| |

| 3 | TABLE ACCESS FULL | SALES | --- Note

---

- SQL plan baseline "SQL_PLAN_2kgpw0an1uph654bc8843" used for this statement

アップグレード後のオプティマイザ統計情報の処理

アップグレードの終了後、システムが安定するまでは10gの統計情報を使用します。アップグレードの後で最初に統計情報を収集するまでの間は、パーティション化されたすべての表に対して増分統計を有効化しておく必要があります。これにより、パーティション・レベルの統計情報を使用して、

パーティション化された表に対するグローバル統計を生成できるため、大きな表の統計情報を収集するためにかかる時間を大幅に短縮できます。

BEGIN

DBMS_STATS.SET_GLOBAL_PREFS('INCREMENTAL','TRUE');

END;

予防的措置として、一時的にすべてのオブジェクトに対して保留統計を有効化します。こうすることで、新しい統計情報を公開し、本番環境で使用し始める前に、テストを実施する機会が得られます。

BEGIN

DBMS_STATS.SET_GLOBAL_PREFS('PENDING','TRUE');

END;

(28)

これで、11gの統計情報を収集する準備が整いました。いずれのパラメータ値も指定しないことで、

デフォルト値が使用されます。この場合、自動的に新しい統計アルゴリズムが適用されます。収集されたすべての統計情報は保留状態で保管されるため、現在の環境に影響を与えることなくテストを実施できます。

BEGIN

DBMS_STATS.GATHER_SCHEMA_STATS('SH');

END;

別の方法として、本番システムからエクスポートした保留統計をテスト・システムにインポートし、

その影響を公開前にテストすることもできます。保留統計をエクスポートするには、EXPORT_

PENDING_STATSプロシージャを使用します。

BEGIN

DBMS_STATS.EXPORT_PENDING_STATS(c);

END

このリリースでは、OPTIMIZER_USE_PENDING_STATISTICSパラメータをTRUEに変更すると、

セッション・レベルで保留統計を適用して重要なSQL文をテストできます。パラメータを変更した後は、このセッション内で発行されたすべてのSQL文に対して、保留統計が使用されます。

ALTER SESSION set optimizer_use_pending_statistics=TRUE;

最終的に11gの統計情報を検証し終わったら、保留統計を無効化して新しい統計情報を公開します。

BEGIN

DBMS_STATS.SET_GLOBAL_PREFS('PENDING','FALSE');

END BEGIN

DBMS_STATS.PUBLISH_PENDING_STATS();

END

アップグレード後のチェックリスト

Oracle Database 11gへのアップグレードが問題なく終了し、アプリケーションの稼働を再開したら、

パフォーマンスの問題や計画の劣化が発生していないことを確認するため、環境を注意深く監視する必要があります。次に、その具体的な方法を簡単に説明します。

1. Oracle Diagnostic Packのライセンスを持っていない場合、Statspackのインストールまたはアップグレードを実行し、レベルを7に設定します。アップグレードする場合は、Statspackの指示に従います。

2. Statspackのスナップショット・スケジュールを1時間ごとに設定します。こうすることで、

11g環境でコストのかかっているSQLがStatspackによって取得されます。Diagnostic Packのライセンスを保有している場合、AWRレポートを使用できます。このレポートは自動的に毎時間生成されます。

3. Statspackレポートと一致するタイミングでOS統計情報を取得します。