OTN Japan - 意外と簡単!? Oracle Database 10g Release 2：Linux版第5章

チューニングで利用されるアドバイザは以下になります。

アドバイザ	説明
SQLチューニング・アドバイザ	SQL文を分析し、パフォーマンスを向上させる推奨項目を作成します。SQL文を変更せずにパフォーマンスを向上させるSQLプロファイルの設定などが可能です。
SQLアクセス・アドバイザ	SQL文を実行する際のアクセス・パスに関するチューニング（索引やマテリアライズド･ビューの作成）を行うことが可能です。
メモリー・アドバイザ	メモリー・サイズのアドバイスを表示します。Oracle Database 10gからはSGAメモリー領域内のメモリー配分を自動的に最適化する機能もあります。
セグメント・アドバイザ	セグメントの断片化レベルを測定し、縮小すべきオブジェクトがあるかのアドバイスを表示します。

各アドバイザはADDMによる分析結果であるパフォーマンス分析の詳細画面で起動を促される場合もありますが、任意のタイミングでデータベース管理者により実行することも可能です。これから行う実習では、手動で各アドバイザを起動してチューニングを行うものとします。

ポイント：SQLチューニング・アドバイザとSQLアクセス・アドバイザの実行にはOracle Database 10g Enterprise EditionとDiagnostic Pack、Tuning Packが必要です。

SQLチューニング・アドバイザ
SQLチューニング・アドバイザではSQL文を書き換えることなくパフォーマンスを向上させるSQLプロファイルを実装できます。SQLプロファイルには、その文に固有の追加のSQL統計が含まれ、より適切な実行計画を生成する問合せオプティマイザを使用可能にします。これによりパフォーマンスを向上させることが可能となります。

ポイント：SQLチューニング・アドバイザによる分析の結果はSQLプロファイル実装のほかに、統計の収集やSQL文の再構築もあります。またこの後で実習しますSQLアクセス・アドバイザを内部的に使用して索引の作成を行う場合もあります。

SQLチューニング・アドバイザを使ってみよう

1. SQLチューニング・アドバイザを使用するための下準備をします。
OracleユーザーでOSにログインして、SQLチューニング・アドバイザ用のスクリプト(sta.tar)をダウンロードします。
任意のディレクトリにsta.tarをコピーし、ファイルを展開します。

$ tar xvf sta.tar
$ cd sta

SQLチューニングのためのスクリプトを実行します。これによりチューニングすべきSQL文が継続的に流れつづけます

$ chmod +x ./setup_perflab.sh
$ ./setup_perflab.sh

ポイント：SQLチューニング・アドバイザではSHスキーマにあるサンプルを利用します。データベース作成時にサンプル・スキーマにチェックを入れて作成するか、後で手動インストールする必要があります。また、Oracle Database Enterprise EditionとPartition Optionが必要になります。詳しくは「Oracle Database サンプル・スキーマ, 10g リリース2」をご覧ください。

2.	SYSユーザでOEMにログインし「パフォーマンス」タブをクリックします。手順1で実行したSQL文により負荷がかかっていることが確認できます。「トップ・アクティビティ」をクリックします。

	ポイント：OEMへのログイン方法については「意外と簡単!? Oracle Database 10g データベース構築基礎編」を参照ください。また、「パフォーマンス」タブをクリックするにはOracle Database 10g Enterprise EditionとDiagnostic Packが必要です。

3.	「トップ・アクティビティ」の画面が表示されます。現在、負荷の高いSQL文が表示されます。一番負荷の高いSQL IDをクリックします。

4.	「SQLの詳細」の画面が表示されます。ここではSQL文や実行プラン、統計情報、チューニング履歴を見ることができます。またボタンでSQLチューニング・アドバイザの起動が可能です。ボタンをクリックします。 SQLの詳細

	統計

	プラン

	チューニング情報

	ポイント：チューニングが行われていない場合、（データなし）と表示されます。

5.	SQLチューニング・アドバイザが起動し、「スケジュール・アドバイザ」の画面が表示されます。アドバイザ起動のスケジュールの設定などか可能です。スケジュールが「即時」になっていることを確認し、ボタンをクリックします。

6.	分析中の画面が表示されます。しばらく分析に時間がかかります。

7.	しばらく経ちますと、分析結果として「SQL IDの推奨」画面が表示されます。推奨としてSQLプロファイルの実装が表示されています。このSQLプロファイルを実装した場合の実行計画はボタンを押すことで表示させることができます。新規実行計画を確認してボタンをクリックします。

8.	SQLプロファイル実装完了の画面が表示されます。SQLプロファイル実装によりパフォーマンスがどのぐらい向上したかを確認します。左上の「データベース: XXX」をクリックし、ホーム画面に戻ります。

9.	「ホーム」画面から「パフォーマンス」タブをクリックします。「平均アクティブ・セッション」グラフを見ると、SQLプロファイルを実装後、ユーザーI/Oが減少しパフォーマンスが向上していることが確認できます。

10.	「トップ・アクティビティ」をクリックし、今チューニングを施したSQL IDをクリックした先で「チューニング情報」をクリックすると今行ったチューニングの履歴が増えていることが確認できます。

11.	最初に実行したスクリプトを停止し、クリーンアップします。
	$ chmod +x cleanup_perflab.sh $ ./cleanup_perflab.sh

以上でSQLチューニング・アドバイザの実習は終了です。

SQLアクセス・アドバイザ
SQL アクセス・アドバイザは、索引、マテリアライズド・ビューなどSQL 問合せを最適化するための適切なアクセス構造の定義に役立ちます。SQLアクセス・アドバイザでは推奨事項を生成するための材料（これをワークロードと言います）を最初に指定し、分析を行います。

SQLアクセス・アドバイザを使ってみよう

SQLアクセス・アドバイザを使用する下準備をします。
OracleユーザーでOSにログインして、SQLアクセス・アドバイザ用のスクリプト(saa.tar)をダウンロードします。
任意のディレクトリにsaa.tarをコピーし、ファイルを展開します。
。

$ tar xvf saa.tar
$ cd saa

SQL*Plusでデータベースに接続します。

$ sqlplus system/<password>

展開されたスクリプトprepare_for_advisor.sqlを実行します。

SQL> @prepare_for_advisor

今回はワークロードとして、共有プールのライブラリ・キャッシュに存在するSQL文の情報を使用します。そのために共有プールにSQLをキャッシュする作業を行います。

SQL> @advisor_cache_setup

2.	SYSユーザでOEMにログインし、ホーム画面下方の関連リンクより「セントラル・アドバイザ」をクリックします。セントラル・アドバイザはさまざまなアドバイザへのポインタとなっています。

3.	「セントラル・アドバイザ」画面が表示されます。「SQLアクセス・アドバイザ」をクリックします。

4.	「SQLアクセス・アドバイザ」の画面が表示されます。ここではワークロードの選択を行います。「SQLアクセス・アドバイザ: 初期オプション」画面が表示され、「デフォルト・オプションを使用」をチェックし、ボタンをクリックします。

5.	「SQLアクセス・アドバイザ: ワークロード・ソース」画面が表示され、ここで分析に使用するワークロードのソースを選択します、「現在と最近のSQLアクティビティ」のチェックされたことを確認し、をクリックします

	「SQLアクセス・アドバイザ: 推奨オプション」画面が表示されます、。

	ポイント：ワークロードとは、アドバイザが推奨事項を生成するための材料となるものです。今回は手順1でキャッシュしたSQL文をワークロードとするため「現在と最近のSQLアクティビティ」を選択しました。表を選択することにより、その表の統計情報から簡単な分析を行うことも可能です。

6.	推奨オプションの設定を行います。分析結果である推奨タイプを「索引とマテリアライズド･ビューの両方」にしてボタンをクリックします。

7.	アドバイザはタスクとして発行されます。ここでは即時で分析を開始させますので、デフォルトのままボタンをクリックします。

8.	SQLアクセス・アドバイザの設定は以上でおわりです。確認の画面が表示されますので、内容を確認しボタンをクリックします。

9.	アドバイザ・タスクが正常に作成されたことが確認できます。今、作成したアドバイザ・タスクを表示するために検索のボタンをクリックします。

10.	最新のSQLアクセス・アドバイザの情報が表示されます。今、作成したタスクのステータスがCOMPLETEDに成っていることを確認し、アドバイザ・タスクの名前をクリックします。

11.	アドバイザの結果として「タスクの推奨」画面が表示されます。「推奨ID」をクリックすることで推奨の詳細をみることができます。コスト改善の最も高い推奨１をクリックします。

12.

マテリアライズド･ビューの作成などが推奨されているのが確認できます。テキストエリアになっているところは変更可能です。今回は、CREATE _MATERIALIZED_VIEWのスキーマをSYSからSHに変更しまし（後でSHスキーマにマテリアライズド･ビューが作成されていることを確認します）、

ボタンをクリックして手順11の画面に戻ります。

13.	ボタンをクリックします。

14.	「スケジュール実装」画面が表示されます。今回は即時で実装を行いますのでデフォルトのままでボタンを押します。

15.	確認画面が表示さます。SQLアクセス・アドバイザの実装ジョブが正常に作成されたことが確認できます。「データベース・インスタンス: XXX」のリンクをクリックします。

16.	「管理」画面が表示されます。スキーマの「マテリアライズド･ビュー」リンクをクリックしてください。新しくマテリアライズド･ビューが作成されたことを確認します。

17.	「マテリアライズド･ビュー」画面が表示されます。スキーマにSHと入力しボタンを押します。これによりSHスキーマのマテリアライズド･ビューの検索ができます。

18.	検索結果が表示されます。新しいマテリアライズド･ビューが追加されていることが確認できます。

以上でSQLアクセス・アドバイザの実習は終了です。

メモリー・アドバイザ
Oracle Databaseにはメモリー領域としてSGA領域とPGA領域があります。それぞれの領域についてメモリーのチューニングを行います。

メモリー領域名	説明
SGA領域 (システム・グローバル領域)	インスタンス起動時に割り当てられるOracleインスタンスの共有メモリー領域です。共有プールやデータベース・バッファ・キャッシュなどから構成されます。
PGA領域 (プログラム・グローバル領域)	サーバー・プロセス起動時に割り当てられるメモリー領域です。非共有メモリー領域で、ソートやカーソル、セッション情報の保持のために使用されます

SGA領域のチューニング
Oracle Database 10gからSGA領域は自動的にチューニングすることが可能です。これは「自動共有メモリー管理」と呼ばれます。自動共有メモリー管理を有効にしていればSGAの合計サイズを指定する他、チューニングは基本的に必要ありません。

自動共有メモリー管理を有効にしよう

1.	SYSユーザでOEMにログインし「管理」画面へ移動後、「メモリー・パラメータ」をクリックします。

2.	「自動共有メモリー管理」が使用不可になっている場合、以下のような画面が表示されます。この状態では自動的にSGA領域がチューニングされません。ボタンをクリックします。

3.	「自動共有メモリー管理の有効化」画面が表示されます。合計SGAサイズを必要に応じて変更します。このサイズは最大SGAサイズ以下である必要があります。通常はそのままボタンをクリックします。

4.	確認画面が表示され、自動共有メモリー管理が有効になったことが確認できます。

以上で自動共有メモリー管理に関する実習は終了です。

PGA領域のチューニング
PGA領域のチューニングは最適なPGA領域サイズを決定し、設定することになります。PGA領域はアドバイザを利用して最適な値を設定することが可能です。

PGAのチューニングをしよう

1.	SYSユーザでOEMにログインし「管理」画面へ移動後、「メモリー・パラメータ」をクリックします。

2.	「SGA」が表示されますので、「PGA」タブをクリックします。

3.	「PGA」画面が表示されます。ボタンを押すことでPGA領域の使用され方を見ることができます。

4.	PGAメモリー使用量の詳細で、単一パス実行（青）やマルチパス実行（赤）がある場合、PGA領域を大きくする必要があると考えてください。最適なPGA領域のサイズを決定するためには、手順3の画面でボタンを押します。

5.	アドバイスの画面ではキャッシュ・ヒット率がなるべく100%に近づくようにPGAターゲット総計を設定します。曲線をクリックすることでPGAターゲット総計を決定することが可能です。適切な値を決定したらボタンを押します。

	ポイント：今回の例ですと60Mバイトで一定のキャッシュ・ヒット率に収束していますので、60M程度が適切なPGAターゲット総計ということになります。

6.	「PGA」画面が変更されます。PGAターゲット総計の値が手順5で設定した値に変更されていることを確認してボタンを押します。

7.	「変更は正常に行われました」とのメッセージが表示されPGAターゲット総計が変更されます。

以上でPGA領域チューニングの実習は終了です。

セグメント・アドバイザ
更新および削除操作の繰返しによって、セグメント領域が断片化し、セグメントに多数の空き領域ができます。その結果、散在するオブジェクトによって、問合せおよびＤＭＬ操作中のパフォーマンスが低下することがあります。
セグメント・アドバイザは、オブジェクト内での領域の断片化のレベルに基づいて再利用が可能な領域がオブジェクトにあるかどうかのアドバイスを行います。

セグメント・アドバイザを使ってみよう

1.	セグメント・アドバイザを使用する下準備をします。 OracleユーザーでOSにログインして、セグメント・アドバイザ用のスクリプト(seg.tar)をダウンロードします。任意のディレクトリにseg.tarをコピーし、ファイルを展開します。 $ tar xvf seg.tar $ cd seg SQL*Plusでデータベースに接続します。 $ sqlplus system/<password> 展開されたスクリプトsetup_seg.sqlを実行します。 SQL> @setup_seg

2.	SYSユーザでOEMにログインし「管理」画面へ移動後、「表」をクリックします。

	ポイント：手順2から手順6はセグメント・アドバイザによるセグメント縮小の効果を比較するための手順となります。

3.	表の検索画面が表示されます。スキーマに「HR」と入力しボタンをクリックします。

4.	HRスキーマの表の一覧が表示されます。表EMPLOYEES1をチェックし、ボタンをクリックします。

5.	「表の編集」画面が表示されます。EMPLOYEES1表のセグメントの状態を確認するために「セグメント」をクリックします。

6.	「セグメント」が表示されます。「使用されている領域」、「割り当てられた領域」のサイズを記憶しておいてください。左上の「データベース・インスタンス：xxx」をクリックします。

7.	「管理」画面が表示されます。表領域をクリックします。

8.	「表領域」画面が表示されます。USERSを選択し、アクションを「セグメント・アドバイザの実行」にしてボタンをクリックします。

9.	「セグメント・アドバイザ：アドバイザ・モード」画面が表示されます。「拡張オプション」をクリックします。

10.	「拡張オプション」画面が表示されます、分析の時間制限が無制限となっていることを確認し、ボタンをクリックします。

11.	「セグメント・アドバイザ：スケジュール」画面が表示されます。適当なタスク名を入力し、スケジュール・タイプを標準にして開始が即時となっていることを確認してボタンをクリックします。

12.	「セグメント・アドバイザ：確認」画面が表示されます。内容を確認して、ボタンをクリックします。

13.	アドバイザ・タスクが正常に作成されたことが確認できます。今、発行したアドバイザ・タスクが表示されます。をクリックします。

14.	推奨として縮小すべきセグメントが表示されます。ボタンを押すと推奨事項の詳細が表示されます。

15.	スキーマHRをチェックし、ボタンをクリックします。

16.	「セグメントの縮小: オプション」画面が表示され、、ボタンを押すと実行されるSQL文が表示されます。SQL文の確認後、ボタンをクリックします。

	SQL表示

	ポイント：「SQL表示」で表示されるalter table XXX shrink spaceは10gで新機能となったセグメント縮小のためのコマンドです。

17.	「セグメントの縮小：スケジュール」画面が表示されます。適当なジョブ名を設定し、開始が即時になっていることを確認してボタンをクリックします。

18.	「確認」画面が表示され、ジョブが正常に発行されたことが確認できます。しばらく待ち、ボタンをクリックします。実行中のジョブがなくなっていることを確認してください。これでセグメント縮小が完了しました。左上の「データベース・インスタンス：xxx」をクリックします。

19.	「管理」画面が表示されます。手順2から手順6をもう一度行いEMPLOYEES1表のセグメント情報を表示させます。

20.	「セグメント」が表示されます。「使用されている領域」、「割り当てられた領域」のサイズをセグメント・アドバイザ前に確認した値と比較してください。セグメントが縮小していることが確認できます。

以上でセグメント・アドバイザの実習は終了です。


Copyright © 2005, Oracle Corporation Japan. All rights reserved.	Legal Notices and Terms of Use • Privacy Statement
この文書はあくまでも参考資料であり、掲載されている情報は予告なしに変更されることがあります。日本オラクル社は本書の内容に関していかなる保証もいたしません。また、本書の内容に関連したいかなる損害についても責任を負いかねます。 Oracleは米国Oracle Corporationの登録商標です。文中に参照されている各製品名及びサービス名は米国Oracle Corporationの商標または登録商標です。その他の製品名及びサービス名はそれぞれの所有者の商標または登録商標の可能性があります。