| Oracle® Databaseパフォーマンス・チューニング・ガイド 11gリリース2 (11.2) B56312-06 |
|
 前 |
 次 |
この章では、Oracle Databaseの自動SQLチューニング機能について説明します。自動SQLチューニングにより、複雑で繰返しの多い時間のかかる手動プロセスを自動化できます。
この章には次の項があります。
|
関連項目: Oracle Enterprise Manager(Enterprise Manager)での自動SQLチューニング機能の使用方法は、『Oracle Database 2日でパフォーマンス・チューニング・ガイド』を参照してください。 |
Oracle Databaseでは、オプティマイザを使用して、発行されたSQL文の実行計画を生成します。オプティマイザは、次のモードで動作します。
標準モード
オプティマイザは、SQLをコンパイルして実行計画を生成します。標準モードでは、大多数のSQL文に対して妥当な実行計画が生成されます。標準モードでは、オプティマイザは通常はミリ秒単位の厳密な時間的制約に従って動作します。
チューニング・モード
オプティマイザは、追加の分析を実行して、標準モードで生成された計画をさらに改善できるかどうかをチェックします。オプティマイザの出力は、実行計画ではなく、さらに優れた計画を生成するための一連のアクションと、その理論的根拠および予測されるメリットを示します。チューニング・モードで実行する場合、オプティマイザは自動チューニング・オプティマイザと呼ばれます。
チューニング・モードのオプティマイザでは、1つの文のチューニングに数分かかることがあります。問合せのハード解析を行うたびに自動チューニング・オプティマイザを起動すると、多くの時間およびリソースが消費されます。自動チューニング・オプティマイザは、データベースに重要な影響を及ぼす複雑で負荷の高いSQL文向けに設計されています。
自動データベース診断モニター(ADDM)は、SQLチューニングの最適な候補となる負荷の高いSQL文を事前に識別します(第6章「自動パフォーマンス診断」を参照)。自動SQLチューニング機能は、問題のあるSQL文を自動的に識別し、自動メンテナンス・タスクとしてシステム・メンテナンス期間中にチューニング推奨事項を実装します。
自動チューニング・オプティマイザは、次のタイプのチューニング分析を実行します。
オプティマイザは、実行計画を生成する際にオブジェクトの統計に依存します。これらの統計が失効しているか統計情報がない場合、オプティマイザは必要な情報を得られないため、不適切な実行計画が生成される可能性があります。自動チューニング・オプティマイザは、問合せオブジェクトごとに統計の欠落や失効がないかどうかをチェックし、次の2つのタイプの出力を生成します。
統計が失効または欠落しているオブジェクトに関して関連統計を収集するための推奨事項
オプティマイザ統計は自動的に収集されリフレッシュされるため、この問題が発生するのは自動オプティマイザ統計収集が無効になっている場合のみです。「自動オプティマイザ統計収集の管理」を参照してください。
統計が欠落しているオブジェクトに関する補足統計と、統計が失効しているオブジェクトに関する統計調整ファクタ
この補足情報は、SQLプロファイルと呼ばれるオブジェクトに格納されます。
SQLプロファイルは、SQL文に固有の補足情報です。概念上、SQL文に対するSQLプロファイルの役割は、表または索引に対する統計情報の役割と同じです。データベースでは、補足情報を使用して実行計画を改善できます。
アクセス・パスは、データベースからデータを取り出すための手段です。たとえば、索引を使用する問合せと、全表スキャンを使用する問合せでは、使用するアクセス・パスが異なります。
索引を使用すると、大規模な表の全体スキャンを実行する必要性が減少し、SQL文のパフォーマンスを大幅に改善できます。効率的な索引付けは、一般的なチューニング・テクニックです。自動チューニング・オプティマイザは、新しい索引によって、問合せパフォーマンスを大幅に改善できるかどうかを調査します。改善できる場合は、アドバイザにより索引作成が推奨されます。
自動チューニング・オプティマイザでは、索引に関する推奨事項がSQL全体のワークロードにどのように影響するかは分析されないため、典型的なSQLワークロードを持つSQL文に対してSQLアクセス・アドバイザを実行することも推奨されます。SQLアクセス・アドバイザは、索引作成がSQL全体のワークロードに与える影響を調べてから、推奨事項を作成します。「自動SQLチューニング機能」を参照してください。
自動チューニング・オプティマイザは、パフォーマンスを低下させる可能性のあるSQL文の構造の一般的な問題を識別します。たとえば、構文、セマンティクスまたは設計上の問題があります。いずれの場合も、自動チューニング・オプティマイザは、文の再構成に関連する提案を行います。提案される代替策は、元の文と類似していますが同じではありません。
たとえば、オプティマイザから、UNION演算子をUNION ALLで置き換えたり、NOT INをNOT EXISTSで置き換えるように提案される場合があります。このとき、提案された事項が、状況に適用可能かどうかを判断します。たとえば、スキーマの設計上、重複が発生する可能性がない場合は、UNION ALL演算子の方が、UNION演算子よりはるかに効率的です。このように変更するには、データ・プロパティを十分に理解し、実装前に慎重に考慮する必要があります。
SQLチューニング・アドバイザは、SQL文をチューニングする際に、リアルタイムおよび履歴パフォーマンス・データに文の代替実行計画があるかどうかを検索します。最初の計画以外の計画が存在する場合、SQLチューニング・アドバイザは、代替計画が見つかったことを報告します。
SQLチューニング・アドバイザは、代替実行計画を検証し、再生可能でない計画がある場合は通知します。再生可能な代替計画が見つかった場合、SQL計画ベースラインを作成して、将来これらの計画を選択するようオプティマイザに指示できます。
例17-1に、SELECT文の代替計画の検出を示します。
例17-1 代替計画の検出
2- Alternative Plan Finding
---------------------------
Some alternative execution plans for this statement were found by searching
the system's real-time and historical performance data.
The following table lists these plans ranked by their average elapsed time.
See section "ALTERNATIVE PLANS SECTION" for detailed information on each
plan.
id plan hash last seen elapsed (s) origin note
-- ---------- -------------------- ------------ --------------- ----------------
1 1378942017 2009-02-05/23:12:08 0.000 Cursor Cache original plan
2 2842999589 2009-02-05/23:12:08 0.002 STS
Information
-----------
- The Original Plan appears to have the best performance, based on the
elapsed time per execution. However, if you know that one alternative
plan is better than the Original Plan, you can create a SQL plan baseline
for it. This will instruct the Oracle optimizer to pick it over any other
choices in the future.
execute dbms_sqltune.create_sql_plan_baseline(task_name => 'TASK_XXXXX',
object_id => 2, task_owner => 'SYS', plan_hash => xxxxxxxx);
例17-1では、SQLチューニング・アドバイザにより、2つの計画が見つかりました(共有SQL領域とSQLチューニング・セットに各1個)。共有SQL領域の計画は、最初の計画と同じです。
SQLチューニング・アドバイザは、最初の計画の経過時間が代替計画よりも劣る場合のみ代替計画を推奨します。この場合、SQLチューニング・アドバイザは、パフォーマンスの最も優れている計画で、SQL計画ベースラインを作成するよう推奨します。例17-1では、代替計画よりも最初の計画の方がパフォーマンスが優れているため、SQLチューニング・アドバイザは代替計画の使用を推奨していません。
例17-2では、SQLチューニング・アドバイザ出力の代替計画セクションに、最初の計画と代替計画が含まれ、それらのパフォーマンスが要約されています。最も重要な統計は、経過時間です。最初の計画では索引が使用されていますが、代替計画では全表スキャンが使用され、経過時間が.002秒だけ増加しています。
例17-2 代替計画セクション
Plan 1 ------ Plan Origin :Cursor Cache Plan Hash Value :1378942017 Executions :50 Elapsed Time :0.000 sec CPU Time :0.000 sec Buffer Gets :0 Disk Reads :0 Disk Writes :0 Notes: 1. Statistics shown are averaged over multiple executions. 2. The plan matches the original plan. -------------------------------------------- | Id | Operation | Name | -------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | MERGE JOIN | | | 3 | INDEX FULL SCAN | TEST1_INDEX | | 4 | SORT JOIN | | | 5 | TABLE ACCESS FULL| TEST | -------------------------------------------- Plan 2 ------ Plan Origin :STS Plan Hash Value :2842999589 Executions :10 Elapsed Time :0.002 sec CPU Time :0.002 sec Buffer Gets :3 Disk Reads :0 Disk Writes :0 Notes: 1. Statistics shown are averaged over multiple executions. ------------------------------------- | Id | Operation | Name | ------------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | HASH JOIN | | | 3 | TABLE ACCESS FULL| TEST | | 4 | TABLE ACCESS FULL| TEST1 | -------------------------------------
SQLチューニング・アドバイザの推奨にかかわらず、代替計画を適用するには、DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINEをコールします。このプロシージャを使用して、任意の既存の再生可能な計画でSQL計画ベースラインを作成できます。
SQLチューニング・アドバイザは、入力として1つ以上のSQL文を取得し、自動チューニング・オプティマイザを起動して文に対するSQLチューニングを実行します。出力には、提案または推奨事項と、各推奨事項の理論的根拠と予測されるメリットが含まれます。推奨事項は、オブジェクトの統計、新しい索引の作成、SQL文の再構成またはSQLプロファイルの作成に関するものです。ユーザーは、推奨事項を受け入れるかどうかを選択してSQL文のチューニングを完了できます。
データベースでは、システム・メンテナンス期間中に、SQLチューニング・アドバイザを使用して、問題のある文を識別し、チューニング推奨事項を実装して、自動的にSQL文をチューニングできます。自動的に実行する場合、SQLチューニング・アドバイザは自動SQLチューニング・アドバイザと呼ばれています。
この項では、自動SQLチューニング・アドバイザの管理方法について説明します。
|
関連項目: 自動メンテナンス・タスクの詳細は、『Oracle Database管理者ガイド』を参照してください。 |
Oracle Databaseでは、自動ワークロード・リポジトリ(AWR)から、チューニングの対象となる選択された高負荷SQL文に対して自動的にSQLチューニング・アドバイザを実行します。このタスクは、自動SQLチューニングと呼ばれ、夜間にデフォルト・メンテナンス・ウィンドウで実行されます。デフォルトでは、自動SQLチューニングの実行は、最大でも約1時間です。メンテナンス・ウィンドウの属性(開始時刻と終了時刻、頻度、曜日など)は、カスタマイズできます。
データベースでは、自動SQLチューニングの開始後、次のステップが実行されます。
AWR内でSQLのチューニング候補を識別します。
Oracle Databaseでは、AWRの統計を分析して、チューニング対象として可能なSQL文のリストを生成します。これらの文には、データベースに重大な影響を及ぼす、繰り返し実行される負荷の高い文が含まれます。
データベースでは、改善の可能性が高い実行計画を持つSQL文のみをチューニングします。再帰的SQL、最近1か月以内にチューニングされた文、パラレル問合せ、DML、DDL、および並行処理の問題によってパフォーマンスに問題のあるSQL文は、無視されます。
チューニングの候補として選択されたSQL文を、パフォーマンスへの影響に基づいて順序付けします。データベースでは、過去1週間に選択された文について、AWRでCPU時間とI/O回数を合計して影響を計算します。
SQL文ごとにSQLチューニング・アドバイザをコールして個別にチューニングします。
チューニング・プロセスでは、すべてのタイプの推奨事項が検討および報告されますが、自動的に実装できるのはSQLプロファイルのみです。
SQL文を実行してSQLプロファイルをテストします。
SQLプロファイルが推奨された場合は、プロファイルを使用したSQL文と、使用しない場合のSQL文を実行して、新しいプロファイルをテストします。パフォーマンスの改善が3倍以上である場合、そのSQLプロファイルを受け入れます(ACCEPT_SQL_PROFILESタスク・パラメータがTRUEの場合のみ)。それ以外の場合は、自動SQLチューニング・レポートで、SQLプロファイルを作成するよう推奨のみを行います。
オプションとして、3倍のパフォーマンスの向上という基準を満たしている場合は、SQLプロファイルを実装します。
SQLプロファイルを実装するかどうかを決定する際に、他の要素も考慮されます。たとえば、SQL文で参照されているオブジェクトのオプティマイザ統計が失効している場合、プロファイルは実装されません。自動的に実装されたSQLプロファイルは、タイプがAUTOであることがDBA_SQL_PROFILESビューに示されます。
データベースでSQL計画管理を使用している場合で、SQL文のSQL計画ベースラインが存在する場合は、SQLプロファイルを作成する際に新しい計画ベースラインが追加されます。これにより、オプティマイザは、プロファイル作成後すぐに新しい計画を使用します。第15章「SQL計画の管理の使用方法」を参照してください。
自動SQLチューニング・レポートを使用すると、自動SQLチューニング・プロセスの実行中または実行後の任意の時点でその結果を参照できます。このレポートには、分析されたすべてのSQL文、生成された推奨事項、および自動的に実装されたSQLプロファイルの詳細が含まれます。
図17-1は、自動SQLチューニング中にデータベースによって実行される手順を示しています。
自動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チューニングも無効になります。
|
関連項目:
|
Oracle Database 11gリリース2(11.2.0.2)以降、DBMS_AUTO_SQLTUNEパッケージを使用して自動SQLチューニング・タスクの動作を設定できるようになりました。前のリリースでは、かわりにDBMS_SQLTUNEを使用します。
表17-2に、自動SQLチューニングに固有の構成パラメータを示します。
表17-1 SET_AUTO_TUNING_TASK_PARAMETERの自動SQLチューニング・パラメータ
| パラメータ | 説明 |
|---|---|
|
|
SQLプロファイルを自動的に受け入れるかどうかを指定します。 |
|
|
アドバイザ・フレームワーク・スキーマにタスク履歴を保存する日数を指定します。デフォルトでは、タスク履歴は30日間保存され、期限切れとなります。 |
|
|
各自動SQLチューニング・タスクで受け入れるSQLプロファイルの制限数を指定します。各自動SQLチューニング・タスクで受け入れるSQLプロファイルの制限数は、日次ベースでシステムに加えることのできる変更の許容レベルに基づいて設定してください。 |
|
|
受け入れるSQLプロファイルの合計制限数を指定します。 |
DBMS_AUTO_SQLTUNEパッケージを使用するには、DBAロールを持つか、管理者によって付与されたEXECUTE権限を持つ必要があります。EXECUTE_AUTO_TUNING_TASKプロシージャのみは例外で、これはSYSによってのみ実行できます。
自動SQLチューニングを設定する方法
SQL*Plusを起動し、DBA権限でデータベースに接続します(またはEXECUTE_AUTO_TUNING_TASKを実行する予定であれば、SYSとして接続します)。
DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETERプロシージャを実行します。
次の例では、SQLチューニング・アドバイザによって推奨されたSQLプロファイルを自動的に受け入れるよう自動SQLチューニング・タスクを設定しています。
BEGIN
DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
parameter => 'ACCEPT_SQL_PROFILES', value => 'TRUE');
END;
/
|
関連項目:
|
Oracle Database 11gリリース2(11.2.0.2)以降、DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASKファンクションを使用して自動SQLチューニング・レポートを生成できるようになりました。前のリリースでは、かわりにDBMS_SQLTUNEパッケージを使用します。
このレポートには、自動SQLチューニング・タスクの実行に関する様々な情報が含まれます。レポートに含まれるセクションに応じて、次のセクションで自動SQLチューニング・タスクに関する情報を参照できます。
一般情報
一般情報セクションには、自動SQLチューニング・タスクの概要(レポートに指定された入力に関する情報、メンテナンス中にチューニングされたSQL文の数、作成されたSQLプロファイルの数など)が含まれます。
サマリー
サマリー・セクションには、メンテナンス・ウィンドウ内にチューニングされたSQL文(SQL識別子別)と各SQLプロファイルの利点の見積り、またはそのSQLプロファイルを使用したSQL文のテスト実行後の実際の実行統計がリストされます。
チューニングの検出結果
このセクションには、SQLチューニング・アドバイザによって分析された各SQL文に関する次の情報が含まれます。
各SQL文に関連付けられたすべての検出結果
データベースでプロファイルが受け入れられたかどうかと、その理由
データベースでSQLプロファイルが現在有効であるかどうか
SQLプロファイルのテスト時に取得された実行統計の詳細
EXPLAIN PLAN
このセクションには、SQLチューニング・アドバイザにより分析された各SQL文で使用された新旧のEXPLAIN PLANが表示されます。
エラー
このセクションには、自動SQLチューニング・タスクで発生したすべてのエラーがリストされます。
DBMS_AUTO_SQLTUNEを使用して自動SQLチューニング・レポートを表示する方法
SQL*Plusを起動し、適切な権限でデータベースに接続します。
DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASKファンクションを実行します。
次の例では、実装されなかった推奨事項を含め、最新の実行において分析されたすべてのSQL文を表示するテキスト・レポートが生成されます。
VARIABLE my_rept CLOB;
BEGIN
:my_rept :=DBMS_AUTO_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チューニング・アドバイザを手動で起動して、1つ以上のSQL文をオンデマンドでチューニングできます。複数の文をチューニングする場合、SQLチューニング・セット(STS)を作成する必要があります。SQLチューニング・セットは、SQL文とその実行コンテキストを格納するデータベース・オブジェクトです。SQLチューニング・セットは、コマンドラインAPIまたはEnterprise Managerを使用して作成できます。「SQLチューニング・セットの管理」を参照してください。
SQLチューニング・アドバイザの入力は、以下を含む複数のソースから取り込むことができます。
ADDM(自動データベース診断モニター)
主入力ソースは、ADDMです。デフォルトで、ADDMは1時間ごとにプロアクティブに実行され、過去1時間に自動ワークロード・リポジトリ(AWR)により収集された主要統計が分析され、高負荷のSQL文など、パフォーマンスの問題が識別されます。高負荷のSQL文が識別されると、ADDMにより、そのSQLに対してSQLチューニング・アドバイザを実行するように推奨されます。「自動データベース診断モニターの概要」を参照してください。
AWR
2番目に重要な入力ソースは、自動ワークロード・リポジトリ(AWR)です。AWRは、CPU使用率や待機時間など、関連統計でランク付けされた高負荷のSQL文を含むシステム・アクティビティについて、通常のスナップショットを作成します。
AWRを参照して負荷の高いSQL文を手動で識別できます。これらの文に対してSQLチューニング・アドバイザを実行できますが、この操作は自動SQLチューニングの一環としてOracle Databaseによって自動的に実行されます。デフォルトで、AWRには過去8日間のデータが保持されます。この方法を使用してAWRの保存期間内に実行された負荷の高いSQLを見つけてチューニングできます。「自動ワークロード・リポジトリの概要」を参照してください。
共有SQL領域
3番目の入力ソースは共有SQL領域です。このソースを使用して、AWRにまだ取得されていない最近のSQL文をチューニングします。共有SQL領域とAWRは、現在の時刻からAWRの許容保存期間(デフォルトは8日以上)までの高負荷のSQL文を識別してチューニングする機能を提供します。
SQLチューニング・セット
SQLチューニング・アドバイザのもう1つの入力ソースは、SQLチューニング・セットです。SQLチューニング・セット(STS)は、SQL文とその実行コンテキストを格納するデータベース・オブジェクトです。STSには、パフォーマンスを個別に測定することや、パフォーマンスが予測より低下しているSQL文を識別することを目的として、まだデプロイされていないSQL文を含めることができます。SQL文のセットを入力として使用する場合、最初にSTSを作成し、使用する必要があります。「SQLチューニング・セットの管理」を参照してください。
SQLチューニング・アドバイザには、チューニング・タスクの有効範囲と期間を管理するオプションがあります。チューニング・タスクの有効範囲は、次のいずれかに設定できます。
制限付き
この場合、SQLチューニング・アドバイザでは、統計チェック、アクセス・パス分析およびSQL構造分析に基づいて推奨事項が生成されます。SQLプロファイルの推奨事項は生成されません。
包括的
この場合、SQLチューニング・アドバイザでは、制限付きの有効範囲で実行されるすべての分析と、SQLプロファイリングが実行されます。このオプションを選択した場合は、チューニング・タスクの時間制限も指定できます。デフォルトでは30分です。
SQL文を分析した後、SQLチューニング・アドバイザにより、実行計画の最適化に関するアドバイス、提案された最適化の理論的根拠、見積られるパフォーマンスの向上およびアドバイスを実装するコマンドが提供されます。SQL文を最適化するには、推奨事項を受け入れるかどうかを選択します。
SQLチューニング・アドバイザを実行するための推奨インタフェースは、Enterprise Managerです。可能なかぎり、SQLチューニング・アドバイザは、『Oracle Database 2日でパフォーマンス・チューニング・ガイド』で説明しているように、Enterprise Managerで実行します。Enterprise Managerが使用可能でない場合、DBMS_SQLTUNEパッケージのプロシージャを使用してSQLチューニング・アドバイザを実行できます。このAPIを使用するには、ユーザーは特定の権限を付与されている必要があります。
DBMS_SQLTUNEパッケージを使用したSQLチューニング・アドバイザの実行は、次のように複数の手順で構成されるプロセスです。
SQLチューニング・セットの作成(複数のSQL文をチューニングする場合)
SQLチューニング・タスクの作成
SQLチューニング・タスクの実行
SQLチューニング・タスクの結果の表示
必要に応じた推奨事項の実装
単一のSQL文に対するSQLチューニング・タスクを作成できます。複数の文をチューニングする場合は、最初にSQLチューニング・セット(STS)を作成する必要があります。STSは、SQL文とその実行コンテキストを格納するデータベース・オブジェクトです。STSは、コマンドラインAPIを使用して手動で作成するか、Enterprise Managerを使用して自動的に作成できます。「SQLチューニング・セットの管理」を参照してください。
図17-2は、DBMS_SQLTUNEパッケージを使用してSQLチューニング・アドバイザを実行する場合に必要な手順を示しています。
この項では、次の項目について説明します。
|
関連項目:
|
チューニング・タスクは、1つのSQL文のテキスト、複数の文を含むSQLチューニング・セット、共有SQL領域からSQL識別子で選択したSQL文、またはAWRから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チューニング・セットからの文のパーセンテージ・フィルタ |
チューニング・タスクを作成した後、タスクを実行し、チューニング・プロセスを開始します。たとえば、次のPL/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文は、AWR、共有SQL領域またはユーザー提供のカスタムSQLなど、様々なSQLソースからSQLチューニング・セットにロードできます。STSに含まれるのは、次のとおりです。
SQL文のセット
関連する実行コンテキスト(ユーザー・スキーマ、アプリケーション・モジュール名およびアクション、バインド値のリストおよびカーソル・コンパイル環境など)
関連する基本実行統計(経過時間、CPUタイム、バッファ読取り、ディスク読取り、処理された行数、カーソル・フェッチ、実行数、実行完了数、オプティマイザ・コストおよびコマンドのタイプなど)
各SQL文の関連実行計画と行ソース統計(オプション)
SQL文は、アプリケーション・モジュール名とアクション、または任意の実行統計を使用してフィルタできます。また、実行統計の任意の組合せに基づいて文をランク付けすることもできます。
STSをSQLチューニング・アドバイザへの入力として使用して、ユーザー指定の他の入力パラメータに基づいてSQL文の自動チューニングを実行できます。SQLチューニングセットを別のデータベースにエクスポートすることで、SQLワークロードをデータベース間で転送してリモート・パフォーマンス診断およびチューニングを実行できます。本番データベースにパフォーマンスの悪いSQL文がある場合、開発者が直接本番システム上で調査およびチューニングを実行することは望ましくありません。DBAにより、開発者が安全に分析およびチューニングできるテスト用データベースに、問題のあるSQL文を転送できます。SQLチューニング・セットを転送するには、DBMS_SQLTUNEパッケージを使用します。
可能な場合、SQLチューニング・セットは、『Oracle Database 2日でパフォーマンス・チューニング・ガイド』で説明しているように、Enterprise Managerで管理します。Enterprise Managerが使用可能でない場合、DBMS_SQLTUNEパッケージのプロシージャを使用してSQLチューニング・セットを管理できます。
通常、STS操作を使用する順序は次のとおりです。
新規STSの作成
このタスクについては、「SQLチューニング・セットの作成」を参照してください。
STSのロード
このタスクについては、「SQLチューニング・セットのロード」を参照してください。
STSを選択して内容を確認
このタスクについては、「SQLチューニング・セットの内容の表示」を参照してください。
必要に応じてSTSを更新
このタスクについては、「SQLチューニング・セットの変更」を参照してください。
入力にSTSを使用してチューニング・タスクを作成
必要に応じて、他のシステムへのSTSの転送
このタスクについては、「SQLチューニング・セットの転送」を参照してください。
完了時にSTSを削除
このタスクについては、「SQLチューニング・セットの削除」を参照してください。
このAPIを使用するには、所有するSQLチューニング・セットを管理するためのADMINISTER SQL TUNING SETシステム権限が必要です。または、任意のSQLチューニング・セットを管理するためのADMINISTER ANY SQL TUNING SETシステム権限が必要です。
図17-3に、SQLチューニング・セットAPIを使用する場合の手順を示します。
この項では、次の項目について説明します。
|
関連項目:
|
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または共有SQL領域です。ワークロード・リポジトリおよび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ビューを使用して表示できます。
検索条件に基づいて、STSで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をインポートします。
Oracle Database 10g(リリース2)以降で作成された任意のデータベースにSQLチューニング・セットを転送できます。これは、SQLパフォーマンス・アナライザを使用してテスト用データベースで回帰をチューニングする場合に役立ちます。たとえば、次のシナリオでSTSを転送できます。
Oracle Database 11gリリース2(11.2)で作成した本番データベースに、低下したSQLを含むSTSが存在する場合。
Oracle Database 11gリリース1(11.1)またはOracle Database 10gで作成したリモートのテスト用データベースでSQLパフォーマンス・アナライザの試行を実行する場合。
STSを本番データベースからテスト用データベースにコピーして、SQLパフォーマンス・アナライザの試行で回帰をチューニングする場合。
SQLチューニング・セットを転送するには、次の手順を実行します。
CREATE_STGTAB_SQLSETプロシージャを使用して、SQLチューニング・セットのエクスポート先にステージング表を作成します。
次の例では、dba1にmy_10g_staging_tableを作成し、ステージング表の形式を10.2に指定します。
BEGIN
DBMS_SQLTUNE.create_stgtab_sqlset(
table_name => 'my_10g_staging_table',
schema_name => 'dba1',
db_version => DBMS_SQLTUNE.STS_STGTAB_10_2_VERSION );
END;
/
PACK_STGTAB_SQLSETプロシージャを使用して、このステージング表にSQLチューニング・セットをエクスポートします。
次の例では、dba1.my_10g_staging_tableに、hrが所有するSTS my_stsを移入します。
BEGIN
DBMS_SQLTUNE.pack_stgtab_sqlset(
sqlset_name => 'my_sts',
sqlset_owner => 'hr',
staging_table_name => 'my_10g_staging_table',
staging_schema_owner => 'dba1',
db_version => DBMS_SQLTUNE.STS_STGTAB_10_2_VERSION );
END;
/
選択したメカニズム(Oracle Data Pumpまたはデータベース・リンクなど)を使用して、SQLチューニング・セットのインポート先のデータベースにステージング表を移動します。
SQLチューニング・セットのインポート先となるデータベースで、UNPACK_STGTAB_SQLSETプロシージャを使用してステージング表からSQLチューニング・セットをインポートします。
次の例は、ステージング表にあるSQLチューニング・セットをインポートする方法を示しています。
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
sqlset_name => '%',
replace => TRUE,
staging_table_name => 'my_10g_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ファンクションでは、特定の間隔で共有SQL領域を繰り返しポーリングして、全システム・ワークロードを取得できます。このファンクションは、SELECT_CURSOR_CACHEおよびLOAD_SQLSETプロシージャを繰り返し使用する場合よりも効率的に共有SQL領域を長期間にわたって取得できます。また、高負荷SQL文のワークロードのみを取得するAWR、またはデータ・ソースに1度のみアクセスするLOAD_SQLSETプロシージャとは対照的に、ワークロード全体を効果的に取得します。
STSへの参照の追加および削除
ADD_SQLSET_REFERENCEファンクションでは、既存のSTSへの新規参照が追加され、クライアントが使用中であることが示されます。このファンクションでは、追加された参照の識別子が戻されます。REMOVE_SQLSET_REFERENCEプロシージャは、STSを非アクティブにし、クライアントにより使用されなくなったことを示します。
SQLプロファイルは、SQL文に固有の補足情報です。
この項では、次の項目について説明します。
|
関連項目: Enterprise Managerを使用してSQLプロファイルを管理する方法は、『Oracle Database 2日でパフォーマンス・チューニング・ガイド』を参照してください。 |
SQLプロファイルには、自動SQLチューニングの際に検出された不適切なオプティマイザの見積りに対する修正が含まれます。この情報により、オプティマイザによるカーディナリティおよび選択性の予測が改善され、より適切な計画を選択できるようになります。
SQLプロファイルには、個別の実行計画に関する情報は含まれません。オプティマイザには、計画を選択する際に、次の情報ソースがあります。
データベース構成、バインド変数値、オプティマイザ統計、データ・セットなどを含む環境。
SQLプロファイルの補足的な統計情報。
SQLプロファイルの環境が変化した場合、オプティマイザは新しい計画を作成できます。
SQLプロファイルは、SQL計画管理とともに使用することも、SQL計画管理なしで使用することもできます。SQL計画管理を使用する場合、オプティマイザが選択する計画は、有効な計画ベースラインである必要があります。ベースラインに文の計画が複数存在する場合、プロファイルを使用することでベースラインで最小コストの計画を選択できます。
図17-4に、SQL文とSQLプロファイルの関係を示します。オプティマイザは、プロファイルと環境を使用して問合せの計画を生成します。この例では、文の計画は、SQL計画ベースラインにあります。
SQLプロファイルには、次の利点があります。
プロファイルは、ヒントおよびストアド・アウトラインとは異なり、オプティマイザを特定の計画またはサブプランに結び付けません。プロファイルは、不適切な見積りを修正し、それぞれの状況に合った最適な計画を選択する柔軟性をオプティマイザに提供します。
プロファイルを使用する場合は、ヒントとは異なり、アプリケーション・ソース・コードの変更は不要です。
データベースによるSQLプロファイルの使用は、ユーザーに対して透過的です。
SQLをチューニングする際に、自動チューニングを行う文を選択し、SQLチューニング・アドバイザを実行します。データベースでは、次のタイプの文をプロファイルできます。
DML文(SELECT、SELECT句を含むINSERT、UPDATEおよびDELETE)
CREATE TABLE文(AS SELECT句の場合のみ)
MERGE文(更新または挿入操作)
SQLチューニング・アドバイザは、自動チューニング・オプティマイザを起動して推奨事項を生成します。SQLプロファイルの受入れの推奨は、検出で発生します。
例17-3では、コストの高いいくつかの結合を使用するSELECT文に対して、よりよい計画が検出されています。DBMS_SQLTUNE.ACCEPT_SQL_PROFILEを実行してプロファイルを受け入れることで、文の実行が98.53%高速化します。
例17-3 サンプルSQLプロファイルの検出
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement. Choose
one of the following SQL profiles to implement.
Recommendation (estimated benefit: 99.45%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'my_task',
object_id => 3, task_owner => 'SH', replace => TRUE);
Validation results
------------------
The SQL profile was tested by executing both its plan and the original plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time.
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: PARTIAL COMPLETE
Elapsed Time(us): 15467783 226902 98.53 %
CPU Time(us): 15336668 226965 98.52 %
User I/O Time(us): 0 0
Buffer Gets: 3375243 18227 99.45 %
Disk Reads: 0 0
Direct Writes: 0 0
Rows Processed: 0 109
Fetches: 0 109
Executions: 0 1
Notes
-----
1. The SQL profile plan was first executed to warm the buffer cache.
2. Statistics for the SQL profile plan were averaged over next 3 executions.
SQLチューニング・アドバイザは、自動並列度(自動DOP)機能を使用するプロファイルを受け入れるよう推奨する場合もあります。パラレル問合せプロファイルは、元の計画がシリアルの場合で、パラレル実行により実行時間の長い問合せのレスポンス時間を大幅に削減できる場合にのみ推奨されます。SQLチューニング・アドバイザが自動DOPを使用するプロファイルを推奨する場合、SQL文にパラレル実行を使用した場合のパフォーマンスのオーバーヘッドの詳細がレポートで提供されます。
パラレル実行の推奨では、SQLチューニング・アドバイザは、2つのSQLプロファイルの推奨(シリアル実行とパラレル実行に各1個)を提供する場合があります。この場合、パラレルで実行するためのディレクティブを除いて、パラレル・プロファイルは標準プロファイルと同一です。
例17-4に、パラレル問合せの推奨を示します。この例では、並列度が7の場合、レスポンス時間が大幅に改善されますが、リソースの消費がほぼ25%増加します。ユーザーは、データベース・スループットの削減がレスポンス時間の増加に値するかどうかを判断する必要があります。
例17-4 パラレル問合せの推奨
Recommendation (estimated benefit: 99.99%)
------------------------------------------
- Consider accepting the recommended SQL profile to use parallel execution
for this statement.
execute dbms_sqltune.accept_sql_profile(task_name => 'gfk_task',
object_id => 3, task_owner => 'SH', replace => TRUE,
profile_type => DBMS_SQLTUNE.PX_PROFILE);
Executing this query parallel with DOP 7 will improve its response time
82.22% over the SQL profile plan. However, there is some cost in enabling
parallel execution. It will increase the statement's resource consumption by
an estimated 24.43% which may result in a reduction of system throughput.
Also, because these resources are consumed over a much smaller duration, the
response time of concurrent statements might be negatively impacted if
sufficient hardware capacity is not available.
The following data shows some sampled statistics for this SQL from the past
week and projected weekly values when parallel execution is enabled.
Past week sampled statistics for this SQL
-----------------------------------------
Number of executions 0
Percent of total activity .29
Percent of samples with #Active Sessions > 2*CPU 0
Weekly DB time (in sec) 76.51
Projected statistics with Parallel Execution
--------------------------------------------
Weekly DB time (in sec) 95.21
プロファイルを承諾した場合、プロファイルが作成されてデータ・ディクショナリに永続的に保存されます。プロファイルが構築された文をユーザーが発行した場合、問合せオプティマイザは(標準モード)、環境およびSQLプロファイルを使用して適切にチューニングされた計画を構築します。
データベースでSQL計画管理を使用している場合で、SQL文のSQL計画ベースラインが存在する場合は、SQLプロファイルを作成する際に、ベースラインに新しい計画が追加されます。それ以外の場合、新しい計画ベースラインは追加されません。
SQLプロファイルと計画ベースラインの間に厳密な関係は存在しません。ハード解析の際に、オプティマイザはSQLプロファイルを使用して、使用可能な計画から最善の計画ベースラインを選択します。場合によっては、SQLプロファイルによって、オプティマイザが異なる計画ベースラインを選択することもあります。
通常、SQLプロファイルは自動SQLチューニングの一部としてEnterprise Managerで処理されますが、DBMS_SQLTUNEパッケージを使用してSQLプロファイルを管理することもできます。APIを使用するには、ADMINISTER SQL MANAGEMENT OBJECT権限が必要です。
表17-3に、SQLプロファイルを管理する主なプロシージャおよびファンクションを示します。
表17-3 SQLプロファイルのDBMS_SQLTUNE API
| プロシージャまたはファンクション | 説明 | 項 |
|---|---|---|
|
|
指定されたチューニング・タスクのSQLプロファイルを作成します。 |
|
|
|
既存のSQLプロファイル・オブジェクトの特定の属性を変更します。 |
|
|
|
指定されたSQLプロファイルをデータベースから削除します。 |
|
|
|
システム間でのSQLプロファイルのコピーに使用されるステージング表を作成します。 |
|
|
|
プロファイル・データを |
|
|
|
ステージング表に格納されたプロファイル・データを使用して、システムでプロファイルを作成します。 |
|
図17-5に、SQLプロファイルのAPIを使用する場合に実行できるアクションを示します。
表が大きくなり、索引が作成または削除されると、プロファイルの計画が変化する可能性があります。対応する文のデータ配分やアクセス・パスに変更があっても、プロファイルは引き続き関連付けられています。一般に、SQLプロファイルをリフレッシュする必要はありません。
長い間に、プロファイルの内容は古くなる可能性があります。この場合、対応するSQL文のパフォーマンスが低下する場合があります。パフォーマンスの悪い文は、負荷の高いSQLまたは上位SQLとして現れる場合があります。この場合、自動SQLチューニング・タスクが文を高負荷のSQLとして再び取得します。文の新しいプロファイルを作成できます。
|
関連項目: DBMS_SQLTUNEパッケージの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。 |
DBMS_SQLTUNE.ACCEPT_SQL_PROFILEプロシージャまたはファンクションを使用して、SQLチューニング・アドバイザが推奨するSQLプロファイルを受け入れることができます。このプロシージャは、SQLプロファイルを作成してデータベースに格納します。
通常では、SQLチューニング・アドバイザが推奨するSQLプロファイルを受け入れます。索引およびSQLプロファイルが推奨されている場合は、両方とも使用するか、SQLプロファイルのみを使用します。索引を作成した場合、オプティマイザは、新しい索引を選択するためにプロファイルが必要になる場合があります。
場合によっては、SQLチューニング・アドバイザは、改善されたシリアル計画よりも、さらによいパラレル計画を検出する場合もあります。この場合は、標準およびパラレルのSQLプロファイルが推奨され、文に対する最善のシリアル計画と最善のパラレル計画のいずれかを選択できます。パラレル計画は、レスポンス時間の増加がスループットの低下に値する場合にのみ受け入れます(例17-4を参照)。
SQLプロファイルを受け入れるには、次のようにします。
DBMS_SQLTUNE.ALTER_SQL_PROFILEプロシージャをコールします。
次の例で、my_sql_tuning_taskはSQLチューニング・タスクの名前で、my_sql_profileはSQLプロファイルの名前です。PL/SQLブロックは、パラレル実行を使用するプロファイルを受け入れます(profile_type)。
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',
profile_type => DBMS_SQLTUNE.PX_PROFILE,
force_match => TRUE );
END;
/
force_match設定は、文の一致を制御します。通常、SQLプロファイルを受け入れると、ハッシュ関数を使用して生成されるSQLシグネチャを通じてSQL文に関連付けられます。このハッシュ関数は、シグネチャを生成する前に、SQL文を大文字に変更し、余分な空白をすべて削除します。したがって、大/小文字および空白のみが異なるすべてのSQL文で同じSQLプロファイルを使用できます。
force_matchをTRUEに設定した場合、リテラル値をバインド変数に正規化した後、同じテキストを持つすべてのSQL文がSQLプロファイルの追加の対象となります。この設定により、テキストのリテラル値のみが異なるSQLでは、SQLプロファイルの共有が可能になるため、リテラル値のみを使用するアプリケーションに有効です。SQLテキストにリテラル値とバインド変数の両方が使用されている場合、またはforce_matchパラメータがFALSE(デフォルト値)に設定されている場合、リテラル値は正規化されません。
SQLプロファイルの情報は、DBA_SQL_PROFILESビューに表示できます。
ALTER_SQL_PROFILEプロシージャで既存のSQLプロファイルの属性を変更できます。変更可能な属性は、STATUS、NAME、DESCRIPTIONおよびCATEGORYです。
CATEGORY属性により、プロファイルを適用できるユーザー・セッションが決まります。DBA_SQL_PROFILES.CATEGORYへの問合せによって、CATEGORY属性を参照できます。デフォルトでは、すべてのプロファイルはDEFAULTカテゴリです。これは、SQLTUNE_CATEGORY初期化パラメータがDEFAULTに設定されたすべてのセッションがプロファイルを使用できることを意味します。
SQLプロファイルのカテゴリを変更することで、どのセッションが作成するプロファイルの影響を受けるかを確認できます。たとえば、カテゴリをDEVに設定すると、SQLTUNE_CATEGORY初期化パラメータがDEVに設定されているセッションのみがプロファイルを使用できます。他のすべてのセッションは、SQLプロファイルにアクセスできないため、SQL文の実行計画がSQLプロファイルの影響を受けることはありません。この方法によって、限定的な環境でプロファイルをテストしてから、他のセッションで使用することができます。
SQLプロファイルを変更するには、次のようにします。
DBMS_SQLTUNE.ALTER_SQL_PROFILEプロシージャをコールします。
次の例では、my_sql_profileのSTATUS属性がDISABLEDに変更されています。これにより、SQLプロファイルは、SQLのコンパイルの際に使用されません。
BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name => 'my_sql_profile',
attribute_name => 'STATUS',
value => 'DISABLED');
END;
/
|
関連項目: SQLTUNE_CATEGORY初期化パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください。 |
DROP_SQL_PROFILEプロシージャを使用して、SQLプロファイルを削除できます。指定したプロファイル名が存在しない場合、エラーを無視するかどうかを指定できます。この例では、デフォルト値のFALSEを使用します。
SQLプロファイルを削除するには、次のようにします。
DBMS_SQLTUNE.DROP_SQL_PROFILEプロシージャをコールします。
次の例では、my_sql_profileというプロファイルを削除します。
BEGIN DBMS_SQLTUNE.DROP_SQL_PROFILE( name => 'my_sql_profile' ); END; /
SQLプロファイルを転送することができます。転送するには、SQLプロファイルをデータベースのSYSスキーマからステージング表にエクスポートした後、ステージング表から別のデータベースにSQLプロファイルをインポートします。SQLプロファイルは、同じリリースまたはそれ以降のリリースで作成された任意のOracleデータベースに転送できます。
SQLプロファイルを転送するには、次のようにします。
CREATE_STGTAB_SQLPROFプロシージャを使用して、SQLプロファイルのエクスポート先のステージング表を作成します。
次の例では、DBA1スキーマにmy_staging_tableを作成します。
BEGIN
DBMS_SQLTUNE.create_stgtab_sqlprof(
table_name => 'my_staging_table',
schema_name => 'DBA1' );
END;
/
PACK_STGTAB_SQLPROFプロシージャを使用して、SQLプロファイルをステージング表にエクスポートします。
次の例では、SQLプロファイルmy_profileをdba1.my_staging_tableに移入します。
BEGIN
DBMS_SQLTUNE.pack_stgtab_sqlprof(
profile_name => 'my_profile',
staging_table_name => 'my_staging_table',
staging_schema_owner => 'dba1' );
END;
/
選択したメカニズム(Oracle Data Pumpまたはデータベース・リンクなど)を使用して、SQLプロファイルのインポート先のデータベースにステージング表を移動します。
SQLプロファイルのインポート先となるデータベースで、UNPACK_STGTAB_SQLPROFプロシージャを使用してステージング表からSQLプロファイルをインポートします。
次の例に、ステージング表にあるSQLプロファイルをインポートする方法を示します。
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(
replace => TRUE,
staging_table_name => 'my_staging_table');
END;
/
この項では、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 = MANUALの場合、SQLプロファイルはSQLチューニング・アドバイザによって手動で作成されました。TYPE = AUTOの場合、プロファイルは自動SQLチューニングによって作成されました。
SQLチューニングの関連情報を含む動的ビュー(V$SQL、V$SQLAREA、V$SQLSTATSおよびV$SQL_BIND_DATAビューなど)。
|
関連項目: 静的データ・ディクショナリ・ビューおよび動的ビューの詳細は、『Oracle Databaseリファレンス』を参照してください。 |
