Oracle SQL Developer 3.2のSQL Tuning Advisor
概要
- Oracle Database 11g Enterprise Edition（Oracle Tuning Management PackおよびOracle Diagnostic Management Packを使用できること、サンプル・スキーマがインストールされていること）
- Oracle SQL Developer 3.2
- Oracle SQL Developer 3.2をこちらからインストールしてください。
目的
このチュートリアルでは、Oracle SQL Developer 3.2でSQL Tuning Advisor機能を使用する方法について説明します。
所要時間
約40分
はじめに
SQL Tuning Advisorは大量のSQL文を分析し、チューニングに関する推奨事項を提供します。 1つ以上のSQL文を入力として受け取り、Automatic Tuning Optimizerを起動して、この文に対するSQLチューニングを実行します。 どのようなSQL文に対するチューニングも実行可能です。 SQL Tuning Advisorは、SQL文をチューニングし、期待されるパフォーマンス向上を実現するためのアドバイスを、正確なSQLアクションの形で提供します。 提供される推奨事項やアドバイスは、オブジェクト統計情報の収集、新規索引の作成、SQL文の再構築、SQLプロファイルの作成などに関連するものです。 ユーザーは、推奨事項を受け入れてSQL文のチューニングを完了するかどうかを選択できます。
Oracle Databaseは、SQL Tuning Advisorを使用して問題のあるSQL文を特定し、チューニングの推奨事項を実装することで、自動的にSQL文をチューニングします。 また、問題があるとして特定された1つまたは複数のSQL文に対して、個別にSQL Tuning Advisorを実行することもできます。
このチュートリアルでは、SQL Tuning Advisorを実行し、推奨事項を確認する方法について学習します。
注： Tuning AdvisorはOracle管理パックの1つであるOracle Tuning Packに含まれていますが、Enterprise Editionと共に購入することもできます。 詳しくは、Oracle Technology Networkまたはオンライン・ドキュメントを参照してください。
ハードウェアとソフトウェアの要件
ハードウェアとソフトウェアの要件リストは、以下のとおりです。
前提条件
注： このチュートリアルを最適な状態で表示するには、ブラウザとしてFirefoxまたはChromeを使用してください。
データベース接続の作成
Oracle SQL Developer 3.2を使用してデータベース・オブジェクトを管理するための最初の手順は、データベース接続を作成することです。
次の手順に従って、データベース接続を作成します。
注： HRおよびSYSTEMへのデータベース接続がすでに作成されている場合は、以下の手順をスキップし、 HRユーザーへの権限付与のトピックへ進んでください。
SQL Developer 3.2が配置されているディレクトリを開いて、sqldeveloper.exe（Windowsの場合）またはsqldeveloper.sh（Linuxの場合）を右クリックし、「Send to」→「Desktop (create shortcut)」の順に選択します。
デスクトップに、sqldeveloper.exeへのショートカットという名前のアイコンが表示されます。 このアイコンをダブルクリックしてSQL Developer 3.2を起動します。
注： アイコンの名前を変更するには、アイコンを選択してから、[F2]を押して新しい名前を入力します。
Oracle SQL Developerが起動します。
接続ナビゲータで、「Connections」を右クリックし、「New Connection」を選択します。
New / Select Database Connectionダイアログ・ボックスが開きます。 以下の接続情報を入力し、「Test」をクリックします。
Connection Name： system
Username： system
Password： ＜使用するパスワード＞（「Save Password」を選択）
Hostname： localhost
SID： ＜使用するSID＞
左下（Helpボタンの上）で接続のステータスを確認します。 Successと表示されているはずです。 「Save」をクリックしてから、「Connect」をクリックします。
hrスキーマに対して新しい接続を作成するため、接続ナビゲータで「Connections」を右クリックして「New Connection」を選択します。
New / Select Database Connectionダイアログ・ボックスが開きます。 以下の接続情報を入力し、「Test」をクリックします。
Connection Name： hr
Username： hr
Password： ＜使用するパスワード＞（「Save Password」を選択）
Hostname： localhost
SID： ＜使用するSID＞
左下（Helpボタンの上）で接続のステータスを確認します。 Successと表示されているはずです。 「Save」をクリックしてから、「Connect」をクリックします。
HRユーザーへの権限付与と既存の統計情報の削除
SQL Tuning Advisorを実行するには、ユーザーに特定の権限が付与されている必要があります。 また、HRスキーマに関する統計情報を収集して管理するため、既存の統計情報を消去する必要があります。 以下の手順では、hrユーザーに対してSQL Tuning Advisor権限を付与し、既存の統計情報を削除します。
「SQL Worksheet」をクリックし、「system」ユーザーを選択します。
SQL Tuning Advisorを実行するための権限をhrユーザーに付与するには、次のコード行を入力し、 「Run Script」をクリックします。
grant advisor to hr;
grant administer sql tuning set to hr;
文に対する出力が表示されます。
Oracle Databaseでは、パフォーマンスを向上するためにさまざまな種類の統計情報を収集できます。 SQL Tuning Advisorが提供するいくつかの機能を示すため、既存の統計情報をHRスキーマから消去します。
スキーマ統計情報を削除するには、次のコード行を入力します。
exec DBMS_STATS.DELETE_SCHEMA_STATS ('hr');
文を選択して「Run Statement」をクリックします。
DBMS_STATSパッケージを使用すると、データベース・オブジェクトに対して収集されたオプティマイザの統計情報を表示して変更することができます。DELETE_SCHEMA_STATSプロシージャは、スキーマ全体の統計情報を削除します。
文に対する出力が表示されます。
SQL文に対するSQL Tuning Advisorの実行
このトピックでは、SQL文に対してSQL Tuning Advisorを実行します。 SQL Tuning Advisorは、SQL文に対して4種類の分析を実行します。
すべての推奨事項はOverviewページに表示されます。 それぞれの推奨事項を個別に表示することもできます。
「SQL Worksheet」をクリックして、hrユーザーに対するSQL Worksheetを開きます。
ワークシートに次のSQL文を入力します。
select sum(e.salary), avg(e.salary), count(1), e.department_id from departments d, employees e group by e.department_id order by e.department_id;
SQL文を選択して「SQL Tuning Advisor」をクリックします。
SQL Tuning Advisorからの出力が表示されます。
左側のナビゲータで「Statistics」をクリックします。 この分析では、統計データが古くなったオブジェクトまたは欠落しているオブジェクトが特定され、問題を解決するための適切な推奨事項が提示されます。
左側のナビゲータで「SQL Profile」をクリックします。 ここで、SQL Tuning Advisorは、SQLプロファイルを生成することで実行計画を改善するように推奨しています。
「Detail」タブ・ページを開き、SQL Profile Findingを確認します。
左側のナビゲータで「Indexes」をクリックします。 ここでは、SQL文が索引によって改善されるかどうかが示されます。 必要に応じて、問合せパフォーマンスを大幅に改善する新しい索引が特定され、推奨事項として提示されます。
「Overview 」タブ・ページをクリックします。 ここでは、索引に関する推奨事項はありません。
左側のナビゲータで「Restructure SQL」をクリックします。 この分析では、選択したSQL文を再構成してパフォーマンスを向上させるための推奨事項が提示されます。
SQL Tuning Advisorによる推奨事項の実装
SQL Tuning Advisorによる推奨事項機能を実装することができます。 これにより、hrスキーマの統計情報を更新できます。 以下の手順を実行して、SQL Tuning Advisorの推奨事項を実装します。
接続ナビゲータで「hr」を右クリックし、「Gather Schema Statistics....」を選択します。
Gather Schema StatisticsのEstimate Percentで、表に含まれるすべての行が読み取られるようにするため、ドロップダウン・リストから「100」を選択します。 これにより、できるだけ正確な統計情報が収集されます。
「Apply」をクリックします。
確認メッセージが表示されます。 「OK」をクリックします。
SQL文に対してもう一度SQL Tuning Advisorを実行するには、SQL文を選択して「SQL Tuning Advisor」をクリックします。
SQL Tuning Advisorからの出力が表示されます。 統計情報が収集されたため、統計情報とSQLプロファイルのアドバイスが削除されました。
左側のナビゲータで、SQL Tuning AdvisorのImplement Typeをそれぞれクリックして、すべての推奨事項が実装されていることを確認します。
表示される問題を確認します。
表示される問題を確認します。
Restructure SQLタイプの推奨事項に未使用表の削除が残っています。
SQL文から"departments"表を削除し、「SQL Tuning Advisor」をクリックします。
select sum(e.salary), avg(e.salary), count(1), e.department_id from employees e
group by e.department_id order by e.department_id;
出力結果が表示されます。 すべての推奨事項が削除されています。
まとめ
- データベース接続の作成
- HRユーザーへの権限付与
- SQL文に対するSQL Tuning Advisorの実行
- SQL Tuning Advisorによる推奨事項の実装
- カリキュラム開発責任者： Dimpi Sarmah、Sharon Stephen
- 共著者： Ashley Chen、Swarnapriya Shridhar、Nancy Greenberg
このチュートリアルで学習した内容は、次のとおりです。
著者
このOracle by Exampleをナビゲートする際、以下の機能を使用できます。
- ヘッダー・ボタンの非表示：
- ヘッダー内のボタンを非表示にするには、タイトルをクリックします。 ボタンを再表示するには、もう一度タイトルをクリックします。
- トピック一覧ボタン：
- すべてのトピックの一覧です。 いずれかのトピックをクリックすると、その項に移動します。
- すべてのトピックを開く/閉じる：
- すべての項に対する詳細を表示または非表示にします。 デフォルトでは、すべてのトピックが閉じられています。
- すべてのイメージを表示/非表示：
- すべてのスクリーンショットを表示または非表示にします。 デフォルトでは、すべてのイメージが表示されています。
- 印刷：
- コンテンツを印刷します。 現在表示または非表示にされているコンテンツが印刷されます。
このチュートリアルの特定の項に移動するには、一覧からトピックを選択してください。