6 実行計画の説明と表示
SQLチューニングには、文を説明する方法と文の計画を表示する方法に関する知識が不可欠です。
6.1 実行計画の概要
実行計画は、SQL文を実行するためにデータベースが実行する一連の操作です。
6.1.1実行計画のコンテンツ
実行計画の操作のみでは、よく調整された文と最適には機能しない文を区別できません。
計画は一連のステップで構成されています。各ステップでは、データベースからデータ行を物理的に取得するか、文を発行したユーザーのためにデータ行を準備します。次の計画は、employees
表とdepartments
表の結合を示しています。
SQL_ID g9xaqjktdhbcd, child number 0
-------------------------------------
SELECT employee_id, last_name, first_name, department_name from
employees e, departments d WHERE e.department_id = d.department_id and
last_name like 'T%' ORDER BY last_name
Plan hash value: 1219589317
----------------------------------------------------------------------------------------
| Id | Operation | Name |Rows | Bytes |Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | NESTED LOOPS | | 5 | 190 | 5 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 5 | 110 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_NAME_IX | 5 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 16 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("LAST_NAME" LIKE 'T%')
filter("LAST_NAME" LIKE 'T%')
4 - filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
行ソース・ツリーは、実行計画の中核です。このツリーには、次の情報が表示されます。
-
文で参照されている表の結合順序
前述の計画では、
employees
は外部行ソース、departments
は内部行ソースです。 -
文に記述された各表へのアクセス・パス
前述の計画では、オプティマイザは
employees
へのアクセスに索引スキャンの使用を選択し、departments
へのアクセスに全体スキャンの使用を選択しています。 -
文の結合操作に影響される表の結合方法
前述の計画では、オプティマイザはネステッド・ループ結合を選択しています。
-
フィルタ、ソート、集計などのデータ操作
前述の計画では、オプティマイザは
T
で始まる姓でフィルタ処理して、department_id
で照合しています。
PLAN TABLEには、行ソース・ツリーの他、次の情報が含まれています。
-
最適化。各操作のコストとカーディナリティについて。
-
パーティション化。アクセスされたパーティションのセットなど。
-
パラレル実行。結合入力の配分方法など。
6.1.2 実行計画の変化理由
実行計画は基礎となるオプティマイザ入力が変化するたびに変化します。
ノート:
実行計画の変更から生じる可能性があるSQLパフォーマンスの低下を回避するには、SQL計画の管理を使用することを検討してください。
関連項目:
-
DBMS_SPM
パッケージについて学習するには、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください
6.1.2.1 スキーマの相違
スキーマは、様々な理由で異なる場合があります。
主な理由は次のとおりです。
-
実行とEXPLAIN PLANのデータベースが異なる場合。
-
文をEXPLAINするユーザーが、文を実行するユーザーとは異なる場合。2人のユーザーが同じデータベース内の異なるオブジェクトを指していれば、異なる実行計画が発生します。
-
2つの操作間でスキーマが変更された場合(多くは索引の変更)。
6.2EXPLAIN PLAN文を使用した計画出力の生成
EXPLAIN PLAN
文により、オプティマイザがSQL文に対して選択した実行計画を確認できます。
6.2.1EXPLAIN PLAN文について
EXPLAIN PLAN
文は、SELECT
、UPDATE
、INSERT
およびDELETE
文に関して、オプティマイザによって選択された実行計画を表示します。
EXPLAIN PLAN
の出力は、説明されたSQL文をデータベースがどのように実行するかを示します。実行環境と実行計画環境の違いのため、実行計画は文の実行中に使用した実際の計画と異なることがあります。
EXPLAIN PLAN
文が発行されると、オプティマイザは実行計画を選択し、実行計画の各ステップを表す行を指定されたPLAN TABLEに挿入します。SQLトレース機能の一部としてEXPLAIN PLAN
文を発行することもできます。
EXPLAIN PLAN
文は、DDL文ではなくDML文です。そのため、EXPLAIN PLAN
文で加えられた変更内容は暗黙的にコミットされません。
関連項目:
-
EXPLAIN PLAN
文について学習するには、Oracle Database SQL言語リファレンスを参照してください
6.2.1.1 PLAN_TABLEについて
PLAN_TABLE
は、EXPLAIN PLAN
文が実行計画について記述している行を挿入するデフォルトのサンプル出力表です。
Oracle Databaseは、SYS
スキーマのグローバル一時表PLAN_TABLE$
を自動的に作成し、シノニムとしてPLAN_TABLE
を作成します。PLAN_TABLE
に必要なすべての権限は、PUBLIC
に付与されています。そのため、すべてのセッションが、そのセッションの一時表領域内にPLAN_TABLE
の専用プライベート・コピーを保持します。
SQLスクリプトcatplan.sql
を使用すると、グローバル一時表とPLAN_TABLE
シノニムを手動で作成できます。このスクリプトの名前と位置は、使用するオペレーティング・システムによって異なります。UNIXおよびLinux上では、このスクリプトは$ORACLE_HOME/rdbms/admin
ディレクトリにあります。たとえば、SQL*Plusセッションを開始し、SYSDBA
権限で接続し、そして次のようにスクリプトを実行します。
@$ORACLE_HOME/rdbms/admin/catplan.sql
配布メディアのSQLスクリプトの中に、サンプル出力表PLAN_TABLE
の定義があります。使用する出力表は、列の名前およびデータ型がこのサンプル表と同じである必要があります。このスクリプトの一般的な名前は、utlxplan.sql
です。正確な名前および位置は、使用するオペレーティング・システムによって異なります。
関連項目:
EXPLAIN PLAN
構文の詳細は、Oracle Database SQL言語リファレンスを参照してください。
6.2.1.2 EXPLAIN PLANの制限事項
Oracle Databaseでは、日付バインド変数の暗黙的な型変換を実行する文でのEXPLAIN PLAN
をサポートしません。
一般にバインド変数では、EXPLAIN PLAN
が実際の実行計画を表していない場合があります。
TKPROF
は、SQL文のテキストからバインド変数の型を判断することはできません。型はVARCHAR
であると想定され、それ以外の場合はエラー・メッセージが表示されます。この制限事項は、SQL文に適切な型変換を入れることで対処できます。
関連項目:
-
SQLデータ型についてさらに学習するには、『Oracle Database SQL言語リファレンス』を参照してください
6.2.2SQL文の説明: 基本ステップ
EXPLAIN PLAN
を使用して、SQL文の計画をPLAN_TABLE
に格納します。
前提条件
このタスクでは、PLAN_TABLE
という名前のサンプル出力表がスキーマに存在していると仮定しています。この表が存在していない場合は、SQLスクリプトcatplan.sql
を実行します。
EXPLAIN PLAN
を実行するには、次の権限が必要です。
-
実行計画を保持するように指定した既存の出力表に行を挿入するための権限が必要です。
-
出力する実行計画の適用対象であるSQL文を実行するための権限も必要です。このSQL文でビューにアクセスする場合は、このビューの基礎になっているすべての表およびビューへのアクセス権限が必要です。このビューが別のビューに基づき、さらにこの別のビューが、ある表に基づいている場合、別のビューとそのビューの基礎になっている表へのアクセス権限が必要です。
EXPLAIN PLAN
で作成された実行計画を検証する場合は、出力表へ問い合せる権限が必要です。
文を説明するには:
-
SQL*PlusまたはSQL Developerを起動して、データベースに必要な権限を持つユーザーとしてログインします。
-
SQL文の直前に、
EXPLAIN PLAN FOR
句を含めます。次の例では、
employees
表の問合せの計画について説明します。EXPLAIN PLAN FOR SELECT e.last_name, d.department_name, e.salary FROM employees e, departments d WHERE salary < 3000 AND e.department_id = d.department_id ORDER BY salary DESC;
-
EXPLAIN PLAN
文を発行した後、Oracle Databaseから提供されるスクリプトまたはパッケージを使用して最新のPLAN TABLE出力を表示します。次の例では、
DBMS_XPLAN.DISPLAY
関数を使用します。SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL'));
-
計画出力を確認します。
たとえば、次の計画はハッシュ結合を示しています。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL')); Plan hash value: 3556827125 ------------------------------------------------------------------------------ | Id | Operation | Name |Rows | Bytes |Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 4 | 124 | 5 (20)| 00:00:01 | | 1 | SORT ORDER BY | | 4 | 124 | 5 (20)| 00:00:01 | |* 2 | HASH JOIN | | 4 | 124 | 4 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| EMPLOYEES | 4 | 60 | 2 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 3 - SEL$1 / E@SEL$1 4 - SEL$1 / D@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") 3 - filter("SALARY"<3000) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=1) INTERNAL_FUNCTION("E"."SALARY")[22], "E"."LAST_NAME"[VARCHAR2,25], "D"."DEPARTMENT_NAME"[VARCHAR2,30] 2 - (#keys=1) "E"."LAST_NAME"[VARCHAR2,25], "SALARY"[NUMBER,22], "D"."DEPARTMENT_NAME"[VARCHAR2,30], "D"."DEPARTMENT_NAME"[VARCHAR2,30] 3 - "E"."LAST_NAME"[VARCHAR2,25], "SALARY"[NUMBER,22], "E"."DEPARTMENT_ID"[NUMBER,22] 4 - "D"."DEPARTMENT_ID"[NUMBER,22], "D"."DEPARTMENT_NAME"[VARCHAR2,30] Note ----- - this is an adaptive plan
計画操作によって、子からのデータが要求されます。EXPLAIN PLAN出力の実行順序は次のとおりです。
- 実行は、子のない最初の操作から始まります。上の例では、
EMPLOYEES
(ID 3)の全体スキャンです。 EMPLOYEES
は、そのデータを親(ID 2)に返します。- その後、実行はハッシュ結合の次の子に進み、
DEPARTMENTS
(ID 4)の全体スキャンを実行します。 DEPARTMENTS
には子がないため、データを親(ID 2)に返します。- ハッシュ結合では、2つの表の行が結合され、
SORT ORDER BY
(ID 1)に渡されます。 - 最後に、
SELECT
はデータをクライアントに返します。
ノート:
この例では、追加の結合などの操作がさらに含まれている場合、実行は各操作の同じパターンに従ってステップ5から計画の最後(SELECT
がクライアントにデータを返す最後のステップ)まで続きます。
ここで説明するEXPLAIN PLAN出力のステップは、データベースによっては異なる場合があります。これは、オプティマイザがデータベース構成に応じて異なるEXECUTION PLANを選択する可能性があるためです。
関連項目:
-
EXPLAIN PLAN
の構文およびセマンティクスについては、『Oracle Database SQL言語リファレンス』を参照してください - 実行計画の読取り方法。このOracleブログ記事では、EXECUTION PLANの読取り方法について説明しますが、同じ実行順序がEXPLAIN PLANに適用されるため、両方のタイプの計画のプロセスをよりよく理解できるようになります。
6.2.3EXPLAIN PLANでの文IDの指定: 例
複数の文があるときは、文の識別子を指定し、その識別子で特定の実行計画を識別できます。
SET STATEMENT ID
を使用する前に、その文と同じ識別子を持つ既存の行を削除してください。次の例では、st1
が文の識別子として指定されています。
例6-1 STATEMENT ID句でのEXPLAIN PLANの使用方法
EXPLAIN PLAN
SET STATEMENT_ID = 'st1' FOR
SELECT last_name FROM employees;
6.2.4EXPLAIN PLAN出力の別の場所の指定: 例
EXPLAIN PLAN
のINTO
句で、出力を格納する別の表を指定します。
名前PLAN_TABLE
を使用しない場合は、catplan.sql
スクリプトの実行後に新しいシノニムを作成します。たとえば:
CREATE OR REPLACE PUBLIC SYNONYM my_plan_table for plan_table$
次の文は、my_plan_table
に出力を送ります。
EXPLAIN PLAN
INTO my_plan_table FOR
SELECT last_name FROM employees;
次の文のように、INTO
句を使用する場合は、文のIDを指定できます。
EXPLAIN PLAN
SET STATEMENT_ID = 'st1'
INTO my_plan_table FOR
SELECT last_name FROM employees;
関連項目:
-
PLAN_TABLE
の列についての説明は、「PLAN_TABLEの列」を参照 -
CREATE SYNONYM
について学習するには、『Oracle Database SQL言語リファレンス』を参照してください
6.2.5 CONTAINERS問合せに対するEXPLAIN PLANの出力: 例
CONTAINERS
句を使用すると、ユーザーが作成した表およびビューと、Oracleが提供する表およびビューの両方を問い合せることができます。すべてのコンテナ間でユーザーが作成した表およびビューを問い合せることができます。
次の例に、CONTAINERS
句を使用した問合せのEXPLAIN PLAN
の出力を示します。
SQL> explain plan for select con_id, count(*) from containers(sys.dba_tables) where con_id < 10 group by con_id order by con_id;
Explained.
SQL> @?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
Plan hash value: 891225627
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 234K| 2970K| 145 (100)| 00:00:01 | | |
| 1 | PX COORDINATOR | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 234K| 2970K| 145 (100)| 00:00:01 | | |
| 3 | SORT GROUP BY | | 234K| 2970K| 145 (100)| 00:00:01 | | |
| 4 | PX RECEIVE | | 234K| 2970K| 145 (100)| 00:00:01 | | |
| 5 | PX SEND RANGE | :TQ10000 | 234K| 2970K| 145 (100)| 00:00:01 | | |
| 6 | HASH GROUP BY | | 234K| 2970K| 145 (100)| 00:00:01 | | |
| 7 | PX PARTITION LIST ITERATOR| | 234K| 2970K| 139 (100)| 00:00:01 | 1 | 9 |
| 8 | CONTAINERS FULL | DBA_TABLES | 234K| 2970K| 139 (100)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------
15 rows selected.
この計画の行8では、CONTAINERS
は値CONTAINERS FULL
でOperation
列に表示されます。同じ行のName列には、CONTAINERS
への引数が示されます。
デフォルトのパーティション化
CONTAINERS
句を使用する問合せは、デフォルトでパーティション化されます。計画の行7で、Operation
列のPX PARTITION LIST ITERATOR
は、問合せがパーティション化されていることを示しています。コンテナの反復は、このパーティション・イテレータに実装されます。同じ行で、Pstart
とPstop
の値1および9は、問合せのcon_id < 10
述語から派生します。
デフォルトの並列処理
CONTAINERS
句を使用する問合せは、デフォルトでパラレル実行サーバーを使用します。前述の計画の行1で、Operation
列のPX COORDINATOR
はパラレル実行サーバーが使用されることを示しています。各コンテナはパラレル実行プロセス(P00*
)に割り当てられます。パラレル実行プロセスが、問合せEXECUTION PLAN
のCONTAINERS FULL
に対応する部分を実行すると、プロセスは作業対象として割り当てられたコンテナに切り替わります。再帰的SQL文を実行して、ベース・オブジェクトから行を取得します。
6.3 実行計画の表示
実行計画を表示する最も簡単な方法は、DBMS_XPLAN
表示関数またはV$
ビューを使用することです。
6.3.1 PLAN_TABLE出力の表示について
PLAN TABLE出力を表示するには、SQLスクリプトまたはDBMS_XPLAN
パッケージを使用できます。
計画をEXPLAINした後、Oracle Databaseから提供される次のSQLスクリプトまたはPL/SQLパッケージを使用して最新のPLAN TABLE出力を表示します。
-
DBMS_XPLAN.DISPLAY
テーブル・ファンクションこのファンクションは、PLAN TABLE出力を表示するオプションを受け入れます。次を指定できます。
-
PLAN_TABLE
とは別の表を使用している場合のPLAN TABLE名 -
EXPLAIN PLAN
に文の識別子を設定した場合の文の識別子 -
詳細レベルを決定するフォーマット・オプション:
BASIC
、SERIAL
、TYPICAL
およびALL
DBMS_XPLAN
を使用してPLAN_TABLE
出力を表示する例を次に示します。SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY()); SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('MY_PLAN_TABLE', 'st1','TYPICAL'));
-
-
utlxpls.sql
このスクリプトは、シリアル処理のためのPLAN TABLE出力を表示します
-
utlxplp.sql
このスクリプトは、パラレル実行列を含むPLAN TABLE出力を表示します。
関連項目:
DBMS_XPLAN
パッケージの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
6.3.1.1 DBMS_XPLAN表示関数
DBMS_XPLAN
表示関数を使用すると計画を表示できます。
表示関数は、PLAN TABLE出力を表示するオプションを受け入れます。次を指定できます。
-
PLAN_TABLE
とは別の表を使用している場合のPLAN TABLE名 -
EXPLAIN PLAN
に文の識別子を設定した場合の文の識別子 -
詳細レベルを決定するフォーマット・オプション:
BASIC
、SERIAL
、TYPICAL
、ALL
および場合によってはADAPTIVE
表6-1 DBMS_XPLAN表示関数
表示関数 | ノート |
---|---|
|
このテーブル・ファンクションは、PLAN TABLEの内容を表示します。 また、この表の列の名前がPLAN TABLE(統計情報が含まれている場合は
|
|
このテーブル・ファンクションは、AWR内に格納されている実行計画のコンテンツを表示します。
|
|
このテーブル・ファンクションは、カーソル・キャッシュにロードされている任意のカーソルの実行計画を表示します。実行計画に加えて、様々な計画統計(I/O、メモリー、タイミングなど)をレポートできます(
|
|
このテーブル・ファンクションは、CLOB 出力タイプの様々な形式でPLAN TABLEの内容を表示します。
|
|
このテーブル・ファンクションは、SQLプラン・ベースラインの指定したSQLハンドルの1つ以上の実行計画を表示します。 このファンクションは、計画ベースラインに保存された計画の情報を使用して計画の詳細を表示します。SQL管理ベースに格納されている |
|
このテーブル・ファンクションは、SQLチューニング・セットに格納されている指定した文の実行計画を表示します。
|
関連項目:
DBMS_XPLAN
表示関数についてさらに学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照
6.3.1.2 計画関連のビュー
動的パフォーマンス・ビューとデータ・ディクショナリ・ビューを問い合せることで、実行計画に関する情報を取得できます。
表6-2 実行計画のビュー
ビュー | 説明 |
---|---|
|
カーソルの統計をリストして、入力した元のSQLテキストの子ごとに1行ずつ格納します。 Oracle Database 19c以降の |
|
特定の子カーソルが既存の子カーソルと共有されない理由を示します。それぞれの列は、カーソルが共有されない具体的な理由を示します。
|
|
共有SQL領域に保存された文ごとの計画を格納します。 このビューの定義は、
|
|
出力行数や経過時間など、計画に含まれる操作ごとに実際の実行統計を示します。出力行数を除き、すべての統計は累積されます。たとえば、結合操作の統計には、2つの入力の統計も含まれます。 |
|
(ソートまたはハッシュ結合に)SQLメモリーを使用する行ソースのメモリー使用量統計が含まれます。このビューは、
|
関連項目:
-
V$SQL_PLAN_MONITOR
ビューについては、「データベース操作の監視」を参照してください -
V$SQL_PLAN
ビューの詳細は、『Oracle Databaseリファレンス』を参照してください -
STATISTICS_LEVEL
初期化パラメータについては、『Oracle Databaseリファレンス』を参照してください
6.3.2 実行計画の表示: 基本ステップ
DBMS_XPLAN.DISPLAY
関数は、説明された計画を表示する簡単な方法です。
デフォルトでは、DISPLAY
関数はTYPICAL
のフォーマット設定を使用します。この場合は、計画に関する最も一般的な情報(操作ID、操作名、操作オプション、行数、バイト数およびオプティマイザ・コスト)が表示されます。プルーニング情報、パラレル情報および述語情報は、適用可能な場合にのみ表示されます。
実行計画を表示するには:
-
SQL*PlusまたはSQL Developerを起動して、計画を説明したセッションにログインします。
-
計画を説明します。
-
DBMS_XPLAN.DISPLAY
を使用してPLAN_TABLE
を問い合せます。次のように問合せを指定します。
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY);
または、
statement_id
パラメータを使用して文IDを指定します。SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY(statement_id => 'statement_id));
例6-2 文ID ex_plan1のEXPLAIN PLAN
この例では、文ID ex_plan1
を使用するemployees
の問合せを説明して、PLAN_TABLE
を問い合せます。
EXPLAIN PLAN
SET statement_id = 'ex_plan1' FOR
SELECT phone_number
FROM employees
WHERE phone_number LIKE '650%';
SELECT PLAN_TABLE_OUTPUT
FROM TABLE(DBMS_XPLAN.DISPLAY(statement_id => 'ex_plan1'));
出力例は次のように表示されます。
Plan hash value: 1445457117
---------------------------------------------------------------------------
|Id | Operation | Name |Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1 | 15 | 2 (0)| 00:00:01 |
|* 1| TABLE ACCESS FULL| EMPLOYEES | 1 | 15 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("PHONE_NUMBER" LIKE '650%')
例6-3 文ID ex_plan2のEXPLAIN PLAN
この例では、文ID ex_plan2
を使用するemployees
の問合せを説明し、BASIC
フォーマットを使用して計画を表示します。
EXPLAIN PLAN
SET statement_id = 'ex_plan2' FOR
SELECT last_name
FROM employees
WHERE last_name LIKE 'Pe%';
SELECT PLAN_TABLE_OUTPUT
FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'ex_plan2','BASIC'));
出力例は次のように表示されます。
----------------------------------------
| Id | Operation | Name |
----------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | INDEX RANGE SCAN| EMP_NAME_IX |
----------------------------------------
関連項目:
DBMS_XPLAN
パッケージの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
6.3.3 適応問合せ計画の表示: チュートリアル
適応オプティマイザは、実行時の統計に基づいて計画を適応させることを可能にするオプティマイザの機能です。すべての適応メカニズムにより、デフォルト・プランと異なる文の最終プランを実行できます。
適応問合せ計画は、現在の文の実行中にサブプランの中から選択されます。対照的に、自動再最適化では、現在の文の実行後に行われる自動再最適化の実行時にのみ計画が変更されます。
計画のNotes
セクションのコメントに基づいて、データベースでSQL文の適応問合せ最適化が使用されたかどうかを判断できます。コメントには、行ソースが動的かどうか、または自動再最適化が計画に適用されたかどうかが示されます。
前提条件
このチュートリアルでは、次のことが前提となっています。
-
STATISTICS_LEVEL
初期化パラメータがALL
に設定されています。 -
データベースでは、適応実行にデフォルト設定が使用されています。
-
ユーザー
oe
として、次の別々の問合せを発行します。SELECT o.order_id, v.product_name FROM orders o, ( SELECT order_id, product_name FROM order_items o, product_information p WHERE p.product_id = o.product_id AND list_price < 50 AND min_price < 40 ) v WHERE o.order_id = v.order_id SELECT product_name FROM order_items o, product_information p WHERE o.unit_price = 15 AND quantity > 1 AND p.product_id = o.product_id
-
各問合せを実行する前に、
DBMS_XPLAN.DISPLAY_PLAN
を問い合せてデフォルトの計画(つまり、適応メカニズムを適用する前にオプティマイザによって選択される計画)を確認します。 -
各問合せの実行後、
DBMS_XPLAN.DISPLAY_CURSOR
を問い合せて最終計画と適応問合せ計画を確認します。 -
SYS
により、oe
に次の権限が付与されています。-
GRANT SELECT ON V_$SESSION TO oe
-
GRANT SELECT ON V_$SQL TO oe
-
GRANT SELECT ON V_$SQL_PLAN TO oe
-
GRANT SELECT ON V_$SQL_PLAN_STATISTICS_ALL TO oe
-
適応最適化の結果を確認するには:
-
SQL*Plusを起動し、データベースにユーザー
oe
として接続します。 -
orders
を問い合せます。たとえば、次の文を使用します。
SELECT o.order_id, v.product_name FROM orders o, ( SELECT order_id, product_name FROM order_items o, product_information p WHERE p.product_id = o.product_id AND list_price < 50 AND min_price < 40 ) v WHERE o.order_id = v.order_id;
-
カーソル内に計画を表示します。
たとえば、次のコマンドを実行します。
SET LINESIZE 165 SET PAGESIZE 0 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'+ALLSTATS'));
次のサンプル出力はページに収まるようにあらかじめ折り返されています。この計画では、オプティマイザは、ネステッド・ループ結合を選択します。元のオプティマイザの見積りは、
E-Rows
列に示されています。一方、実行中に収集された実際の統計は、A-Rows
列に示されています。MERGE JOIN
操作では、見積られた行数と実際の行数の差異は大きなものになります。-------------------------------------------------------------------------------------------- |Id| Operation | Name |Start|E-Rows|A-Rows|A-Time|Buff|OMem|1Mem|O/1/M| -------------------------------------------------------------------------------------------- | 0| SELECT STATEMENT | | 1| | 269|00:00:00.09|1338| | | | | 1| NESTED LOOPS | | 1| 1| 269|00:00:00.09|1338| | | | | 2| MERGE JOIN CARTESIAN| | 1| 4|9135|00:00:00.03| 33| | | | |*3| TABLE ACCESS FULL |PRODUCT_INFORMAT| 1| 1| 87|00:00:00.01| 32| | | | | 4| BUFFER SORT | | 87|105|9135|00:00:00.01| 1|4096|4096|1/0/0| | 5| INDEX FULL SCAN | ORDER_PK | 1|105| 105|00:00:00.01| 1| | | | |*6| INDEX UNIQUE SCAN | ORDER_ITEMS_UK |9135| 1| 269|00:00:00.03|1305| | | | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(("MIN_PRICE"<40 AND "LIST_PRICE"<50)) 6 - access("O"."ORDER_ID"="ORDER_ID" AND "P"."PRODUCT_ID"="O"."PRODUCT_ID")
-
ステップ
2
で実行したものと同じordersの問合せを実行します。 -
ステップ
3
で実行したものと同じSELECT文を使用して、カーソル内に実行計画を表示します。次の例は、オプティマイザがハッシュ結合を使用する別の計画を選択したことを示しています。Noteセクションには、オプティマイザが統計フィードバックを使用して、問合せの2回目の実行に対するコストの見積りを調整したことが示されています。これは自動再最適化のよい例です。
-------------------------------------------------------------------------------------------- |Id| Operation |Name |Start|E-Rows|A-Rows|A-Time|Buff|Reads|OMem|1Mem|O/1/M| -------------------------------------------------------------------------------------------- | 0| SELECT STATEMENT | | 1 | |269|00:00:00.02|60|1| | | | | 1| NESTED LOOPS | | 1 |269|269|00:00:00.02|60|1| | | | |*2| HASH JOIN | | 1 |313|269|00:00:00.02|39|1|1000K|1000K|1/0/0| |*3| TABLE ACCESS FULL |PRODUCT_INFORMA| 1 | 87| 87|00:00:00.01|15|0| | | | | 4| INDEX FAST FULL SCAN|ORDER_ITEMS_UK | 1 |665|665|00:00:00.01|24|1| | | | |*5| INDEX UNIQUE SCAN |ORDER_PK |269| 1|269|00:00:00.01|21|0| | | | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID") 3 - filter(("MIN_PRICE"<40 AND "LIST_PRICE"<50)) 5 - access("O"."ORDER_ID"="ORDER_ID") Note ----- - statistics feedback used for this statement
-
V$SQL
を問い合せて、パフォーマンスが改善されていることを確認します。次の問合せは、2つの文のパフォーマンスを示しています(出力例も示します)。
SELECT CHILD_NUMBER, CPU_TIME, ELAPSED_TIME, BUFFER_GETS FROM V$SQL WHERE SQL_ID = 'gm2npz344xqn8'; CHILD_NUMBER CPU_TIME ELAPSED_TIME BUFFER_GETS ------------ ---------- ------------ ----------- 0 92006 131485 1831 1 12000 24156 60
実行された2つ目の文(子番号
1
)では、統計フィードバックが使用されました。CPU時間、経過時間およびバッファ取得はいずれも、大幅に低下しています。 -
order_items
の問合せに対する計画をEXPLAINします。たとえば、次の文を使用します。
EXPLAIN PLAN FOR SELECT product_name FROM order_items o, product_information p WHERE o.unit_price = 15 AND quantity > 1 AND p.product_id = o.product_id
-
PLAN TABLE内に計画を表示します。
たとえば、次の文を実行します。
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
出力例は次のように表示されます。
------------------------------------------------------------------------------- |Id| Operation | Name |Rows|Bytes|Cost (%CPU)|Time| ------------------------------------------------------------------------------- | 0| SELECT STATEMENT | |4|128|7 (0)|00:00:01| | 1| NESTED LOOPS | | | | | | | 2| NESTED LOOPS | |4|128|7 (0)|00:00:01| |*3| TABLE ACCESS FULL |ORDER_ITEMS |4|48 |3 (0)|00:00:01| |*4| INDEX UNIQUE SCAN |PRODUCT_INFORMATION_PK|1| |0 (0)|00:00:01| | 5| TABLE ACCESS BY INDEX ROWID|PRODUCT_INFORMATION |1|20 |1 (0)|00:00:01| ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("O"."UNIT_PRICE"=15 AND "QUANTITY">1) 4 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
この計画では、オプティマイザは、ネステッド・ループ結合を選択します。
-
以前にEXPLAINした問合せを実行します。
たとえば、次の文を使用します。
SELECT product_name FROM order_items o, product_information p WHERE o.unit_price = 15 AND quantity > 1 AND p.product_id = o.product_id
-
カーソル内に計画を表示します。
たとえば、次のコマンドを実行します。
SET LINESIZE 165 SET PAGESIZE 0 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT=>'+ADAPTIVE'));
出力例は次のように表示されます。実行時(ステップ4)に収集された統計に基づいて、オプティマイザは、ネステッド・ループ結合ではなく、ハッシュ結合を選択します。ダッシュ(
-
)は、オプティマイザによって考慮されたが最終的に選択されなかった、ネステッド・ループ計画におけるステップを表します。スイッチは、適応問合せ計画機能を示しています。------------------------------------------------------------------------------- |Id | Operation | Name |Rows|Bytes|Cost(%CPU)|Time | ------------------------------------------------------------------------------- | 0| SELECT STATEMENT | |4|128|7(0)|00:00:01| | *1| HASH JOIN | |4|128|7(0)|00:00:01| |- 2| NESTED LOOPS | | | | | | |- 3| NESTED LOOPS | | |128|7(0)|00:00:01| |- 4| STATISTICS COLLECTOR | | | | | | | *5| TABLE ACCESS FULL | ORDER_ITEMS |4| 48|3(0)|00:00:01| |-*6| INDEX UNIQUE SCAN | PRODUCT_INFORMATI_PK|1| |0(0)|00:00:01| |- 7| TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION |1| 20|1(0)|00:00:01| | 8| TABLE ACCESS FULL | PRODUCT_INFORMATION |1| 20|1(0)|00:00:01| ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID") 5 - filter("O"."UNIT_PRICE"=15 AND "QUANTITY">1) 6 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID") Note ----- - this is an adaptive plan (rows marked '-' are inactive)
関連項目:
-
「表 6-1」
-
STATISTICS_LEVEL
初期化パラメータについて学習するには、『Oracle Databaseリファレンス』を参照してください。 -
DBMS_XPLAN
についてさらに学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
6.3.4 実行計画の表示: 例
ここでは、実行計画の様々な表示方法の例を示します。
6.3.4.1 PLAN_TABLE出力のカスタマイズ
文の識別子を指定した場合は、PLAN_TABLE
を問い合せるための独自のスクリプトを書くことができます。
たとえば:
-
START WITH ID = 0および
STATEMENT_ID
を指定します。 -
CONNECT BY
句を使用して親から子へツリーを移動します。結合キーは、STATEMENT_ID = PRIOR STATMENT_ID
およびPARENT_ID = PRIOR ID
です。 -
疑似列
LEVEL
(CONNECT BY
に関連付けられている)を使用して子をインデントします。SELECT cardinality "Rows", lpad(' ',level-1) || operation ||' '||options||' '||object_name "Plan" FROM PLAN_TABLE CONNECT BY prior id = parent_id AND prior statement_id = statement_id START WITH id = 0 AND statement_id = 'st1' ORDER BY id; Rows Plan ------- ---------------------------------------- SELECT STATEMENT TABLE ACCESS FULL EMPLOYEES
Rows
列のNULL
は、オプティマイザが表に統計を持っていないことを示します。表をANALYZEすると、次の内容が表示されます。Rows Plan ------- ---------------------------------------- 16957 SELECT STATEMENT 16957 TABLE ACCESS FULL EMPLOYEES
COST
も選択できます。これは、実行計画を比較する場合や、オプティマイザが複数の中からある実行計画を選択した理由を理解する場合に便利です。ノート:
これらの単純な例は、再帰的SQLの場合には有効ではありません。
6.3.4.2 パラレル実行計画の表示: 例
パラレル問合せの計画は、重要な点でシリアル問合せの計画と異なります。
6.3.4.2.1 EXPLAIN PLANおよびパラレル問合せについて
パラレル問合せのチューニングは、パラレルでない問合せのチューニングの場合と同様に駆動表を選択することにより、開始されます。ただし、選択を管理するルールは異なります。
シリアルの場合は、制限条件を適用した後に最も少ない行が生成される駆動表が最適です。データベースは、一意でない索引を使用して少数の行を大きな表に結合します。
たとえば、customer
、account
およびtransaction
で構成された表階層の場合について考えます。
この例では、customer
が最も小さな表で、transaction
が最も大きな表です。通常のOLTP問合せでは、特定の顧客のアカウントに関する取引情報を取得します。問合せはcustomer
表から駆動されます。目標は論理I/Oを最少化することで、これにより、通常、物理I/OやCPUタイムを含むその他の重要なリソースも最少化されます。
パラレル問合せの場合は、通常では最も大きな表が駆動表になります。この場合はパラレル問合せを使用するのは効率的ではありません。各表からアクセスされる行がごくわずかであるためです。ここで、前月に特定のタイプの取引を持つすべての顧客を識別する必要が生じた場合を考えてみます。customer
表には制限条件がないため、問合せはtransaction
表から行うほうが効率的です。データベースは、transaction
表から取り出した行をaccount
表に結合し、最終的には結果セットをcustomer
表に結合します。この場合、account
およびcustomer
表で使用される索引は、通常、最初の問合せで使用される一意でない索引ではなく、選択性の高い主キーまたは一意の索引になります。transaction
表は大きく、列に選択性がないため、transaction
表から駆動されるパラレル問合せを使用したほうが有効です。
パラレル操作には次のものがあります。
-
PARALLEL_TO_PARALLEL
-
PARALLEL_TO_SERIAL
PARALLEL_TO_SERIAL
操作は、パラレル操作からの行が問合せコーディネータによって使用される場合、常に発生するステップです。この問合せでは発生しない他の種類の操作には、SERIAL
操作があります。このような操作が発生した場合は、これらもボトルネックになる可能性があるため、パフォーマンスの改善のためにパラレル操作を検討します。 -
PARALLEL_FROM_SERIAL
-
PARALLEL_TO_PARALLEL
各ステップのワークロードがほぼ同じである場合、通常は、
PARALLEL_TO_PARALLEL
操作によって最適なパフォーマンスが得られます。 -
PARALLEL_COMBINED_WITH_CHILD
-
PARALLEL_COMBINED_WITH_PARENT
PARALLEL_COMBINED_WITH_PARENT
操作は、ステップを親ステップと同時に実行する場合に発生します。
パラレル・ステップで多数の行が生成される場合、行の生成される速さにQCの処理が追いつかない場合があります。この状況を改善する方法はほとんどありません。
関連項目:
「PLAN_TABLE列」のOTHER_TAG
列
6.3.4.2.2 EXPLAIN PLANによるパラレル問合せの表示: 例
EXPLAIN PLAN
をパラレル問合せとともに使用すると、データベースは1つのパラレル計画をコンパイルおよび実行します。この計画は、QC計画にパラレル・サポートに固有の行ソースを割り当てることで、シリアル計画から導出されます。
2つのパラレル実行サーバー・セットPQモデルで要求される、表キューの行ソース(PX
Send
およびPX
Receive
)、グラニュル・イテレータおよびバッファ・ソートは、パラレル計画に直接挿入されます。この計画は、パラレルで実行された場合はすべてのパラレル実行サーバーで、またシリアルで実行された場合はすべてのQCで、同じ計画となります。
例6-4 パラレル問合せのEXPLAIN PLAN
次の単純な例は、パラレル問合せのEXPLAIN
PLAN
を示しています。
CREATE TABLE emp2 AS SELECT * FROM employees;
ALTER TABLE emp2 PARALLEL 2;
EXPLAIN PLAN FOR
SELECT SUM(salary)
FROM emp2
GROUP BY department_id;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
-------------------------------------------------------------------------------------
|Id | Operation | Name |Rows| Bytes |Cost %CPU| TQ |IN-OUT|PQ Distrib|
-------------------------------------------------------------------------------------
|0| SELECT STATEMENT | |107| 2782 | 3 (34) | | | |
|1| PX COORDINATOR | | | | | | | |
|2| PX SEND QC (RANDOM) |:TQ10001|107| 2782 | 3 (34) | Q1,01 | P->S |QC (RAND) |
|3| HASH GROUP BY | |107| 2782 | 3 (34) | Q1,01 | PCWP | |
|4| PX RECEIVE | |107| 2782 | 3 (34) | Q1,01 | PCWP | |
|5| PX SEND HASH |:TQ10000|107| 2782 | 3 (34) | Q1,00 | P->P |HASH |
|6| HASH GROUP BY | |107| 2782 | 3 (34) | Q1,00 | PCWP | |
|7| PX BLOCK ITERATOR | |107| 2782 | 2 (0) | Q1,00 | PCWP | |
|8| TABLE ACCESS FULL|EMP2 |107| 2782 | 2 (0) | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------
パラレル実行サーバーの1つのセットがパラレルにEMP2
をスキャンし、2つ目のセットは、GROUP BY
操作に対する集計を実行します。PX BLOCK ITERATOR
行ソースは、EMP2
表が複数のピースに分割されて、スキャンのワークロードがパラレル実行サーバー間で分けられることを表します。PX SEND
およびPX RECEIVE
行ソースは、2つのパラレル実行サーバー・セットをパラレル・スキャンからの行フローとして接続するパイプを表し、HASH
表キューを介して再びパーティション化されてから、上位スレーブ・セットにより読み取られて集計されます。PX SEND QC
行ソースは、QCにランダム(RAND)な順序で送信される集計値を表します。PX COORDINATOR
行ソースは、計画ツリーで下に表示されるパラレル計画を制御しスケジュールするQC(問合せコーディネータ)を表します。
6.3.4.3 ビットマップ索引計画の表示: 例
ビットマップ索引を使用する索引行ソースは、索引のタイプを示す語BITMAP
とともにEXPLAIN PLAN
出力に表示されます。
例6-5 ビットマップ検索によるEXPLAIN PLAN
この例では、述語c1=2
によってビットマップが生成され、そこから減算が行われます。このビットマップから、c2=6
に対応するビットマップ内のビットが減算されます。同様に、c2 IS NULL
に対応するビットマップ内のビットが減算され、この計画の中に2つのMINUS
行ソースがある理由がわかります。NULL
減算は、NOT NULL
制約が列に設定されていないかぎり、意味上の正確さを保つために必要です。TO ROWIDS
オプションは、表アクセスに必要な行IDを生成します。
ノート:
ビットマップ結合索引を使用した問合せは、ビットマップ結合索引のアクセス・パスを指示します。ビットマップ結合索引の操作は、ビットマップ索引と同じです。
EXPLAIN PLAN FOR SELECT *
FROM t
WHERE c1 = 2
AND c2 <> 6
OR c3 BETWEEN 10 AND 20;
SELECT STATEMENT
TABLE ACCESS T BY INDEX ROWID
BITMAP CONVERSION TO ROWID
BITMAP OR
BITMAP MINUS
BITMAP MINUS
BITMAP INDEX C1_IND SINGLE VALUE
BITMAP INDEX C2_IND SINGLE VALUE
BITMAP INDEX C2_IND SINGLE VALUE
BITMAP MERGE
BITMAP INDEX C3_IND RANGE SCAN
6.3.4.4 結果キャッシュ計画の表示: 例
問合せにresult_cache
ヒントが含まれる場合、ResultCache
演算子が実行計画に挿入されます。
たとえば、次の問合せを考えてみます。
SELECT /*+ result_cache */ deptno, avg(sal)
FROM emp
GROUP BY deptno;
次のコマンドを使用して、この問合せに対するEXPLAIN PLAN
を表示できます。
EXPLAIN PLAN FOR
SELECT /*+ result_cache */ deptno, avg(sal)
FROM emp
GROUP BY deptno;
SELECT PLAN_TABLE_OUTPUT FROM TABLE (DBMS_XPLAN.DISPLAY());
この問合せのEXPLAIN PLAN
出力は、次のようになります。
--------------------------------------------------------------------------------
|Id| Operation | Name |Rows|Bytes|Cost(%CPU)|Time |
--------------------------------------------------------------------------------
|0| SELECT STATEMENT | | 11 | 77 | 4 (25)| 00:00:01|
|1| RESULT CACHE |b06ppfz9pxzstbttpbqyqnfbmy| | | | |
|2| HASH GROUP BY | | 11 | 77 | 4 (25)| 00:00:01|
|3| TABLE ACCESS FULL| EMP |107 | 749| 3 (0) | 00:00:01|
--------------------------------------------------------------------------------
このEXPLAIN PLAN
では、ResultCache
演算子はそのCacheId
(b06ppfz9pxzstbttpbqyqnfbmy
)によって識別されます。このCacheId
を使用して、V$RESULT_CACHE_OBJECTS
ビューに対して問合せを実行できます。
6.3.4.5 パーティション・オブジェクトの計画の表示: 例
EXPLAIN PLAN
を使用して、特定の問合せのパーティション・オブジェクトへのOracle Databaseのアクセス方法を確認します。
プルーニング後にアクセスされたパーティションは、PARTITION START
列とPARTITION STOP
列に表示されます。レンジ・パーティションの行ソース名は、PARTITION RANGE
です。ハッシュ・パーティションの場合、行ソース名はPARTITION HASH
です。
結合されるいずれかの表のPLAN TABLEのDISTRIBUTION
列にPARTITION(KEY)
が存在する場合、結合はパーシャル・パーティション・ワイズ結合を使用して実装されます。パーシャル・パーティション・ワイズ結合が可能なのは、結合される表のいずれかが結合列でパーティション化されており、かつ、表がパラレル化されている場合です。
EXPLAIN PLAN
出力の結合行ソースの前にパーティション行ソースがある場合、結合はフル・パーティション・ワイズ結合を使用して実装されます。フル・パーティション・ワイズ結合が可能なのは、両方の結合表がそれぞれの結合列で同一レベル・パーティション化されている場合のみです。次に、いくつかの種類のパーティションに対する実行計画の例を示します。
6.3.4.5.1 EXPLAIN PLANによるレンジ・パーティション化およびハッシュ・パーティション化の表示: 例
この例では、hire_date
に基づいてレンジでパーティション化されているemp_range
表を使用してプルーニングについて説明します。
Oracle Databaseサンプル・スキーマの表employees
およびdepartments
が存在することを想定しています。
CREATE TABLE emp_range
PARTITION BY RANGE(hire_date)
(
PARTITION emp_p1 VALUES LESS THAN (TO_DATE('1-JAN-1992','DD-MON-YYYY')),
PARTITION emp_p2 VALUES LESS THAN (TO_DATE('1-JAN-1994','DD-MON-YYYY')),
PARTITION emp_p3 VALUES LESS THAN (TO_DATE('1-JAN-1996','DD-MON-YYYY')),
PARTITION emp_p4 VALUES LESS THAN (TO_DATE('1-JAN-1998','DD-MON-YYYY')),
PARTITION emp_p5 VALUES LESS THAN (TO_DATE('1-JAN-2001','DD-MON-YYYY'))
)
AS SELECT * FROM employees;
最初の例では、次の文を検討します。
EXPLAIN PLAN FOR
SELECT * FROM emp_range;
次のような結果が表示されます。
--------------------------------------------------------------------
|Id| Operation | Name |Rows| Bytes|Cost|Pstart|Pstop|
--------------------------------------------------------------------
| 0| SELECT STATEMENT | | 105| 13965 | 2 | | |
| 1| PARTITION RANGE ALL| | 105| 13965 | 2 | 1 | 5 |
| 2| TABLE ACCESS FULL | EMP_RANGE | 105| 13965 | 2 | 1 | 5 |
--------------------------------------------------------------------
データベースでは、表アクセス行ソースに加えて、パーティション行ソースが作成されます。これが、アクセスされるパーティションのセットに対して繰り返されます。この例では、述語がプルーニングに使用されていないので、パーティション・イテレータはすべてのパーティション(ALLオプション)を対象とします。
PLAN_TABLE
のPARTITION_START列とPARTITION
_STOP
列は、1から5のすべてのパーティションへのアクセスを示します。
次の例では、次の文を検討します。
EXPLAIN PLAN FOR
SELECT *
FROM emp_range
WHERE hire_date >= TO_DATE('1-JAN-1996','DD-MON-YYYY');
-----------------------------------------------------------------------
| Id | Operation | Name |Rows|Bytes|Cost|Pstart|Pstop|
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 399 | 2 | | |
| 1 | PARTITION RANGE ITERATOR| | 3 | 399 | 2 | 4 | 5 |
| *2 | TABLE ACCESS FULL |EMP_RANGE| 3 | 399 | 2 | 4 | 5 |
-----------------------------------------------------------------------
前の例では、hire_date
の述語を使用してその他のパーティションをプルーニングするため、パーティション行ソースはパーティション4から5を反復します。
最後に、次の文を検討します。
EXPLAIN PLAN FOR
SELECT *
FROM emp_range
WHERE hire_date < TO_DATE('1-JAN-1992','DD-MON-YYYY');
-----------------------------------------------------------------------
| Id | Operation | Name |Rows|Bytes|Cost|Pstart|Pstop|
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 133 | 2 | | |
| 1 | PARTITION RANGE SINGLE| | 1 | 133 | 2 | 1 | 1 |
|* 2 | TABLE ACCESS FULL | EMP_RANGE | 1 | 133 | 2 | 1 | 1 |
-----------------------------------------------------------------------
この例では、パーティション1のみがアクセスされ、それがコンパイル時に認識されます。したがって、パーティション行ソースは必要ありません。
ノート:
ハッシュ・パーティション・オブジェクトでは、パーティション行ソース名がPARTITION RANGE
ではなくPARTITION HASH
であることを除き、同じ情報が表示されます。また、ハッシュ・パーティション化では、プルーニングが可能なのは等価述語かIN
リスト述語を使用している場合のみです。
6.3.4.5.2 コンポジット・パーティション・オブジェクトでのプルーニング情報: 例
Oracle Databaseでコンポジット・パーティション・オブジェクトのプルーニング情報を表示する方法を示すには、表emp_comp
を検討してください。この表は雇用日
のレンジでパーティション化され、部門番号
のハッシュでサプパーティション化されています。
CREATE TABLE emp_comp PARTITION BY RANGE(hire_date)
SUBPARTITION BY HASH(department_id) SUBPARTITIONS 3
(
PARTITION emp_p1 VALUES LESS THAN (TO_DATE('1-JAN-1992','DD-MON-YYYY')),
PARTITION emp_p2 VALUES LESS THAN (TO_DATE('1-JAN-1994','DD-MON-YYYY')),
PARTITION emp_p3 VALUES LESS THAN (TO_DATE('1-JAN-1996','DD-MON-YYYY')),
PARTITION emp_p4 VALUES LESS THAN (TO_DATE('1-JAN-1998','DD-MON-YYYY')),
PARTITION emp_p5 VALUES LESS THAN (TO_DATE('1-JAN-2001','DD-MON-YYYY'))
)
AS SELECT * FROM employees;
最初の例では、次の文を検討します。
EXPLAIN PLAN FOR
SELECT * FROM emp_comp;
-----------------------------------------------------------------------
|Id| Operation | Name | Rows | Bytes |Cost|Pstart|Pstop|
-----------------------------------------------------------------------
| 0| SELECT STATEMENT | | 10120 | 1314K| 78 | | |
| 1| PARTITION RANGE ALL| | 10120 | 1314K| 78 | 1 | 5 |
| 2| PARTITION HASH ALL| | 10120 | 1314K| 78 | 1 | 3 |
| 3| TABLE ACCESS FULL| EMP_COMP | 10120 | 1314K| 78 | 1 | 15 |
-----------------------------------------------------------------------
この例では、Oracle Databaseがコンポジット・オブジェクトの全パーティションの全サブパーティションにアクセスする場合の計画を示します。この目的を達成するために、2つのパーティション行ソースを使用します。1つはパーティションを反復するレンジ・パーティション行ソースで、もう1つはアクセスされる各パーティションのサブパーティションを反復するハッシュ・パーティション行ソースです。
次の例では、プルーニングを実行しないため、レンジ・パーティション行ソースはパーティション1から5を反復します。各パーティション内では、ハッシュ・パーティション行ソースは現在のパーティションのサブパーティション1から3を反復します。その結果、表アクセス行ソースがサブパーティション1から15にアクセスします。つまり、データベースはコンポジット・オブジェクトのすべてのサブパーティションにアクセスします。
EXPLAIN PLAN FOR
SELECT *
FROM emp_comp
WHERE hire_date = TO_DATE('15-FEB-1998', 'DD-MON-YYYY');
-----------------------------------------------------------------------
| Id | Operation | Name |Rows|Bytes |Cost|Pstart|Pstop|
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 2660 | 17 | | |
| 1 | PARTITION RANGE SINGLE| | 20 | 2660 | 17 | 5 | 5 |
| 2 | PARTITION HASH ALL | | 20 | 2660 | 17 | 1 | 3 |
|* 3 | TABLE ACCESS FULL | EMP_COMP | 20 | 2660 | 17 | 13 | 15 |
-----------------------------------------------------------------------
この例では、最後のパーティション5のみがアクセスされます。このパーティションはコンパイル時に認識されるので、計画では表示する必要がありません。ハッシュ・パーティション行ソースは、そのパーティション内のすべてのサブパーティションのアクセスを表示します。つまり、サブパーティション1から3が表示されることになりますが、これはemp_comp
表のサブパーティション13から15に変換されます。
次の文を考えてみます。
EXPLAIN PLAN FOR
SELECT *
FROM emp_comp
WHERE department_id = 20;
------------------------------------------------------------------------
| Id | Operation |Name |Rows | Bytes |Cost|Pstart|Pstop|
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101 | 13433 | 78 | | |
| 1 | PARTITION RANGE ALL | | 101 | 13433 | 78 | 1 | 5 |
| 2 | PARTITION HASH SINGLE| | 101 | 13433 | 78 | 3 | 3 |
|* 3 | TABLE ACCESS FULL | EMP_COMP | 101 | 13433 | 78 | | |
------------------------------------------------------------------------
前の例では、述語deptno=20
により各パーティション内のハッシュ・ディメンションでのプルーニングが可能になります。したがって、Oracle Databaseでは単一のサブパーティションのアクセスのみが必要です。このサブパーティションの番号はコンパイル時に認識されるので、ハッシュ・パーティション行ソースは必要ありません。
最後に、次の文を検討します。
VARIABLE dno NUMBER;
EXPLAIN PLAN FOR
SELECT *
FROM emp_comp
WHERE department_id = :dno;
-----------------------------------------------------------------------
| Id| Operation | Name |Rows| Bytes |Cost|Pstart|Pstop|
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101| 13433 | 78 | | |
| 1 | PARTITION RANGE ALL | | 101| 13433 | 78 | 1 | 5 |
| 2 | PARTITION HASH SINGLE| | 101| 13433 | 78 | KEY | KEY |
|*3 | TABLE ACCESS FULL | EMP_COMP | 101| 13433 | 78 | | |
-----------------------------------------------------------------------
最後の2つの例は、deptno=20
がdepartment_id
= :dno
に置き換えられたこと以外は同じです。この最後の場合、サブパーティションの番号はコンパイル時には不明であり、ハッシュ・パーティション行ソースが割り当てられます。Oracle Databaseでは、各パーティション内の1つのサブパーティションにのみアクセスするため、この行ソースのオプションはSINGLE
です。ステップ2では、PARTITION
_START
およびPARTITION
_STOP
の両方がKEY
に設定されます。この値は、Oracle Databaseでは実行時にサブパーティションの数が決まることを意味します。
6.3.4.5.3 パーシャル・パーティション・ワイズ結合の例
これらの例では、問合せオプティマイザがこの問合せのコストに基づいて異なる計画を選択する可能性があるため、PQ_DISTRIBUTE
ヒントにより、パーシャル・パーティション・ワイズ結合が明示的に強制されています。
例6-6 レンジ・パーティション化を使用したパーシャル・パーティション・ワイズ結合
次の例では、emp_range_did
がパーティション化列department_id
で結合され、パラレル化されます。dept2
表がパーティション化されていないことにより、データベースでは、パーシャル・パーティション・ワイズ結合が使用可能になります。Oracle Databaseでは、結合前にdept2
表を動的にパーティション化します。
CREATE TABLE dept2 AS SELECT * FROM departments;
ALTER TABLE dept2 PARALLEL 2;
CREATE TABLE emp_range_did PARTITION BY RANGE(department_id)
(PARTITION emp_p1 VALUES LESS THAN (150),
PARTITION emp_p5 VALUES LESS THAN (MAXVALUE) )
AS SELECT * FROM employees;
ALTER TABLE emp_range_did PARALLEL 2;
EXPLAIN PLAN FOR
SELECT /*+ PQ_DISTRIBUTE(d NONE PARTITION) ORDERED */ e.last_name,
d.department_name
FROM emp_range_did e, dept2 d
WHERE e.department_id = d.department_id;
-------------------------------------------------------------------------------------------
|Id| Operation |Name |Row|Byte|Cost|Pstart|Pstop|TQ|IN-OUT|PQ Distrib|
-------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | |284 |16188|6| | | | | |
| 1| PX COORDINATOR | | | | | | | | | |
| 2| PX SEND QC (RANDOM) |:TQ10001 |284 |16188|6| | | Q1,01 |P->S|QC (RAND) |
|*3| HASH JOIN | |284 |16188|6| | | Q1,01 |PCWP| |
| 4| PX PARTITION RANGE ALL | |284 |7668 |2|1 |2| Q1,01 |PCWC| |
| 5| TABLE ACCESS FULL |EMP_RANGE_DID|284 |7668 |2|1 |2| Q1,01 |PCWP| |
| 6| BUFFER SORT | | | | | | | Q1,01 |PCWC| |
| 7| PX RECEIVE | | 21 | 630 |2| | | Q1,01 |PCWP| |
| 8| PX SEND PARTITION (KEY)|:TQ10000 | 21 | 630 |2| | | |S->P|PART (KEY)|
| 9| TABLE ACCESS FULL |DEPT2 | 21 | 630 |2| | | | | |
-------------------------------------------------------------------------------------------
この実行計画は、dept2
表がシリアルにスキャンされ、emp_range_didの同じパーティション化列値(department_id)
を持つすべての行が、パーティション・キーを示すPART (KEY)
、表キューを介して、パーシャル・パーティション・ワイズ結合を実行する同じパラレル実行サーバーに送られることを示します。
例6-7 コンポジット・パーティション化を使用したパーシャル・パーティション・ワイズ結合
次の例では、emp_comp
がパーティション化列で結合され、パラレル化されます。dept2
表がパーティション化されていないことにより、パーシャル・パーティション・ワイズ結合が使用可能になります。データベースでは、結合前にdept2
を動的にパーティション化します。
ALTER TABLE emp_comp PARALLEL 2;
EXPLAIN PLAN FOR
SELECT /*+ PQ_DISTRIBUTE(d NONE PARTITION) ORDERED */ e.last_name,
d.department_name
FROM emp_comp e, dept2 d
WHERE e.department_id = d.department_id;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
-------------------------------------------------------------------------------------------
| Id| Operation | Name |Rows |Bytes |Cost|Pstart|Pstop|TQ |IN-OUT|PQ Distrib|
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 445 |17800| 5 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) |:TQ10001| 445 |17800| 5 | | | Q1,01 |P->S| QC (RAND)|
|*3 | HASH JOIN | | 445 |17800| 5 | | | Q1,01 |PCWP| |
| 4 | PX PARTITION RANGE ALL | | 107 | 1070| 3 |1 | 5 | Q1,01 |PCWC| |
| 5 | PX PARTITION HASH ALL | | 107 | 1070| 3 |1 | 3 | Q1,01 |PCWC| |
| 6 | TABLE ACCESS FULL |EMP_COMP| 107 | 1070| 3 |1 | 15| Q1,01 |PCWP| |
| 7 | PX RECEIVE | | 21 | 630| 1 | | | Q1,01 |PCWP| |
| 8 | PX SEND PARTITION (KEY)|:TQ10000| 21 | 630| 1 | | | Q1,00 |P->P|PART (KEY)|
| 9 | PX BLOCK ITERATOR | | 21 | 630| 1 | | | Q1,00 |PCWC| |
|10 | TABLE ACCESS FULL |DEPT2 | 21 | 630| 1 | | | Q1,00 |PCWP| |
-------------------------------------------------------------------------------------------
この計画は、オプティマイザが2つの列の一方からパーシャル・パーティション・ワイズ結合を選択することを示します。PX SEND
のノード・タイプはPARTITION (KEY)
で、PQ Distrib
列にはパーティション・キーを示すテキストPART (KEY)
が含まれています。これは、EMP_COMP
のスキャンと結合を実行するパラレル実行サーバーに送られる結合列department_id
に基づいて、dept2
表が再びパーティション化されることを意味します。
6.3.4.5.4 フル・パーティション・ワイズ結合の例
この例では、emp_comp
とdept_hash
がハッシュ・パーティション化列で結合され、フル・パーティション・ワイズ結合を使用できるようになります。
PARTITION HASH
行ソースが、PLAN TABLE出力の結合行ソースの上に表示されます。
CREATE TABLE dept_hash
PARTITION BY HASH(department_id)
PARTITIONS 3
PARALLEL 2
AS SELECT * FROM departments;
EXPLAIN PLAN FOR
SELECT /*+ PQ_DISTRIBUTE(e NONE NONE) ORDERED */ e.last_name,
d.department_name
FROM emp_comp e, dept_hash d
WHERE e.department_id = d.department_id;
-------------------------------------------------------------------------------------------
|Id| Operation | Name |Rows|Bytes|Cost|Pstart|Pstop|TQ |IN-OUT|PQ Distrib|
-------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 106 | 2544 |8| | | | | |
| 1| PX COORDINATOR | | | | | | | | | |
| 2| PX SEND QC (RANDOM) |:TQ10000 | 106 | 2544 |8| | | Q1,00 | P->S |QC (RAND)|
| 3| PX PARTITION HASH ALL | | 106 | 2544 |8|1 | 3 | Q1,00 | PCWC | |
|*4| HASH JOIN | | 106 | 2544 |8| | | Q1,00 | PCWP | |
| 5| PX PARTITION RANGE ALL| | 107 | 1070 |3|1 | 5 | Q1,00 | PCWC | |
| 6| TABLE ACCESS FULL |EMP_COMP | 107 | 1070 |3|1 |15 | Q1,00 | PCWP | |
| 7| TABLE ACCESS FULL |DEPT_HASH | 27 | 378 |4|1 | 3 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------
PX PARTITION HASH
行ソースはPLAN TABLE出力で結合行ソースの上に表示されますが、PX PARTITION RANGE
行ソースはemp_comp
のスキャンにまたがって表示されます。各パラレル実行サーバーは、emp_comp
のハッシュ・パーティション全体とdept_hash
のパーティション全体の結合を実行します。
6.3.4.5.5 INLIST ITERATORおよびEXPLAIN PLANの例
INLIST ITERATOR
操作は、索引がIN
リスト述語を実装する場合に、EXPLAIN PLAN
出力に表示されます。
次の文を考えてみます。
SELECT * FROM emp WHERE empno IN (7876, 7900, 7902);
EXPLAIN PLAN
出力は次のようになります。
OPERATION OPTIONS OBJECT_NAME
---------------- --------------- --------------
SELECT STATEMENT
INLIST ITERATOR
TABLE ACCESS BY ROWID EMP
INDEX RANGE SCAN EMP_EMPNO
INLIST ITERATOR
操作は、IN
リスト述語内の各値に対して、計画内の次の操作を反復します。次の各項では、パーティション表およびパーティション索引で使用可能な3種類のIN
リスト列について説明します。
6.3.4.5.5.1 INリスト列が索引列である場合: 例
IN
リスト列empno
が索引列で、パーティション列ではない場合、IN
リスト演算子演算子は、計画で、表操作よりも前、かつパーティション操作よりも後に表示されます。
OPERATION OPTIONS OBJECT_NAME PARTIT_START PARTITI_STOP
---------------- ------------ ----------- ------------ ------------
SELECT STATEMENT
PARTITION RANGE ALL KEY(INLIST) KEY(INLIST)
INLIST ITERATOR
TABLE ACCESS BY LOCAL INDEX ROWID EMP KEY(INLIST) KEY(INLIST)
INDEX RANGE SCAN EMP_EMPNO KEY(INLIST) KEY(INLIST)
パーティションの開始キーおよび終了キーに対するKEY(INLIST)
指定は、索引の開始キーおよび終了キーにIN
リスト述語が表示されることを指定します。
6.3.4.5.5.2 INリスト列が索引でありパーティション列である場合: 例
empno
が索引付けされている列で、それがパーティション列でもある場合、計画にはパーティション操作の前にINLIST ITERATOR
操作が含まれています。
OPERATION OPTIONS OBJECT_NAME PARTITION_START PARTITION_STOP
---------------- ------------ ----------- --------------- --------------
SELECT STATEMENT
INLIST ITERATOR
PARTITION RANGE ITERATOR KEY(INLIST) KEY(INLIST)
TABLE ACCESS BY LOCAL INDEX ROWID EMP KEY(INLIST) KEY(INLIST)
INDEX RANGE SCAN EMP_EMPNO KEY(INLIST) KEY(INLIST)
6.3.4.5.5.3 INリスト列がパーティション列である場合: 例
empno
がパーティション列で、索引が存在しない場合は、INLIST ITERATOR
操作は割り当てられません。
OPERATION OPTIONS OBJECT_NAME PARTITION_START PARTITION_STOP
---------------- ------------ ----------- --------------- --------------
SELECT STATEMENT
PARTITION RANGE INLIST KEY(INLIST) KEY(INLIST)
TABLE ACCESS FULL EMP KEY(INLIST) KEY(INLIST)
emp_empno
がビットマップ索引である場合、計画は次のとおりです。
OPERATION OPTIONS OBJECT_NAME
---------------- --------------- --------------
SELECT STATEMENT
INLIST ITERATOR
TABLE ACCESS BY INDEX ROWID EMP
BITMAP CONVERSION TO ROWIDS
BITMAP INDEX SINGLE VALUE EMP_EMPNO
6.3.4.5.6 ドメイン索引およびEXPLAIN PLANの例
EXPLAIN PLAN
を使用して、ドメイン索引に対するユーザー定義のCPUおよびI/Oコストを導出できます。
EXPLAIN PLAN
は、ドメイン索引統計をPLAN_TABLE
のOTHER
列に表示します。たとえば、resume
列にドメイン索引emp_resume
を持つユーザー定義演算子CONTAINS
が表emp
に存在し、emp_resume
の索引タイプが演算子CONTAINS
をサポートしている場合に、次の問合せをします。次の問合せの計画をEXPLAINします。
SELECT * FROM emp WHERE CONTAINS(resume, 'Oracle') = 1
次のような計画が表示されます。
OPERATION OPTIONS OBJECT_NAME OTHER
----------------- ----------- ------------ ----------------
SELECT STATEMENT
TABLE ACCESS BY ROWID EMP
DOMAIN INDEX EMP_RESUME CPU: 300, I/O: 4
6.4 実行計画の比較
計画比較ツールは、参照プランと任意のテスト・プランのリストを受け取って、それらの間の相違点を強調表示します。計画は、行ごとではなく論理的に比較されます。
6.4.1 計画の比較の目的
計画の比較レポートでは、相違の原因を特定します。これは、ユーザーが計画の再現性の問題を絞り込む際に役立ちます。
計画の比較レポートは、特に次のシナリオで役立ちます。
-
パフォーマンスが低下している問合せの現在の計画とAWRで取得した古い計画を比較する場合。
-
SQL計画ベースラインは当初に意図していた計画の再現に失敗していて、新しい計画と意図していた計画の相違点を判断する場合。
-
ヒントの追加方法、パラメータの変更方法または索引の作成方法が、計画に与える影響を判断する場合。
-
SQLプロファイルに基づいて生成された計画またはSQLパフォーマンス・アナライザによって生成された計画が当初の計画と異なる点を判断する場合。
6.4.2 計画の比較のユーザー・インタフェース
DBMS_XPLAN.COMPARE_PLANS
を使用すると、テキスト、XMLまたはHTML形式でレポートを生成できます。
計画の比較レポートの形式
レポートはサマリーから始まります。COMPARE PLANS REPORT
セクションには、次の例に示すように、レポートを実行したユーザーや比較した計画の数などの情報が含まれます。
COMPARE PLANS REPORT
-------------------------------------------------------------------------
Current user : SH
Total number of plans : 2
Number of findings : 1
-------------------------------------------------------------------------
レポートのCOMPARISON DETAILS
セクションには、次の情報が含まれています。
-
計画の情報
この情報には、計画の番号、計画のソース、計画の属性(ソースによって異なる)、解析スキーマおよびSQLテキストが含まれます。
-
計画
このセクションには、述語やノートを含む計画の行が表示されます。
-
比較結果
このセクションには、比較結果の概要が示されます。結合順序、結合方法、アクセス・パス、パラレル配分方法などの論理的な相違点が強調表示されています。結果は番号
1
から始まります。特定の問合せブロックに関連する結果の場合、テキストの冒頭はブロックの名前になります。特定のオブジェクトの別名に関連する結果の場合、テキストの冒頭は問合せブロックの名前とオブジェクトの別名になります。次に例を示します。Comparison Results (1): ----------------------------- 1. Query block SEL$1, Alias PRODUCTS@SEL$1: Some columns (OPERATION, OPTIONS, OBJECT_NAME) do not match between the reference plan (id: 2) and the current plan (id: 2).
DBMS_XPLAN.PLAN_OBJECT_LIST表タイプ
plan_object_list
タイプにより、汎用オブジェクトのリストがDBMS_XPLAN.COMPARE_PLANS
関数への入力として使用できるようになります。構文は次のとおりです。
TYPE plan_object_list IS TABLE OF generic_plan_object;
汎用オブジェクトは、すべての計画ソースから計画の共通属性を抽象化します。すべての計画ソースは、plan_object_list
スーパークラスのサブクラスです。次の表に、様々な計画ソースのまとめを示します。オプション・パラメータがnullの場合は、複数のオブジェクトに対応できる点に注意してください。たとえば、cursor_cache_object
に子番号を指定しないと、指定したSQL IDのすべてのカーソル・キャッシュ文と一致します。
表6-3 PLAN_OBJECT_LISTの計画ソース
計画ソース | 指定 | 説明 |
---|---|---|
PLAN TABLE |
|
このパラメータは次のとおりです。
|
カーソル・キャッシュ |
|
このパラメータは次のとおりです。
|
AWR |
|
このパラメータは次のとおりです。
|
SQLチューニング・セット |
|
このパラメータは次のとおりです。
|
SQL計画管理 |
|
このパラメータは次のとおりです。
|
SQLプロファイル |
|
|
アドバイザ |
|
このパラメータは次のとおりです。
|
DBMS_XPLAN.COMPARE_PLANS関数
計画の比較ツールのインタフェースは次の関数です。
DBMS_XPLAN.COMPARE_PLANS(
reference_plan IN generic_plan_object,
compare_plan_list IN plan_object_list,
type IN VARCHAR2 := 'TEXT',
level IN VARCHAR2 := 'TYPICAL',
section IN VARCHAR2 := 'ALL')
RETURN CLOB;
次の表では、比較する計画を指定するパラメータについて説明します。
表6-4 COMPARE_PLANS関数のパラメータ
パラメータ | 説明 |
---|---|
|
|
|
計画オブジェクトのリストを指定します。1つのオブジェクトは1つ以上の計画に対応します。 |
例6-8 子カーソルからの計画の比較
この例では、SQL IDが8mkxm7ur07za0
で子カーソル番号が2の計画と、同じSQL IDで子カーソル番号が4の計画を比較します。
VAR v_report CLOB;
BEGIN
:v_report := DBMS_XPLAN.COMPARE_PLANS(
reference_plan => CURSOR_CACHE_OBJECT('8mkxm7ur07za0', 2),
compare_plan_list => PLAN_OBJECT_LIST(CURSOR_CACHE_OBJECT('8mkxm7ur07za0', 4)));
END;
/
PRINT v_report
例6-9 子カーソルからの計画とSQL計画ベースラインからの計画の比較
この例では、SQL IDが8mkxm7ur07za0
で子カーソル番号が2の計画とSQL計画ベースラインからの計画を比較します。このベースライン問合せのSQLハンドルはSQL_024d0f7d21351f5d
で、計画名はSQL_PLAN_sdfjkd
です。
VAR v_report CLOB;
BEGIN
:v_report := DBMS_XPLAN.COMPARE_PLANS( -
reference_plan => CURSOR_CACHE_OBJECT('8mkxm7ur07za0', 2),
compare_plan_list => PLAN_OBJECT_LIST(SPM_OBJECT('SQL_024d0f7d21351f5d', 'SQL_PLAN_sdfjkd')));
END;
PRINT v_report
例6-10 1つの計画と複数のソースからの計画の比較
この例では、サマリー・セクションのみが出力されます。このプログラムでは、SQL IDが8mkxm7ur07za0
で子カーソル番号が2の計画と、次のリストに含まれるすべての計画を比較します。
-
SQL ID
8mkxm7ur07za0
に対して生成された共有SQL領域内のすべての計画 -
SQL ID
6vfqvav0rgyad
に対してSQLチューニング・セットSH. SQLT_WORKLOAD
内で生成されたすべての計画 -
データベースID 5およびSQL ID
6vfqvav0rgyad
に対して取得されるAWRのすべての計画 -
SQL_PLAN_sdfjkd
という名前のハンドルSQL_024d0f7d21351f5d
を持つ問合せに対する計画ベースライン -
plan_id=38
で識別されるsh.plan_table
に格納された計画 -
SQLプロファイル名
pe3r3ejsfd
で識別される計画 -
タスク名
TASK_1228
、実行名EXEC_1928
およびSQL ID8mkxm7ur07za0
で識別されるSQLに格納されているすべての計画
VAR v_report CLOB
BEGIN
:v_report := DBMS_XPLAN.COMPARE_PLANS(
reference_plan => CURSOR_CACHE_OBJECT('8mkxm7ur07za0', 2),
compare_plan_list => plan_object_list(
cursor_cache_object('8mkxm7ur07za0'),
sqlset_object('SH', 'SQLT_WORKLOAD', '6vfqvav0rgyad'),
awr_object('6vfqvav0rgyad', 5),
spm_object('SQL_024d0f7d21351f5d', 'SQL_PLAN_sdfjkd'),
plan_table_object('SH', 'plan_table', 38),
sql_profile_object('pe3r3ejsfd'),
advisor_object('TASK_1228', 'EXEC_1928', '8mkxm7ur07za0')),
type => 'XML',
level => 'ALL',
section => 'SUMMARY');
END;
/
PRINT v_report
ノート:
DBMS_XPLAN
パッケージの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
6.4.3 実行計画の比較: チュートリアル
計画を比較するには、DBMS_XPLAN.COMPARE_PLANS
関数を使用します。
前提条件
このチュートリアルでは、ユーザーsh
が次の問合せを発行したと仮定しています。
select count(*)
from products p, sales s
where p.prod_id = s.prod_id
and p.prod_min_price > 200;
select count(*)
from products p, sales s
where p.prod_id = s.prod_id
and s.quantity_sold = 43;
実行計画を比較するには:
-
SQL*Plusを起動し、管理権限でデータベースにログインします。
-
V$SQL
を問い合せて、2つの問合せのSQL IDを確認します。次の問合せは、文字列
products
が含まれている問合せについてV$SQL
を問い合せます。SET LINESIZE 120 COL SQL_ID FORMAT a20 COL SQL_TEXT FORMAT a60 SELECT SQL_ID, SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%products%' AND SQL_TEXT NOT LIKE '%SQL_TEXT%' ORDER BY SQL_ID; SQL_ID SQL_TEXT ----------------- ------------------------------------------------ 0hxmvnfkasg6q select count(*) from products p, sales s where p.prod_id = s.prod_id and s.quantity_sold = 43 10dqxjph6bwum select count(*) from products p, sales s where p.prod_id = s.prod_id and p.prod_min_price > 200
-
sh
ユーザーとしてデータベースにログインします。 -
前のステップで取得したSQL IDを指定して、
DBMS_XPLAN.COMPARE_PLANS
関数を実行します。たとえば、次のプログラムを実行します。
VARIABLE v_rep CLOB BEGIN :v_rep := DBMS_XPLAN.COMPARE_PLANS( reference_plan => cursor_cache_object('0hxmvnfkasg6q', NULL), compare_plan_list => plan_object_list(cursor_cache_object('10dqxjph6bwum', NULL)), type => 'TEXT', level => 'TYPICAL', section => 'ALL'); END; /
-
レポートを印刷します。
たとえば、次の問合せを実行します。
SET PAGESIZE 50000 SET LONG 100000 SET LINESIZE 210 COLUMN report FORMAT a200 SELECT :v_rep REPORT FROM DUAL;
次のサンプル・レポートの「
Comparison Results
」セクションは、最初の問合せのみが結合消去変換を使用していることを示しています。REPORT --------------------------------------------------------------------------------------- COMPARE PLANS REPORT --------------------------------------------------------------------------------------- Current user : SH Total number of plans : 2 Number of findings : 1 --------------------------------------------------------------------------------------- COMPARISON DETAILS --------------------------------------------------------------------------------------- Plan Number : 1 (Reference Plan) Plan Found : Yes Plan Source : Cursor Cache SQL ID : 0hxmvnfkasg6q Child Number : 0 Plan Database Version : 19.0.0.0 Parsing Schema : "SH" SQL Text : select count(*) from products p, sales s where p.prod_id = s.prod_id and s.quantity_sold = 43 Plan ----------------------------- Plan Hash Value : 3519235612 ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 469 | | | 1 | SORT AGGREGATE | | 1 | 3 | | | | 2 | PARTITION RANGE ALL | | 1 | 3 | 469 | 00:00:01 | | * 3 | TABLE ACCESS FULL | SALES | 1 | 3 | 469 | 00:00:01 | ------------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------ * 3 - filter("S"."QUANTITY_SOLD"=43) --------------------------------------------------------------------------------------- Plan Number : 2 Plan Found : Yes Plan Source : Cursor Cache SQL ID : 10dqxjph6bwum Child Number : 0 Plan Database Version : 19.0.0.0 Parsing Schema : "SH" SQL Text : select count(*) from products p, sales s where p.prod_id = s.prod_id and p.prod_min_price > 200 Plan ----------------------------- Plan Hash Value : 3037679890 --------------------------------------------------------------------------------------- |Id| Operation | Name | Rows | Bytes |Cost |Time | --------------------------------------------------------------------------------------- | 0| SELECT STATEMENT | | | |34| | | 1| SORT AGGREGATE | | 1 | 13 | | | |*2| HASH JOIN | |781685 |10161905 |34|00:00:01| |*3| TABLE ACCESS FULL | PRODUCTS | 61 | 549 | 2|00:00:01| | 4| PARTITION RANGE ALL | |918843 | 3675372 |29|00:00:01| | 5| BITMAP CONVERSION TO ROWIDS | |918843 | 3675372 |29|00:00:01| | 6| BITMAP INDEX FAST FULL SCAN | SALES_PROD_BIX | | | | | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------ * 2 - access("P"."PROD_ID"="S"."PROD_ID") * 3 - filter("P"."PROD_MIN_PRICE">200) Notes ----- - This is an adaptive plan Comparison Results (1): ----------------------------- 1. Query block SEL$1: Transformation JOIN REMOVED FROM QUERY BLOCK occurred only in the reference plan (result query block: SEL$A43D1678).
関連項目:
DBMS_XPLAN
パッケージの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
6.4.4 実行計画の比較: 例
ここでは、sh
スキーマの表の問合せに関する計画の比較レポートを生成する方法の例を示します。
例6-11 説明された計画とカーソル内の計画の比較
この例では、sh
スキーマの表の問合せの計画について説明して、その問合せを実行します。
EXPLAIN PLAN
SET STATEMENT_ID='TEST' FOR
SELECT c.cust_city, SUM(s.quantity_sold)
FROM customers c, sales s, products p
WHERE c.cust_id=s.cust_id
AND p.prod_id=s.prod_id
AND prod_min_price>100
GROUP BY c.cust_city;
SELECT c.cust_city, SUM(s.quantity_sold)
FROM customers c, sales s, products p
WHERE c.cust_id=s.cust_id
AND p.prod_id=s.prod_id
AND prod_min_price>100
GROUP BY c.cust_city;
実行した問合せのSQL IDは、9mp7z6qq83k5y
だと仮定しています。次のPL/SQLプログラムでは、PLAN_TABLE
の計画と共有SQL領域の計画を比較します。
BEGIN
:v_rep := DBMS_XPLAN.COMPARE_PLANS(
reference_plan => plan_table_object('SH', 'PLAN_TABLE', 'TEST', NULL),
compare_plan_list => plan_object_list(cursor_cache_object('9mp7z6qq83k5y')),
type => 'TEXT',
level => 'TYPICAL',
section => 'ALL');
END;
/
PRINT v_rep
次のサンプル・レポートは、計画が同じになることを示しています。
COMPARE PLANS REPORT
-------------------------------------------------------------------------
Current user : SH
Total number of plans : 2
Number of findings : 1
-------------------------------------------------------------------------
COMPARISON DETAILS
-------------------------------------------------------------------------
Plan Number : 1 (Reference Plan)
Plan Found : Yes
Plan Source : Plan Table
Plan Table Owner : SH
Plan Table Name : PLAN_TABLE
Statement ID : TEST
Plan ID : 52
Plan Database Version : 19.0.0.0
Parsing Schema : "SH"
SQL Text : No SQL Text
Plan
-----------------------------
Plan Hash Value : 3473931970
--------------------------------------------------------------------------
| Id| Operation | Name | Rows | Bytes |Cost| Time |
--------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 620| 22320|1213| 00:00:01 |
| 1| HASH GROUP BY | | 620| 22320|1213| 00:00:01 |
|* 2| HASH JOIN | |160348| 5772528|1209| 00:00:01 |
| 3| TABLE ACCESS FULL |CUSTOMERS| 55500| 832500| 414| 00:00:01 |
|* 4| HASH JOIN | |160348| 3367308| 472| 00:00:01 |
|* 5| TABLE ACCESS FULL |PRODUCTS | 13| 117| 2| 00:00:01 |
| 6| PARTITION RANGE ALL | |918843|11026116| 467| 00:00:01 |
| 7| TABLE ACCESS FULL |SALES |918843|11026116| 467| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("C"."CUST_ID"="S"."CUST_ID")
* 4 - access("P"."PROD_ID"="S"."PROD_ID")
* 5 - filter("PROD_MIN_PRICE">100)
Notes
-----
- This is an adaptive plan
--------------------------------------------------------------------------
Plan Number : 2
Plan Found : Yes
Plan Source : Cursor Cache
SQL ID : 9mp7z6qq83k5y
Child Number : 0
Plan Database Version : 19.0.0.0
Parsing Schema : "SH"
SQL Text : select c.cust_city, sum(s.quantity_sold) from
customers c, sales s, products p where
c.cust_id=s.cust_id and p.prod_id=s.prod_id and
prod_min_price>100 group by c.cust_city
Plan
-----------------------------
Plan Hash Value : 3473931970
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost|Time |
---------------------------------------------------------------------------
| 0| SELECT STATEMENT | | | |1213 | |
| 1| HASH GROUP BY | | 620| 22320|1213 |00:00:01 |
|* 2| HASH JOIN | |160348| 5772528|1209 |00:00:01 |
| 3| TABLE ACCESS FULL |CUSTOMERS | 55500| 832500| 414 |00:00:01 |
|* 4| HASH JOIN | |160348| 3367308| 472 |00:00:01 |
|* 5| TABLE ACCESS FULL |PRODUCTS | 13| 117| 2 |00:00:01 |
| 6| PARTITION RANGE ALL | |918843|11026116| 467 |00:00:01 |
| 7| TABLE ACCESS FULL |SALES |918843|11026116| 467 |00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("C"."CUST_ID"="S"."CUST_ID")
* 4 - access("P"."PROD_ID"="S"."PROD_ID")
* 5 - filter("PROD_MIN_PRICE">100)
Notes
-----
- This is an adaptive plan
Comparison Results (1):
-----------------------------
1. The plans are the same.
例6-12 ベースラインとSQLチューニング・セットの計画の比較
次の問合せの計画を比較するとします(副問合せに含まれるNO_MERGE
ヒントのみが異なっています)。
SELECT c.cust_city, SUM(s.quantity_sold)
FROM customers c, sales s,
(SELECT prod_id FROM products WHERE prod_min_price>100) p
WHERE c.cust_id=s.cust_id
AND p.prod_id=s.prod_id
GROUP BY c.cust_city;
SELECT c.cust_city, SUM(s.quantity_sold)
FROM customers c, sales s,
(SELECT /*+ NO_MERGE */ prod_id FROM products WHERE prod_min_price>100)
WHERE c.cust_id=s.cust_id
AND p.prod_id=s.prod_id
GROUP BY c.cust_city;
最初の問合せの計画は、SQLハンドルSQL_c522f5888cc4613e
のSQL計画管理ベースラインで取得されます。2番目の問合せの計画は、MYSTS1
という名前のSQLチューニング・セットに格納されていて、SQL IDはd07p7qmrm13nc
です。次のPL/SQLプログラムを実行して計画を比較します。
VAR v_rep CLOB
BEGIN
v_rep := DBMS_XPLAN.COMPARE_PLANS(
reference_plan => spm_object('SQL_c522f5888cc4613e'),
compare_plan_list => plan_object_list(sqlset_object('SH', 'MYSTS1', 'd07p7qmrm13nc', null)),
type => 'TEXT',
level => 'TYPICAL',
section => 'ALL');
END;
/
PRINT v_rep
次の出力は、ヒントのない問合せに対応する参照プランのみがビューのマージを使用したことを示しています。
---------------------------------------------------------------------------
COMPARE PLANS REPORT
---------------------------------------------------------------------------
Current user : SH
Total number of plans : 2
Number of findings : 1
---------------------------------------------------------------------------
COMPARISON DETAILS
---------------------------------------------------------------------------
Plan Number : 1 (Reference Plan)
Plan Found : Yes
Plan Source : SQL Plan Baseline
SQL Handle : SQL_c522f5888cc4613e
Plan Name : SQL_PLAN_ca8rpj26c8s9y7c2279c4
Plan Database Version : 19.0.0.0
Parsing Schema : "SH"
SQL Text : select c.cust_city, sum(s.quantity_sold) from
customers c, sales s, (select prod_id from
products where prod_min_price>100) p where
c.cust_id=s.cust_id and p.prod id=s.prod_id
group by c.cust_city
Plan
-----------------------------
Plan Hash Value : 2082634180
---------------------------------------------------------------------------
| Id | Operation | Name |Rows |Bytes |Cost | Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 22 | |
| 1 | HASH GROUP BY | | 300 |11400 | 22 | 00:00:01 |
| 2 | HASH JOIN | | 718 |27284 | 21 | 00:00:01 |
| 3 | TABLE ACCESS FULL | CUSTOMERS | 630 | 9450 | 5 | 00:00:01 |
| 4 | HASH JOIN | | 718 |16514 | 15 | 00:00:01 |
| 5 | TABLE ACCESS FULL | PRODUCTS | 573 | 5730 | 9 | 00:00:01 |
| 6 | PARTITION RANGE ALL | | 960 |12480 | 5 | 00:00:01 |
| 7 | TABLE ACCESS FULL | SALES | 960 |12480 | 5 | 00:00:01 |
---------------------------------------------------------------------------
---------------------------------------------------------------------------
Plan Number : 2
Plan Found : Yes
Plan Source : SQL Tuning Set
SQL Tuning Set Owner : SH
SQL Tuning Set Name : MYSTS1
SQL ID : d07p7qmrm13nc
Plan Hash Value : 655891922
Plan Database Version : 19.0.0.0
Parsing Schema : "SH"
SQL Text : select c.cust_city, sum(s.quantity_sold) from
customers c, sales s, (select /*+ NO_MERGE */
prod_id from products where prod_min_price>100)
p where c.cust_id=s.cust_id and
p.prod_id=s.prod_id group by c.cust_city
Plan
-----------------------------
Plan Hash Value : 655891922
-------------------------------------------------------------------------
|Id | Operation | Name |Rows | Bytes |Cost| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 23 | |
| 1 | HASH GROUP BY | | 300 | 9900 | 23 |00:00:01 |
| 2 | HASH JOIN | | 718 | 23694 | 21 |00:00:01 |
| 3 | HASH JOIN | | 718 | 12924 | 15 |00:00:01 |
| 4 | VIEW | | 573 | 2865 | 9 |00:00:01 |
| 5 | TABLE ACCESS FULL | PRODUCTS | 573 | 5730 | 9 |00:00:01 |
| 6 | PARTITION RANGE ALL | | 960 | 12480 | 5 |00:00:01 |
| 7 | TABLE ACCESS FULL | SALES | 960 | 12480 | 5 |00:00:01 |
| 8 | TABLE ACCESS FULL | CUSTOMERS | 630 | 9450 | 5 |00:00:01 |
-------------------------------------------------------------------------
Notes
-----
- This is an adaptive plan
Comparison Results (1):
-----------------------------
1. Query block SEL$1: Transformation VIEW MERGE occurred only in the
reference plan (result query block: SEL$F5BB74E1).
例6-13 索引の追加前後の計画の比較
この例では、問合せ計画の索引の効果をテストします。
EXPLAIN PLAN
SET STATEMENT_ID='TST1' FOR
SELECT COUNT(*) FROM products WHERE prod_min_price>100;
CREATE INDEX newprodidx ON products(prod_min_price);
EXPLAIN PLAN
SET STATEMENT_ID='TST2' FOR
SELECT COUNT(*) FROM products WHERE prod_min_price>100;
次のPL/SQLプログラムを実行してレポートを生成します。
VAR v_rep CLOB
BEGIN
:v_rep := DBMS_XPLAN.COMPARE_PLANS(
reference_plan => plan_table_object('SH','PLAN_TABLE','TST1',NULL),
compare_plan_list => plan_object_list(plan_table_object('SH','PLAN_TABLE','TST2',NULL)),
TYPE => 'TEXT',
level => 'TYPICAL',
section => 'ALL');
END;
/
PRINT v_rep
次のレポートは、2つの計画の操作が異なっていることを示しています。
COMPARE PLANS REPORT
--------------------------------------------------------------------------
Current user : SH
Total number of plans : 2
Number of findings : 1
--------------------------------------------------------------------------
COMPARISON DETAILS
--------------------------------------------------------------------------
Plan Number : 1 (Reference Plan)
Plan Found : Yes
Plan Source : Plan Table
Plan Table Owner : SH
Plan Table Name : PLAN_TABLE
Statement ID : TST1
Plan ID : 56
Plan Database Version : 19.0.0.0
Parsing Schema : "SH"
SQL Text : No SQL Text
Plan
-----------------------------
Plan Hash Value : 3421487369
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 2 | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| * 2 | TABLE ACCESS FULL | PRODUCTS | 13 | 65 | 2 | 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter("PROD_MIN_PRICE">100)
--------------------------------------------------------------------------
Plan Number : 2
Plan Found : Yes
Plan Source : Plan Table
Plan Table Owner : SH
Plan Table Name : PLAN_TABLE
Statement ID : TST2
Plan ID : 57
Plan Database Version : 19.0.0.0
Parsing Schema : "SH"
SQL Text : No SQL Text
Plan
-----------------------------
Plan Hash Value : 2694011010
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 1 | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| * 2 | INDEX RANGE SCAN | NEWPRODIDX | 13 | 65 | 1 | 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("PROD_MIN_PRICE">100)
Comparison Results (1):
-----------------------------
1. Query block SEL$1, Alias PRODUCTS@SEL$1: Some columns (OPERATION,
OPTIONS, OBJECT_NAME) do not match between the reference plan
(id: 2) and the current plan (id: 2).
例6-14 可視索引と不可視索引の計画の比較
この例では、アプリケーションは次の問合せを実行します。
select count(*)
from products p, sales s
where p.prod_id = s.prod_id
and p.prod_status = 'obsolete';
この問合せの計画では、2つの索引sales_prod_bix
およびproducts_prod_status_bix
が使用されます。データベースでは、両方の索引に対する可視と不可視のすべての組合せを使用して4つの計画が生成されます。SQL計画管理は、次の問合せのベースラインの計画を受け入れるとします。
-
可視の
sales_prod_bix
および可視のproducts_prod_status_bix
-
可視の
sales_prod_bix
および不可視のproducts_prod_status_bix
-
不可視の
sales_prod_bix
および可視のproducts_prod_status_bix
両方の索引を不可視にしてから、問合せを再度実行します。オプティマイザは、不可視の索引を使用できないため新しい計画を生成します。3つのベースライン計画(少なくとも1つの索引が可視であることに依存するものすべて)が再現に失敗します。そのため、オプティマイザは新しい計画を使用して問合せのSQL計画ベースラインに追加します。現在共有SQL領域にある計画(参照プラン)とベースラインの4つの計画すべてを比較するには、次のPL/SQLコードを実行します。
VAR v_rep CLOB
BEGIN
:v_rep := DBMS_XPLAN.COMPARE_PLANS(
reference_plan => cursor_cache_object('45ns3tzutg0ds'),
compare_plan_list => plan_object_list(spm_object('SQL_aec814b0d452da8a')),
TYPE => 'TEXT',
level => 'TYPICAL',
section => 'ALL');
END;
/
PRINT v_rep
次のレポートでは、5つのすべての計画を比較しています。
-----------------------------------------------------------------------------
COMPARE PLANS REPORT
-----------------------------------------------------------------------------
Current user : SH
Total number of plans : 5
Number of findings : 19
-----------------------------------------------------------------------------
COMPARISON DETAILS
-----------------------------------------------------------------------------
Plan Number : 1 (Reference Plan)
Plan Found : Yes
Plan Source : Cursor Cache
SQL ID : 45ns3tzutg0ds
Child Number : 0
Plan Database Version : 19.0.0.0
Parsing Schema : "SH"
SQL Text : select count(*) from products p, sales s where p.prod_id
= s.prod_id and p.prod_status = 'obsolete'
Plan
-----------------------------
Plan Hash Value : 1136711713
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 15 | |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
| * 2 | HASH JOIN | | 320 | 9600 | 15 | 00:00:01 |
| 3 | JOIN FILTER CREATE | :BF0000 | 255 | 6375 | 9 | 00:00:01 |
| * 4 | TABLE ACCESS FULL | PRODUCTS | 255 | 6375 | 9 | 00:00:01 |
| 5 | JOIN FILTER USE | :BF0000 | 960 | 4800 | 5 | 00:00:01 |
| 6 | PARTITION RANGE ALL | | 960 | 4800 | 5 | 00:00:01 |
| * 7 | TABLE ACCESS FULL | SALES | 960 | 4800 | 5 | 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("P"."PROD_ID"="S"."PROD_ID")
* 4 - filter("P"."PROD_STATUS"='obsolete')
* 7 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"S"."PROD_ID"))
Notes
-----
- baseline_repro_fail = yes
-----------------------------------------------------------------------------------
Plan Number : 2
Plan Found : Yes
Plan Source : SQL Plan Baseline
SQL Handle : SQL_aec814b0d452da8a
Plan Name : SQL_PLAN_axk0nq3a55qna6e039463
Plan Database Version : 19.0.0.0
Parsing Schema : "SH"
SQL Text : select count(*) from products p, sales s where p.prod_id =
s.prod_id and p.prod_status = 'obsolete'
Plan
-----------------------------
Plan Hash Value : 1845728355
-------------------------------------------------------------------------------------------
| Id| Operation | Name |Rows|Bytes|Cost| Time |
-------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1| 30 |11 |00:00:01|
| 1| SORT AGGREGATE | | 1| 30 | | |
| *2| HASH JOIN | |320|9600 |11 |00:00:01|
| 3| JOIN FILTER CREATE | :BF0000 |255|6375 | 5 |00:00:01|
| *4| VIEW | index$_join$_001 |255|6375 | 5 |00:00:01|
| *5| HASH JOIN | | | | | |
| 6| BITMAP CONVERSION TO ROWIDS | |255|6375 | 1 |00:00:01|
| *7| BITMAP INDEX SINGLE VALUE | PRODUCTS_PROD_STATUS_BIX| | | | |
| 8| INDEX FAST FULL SCAN | PRODUCTS_PK |255|6375 | 4 |00:00:01|
| 9| JOIN FILTER USE | :BF0000 |960|4800 | 5 |00:00:01|
| 10| PARTITION RANGE ALL | |960|4800 | 5 |00:00:01|
|*11| TABLE ACCESS FULL | SALES |960|4800 | 5 |00:00:01|
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("P"."PROD_ID"="S"."PROD_ID")
* 4 - filter("P"."PROD_STATUS"='obsolete')
* 5 - access(ROWID=ROWID)
* 7 - access("P"."PROD_STATUS"='obsolete')
* 11 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"S"."PROD_ID"))
Comparison Results (4):
-----------------------------
1. Query block SEL$1, Alias P@SEL$1: Some lines (id: 4) in the reference plan are missing
in the current plan.
2. Query block SEL$1, Alias S@SEL$1: Some columns (ID) do not match between the reference
plan (id: 5) and the current plan (id: 9).
3. Query block SEL$1, Alias S@SEL$1: Some columns (ID, PARENT_ID, PARTITION_ID) do not
match between the reference plan (id: 6) and the current plan (id: 10).
4. Query block SEL$1, Alias S@SEL$1: Some columns (ID, PARENT_ID, PARTITION_ID) do not
match between the reference plan (id: 7) and the current plan (id: 11).
-------------------------------------------------------------------------------------------
Plan Number : 3
Plan Found : Yes
Plan Source : SQL Plan Baseline
SQL Handle : SQL_aec814b0d452da8a
Plan Name : SQL_PLAN_axk0nq3a55qna43c0d821
Plan Database Version : 19.0.0.0
Parsing Schema : "SH"
SQL Text : select count(*) from products p, sales s where p.prod_id = s.prod_id and
p.prod_status = 'obsolete'
Plan
-----------------------------
Plan Hash Value : 1136711713
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 15 | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
| * 2 | HASH JOIN | | 320 | 9600 | 15 | 00:00:01 |
| 3 | JOIN FILTER CREATE | :BF0000 | 255 | 6375 | 9 | 00:00:01 |
| * 4 | TABLE ACCESS FULL | PRODUCTS | 255 | 6375 | 9 | 00:00:01 |
| 5 | JOIN FILTER USE | :BF0000 | 960 | 4800 | 5 | 00:00:01 |
| 6 | PARTITION RANGE ALL | | 960 | 4800 | 5 | 00:00:01 |
| * 7 | TABLE ACCESS FULL | SALES | 960 | 4800 | 5 | 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("P"."PROD_ID"="S"."PROD_ID")
* 4 - filter("P"."PROD_STATUS"='obsolete')
* 7 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"S"."PROD_ID"))
Comparison Results (1):
-----------------------------
1. The plans are the same.
------------------------------------------------------------------------------
Plan Number : 4
Plan Found : Yes
Plan Source : SQL Plan Baseline
SQL Handle : SQL_aec814b0d452da8a
Plan Name : SQL_PLAN_axk0nq3a55qna1b7aea6c
Plan Database Version : 19.0.0.0
Parsing Schema : "SH"
SQL Text : select count(*) from products p, sales s where p.prod_id = s.prod_id and
p.prod_status = 'obsolete'
Plan
-----------------------------
Plan Hash Value : 461040236
-------------------------------------------------------------------------------------
| Id | Operation | Name |Rows|Bytes | Cost | Time |
--------------------------------------------------------- ---------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 10 | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
| 2 | NESTED LOOPS | |320 | 9600 | 10 | 00:00:01 |
|* 3 | TABLE ACCESS FULL | PRODUCTS |255 | 6375 | 9 | 00:00:01 |
| 4 | PARTITION RANGE ALL | | 1 | 5 | 10 | 00:00:01 |
| 5 | BITMAP CONVERSION COUNT | | 1 | 5 | 10 | 00:00:01 |
|* 6 | BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX | | | | |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 3 - filter("P"."PROD_STATUS"='obsolete')
* 6 - access("P"."PROD_ID"="S"."PROD_ID")
Comparison Results (7):
-----------------------------
1. Query block SEL$1, Alias P@SEL$1: Some lines (id: 3) in the reference plan are missing
in the current plan.
2. Query block SEL$1, Alias S@SEL$1: Some lines (id: 5) in the reference plan are missing
in the current plan.
3. Query block SEL$1, Alias S@SEL$1: Some lines (id: 7) in the reference plan are missing
in the current plan.
4. Query block SEL$1, Alias S@SEL$1: Some lines (id: 5,6) in the current plan are missing
in the reference plan.
5. Query block SEL$1, Alias P@SEL$1: Some columns (OPERATION) do not match between the
reference plan (id: 2) and the current plan (id: 2).
6. Query block SEL$1, Alias P@SEL$1: Some columns (ID, PARENT_ID, DEPTH) do not match
between the reference plan (id: 4) and the current plan (id: 3).
7. Query block SEL$1, Alias S@SEL$1: Some columns (ID, PARENT_ID, DEPTH, POSITION,
PARTITION_ID) do not match between the reference plan (id: 6) and the current plan (id: 4).
-------------------------------------------------------------------------------------------
Plan Number : 5
Plan Found : Yes
Plan Source : SQL Plan Baseline
SQL Handle : SQL_aec814b0d452da8a
Plan Name : SQL_PLAN_axk0nq3a55qna0628afbd
Plan Database Version : 19.0.0.0
Parsing Schema : "SH"
SQL Text : select count(*) from products p, sales s where p.prod_id = s.prod_id and
p.prod_status = 'obsolete'
Plan
-----------------------------
Plan Hash Value : 103329725
-------------------------------------------------------------------------------------------
|Id| Operation | Name | Rows|Bytes|Cost|Time |
-------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | | | 5 | |
| 1| SORT AGGREGATE | | 1 | 30 | | |
| 2| NESTED LOOPS | |320 |9600 | 5 |00:00:01|
| 3| VIEW | index$_join$_001 |255 |6375 | 5 |00:00:01|
| 4| HASH JOIN | | | | | |
| 5| BITMAP CONVERSION TO ROWIDS | |255 |6375 | 1 |00:00:01|
| 6| BITMAP INDEX SINGLE VALUE | PRODUCTS_PROD_STATUS_BIX | | | | |
| 7| INDEX FAST FULL SCAN | PRODUCTS_PK |255 |6375 | 4 |00:00:01|
| 8| PARTITION RANGE ALL | | 1 | 5 | 5 |00:00:01|
| 9| BITMAP CONVERSION TO ROWIDS | | 1 | 5 | 5 |00:00:01|
|10| BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX | | | | |
-------------------------------------------------------------------------------------------
Comparison Results (7):
-----------------------------
1. Query block SEL$1, Alias P@SEL$1: Some lines (id: 3) in the reference plan are missing
in the current plan.
2. Query block SEL$1, Alias P@SEL$1: Some lines (id: 4) in the reference plan are missing
in the current plan.
3. Query block SEL$1, Alias S@SEL$1: Some lines (id: 5) in the reference plan are missing
in the current plan.
4. Query block SEL$1, Alias S@SEL$1: Some lines (id: 7) in the reference plan are missing
in the current plan.
5. Query block SEL$1, Alias S@SEL$1: Some lines (id: 9,10) in the current plan are missing
in the reference plan.
6. Query block SEL$1, Alias P@SEL$1: Some columns (OPERATION) do not match between the
reference plan (id: 2) and the current plan (id: 2).
7. Query block SEL$1, Alias S@SEL$1: Some columns (ID, PARENT_ID, DEPTH, POSITION,
PARTITION_ID) do not match between the reference plan (id: 6) and the current plan (id: 8).
前述のレポートには、次の情報が示されています。
-
計画1は、共有SQL領域の参照プランです。この計画は、索引を使用しません(どちらも不可視になっています)。また、この計画はベースライン計画を再現しません。
-
計画2は、ベースラインにあります。この計画は、
sales_prod_bix
が不可視でproducts_prod_status_bix
が可視であることを前提としています。 -
計画3は、ベースラインにあります。この計画は、両方の索引が不可視であることを前提としています。計画1と計画3は同じです。
-
計画4は、ベースライン内にあります。この計画は、
sales_prod_bix
が可視であり、products_prod_status_bix
が不可視であることを前提としています。 -
計画5は、ベースラインにあります。この計画は、両方の索引が可視であることを前提としています。
この比較レポートは、計画1がそのベースラインからの計画を再現できなかったことを示しています。その理由は、実行時に使用可能なベースライン計画がなかったために、カーソル内の計画(計画1)がベースラインに追加されたからです。そのため、データベースでソフト解析が実行されて、索引のない計画が生成されました。現在のカーソルが無効になっているときに問合せが再度実行されると、比較レポートは、カーソル・プランがベースライン計画を再現したことを示します。
関連項目:
DBMS_XPLAN
パッケージの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
例6-15 再現に失敗したベースラインの比較
1つのユースケースとして、コストベース計画とSQL計画ベースラインを比較します。この例では、一意索引を作成します。データベースは、この索引を使用する計画ベースラインを取得します。索引を不可視にしてから、この問合せを再度実行します。索引が可視化されていないためオプティマイザは別の計画の選択を強制され、ベースライン計画は再現に失敗します。ベースライン計画と原価ベース計画についての計画の比較レポートは、この2つの計画にアクセス・パスの違いがあることを示しています。
-
データベースにユーザー
hr
としてログインして、PLAN TABLEを作成します。CREATE TABLE PLAN_TABLE ( STATEMENT_ID VARCHAR2(30), PLAN_ID NUMBER, TIMESTAMP DATE, REMARKS VARCHAR2(4000), OPERATION VARCHAR2(30), OPTIONS VARCHAR2(255), OBJECT_NODE VARCHAR2(128), OBJECT_OWNER VARCHAR2(30), OBJECT_NAME VARCHAR2(30), OBJECT_ALIAS VARCHAR2(65), OBJECT_INSTANCE NUMBER(38), OBJECT_TYPE VARCHAR2(30), OPTIMIZER VARCHAR2(255), SEARCH_COLUMNS NUMBER, ID NUMBER(38), PARENT_ID NUMBER(38), DEPTH NUMBER(38), POSITION NUMBER(38), COST NUMBER(38), CARDINALITY NUMBER(38), BYTES NUMBER(38), OTHER_TAG VARCHAR2(255), PARTITION_START VARCHAR2(255), PARTITION_STOP VARCHAR2(255), PARTITION_ID NUMBER(38), OTHER LONG, DISTRIBUTION VARCHAR2(30), CPU_COST NUMBER(38), IO_COST NUMBER(38), TEMP_SPACE NUMBER(38), ACCESS_PREDICATES VARCHAR2(4000), FILTER_PREDICATES VARCHAR2(4000), PROJECTION VARCHAR2(4000), TIME NUMBER(38), QBLOCK_NAME VARCHAR2(30), OTHER_XML CLOB);
-
次のDDL文を実行します。この文では、
staff
という表とstaff.employee_id
列に対する索引を作成します。CREATE TABLE staff AS (SELECT * FROM employees); CREATE UNIQUE INDEX staff_employee_id ON staff (employee_id);
-
次の文を実行して、
staff
の問合せをSQL計画管理で保護し、索引を不可視にします。ALTER SESSION SET optimizer_capture_sql_plan_baselines = TRUE; SELECT COUNT(*) FROM staff WHERE employee_id = 20; -- execute query a second time to create a baseline SELECT COUNT(*) FROM staff WHERE employee_id = 20; ALTER SESSION SET optimizer_capture_sql_plan_baselines = FALSE; ALTER INDEX staff_employee_id INVISIBLE;
-
計画を説明して、PLAN TABLEを問い合せます(出力例も示します)。
EXPLAIN PLAN SET STATEMENT_ID='STAFF' FOR SELECT COUNT(*) FROM staff WHERE employee_id = 20; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT=>'TYPICAL')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------ Plan hash value: 1778552452 ------------------------------------------------------------------------ | Id | Operation | Name |Rows |Bytes |Cost (%CPU)|Time | ------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 4 | 2 (0)|00:00:01 | | 1 | SORT AGGREGATE | | 1 | 4 | | | |* 2 | TABLE ACCESS FULL| STAFF | 1 | 4 | 2 (0)|00:00:01 | ------------------------------------------------------------------------ Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT ------------------------------------------------------------------------ 2 - filter("EMPLOYEE_ID"=20) Note ----- - dynamic statistics used: dynamic sampling (level=2) - Failed to use SQL plan baseline for this statement
前述の出力が示すように、索引が不可視になっているため、オプティマイザは全表スキャンを選択します。SQL計画ベースラインでは索引が使用されているため、オプティマイザは計画を再現できません。
-
別のセッションで、
SYS
としてログインし、SQL計画ベースラインのハンドルと計画名を問い合せます(出力例も示します)。SET LINESIZE 120 COL SQL_HANDLE FORMAT a25 COL PLAN_NAME FORMAT a35 SELECT DISTINCT SQL_HANDLE,PLAN_NAME,ACCEPTED FROM DBA_SQL_PLAN_BASELINES WHERE PARSING_SCHEMA_NAME = 'HR'; SQL_HANDLE PLAN_NAME ACC ------------------------- ----------------------------------- --- SQL_3fa3b23c5ba1bf60 SQL_PLAN_3z8xk7jdu3gv0b7aa092a YES
-
前のステップで取得したSQLハンドルと計画ベースライン名を指定して、計画を比較します。
VAR v_report CLOB BEGIN :v_report := DBMS_XPLAN.COMPARE_PLANS( reference_plan => plan_table_object('HR', 'PLAN_TABLE', 'STAFF'), compare_plan_list => plan_object_list (SPM_OBJECT('SQL_3fa3b23c5ba1bf60','SQL_PLAN_3z8xk7jdu3gv0b7aa092a')), type => 'TEXT', level => 'ALL', section => 'ALL'); END; /
-
計画の比較レポートを問い合せます(出力例も示します)。
SET LONG 1000000 SET PAGESIZE 50000 SET LINESIZE 200 SELECT :v_report rep FROM DUAL; REP ------------------------------------------------------------------------ COMPARE PLANS REPORT ------------------------------------------------------------------------ Current user : SYS Total number of plans : 2 Number of findings : 1 ------------------------------------------------------------------------ COMPARISON DETAILS ------------------------------------------------------------------------ Plan Number : 1 (Reference Plan) Plan Found : Yes Plan Source : Plan Table Plan Table Owner : HR Plan Table Name : PLAN_TABLE Statement ID : STAFF Plan ID : 72 Plan Database Version : 19.0.0.0 Parsing Schema : "HR" SQL Text : No SQL Text Plan ----------------------------- Plan Hash Value : 1766070819 -------------------------------------------------------------------- | Id | Operation | Name |Rows| Bytes | Cost | Time | -------------------------------------------------------------------- | 0| SELECT STATEMENT | | 1 | 13 | 2 | 00:00:01 | | 1| SORT AGGREGATE | | 1 | 13 | | | | * 2| TABLE ACCESS FULL | STAFF | 1 | 13 | 2 | 00:00:01 | -------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------ * 2 - filter("EMPLOYEE_ID"=20) Notes ----- - Dynamic sampling used for this statement ( level = 2 ) - baseline_repro_fail = yes -------------------------------------------------------------------- Plan Number : 2 Plan Found : Yes Plan Source : SQL Plan Baseline SQL Handle : SQL_3fa3b23c5ba1bf60 Plan Name : SQL_PLAN_3z8xk7jdu3gv0b7aa092a Plan Database Version : 19.0.0.0 Parsing Schema : "HR" SQL Text : SELECT COUNT(*) FROM staff WHERE employee_id = 20 Plan ----------------------------- Plan Hash Value : 3081373994 ------------------------------------------------------------------------ |Id| Operation | Name |Rows|Bytes |Cost |Time | ------------------------------------------------------------------------ | 0| SELECT STATEMENT | | 1 | 13 | 0 |00:00:01| | 1| SORT AGGREGATE | | 1 | 13 | | | |*2| INDEX UNIQUE SCAN | STAFF_EMPLOYEE_ID | 1 | 13 | 0 |00:00:01| ------------------------------------------------------------------------ Predicate Information (identified by operation id): ------------------------------------------ * 2 - access("EMPLOYEE_ID"=20) Comparison Results (1): ----------------------------- 1. Query block SEL$1, Alias "STAFF"@"SEL$1": Some columns (OPERATION, OPTIONS, OBJECT_NAME) do not match between the reference plan (id: 2) and the current plan (id: 2) ------------------------------------------------------------------------