| Oracle Databaseパフォーマンス・チューニング・ガイド 11gリリース1（11.1） E05743-02 |
|
 戻る |
 次へ |
この章では、Oracle Databaseの自動SQLチューニング機能について説明します。自動SQLチューニングにより、複雑で繰返しの多い時間のかかる手動プロセスを自動化できます。
この章には次の項があります。
|
関連項目: Oracle Enterprise ManagerでOracle Databaseの自動SQLチューニング機能を使用する方法の詳細は、『Oracle Database 2日でパフォーマンス・チューニング・ガイド』を参照してください。 |
OracleデータベースでSQL文が実行されると、問合せオプティマイザの使用によりSQL文の実行計画が生成されます。問合せオプティマイザは、標準モードとチューニング・モードという2つのモードで動作します。
標準モードのオプティマイザでは、SQLがコンパイルされて実行計画が生成されます。このモードで生成される実行計画は、大多数のSQL文に対して妥当なものです。標準モードでは、オプティマイザは通常はミリ秒単位の厳密な時間的制約に従って動作し、その期間内に適切な実行計画を検出する必要があります。
チューニング・モードのオプティマイザでは、追加の分析が実行され、標準モードで生成された実行計画をさらに改善できるかどうかがチェックされます。問合せオプティマイザの出力には、実行計画ではなく、きわめて優れた計画を生成するための一連のアクション、その理論的根拠および予測されるメリットが示されます。チューニング・モードで実行されるオプティマイザを、自動チューニング・オプティマイザと呼びます。
チューニング・モードのオプティマイザでは、1つの文のチューニングに数分かかることがあります。問合せをハード解析するたびに、自動チューニング・オプティマイザを起動するための時間およびリソースの両方が集中的に使用されます。自動チューニング・オプティマイザは、システム全体に通常とは異なる影響を与える複雑で高負荷なSQL文に使用されることを意図した機能です。SQLチューニングの適切な候補となる高負荷のSQL文は、自動データベース診断モニター（ADDM）によりプロアクティブに識別されます。第6章「自動パフォーマンス診断」を参照してください。Oracle Databaseの自動SQLチューニング機能では、問題のあるSQL文を自動的に識別し、自動化メンテナンス・タスクとしてシステム・メンテナンス・ウィンドウ内にチューニング推奨事項を実装します。
自動チューニング・オプティマイザでは、次の4つのタイプのチューニング分析が実行されます。
問合せオプティマイザは、オブジェクト統計に依存して実行計画を生成します。これらの統計が失効または欠落している場合、オプティマイザに必要な情報がなく、不適切な実行計画が生成される可能性があります。自動チューニング・オプティマイザは、問合せオブジェクトごとに統計の欠落や失効がないかどうかをチェックし、次の2つのタイプの出力を生成します。
統計が失効または欠落しているオブジェクトに関して関連統計を収集するための推奨事項
オプティマイザ統計は自動的に収集されリフレッシュされるため、この問題が発生するのは自動オプティマイザ統計収集がオフになっていた場合のみです。「自動オプティマイザ統計収集」を参照してください。
統計が欠落しているオブジェクトに関する統計形式の補足情報と、統計が失効しているオブジェクトに関する統計調整ファクタ
この補足情報は、SQLプロファイルと呼ばれるオブジェクトに格納されます。
問合せオプティマイザでは、情報の欠落が原因で文の属性に関して不正確な見積りが生成され、そのために不適切な実行計画が生成される場合があります。従来は、オプティマイザが適切に決定できるようにアプリケーション・コードに手動でヒントを追加することで、この問題を解決してきました。パッケージ化されたアプリケーションの場合は、アプリケーション・コードを変更できず、不具合のログをアプリケーション・ベンダーに提供して修正されるまで待つ必要があります。
自動SQLチューニングは、この問題にSQLプロファイリング機能で対処します。自動チューニング・オプティマイザでは、SQLプロファイルと呼ばれるSQL文のプロファイルが作成されます。このプロファイルは、その文に固有の補助統計で構成されます。標準モードの問合せオプティマイザではカーディナリティ、選択性およびコストを見積りますが、これらの値が大幅にずれているために不適切な実行計画が生成されることがあります。SQLプロファイルは、サンプリングおよび部分実行テクニックを使用して追加情報を収集し、これらの見積りを検証し、必要に応じて調整することで、この問題に対処します。
SQLプロファイリング中に、自動チューニング・オプティマイザはSQL文の実行履歴情報も使用して、そのSQL文のOPTIMIZER_MODE初期化パラメータの設定をALL_ROWSからFIRST_ROWSに変更するなど、オプティマイザのパラメータを適切に設定します。
このタイプの分析の出力は、SQLプロファイルを受け入れるための推奨事項です。受け入れたSQLプロファイルは、データ・ディクショナリに永続的に格納されます。SQLプロファイルは特定の問合せに固有であることに注意してください。SQLプロファイルを受け入れると、標準モードのオプティマイザでは、実行計画の生成時にSQLプロファイル内の情報と通常のデータベース統計が併用されます。追加情報が使用可能になることで、アプリケーション・コードを変更しなくても、対応するSQL文に関して適切にチューニングされた計画を生成できます。
SQLプロファイルの有効範囲は、CATEGORYプロファイル属性で制御できます。この属性により、どのユーザー・セッションでプロファイルを適用できるかが決まります。SQLプロファイルのCATEGORY属性は、DBA_SQL_PROFILESビューのCATEGORY列で確認できます。デフォルトでは、すべてのプロファイルはDEFAULTカテゴリに作成されます。つまり、SQLTUNE_CATEGORY初期化パラメータがDEFAULTに設定されているユーザー・セッションはすべて、そのプロファイルを使用できます。
SQLプロファイルのカテゴリを変更すると、プロファイル作成の影響を受けるセッションを決定できます。たとえば、SQLプロファイルのカテゴリをDEVに設定すると、そのプロファイルを使用できるのはSQLTUNE_CATEGORY初期化パラメータがDEVに設定されているユーザー・セッションのみとなります。他のすべてのセッションにはSQLプロファイルへのアクセス権がなく、SQL文の実行計画はSQLプロファイルの影響を受けません。このテクニックを使用すると、SQLプロファイルを他のユーザー・セッションで使用可能にする前に、限定的な環境でテストできます。
|
関連項目: SQLTUNE_CATEGORY初期化パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください。 |
ストアド・アウトラインとは異なり、SQLプロファイルではSQL文の実行計画が凍結されないことに注意する必要があります。表が拡張されたり索引が作成または削除されるたびに、同じSQLプロファイルを使用して実行計画を変更できます。対応する文のデータ配分やアクセス・パスに変更があっても、SQLプロファイルに格納された情報は引き続き関連付けられています。一般的に、SQLプロファイルをリフレッシュする必要はありません。ただし、長期的には、その内容が陳腐化することがあり、再生成が必要になります。そのためには、同じ文に対してSQLチューニング・アドバイザを再実行し、SQLプロファイルを再生成します。
SQLプロファイルは、次のタイプの文に適用されます。
SELECT文
UPDATE文
INSERT文（SELECT句の場合のみ）
DELETE文
CREATE TABLE文（AS SELECT句の場合のみ）
MERGE文（更新または挿入操作）
SQLプロファイルの管理用に、完全なファンクション・セットが用意されています。「SQLプロファイル」を参照してください。
索引を使用すると、大規模な表の全表スキャンを実行する必要性が減少し、SQL文のパフォーマンスを大幅に改善できます。効率的な索引付けは、一般的なチューニング・テクニックです。自動チューニング・オプティマイザも、新規索引で問合せのパフォーマンスを大幅に改善できるかどうかを探索します。この種の索引が識別されると、その作成が推奨されます。
自動チューニング・オプティマイザでは、索引に関する推奨事項がSQL全体のワークロードにどのように影響するかは分析されないため、典型的なSQLワークロードを持つSQL文に対してSQLアクセス・アドバイザ・ユーティリティを実行することも推奨されます。SQLアクセス・アドバイザは、索引作成がSQL全体のワークロードに与える影響を調べてから、推奨事項を作成します。「自動SQLチューニング機能」を参照してください。
自動チューニング・オプティマイザでは、パフォーマンスを低下させる可能性のあるSQL文の構造に関して一般的な問題が識別されます。たとえば、文の構文、セマンティクスまたは設計上の問題があります。このような問題ごとに、自動チューニング・オプティマイザはSQL文の再構成について関連する提案を行います。提案される代替策は、元の文と類似していますが同じではありません。
たとえば、オプティマイザから、UNION演算子をUNION ALLで置き換えたり、NOT INをNOT EXISTSで置き換えるように提案される場合があります。その場合、アプリケーション開発者はアドバイスが状況に適用可能かどうかを判断できます。たとえば、スキーマ設計上、重複の発生が不可能な場合は、UNION演算子よりもUNION ALL演算子のほうが効率的です。このように変更するには、データ・プロパティを十分に理解し、実装前に慎重に考慮する必要があります。
SQLチューニング・アドバイザは、入力として1つ以上のSQL文を取り、自動チューニング・オプティマイザを起動して文に対するSQLチューニングを実行します。SQLチューニング・アドバイザの出力はアドバイスまたは推奨事項の形式で、各推奨事項の理論的根拠と予測されるメリットが含まれます。推奨事項は、オブジェクト統計の収集、新規索引の作成、SQL文の再構成またはSQLプロファイルの作成に関するものです。ユーザーは、推奨事項を受け入れるかどうかを選択してSQL文のチューニングを完了できます。
Oracle Databaseでは、問題のあるSQL文を識別し、システム・メンテナンス・ウィンドウ内にSQLチューニング・アドバイザを使用してチューニング推奨事項を実装することで、自動的にSQL文をチューニングできます。問題があると識別された単一のSQL文またはSQL文のセットに選択的にSQLチューニング・アドバイザを実行することも可能です。
Oracle Databaseでは、チューニング候補として適切な、自動ワークロード・リポジトリ（AWR）から選択された高負荷SQL文に対して自動的にSQLチューニング・アドバイザを実行します。このタスクは、自動SQLチューニングと呼ばれ、夜間の定期的なデフォルト・メンテナンス・ウィンドウで実行されます。メンテナンス・ウィンドウの属性（開始時刻と終了時刻、頻度、曜日など）は、カスタマイズできます。
|
関連項目: 自動化メンテナンス・タスクの詳細は、『Oracle Database管理者ガイド』を参照してください。 |
自動SQLチューニングが開始されると、デフォルトではメンテナンス・ウィンドウ内に1時間を限度として次の手順が実行されます。
AWR内でSQLのチューニング候補を識別します。
Oracle Databaseは、AWRの統計を分析し、チューニング候補となり得るSQL文のリストを生成します。これらの文には、システムに多大な影響を与えている反復的な高負荷の文が含まれます。改善の可能性の高い実行計画を持つSQL文のみがチューニングされます。最近1か月以内にチューニングされた再帰的SQLおよび文（パラレル問合せ、DML、DDL、および同時実行性を原因とするパフォーマンス問題のあるSQL文など）は、無視されます。候補として選択されたSQL文は、そのパフォーマンスへの影響に基づいて順序付けされます。SQL文のパフォーマンスへの影響は、過去1週間にそのSQL文に対してAWRで取得されたCPU時間とI/O時間を合計することで計算されます。
SQLチューニング・アドバイザをコールして各SQL文を個別にチューニングします。
チューニング・プロセス中は、すべての推奨事項タイプが考慮およびレポートされますが、自動的に実装されるのはSQLプロファイルのみです。
SQL文を実行してSQLプロファイルをテストします。
SQLプロファイルが推奨されると、その新規SQLプロファイルをテストするためにSQLプロファイルがある場合とない場合に分けてSQL文が実行されます。少なくとも3倍はパフォーマンスが向上する場合、そのSQLプロファイルは受け入れられます（ACCEPT_SQL_PROFILESタスク・パラメータがTRUEに設定されている場合）。それ以外の場合は、SQLプロファイルの作成を促す推奨事項が、自動SQLチューニング・レポートにレポートされるのみです。
SQLプロファイルが3倍のパフォーマンス向上という基準を満たしている場合、オプションでそのSQLプロファイルを実装します。
SQLプロファイルを実装するかどうかを決定する場合、その他の要因も考慮されます。たとえば、SQL文で参照されているオブジェクトに関して失効したオプティマイザ統計がある場合、SQLプロファイルは実装されません。自動的に実装されたSQLプロファイルを識別するには、そのタイプがDBA_SQL_PROFILESビューでAUTOに設定されているSQLプロファイルを確認します。
SQL計画の管理が使用され、SQL文に関して既存の計画ベースラインがすでに存在する場合、SQLプロファイルが作成されると新規計画ベースラインが追加されます。したがって、SQLプロファイルが作成された直後に、改善された新規SQL実行計画がオプティマイザによって使用されます。SQL計画の管理の詳細は、第15章「SQL計画の管理の使用方法」を参照してください。
自動SQLチューニング・レポートを使用すると、自動SQLチューニング・プロセスの実行中または実行後の任意の時点でその結果を参照できます。このレポートには、分析されたすべてのSQL文、生成された推奨事項、および自動的に実装されたSQLプロファイルの詳細が含まれます。
図17-1は、自動SQLチューニング・プロセス中にOracle Databaseによって実行される手順を示しています。
この項では、次の項目について説明します。
自動SQLチューニングは、自動化メンテナンス・タスク・インフラストラクチャの一部として実行されます。
自動SQLチューニングを有効化するには、次のようにDBMS_AUTO_TASK_ADMINパッケージのENABLEプロシージャを使用します。
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
自動SQLチューニングを無効化するには、次のようにDBMS_AUTO_TASK_ADMINパッケージのDISABLEプロシージャを使用します。
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
window_nameパラメータを使用して特定のウィンドウ名を渡すと、そのメンテナンス・ウィンドウのタスクのみを有効化または無効化できます。
STATISTICS_LEVELパラメータをBASICに設定すると、AWRによる自動統計収集が無効化され、結果として自動SQLチューニングも無効化されます。
|
関連項目:
|
自動SQLチューニング・タスクの動作は、DBMS_SQLTUNEパッケージを使用して構成します。APIを使用する場合、ユーザーには少なくともADVISOR権限が必要です。
DBMS_SQLTUNEパッケージでは、SQLチューニング・アドバイザの標準動作を構成する以外に、SET_TUNING_TASK_PARAMETERプロシージャを使用してタスク・パラメータを指定することで自動SQLチューニングを構成できます。自動チューニング・タスクはSYSに所有されるため、SYSユーザーのみがタスク・パラメータを設定できます。
表17-1に、構成可能な自動SQLチューニングに固有のパラメータを示します。
表17-1 SET_TUNING_TASK_PARAMETERの自動SQLチューニング・パラメータ
| パラメータ | 説明 |
|---|---|
|
|
SQLプロファイルを自動的に受け入れるかどうかを指定します。 |
|
|
各自動SQLチューニング・タスクで受け入れるSQLプロファイルの制限数を指定します。各自動SQLチューニング・タスクで受け入れるSQLプロファイルの制限数は、日次ベースでシステムに加えることのできる変更の許容レベルに基づいて設定してください。 |
|
|
受け入れるSQLプロファイルの合計制限数を指定します。 |
|
|
アドバイザ・フレームワーク・スキーマにタスク履歴を保存する日数を指定します。デフォルトでは、タスク履歴は30日間保存され、期限切れとなります。 |
自動SQLチューニングを構成するには、次のようにDBMS_SQLTUNEパッケージのSET_TUNING_TASK_PARAMETERプロシージャを使用します。
BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
task_name => 'SYS_AUTO_SQL_TUNING_TASK',
parameter => 'ACCEPT_SQL_PROFILES', value => 'TRUE');
END;
/
この例では、SQLチューニング・アドバイザによって推奨されたSQLプロファイルを自動的に受け入れるよう自動SQLチューニング・タスクが構成されます。
|
関連項目:
|
DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASKファンクションを使用して生成される自動SQLチューニング・レポートには、自動SQLチューニング・タスクのすべての実行に関する情報が含まれます。このレポートを実行するには、DBA_ADVISORビューに対するADVISOR権限とSELECT権限が必要です。DBMS_SQLTUNE.REPORT_TUNING_TASKファンクションを使用して生成される標準のSQLチューニング・レポートには、SQLチューニング・アドバイザの単一のタスク実行に関する情報のみが含まれますが、自動SQLチューニング・レポートには、自動SQLチューニング・タスクの複数の実行に関する情報が含まれます。
自動SQLチューニング・レポートを表示するには、次のようにDBMS_SQLTUNEパッケージのREPORT_AUTO_TUNING_TASKファンクションを実行します。
variable my_rept CLOB;
BEGIN
:my_rept :=DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK(
begin_exec => NULL,
end_exec => NULL,
type => 'TEXT',
level => 'TYPICAL',
section => 'ALL',
object_id => NULL,
result_limit => NULL);
END;
/
print :my_rept
この例では、実装されなかった推奨事項を含め、最新の実行において分析されたすべてのSQL文を表示するテキスト・レポートが生成されます（レポートのすべてのセクションが含まれます）。
|
関連項目: DBMS_SQLTUNEパッケージの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。 |
レポートに含まれるセクションに応じて、自動SQLチューニング・タスクに関する情報を参照できます。レポートのセクションは次のとおりです。
一般情報
一般情報セクションには、自動SQLチューニング・タスクの概要（レポートに指定された入力に関する情報、メンテナンス中にチューニングされたSQL文の数、作成されたSQLプロファイルの数など）が含まれます。
サマリー
サマリー・セクションには、メンテナンス・ウィンドウ内にチューニングされたSQL文（SQL識別子別）と各SQLプロファイルの利点の見積り、またはそのSQLプロファイルを使用したSQL文のテスト実行後の実際の実行統計がリストされます。
チューニングの検出結果
このセクションには、SQLチューニング・アドバイザによって分析された各SQL文に関する次の情報が含まれます。
各SQL文に関連付けられたすべての検出結果
システムでプロファイルが受け入れられたかどうかと、その理由
システムでSQLプロファイルが現在有効であるかどうか
SQLプロファイルのテスト時に取得された実行統計の詳細
EXPLAIN PLAN
このセクションには、SQLチューニング・アドバイザにより分析された各SQL文で使用された新旧のEXPLAIN PLANが表示されます。
エラー
このセクションには、自動SQLチューニング・タスクで発生したすべてのエラーがリストされます。
SQLチューニング・アドバイザは、1つ以上のSQL文のオンデマンド・チューニング用として手動で起動できます。複数の文をチューニングする場合、SQLチューニング・セット（STS）を作成する必要があります。SQLチューニング・セットは、SQL文とその実行コンテキストを格納するデータベース・オブジェクトです。SQLチューニング・セットは、コマンドラインAPIまたはOracle Enterprise Managerを使用して作成できます。「SQLチューニング・セット」を参照してください。
この項では、次の項目について説明します。
SQLチューニング・アドバイザの入力は、複数のソースから取り込むことができます。次のような入力ソースがあります。
自動データベース診断モニター
主入力ソースは、自動データベース診断モニター（ADDM）です。デフォルトで、ADDMは1時間ごとにプロアクティブに実行され、過去1時間に自動ワークロード・リポジトリ（AWR）により収集された主要統計が分析され、高負荷のSQL文など、パフォーマンスの問題が識別されます。高負荷のSQL文が識別されると、そのSQLに対してSQLチューニング・アドバイザを実行するように推奨されます。「自動データベース診断モニターの概要」を参照してください。
自動ワークロード・リポジトリ
2番目に重要な入力ソースは、自動ワークロード・リポジトリ（AWR）です。AWRは、CPU使用率や待機時間など、関連統計でランク付けされた高負荷のSQL文を含むシステム・アクティビティについて、通常のスナップショットを作成します。
AWRを表示して高負荷SQL文を手動で識別し、それらの文に対してSQLチューニング・アドバイザを実行できます（ただし、この操作は自動SQLチューニング・プロセスの一環としてOracle Databaseによって自動的に実行されます）。デフォルトで、AWRには過去8日間のデータが保持されます。この方法を使用してAWRの保存期間内に実行された高負荷SQLを検索し、チューニングできます。「自動ワークロード・リポジトリの概要」を参照してください。
カーソル・キャッシュ
3番目の入力ソースはカーソル・キャッシュです。このソースは、まだAWRに収集されていない最新のSQL文のチューニングに使用されます。カーソル・キャッシュとAWRには、現在の時刻からAWRの許容保存期間（デフォルトは8日以上）の範囲内でさかのぼって、高負荷のSQL文を識別してチューニングする機能が用意されています。
SQLチューニング・セット
その他の可能なSQLチューニング・アドバイザの入力ソースは、SQLチューニング・セットです。SQLチューニング・セット（STS）は、SQL文とその実行コンテキストを格納するデータベース・オブジェクトです。STSには、パフォーマンスを個別に測定することや、パフォーマンスが予測より低下しているSQL文を識別することを目的として、まだデプロイされていないSQL文を含めることができます。SQL文セットを入力として使用する場合は、最初にSQLチューニング・セット（STS）を構成して格納する必要があります。「SQLチューニング・セット」を参照してください。
SQLチューニング・アドバイザには、チューニング・タスクの有効範囲と期間を管理するためのオプションが用意されています。チューニング・タスクの有効範囲は、制限付きまたは包括的として設定できます。
制限付きオプションを選択すると、SQLチューニング・アドバイザでは、統計チェック、アクセス・パス分析およびSQL構造分析に基づいて推奨事項が生成されます。SQLプロファイルの推奨事項は生成されません。
包括的オプションを選択すると、SQLチューニング・アドバイザでは、制限付きの有効範囲で実行されるすべての分析とSQLプロファイリングが実行されます。このオプションを選択した場合は、チューニング・タスクの時間制限も指定できます。デフォルトでは30分です。
SQL文を分析した後、SQLチューニング・アドバイザにより、実行計画の最適化に関するアドバイス、提案された最適化の理論的根拠、見積られるパフォーマンスの向上およびアドバイスを実装するコマンドが提供されます。SQL文を最適化するには、推奨事項を受け入れるかどうかを選択するだけです。
SQLチューニング・アドバイザを実行するための推奨インタフェースは、Oracle Enterprise Managerです。可能なかぎり、SQLチューニング・アドバイザは、『Oracle Database 2日でパフォーマンス・チューニング・ガイド』の手順に従ってOracle Enterprise Managerで実行する必要があります。Oracle Enterprise Managerが使用できない場合、DBMS_SQLTUNEパッケージのプロシージャを使用してSQLチューニング・アドバイザを実行します。このAPIを使用するには、ユーザーは特定の権限を付与されている必要があります。
|
関連項目: DBMS_SQLTUNEパッケージのセキュリティ・モデルの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。 |
DBMS_SQLTUNEパッケージを使用したSQLチューニング・アドバイザの実行は、次のように複数の手順で構成されるプロセスです。
SQLチューニング・セットの作成（複数のSQL文をチューニングする場合）
SQLチューニング・タスクの作成
SQLチューニング・タスクの実行
SQLチューニング・タスクの結果の表示
必要に応じた推奨事項の実装
SQLチューニング・タスクは単一のSQL文に対して作成できます。複数の文をチューニングする場合は、最初にSQLチューニング・セット（STS）を作成する必要があります。STSは、SQL文とその実行コンテキストを格納するデータベース・オブジェクトです。STSは、コマンドラインAPIを使用して手動で作成する方法と、Oracle Enterprise Managerを使用して自動的に作成する方法があります。「SQLチューニング・セット」を参照してください。
図17-2は、DBMS_SQLTUNEパッケージを使用してSQLチューニング・アドバイザを実行する場合に必要な手順を示しています。
この項では、次の項目について説明します。
|
関連項目: DBMS_SQLTUNEパッケージの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。 |
チューニング・タスクは、1つのSQL文、複数の文を含むSQLチューニング・セット、カーソル・キャッシュからSQL識別子で選択したSQL文または自動ワークロード・リポジトリからSQL識別子で選択したSQL文のテキストから作成できます。
たとえば、SQLチューニング・アドバイザを使用して指定のSQL文テキストを最適化するには、CLOB引数として渡すSQL文を指定してチューニング・タスクを作成する必要があります。次のPL/SQLコードでは、ユーザーHRにADVISOR権限が付与されており、ファンクションはHRスキーマのemployees表に対してユーザーHRとして実行されます。
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'SELECT /*+ ORDERED */ * ' ||
'FROM employees e, locations l, departments d ' ||
'WHERE e.department_id = d.department_id AND ' ||
'l.location_id = d.location_id AND ' ||
'e.employee_id < :bnd';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
bind_list => sql_binds(anydata.ConvertNumber(100)),
user_name => 'HR',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'my_sql_tuning_task',
description => 'Task to tune a query on a specified employee');
END;
/
この例で、100はSQL_BINDS型のファンクション引数として渡された:bndバインド変数の値、HRはCREATE_TUNING_TASKファンクションでSQL文の分析に使用されるユーザーです。有効範囲はアドバイザでSQLプロファイル分析も実行されることを意味するCOMPREHENSIVEに設定されており、60はファンクションを実行できる最大秒数です。この他に、タスク名および説明の値が提供されています。
CREATE_TUNING_TASKファンクションでは、指定したタスク名が戻されるか、一意のタスク名が生成されます。他のAPIを使用している場合にこのタスクを指定するには、このタスク名を使用できます。特定の所有者に関連付けられているタスク名を表示するには、次の文を実行します。
SELECT task_name FROM DBA_ADVISOR_LOG WHERE owner = 'HR';
作成後にそのパラメータをDBMS_SQLTUNEパッケージのSET_TUNING_TASK_PARAMETERプロシージャを使用して構成することで、SQLチューニング・タスクを微調整できます。
BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
task_name => 'my_sql_tuning_task',
parameter => 'TIME_LIMIT', value => 300);
END;
/
この例では、SQLチューニング・タスクの最大実行時間が300秒に変更されています。
表17-2に、SET_TUNING_TASK_PARAMETERプロシージャを使用して構成可能なパラメータを示します。
表17-2 SET_TUNING_TASK_PARAMETERプロシージャのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
チューニング・タスクの有効範囲を次のように指定します。
|
|
|
SQL文を解析する際のユーザー名 |
|
|
タスクが削除されるまでの日数 |
|
|
タスクの実行時に |
|
|
タスクがタイムアウトするまでの時間制限（秒単位） |
|
|
各SQL文の時間制限（秒単位） |
|
|
推奨事項の利点を検証するために、SQLチューニング・アドバイザでSQL文をテスト実行するかどうかを次のように指定します。
|
|
|
SQLチューニング・セットで使用される基本フィルタ |
|
|
SQLチューニング・セットで使用されるオブジェクト・フィルタ |
|
|
SQLチューニング・セットで使用されるプラン・フィルタ |
|
|
SQLチューニング・セットで使用される第1ランキング・メジャー |
|
|
SQLチューニング・セットで使用される第2ランキング・メジャー |
|
|
SQLチューニング・セットで使用される第3ランキング・メジャー |
|
|
SQLチューニング・セットで使用される（ |
|
|
チューニングされるSQL文の最大数 |
|
|
SQLチューニング・セットからの文のパーセンテージ・フィルタ |
チューニング・タスクを作成した後、タスクを実行し、チューニング・プロセスを開始する必要があります。たとえば、次のようにします。
BEGIN DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task' ); END; /
他のSQLチューニング・アドバイザ・タスクと同様に、EXECUTE_TUNING_TASK APIを使用して自動チューニング・タスクSYS_AUTO_SQL_TUNING_TASKを実行することもできます。SQLチューニング・アドバイザにより、自動実行時と同じ分析およびアクションが実行されます。実行名をAPIに渡して、新規実行の名前を付けることも可能です。
USER_ADVISOR_TASKSビューの情報を検討してタスクの状態をチェックするか、V$SESSION_LONGOPSビューでタスク実行の進捗をチェックできます。たとえば、次のようにします。
SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'my_sql_tuning_task';
V$ADVISOR_PROGRESSビューでSQLチューニング・アドバイザの実行の進捗状況をチェックできます。たとえば、次のようにします。
SELECT sofar, totalwork FROM V$ADVISOR_PROGRESS WHERE user_name = 'HR' AND task_name = 'my_sql_tuning_task';
|
関連項目: V$ADVISOR_PROGRESSビューの詳細は、『Oracle Databaseリファレンス』を参照してください。 |
タスクの実行後に、REPORT_TUNING_TASKファンクションを使用して結果レポートを表示します。たとえば、次のようにします。
SET LONG 1000 SET LONGCHUNKSIZE 1000 SET LINESIZE 100 SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task') FROM DUAL;
このレポートには、SQLチューニング・アドバイザのすべての検出結果および推奨事項が含まれます。提案される推奨事項ごとに、その実装に必要なSQLコマンド、理論的根拠およびメリットが提供されます。
チューニング・タスクおよび結果の追加情報は、DBAビューに表示されます。「SQLチューニング情報ビュー」を参照してください。
次のAPIを使用して、SQLチューニング・タスクを管理できます。
INTERRUPT_TUNING_TASK（実行中にタスクに割り込み、中間結果を取得して通常終了）
RESUME_TUNING_TASK（前回割り込まれたタスクを再開）
CANCEL_TUNING_TASK（実行中にタスクを取り消し、タスクからすべての結果を削除）
RESET_TUNING_TASK（実行中にタスクをリセットし、タスクからすべての結果を削除し、タスクを初期の状態に戻す）
DROP_TUNING_TASK（タスクを削除し、タスクに関連付けられたすべての結果を削除）
SQLチューニング・セット（STS）は、1つ以上のSQL文とその実行統計および実行コンテキストを含むデータベース・オブジェクトであり、ユーザーによる優先順位ランキングを含む場合もあります。SQL文は、自動ワークロード・リポジトリ、カーソル・キャッシュまたはユーザー提供のカスタムSQLなど、様々なSQLソースからSQLチューニング・セットにロードできます。STSに含まれるのは、次のとおりです。
SQL文のセット
関連する実行コンテキスト（ユーザー・スキーマ、アプリケーション・モジュール名およびアクション、バインド値のリストおよびカーソル・コンパイル環境など）
関連する基本実行統計（経過時間、CPUタイム、バッファ読取り、ディスク読取り、処理された行数、カーソル・フェッチ、実行数、実行完了数、オプティマイザ・コストおよびコマンドのタイプなど）
各SQL文の関連実行計画と行ソース統計（オプション）
SQL文は、アプリケーション・モジュール名とアクション、または任意の実行統計を使用してフィルタできます。また、実行統計の任意の組合せに基づいてSQL文をランク付けすることもできます。
SQLチューニング・セットをSQLチューニング・アドバイザへの入力として使用すると、ユーザーが指定した他の入力パラメータに基づいてSQL文の自動チューニングが実行されます。SQLチューニング・セットはデータベース間で転送可能であり、あるシステムから別のシステムへエクスポートできます。これにより、リモート・パフォーマンス診断およびチューニングのためのSQLワークロードをデータベース間で転送できます。本番システム上にパフォーマンスの悪いSQL文がある場合、開発者が直接本番システム上で調査およびチューニングを実行しないようにすることをお薦めします。この機能を使用すると、DBAは、開発者が安全に分析およびチューニングできるテスト・システムに、問題のあるSQL文を転送できます。SQLチューニング・セットを転送するには、DBMS_SQLTUNEパッケージ・プロシージャを使用します。
SQLチューニング・セットを管理するための推奨インタフェースは、Oracle Enterprise Managerです。可能なかぎり、SQLチューニング・セットは、『Oracle Database 2日でパフォーマンス・チューニング・ガイド』の手順に従ってOracle Enterprise Managerで管理する必要があります。Oracle Enterprise Managerが使用できない場合、DBMS_SQLTUNEパッケージのプロシージャを使用してSQLチューニング・セットを管理します。一般に、STS操作は次の順序で使用します。
新規STSの作成
STSのロード
STSを選択して内容を確認
必要に応じてSTSを更新
入力にSTSを使用してチューニング・タスクを作成
必要に応じて他システムへSTSを転送
完了時にSTSを削除
このAPIを使用するには、所有するSQLチューニング・セットを管理するADMINISTER SQL TUNING SETシステム権限が必要です。または、任意のSQLチューニング・セットを管理するADMINISTER ANY SQL TUNING SETシステム権限が必要です。
図17-3は、SQLチューニング・セットAPIを使用した場合に必要な手順を示しています。
この項では、次の項目について説明します。
|
関連項目: DBMS_SQLTUNEパッケージの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。 |
CREATE_SQLSETプロシージャは、データベースに空のSTSオブジェクトを作成するために使用されます。たとえば、次のプロシージャでは、特定の期間中にI/O集中型のSQL文をチューニングするために使用できるSTSオブジェクトが作成されます。
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'my_sql_tuning_set',
description => 'I/O intensive workload');
END;
/
my_sql_tuning_setはデータベース内のSTSの名前であり、'I/O intensive workload'はSTSに割り当てられた説明です。
LOAD_SQLSETプロシージャでは、選択したSQL文がSTSに移入されます。STSに移入するための標準ソースは、ワークロード・リポジトリ、他のSTSまたはカーソル・キャッシュです。ワークロード・リポジトリおよびSTSのどちらの場合も、事前定義済のテーブル・ファンクションを使用して、新規STSに移入する列をソースから選択できます。
次の例では、プロシージャ・コールを使用して、AWRベースラインpeak baselineからmy_sql_tuning_setがロードされます。このデータは、経過時間の順に上位30のSQL文のみが選択されるようにフィルタ済です。最初のREFカーソルがオープンされ、指定のベースラインから選択します。次に、文とその統計がベースラインからSTSにロードされます。
DECLARE
baseline_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN baseline_cursor FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
'peak baseline',
NULL, NULL,
'elapsed_time',
NULL, NULL, NULL,
30)) p;
DBMS_SQLTUNE.LOAD_SQLSET(
sqlset_name => 'my_sql_tuning_set',
populate_cursor => baseline_cursor);
END;
/
SELECT_SQLSETテーブル・ファンクションでは、STSの内容が読み取られます。STSが作成および移入された後、異なるフィルタ基準を使用してSTS内のSQLを参照できます。この目的のため、SELECT_SQLSETプロシージャが提供されます。
次の例では、STS内でバッファ取得に対するディスク読取りの比率が75%以上のSQL文が表示されます。
SELECT * FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET( 'my_sql_tuning_set', '(disk_reads/buffer_gets) >= 0.75'));
作成されてロードされたSQLチューニング・セットのその他の詳細も、DBA_SQLSET、DBA_SQLSET_STATEMENTSおよびDBA_SQLSET_BINDSなどのDBAビューを使用して表示できます。
SQL文は、検索条件に基づいてSQLチューニング・セットから更新および削除できます。次の例では、実行回数が49回以下のSQL文がDELETE_SQLSETプロシージャによりmy_sql_tuning_setから削除されます。
BEGIN
DBMS_SQLTUNE.DELETE_SQLSET(
sqlset_name => 'my_sql_tuning_set',
basic_filter => 'executions < 50');
END;
/
SQLチューニング・セットは、他のシステムへ転送できます。まずSTSをあるシステムからステージング表にエクスポートし、次にステージング表から別のシステムにSTSをインポートします。
SQLチューニング・セットを転送する手順は次のとおりです。
CREATE_STGTAB_SQLSETプロシージャを使用して、SQLチューニング・セットをエクスポートする場所にステージング表を作成します。
次の例は、staging_tableという名前のステージング表の作成方法を示しています。表名では大/小文字が区別されます。
BEGIN DBMS_SQLTUNE.CREATE_STGTAB_SQLSET( table_name => 'staging_table' ); END; /
PACK_STGTAB_SQLSETプロシージャを使用して、このステージング表にSQLチューニング・セットをエクスポートします。
次の例は、my_stsという名前のSQLチューニング・セットをステージング表にエクスポートする方法を示しています。
BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
sqlset_name => 'my_sts',
staging_table_name => 'staging_table');
END;
/
選択したメカニズム（datapumpまたはデータベース・リンクなど）を使用してSQLチューニング・セットがインポートされるシステムに、ステージング表を移動します。
SQLチューニング・セットのインポート対象となるシステムで、UNPACK_STGTAB_SQLSETプロシージャを使用して、ステージング表からSQLチューニング・セットをインポートします。
次の例は、ステージング表にあるSQLチューニング・セットをインポートする方法を示しています。
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
sqlset_name => '%',
replace => TRUE,
staging_table_name => 'staging_table');
END;
/
DROP_SQLSETプロシージャは、不要になったSTSを削除するために使用されます。たとえば、次のようにします。
BEGIN DBMS_SQLTUNE.DROP_SQLSET( sqlset_name => 'my_sql_tuning_set' ); END; /
次のAPIを使用してSTSを管理できます。
STS内のSQL文の属性の更新
UPDATE_SQLSETプロシージャは、STS名およびSQL識別子で識別される既存のSTS内のSQL文の属性（PRIORITYまたはOTHERなど）を更新します。
全システム・ワークロードの取得
CAPTURE_CURSOR_CACHE_SQLSETファンクションでは、特定の間隔でカーソル・キャッシュを繰り返しポーリングして、全システム・ワークロードを取得できます。このファンクションは、SELECT_CURSOR_CACHEおよびLOAD_SQLSETプロシージャを繰り返し使用するよりも効果的に、長期間にわたりカーソル・キャッシュを取得できます。また、高負荷SQL文のワークロードのみを取得するAWR、またはデータ・ソースに1度のみアクセスするLOAD_SQLSETプロシージャとは対照的に、ワークロード全体を効果的に取得します。
STSへの参照の追加および削除
ADD_SQLSET_REFERENCEファンクションでは、既存のSTSへの新規参照が追加され、クライアントが使用中であることが示されます。このファンクションでは、追加された参照の識別子が戻されます。REMOVE_SQLSET_REFERENCEプロシージャは、STSを非アクティブにし、クライアントにより使用されなくなったことを示すために使用されます。
通常、SQLプロファイルは、自動SQLチューニング・プロセスの一部としてOracle Enterprise Managerで処理されますが、DBMS_SQLTUNEパッケージを介して管理できます。SQLプロファイルAPIを使用するには、ADMINISTER SQL MANAGEMENT OBJECT権限が必要です。
図17-4は、SQLプロファイルAPIを使用した場合に必要な手順を示しています。
この項では、次の項目について説明します。
|
関連項目: DBMS_SQLTUNEパッケージの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。 |
SQLチューニング・アドバイザがSQLプロファイルの使用を推奨する場合、推奨されたSQLプロファイルを受け入れる必要があります。SQLチューニング・アドバイザで索引およびSQLプロファイルの使用が推奨されている場合、両方を使用する必要があります。DBMS_SQLTUNE.ACCEPT_SQL_PROFILEプロシージャを使用して、SQLチューニング・アドバイザにより推奨されたSQLプロファイルを受け入れることができます。これにより、SQLプロファイルが作成され、データベースに格納されます。たとえば、次のようにします。
DECLARE
my_sqlprofile_name VARCHAR2(30);
BEGIN
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name => 'my_sql_tuning_task',
name => 'my_sql_profile',
force_match => TRUE);
END;
この例では、my_sql_tuning_taskは、SQLチューニング・タスクの名前であり、my_sql_profileは受け入れるSQLプロファイルの名前です。
通常、受け入れたSQLプロファイルは、ハッシュ関数を使用して生成された特殊なSQLシグネチャを介してSQL文と関連付けられます。このハッシュ関数は、シグネチャを生成する前に、SQL文の大/小文字（SQL文全体を大文字に変更）および空白（余分な空白を削除）を正規化します。このように、同じSQLプロファイルは、大/小文字の使用と空白のみが異なる、本質的に同じすべてのSQL文に対して有効です。ただし、force_matchをtrueに設定することで、SQLプロファイルは、リテラル値をバインド変数に正規化した後で同じテキストを持つ全SQL文に対しても有効です。これは、リテラル値のみが異なるテキストを持つSQLにSQLプロファイルの共有を許可するため、バインド変数よりもリテラル値を使用するアプリケーションで便利な場合があります。SQLテキストにリテラル値とバインド変数の両方が使用されている場合、またはこのパラメータがfalse（デフォルト値）に設定されている場合、リテラル値は正規化されません。
SQL計画の管理が使用され、SQL文に関して既存の計画ベースラインがすでに存在する場合、SQLプロファイルが作成されると新規計画ベースラインが追加されます。SQL計画の管理が使用されない場合、SQLプロファイルが作成されても新規計画ベースラインは追加されません。SQLプロファイルと計画ベースラインの間に厳密な関係はありません。SQL文をハード解析する場合、オプティマイザでは、SQLプロファイルを使用して、利用可能な計画ベースラインから最善のものを選択します。条件が異なると、SQLプロファイルの影響で、オプティマイザは異なる計画ベースラインを選択する場合があります。SQL計画の管理の詳細は、第15章「SQL計画の管理の使用方法」を参照してください。
SQLプロファイルの情報は、DBA_SQL_PROFILESビューで表示できます。
既存のSQLプロファイルのSTATUS、NAME、DESCRIPTIONおよびCATEGORY属性を、ALTER_SQL_PROFILEプロシージャで変更できます。たとえば、次のようにします。
BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name => 'my_sql_profile',
attribute_name => 'STATUS',
value => 'DISABLED');
END;
/
この例でmy_sql_profileは、変更するSQLプロファイルの名前です。ステータス属性がDISABLEDに変更されているのは、SQLコンパイル時にSQLプロファイルが使用されないことを意味します。
DROP_SQL_PROFILEプロシージャによりSQLプロファイルを削除できます。たとえば、次のようにします。
BEGIN DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'my_sql_profile'); END; /
この例では、my_sql_profileは、削除するSQLプロファイルの名前です。名前が存在しない場合に発生したエラーを無視するかどうかも指定できます。この例の場合、デフォルト値のFALSEが確定されます。
この項では、SQL文のチューニング用に収集された情報を確認するために表示できるビューについて説明します。これらのビューにアクセスするには、DBA権限が必要です。
アドバイザ情報ビュー（DBA_ADVISOR_TASKS、DBA_ADVISOR_EXECUTIONS、DBA_ADVISOR_FINDINGS、DBA_ADVISOR_RECOMMENDATIONSおよびDBA_ADVISOR_RATIONALEビューなど）。
SQLチューニング情報ビュー（DBA_SQLTUNE_STATISTICS、DBA_SQLTUNE_BINDSおよびDBA_SQLTUNE_PLANSビューなど）。
SQLチューニング・セット・ビュー（DBA_SQLSET、DBA_SQLSET_BINDS、DBA_SQLSET_STATEMENTSおよびDBA_SQLSET_REFERENCESビューなど）。
SQLチューニング・セット内の文に対して取得した実行計画の情報は、DBA_SQLSET_PLANSビューおよびUSER_SQLSET_PLANSビューに表示されます。
SQLプロファイル情報はDBA_SQL_PROFILESビューに表示されます。
TYPEパラメータは、SQLプロファイルがSQLチューニング・アドバイザによって手動で作成されたか（TYPE = MANUAL）、自動SQLチューニングによって自動で作成されたか（TYPE = AUTO）を示します。
SQLチューニングの関連情報を含む動的ビュー（V$SQL、V$SQLAREA、V$SQLSTATSおよびV$SQL_BINDSビューなど）。
|
関連項目: 静的データ・ディクショナリ・ビューおよび動的ビューの詳細は、『Oracle Databaseリファレンス』を参照してください。 |
