実行計画の表示と解析について
“Inside the Oracle Optimizer - Removing the black magic”からの翻訳です。実行計画の表示方法について解説しています。
原文:Displaying and reading the execution plans for a SQL statement
— beginning of translation —
SQL文の実行計画を表示し解析する
SQL文の実行計画を生成し表示することは、多くのDBAやSQLを使用する開発者、またパフォーマンス分析の専門家にとって日常的な作業である。それは実行計画がSQL文のパフォーマンス特性を提供しているためである。実行計画はSQL文を実行するために必要な詳細手順を示す。これらの手順は行の処理や生成といった一連のデータベース処理として表される。処理の順序とその実行方法は、問合せオプティマイザがクエリ変換と物理的な最適化技術を組み合わせて使用し決定する。
実行計画は、一般的に表形式で表示されるが、実際の計画はツリー状になっている。例えば、SHスキーマ(Sales History)に基づく以下のクエリを考察してみよう。
select prod_category, avg(amount_sold) from sales s, products p where p.prod_id = s.prod_id group by prod_category;
このクエリの実行計画を次に示す。
------------------------------------------ Id Operation Name ------------------------------------------ 0 SELECT STATEMENT 1 HASH GROUP BY 2 HASH JOIN 3 TABLE ACCESS FULL PRODUCTS 4 PARTITION RANGE ALL 5 TABLE ACCESS FULL SALES ------------------------------------------
一方、ツリー形式の表記は次の通りである。
GROUP BY
|
JOIN
_____|_______
| |
ACCESS ACCESS
(PRODUCTS) (SALES)
計画のツリーを解析するときは下から上へと見ていく。上記の例では、アクセス方法（すなわち木の葉の部分）から見ればよい。このケースのアクセス方法には全表走査が使われている。それぞれの表走査で生成された行は結合操作で処理される。ここでの結合操作ではハッシュ結合（他にはネステットループ結合やソートマージ結合がある）が行われている。最後に、group-by操作では、結合操作で生成された行がハッシュ（他の選択肢としてはソートがある）を使用して処理されている。
1つのSQL文に対して実行計画は何通りも存在し、生成された実行計画は、その中から問合せオプティマイザによって選択された1つにすぎない。問合せオプティマイザは最もコストが低い実行計画を選択する。コストはパフォーマンスの代わりとなるもので、コストが低いほどパフォーマンスは向上する。問合せオプティマイザが使用するコストモデルは、クエリ内のIO、CPU、ネットワーク使用量で占められている。
SQL文の実行計画を調べるには2通りの方法がある。
1. EXPLAIN PLAN コマンド - このコマンドは実際にSQL文を実行せずに実行計画を表示する。
2. V$SQL_PLAN - Oracle 9iで導入されたディクショナリ・ビューであり、カーソルキャッシュのカーソルにコンパイルされたSQL文の実行計画を表示する。
特定の状況において、EXPLAIN PLANを使用した場合とV$SQL_PLANを使用した場合とで実行計画が異なることがある。例えば、SQL文にバインド変数が含まれていると、EXPLAIN PLANを使用した実行計画はバインド変数の値を無視する。一方、V$SQL_PLANでは実行計画の生成段階でバインド変数の値を考慮する。
Oracle 9iで導入され、後続リリースで強化されたdbms_xplanパッケージにより、実行計画の表示はより容易になった。このパッケージには複数のPL/SQLプロシージャが用意されており、以下に示す様々な情報をもとに実行計画を表示する。
1.EXPLAIN PLANコマンド
2.V$SQL_PLAN
3.Automatic Workload Repository (AWR)
4.SQL Tuning Set (STS)
5.SQL Plan Baseline (SPM)
以下の例では、独自にSQL文を用意し、dbms_xplanパッケージが提供するファンクションをいくつか使用しながら、実行計画の生成と表示方法について説明する。
例1 EXPLAIN PLANコマンドとdbms_xplan.displayファンクションを使用する。
SQL> EXPLAIN PLAN FOR
2 select prod_category, avg(amount_sold)
3 from sales s, products p
4 where p.prod_id = s.prod_id
5 group by prod_category;
Explained.
SQL> select plan_table_output
2 from table(dbms_xplan.display('plan_table',null,'basic'));
------------------------------------------
Id Operation Name
------------------------------------------
0 SELECT STATEMENT
1 HASH GROUP BY
2 HASH JOIN
3 TABLE ACCESS FULL PRODUCTS
4 PARTITION RANGE ALL
5 TABLE ACCESS FULL SALES
------------------------------------------
dbms_xplan.displayの引数は以下の通りである。
* plan table name (デフォルトは’PLAN_TABLE’),
* statement_id (デフォルトはnull),
* format (デフォルトは’TYPICAL’)
詳しくは、$ORACLE_HOME/rdbms/admin/dbmsxpln.sqlを参照のこと。
例2 セッションの最後に実行されたSQL文の実行計画を生成し表示する。
SQL> select prod_category, avg(amount_sold) 2 from sales s, products p 3 where p.prod_id = s.prod_id 4 group by prod_category; no rows selected SQL> select plan_table_output 2 from table(dbms_xplan.display_cursor(null,null,'basic')); ------------------------------------------ Id Operation Name ------------------------------------------ 0 SELECT STATEMENT 1 HASH GROUP BY 2 HASH JOIN 3 TABLE ACCESS FULL PRODUCTS 4 PARTITION RANGE ALL 5 TABLE ACCESS FULL SALES ------------------------------------------
dbms_xplay.display_cursorの引数は以下の通りである。
* SQL ID (デフォルトはnull。nullの場合、セッションで最後に実行されたSQL文が表示される),
* child number (デフォルトは0),
* format (デフォルトは’TYPICAL’)
詳細は$ORACLE_HOME/rdbms/admin/dbmsxpln.sqlを参照のこと。
例3 SQL IDを直接または間接的に指定し、他のSQL文の実行計画を表示する。
1.直接指定する場合
SQL> select plan_table_output from
2 table(dbms_xplan.display_cursor('fnrtqw9c233tt',null,'basic'));
2.間接的に指定する場合
SQL> select plan_table_output 2 from v$sql s, 3 table(dbms_xplan.display_cursor(s.sql_id, 4 s.child_number, 'basic')) t 5 where s.sql_text like 'select PROD_CATEGORY%';
例4 SQL計画ベースラインに一致する実行計画を表示する。
SQL計画ベースラインは、SQL Plan Management 機能(SPM)をサポートするためOracle 11gで導入された。このケースを説明するには、始めにSQL計画ベースラインを作成する必要がある。
SQL> alter session set optimizer_capture_sql_plan_baselines=true; Session altered. SQL> select prod_category, avg(amount_sold) 2 from sales s, products p 3 where p.prod_id = s.prod_id 4 group by prod_category; no rows selected
上記の文が2回以上実行されている場合、文に対するSQL計画ベースラインが作成され、次のクエリを使用して検証できる。
SQL> select SQL_HANDLE, PLAN_NAME, ACCEPTED 2 from dba_sql_plan_baselines 3 where sql_text like 'select prod_category%'; SQL_HANDLE PLAN_NAME ACC ------------------------------ ------------------------------ --- SYS_SQL_1899bb9331ed7772 SYS_SQL_PLAN_31ed7772f2c7a4c2 YES
上記で作成されたSQL計画ベースラインの実行計画は、直接または間接的に表示できる。
1.直接表示する場合
select t.* from
table(dbms_xplan.display_sql_plan_baseline('SYS_SQL_1899bb9331ed7772',
format => 'basic')) t
2.間接的に表示する場合
select t.*
from (select distinct sql_handle
from dba_sql_plan_baselines
where sql_text like 'select prod_category%') pb,
table(dbms_xplan.display_sql_plan_baseline(pb.sql_handle,
null,'basic')) t;
上記2つの文の実行計画はどちらも以下の通りである。
----------------------------------------------------------------------------
SQL handle: SYS_SQL_1899bb9331ed7772
SQL text: select prod_category, avg(amount_sold) from sales s, products p
where p.prod_id = s.prod_id group by prod_category
----------------------------------------------------------------------------
----------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_31ed7772f2c7a4c2
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
----------------------------------------------------------------------------
Plan hash value: 4073170114
---------------------------------------------------------
Id Operation Name
---------------------------------------------------------
0 SELECT STATEMENT
1 HASH GROUP BY
2 HASH JOIN
3 VIEW index$_join$_002
4 HASH JOIN
5 INDEX FAST FULL SCAN PRODUCTS_PK
6 INDEX FAST FULL SCAN PRODUCTS_PROD_CAT_IX
7 PARTITION RANGE ALL
8 TABLE ACCESS FULL SALES
---------------------------------------------------------
書式設定
format引数はカスタマイズが容易なため、実行計画の詳細レベルを必要に応じて高レベルにも低レベルに設定し出力できる。高レベルオプションは以下の通りである。
1.Basic
計画には、オペレーション、オプション、オブジェクト名（表、索引、マテリアライズド・ビューなど）が含まれる。
2.Typical
BASICで表示される情報に加えて、コスト、サイズ、カーディナリティなどオプティマイザに関連する内部情報も含まれる。
これらの情報は計画内の各操作に対して表示され、オプティマイザが処理コストや生成される行数などを考慮しているということを示している。また、操作による述語評価も表示される。述語にはACCESSとFILTERの2種類がある。索引用の述語ACCESSは検索対象の列に該当するブロックをフェッチするために使用される。述語FILTERはブロックがフェッチされた後に評価される。
3.All
TYPICALで表示される情報に加え、各操作によって生成される表現（列）、Object Alias（表またはビューの別名）やQuery Block Name（問合せブロックの名前）などその操作が属するのリストを表示する。最後の2つの情報は文にヒントを付加するための引数として使用される。
低レベルオプションを指定することにより、述語情報やコストなど、詳細項目を表示または除外することができる。以下に例を示す。
select plan_table_output
from table(dbms_xplan.display('plan_table',null,'basic +predicate +cost'));
-------------------------------------------------------
Id Operation Name Cost (%CPU)
-------------------------------------------------------
0 SELECT STATEMENT 17 (18)
1 HASH GROUP BY 17 (18)
* 2 HASH JOIN 15 (7)
3 TABLE ACCESS FULL PRODUCTS 9 (0)
4 PARTITION RANGE ALL 5 (0)
5 TABLE ACCESS FULL SALES 5 (0)
-------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P"."PROD_ID"="S"."PROD_ID")
select plan_table_output from
table(dbms_xplan.display('plan_table',null,'typical -cost -bytes'));
----------------------------------------------------------------------------
Id Operation Name Rows Time Pstart Pstop
----------------------------------------------------------------------------
0 SELECT STATEMENT 4 00:00:01
1 HASH GROUP BY 4 00:00:01
* 2 HASH JOIN 960 00:00:01
3 TABLE ACCESS FULL PRODUCTS 766 00:00:01
4 PARTITION RANGE ALL 960 00:00:01 1 16
5 TABLE ACCESS FULL SALES 960 00:00:01 1 16
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P"."PROD_ID"="S"."PROD_ID")
Noteセクション
dbms_xplanパッケージは、計画の表示以外にクエリの最適化段階で動的サンプリングの有無やクエリへスター変換がされたか、などといった注釈をNoteセクションに表示する。例えば、表SALESに統計情報がない場合、オプティマイザは動的サンプリングを使用し、計画には以下のように表示される（クエリの’+note’部分を参照）。
select plan_table_output
from table(dbms_xplan.display('plan_table',null,'basic +note'));
------------------------------------------
Id Operation Name
------------------------------------------
0 SELECT STATEMENT
1 HASH GROUP BY
2 HASH JOIN
3 TABLE ACCESS FULL PRODUCTS
4 PARTITION RANGE ALL
5 TABLE ACCESS FULL SALES
------------------------------------------
Note
-----
- dynamic sampling used for this statement
バインドピーク
問合せオプティマイザは、実行計画を生成する際にバインド変数の値を考慮し、一般に「バインドピーク」と呼ばれる処理を行う。本ブログの最初の投稿を見てほしい。そこで、バインドピークの概念、およびバインドピークが実行計画とSQL文のパフォーマンスに与える影響について述べている。先にも述べたとおり、V$SQL_PLANで表示される計画はバインド変数の値を考慮するが、EXPLAIN PLANを使用した場合は考慮されない。10gR2からは、dbms_xplanパッケージにより特定のカーソルまたは計画を生成する際に使用したバインド変数の値を表示できるようになった。これは、display_cursor()プロシージャを使用し、書式設定引数に「+peeked_binds」を付加することで行われる。以下でこの例を説明する。
variable pcat varchar2(50) exec :pcat := 'Women' select PROD_CATEGORY, avg(amount_sold) from sales s, products p where p.PROD_ID = s.PROD_ID and prod_category != :pcat group by PROD_CATEGORY; select plan_table_output from table(dbms_xplan.display_cursor(null,null,'basic +PEEKED_BINDS')); ------------------------------------------ Id Operation Name ------------------------------------------ 0 SELECT STATEMENT 1 HASH GROUP BY 2 HASH JOIN 3 TABLE ACCESS FULL PRODUCTS 4 PARTITION RANGE ALL 5 TABLE ACCESS FULL SALES ------------------------------------------ Peeked Binds (identified by position): -------------------------------------- 1 - :PCAT (VARCHAR2(30), CSID=2): 'Women'