Oracle SQL実行計画を確認し、パフォーマンスを高めよう

2017.11.16 株式会社システムインテグレータ

SQLを扱う場合に必ず意識しなければいけないものが「実行計画」です。
SQLの性能改善を計画しチューニングを行う場合などは、実行計画を理解しておくことがとても重要になります。今回はOracleを取り上げ、実行計画の説明から、その扱い方まで詳しく見ていきます。

実行計画とは

OracleがSQLを実行するために使用する手順のことを、実行計画と言います。
実行計画が不適切だと、SQLの実行時に正しい手順が踏めず、パフォーマンスの低下などを引き起こします。
そのため、実行計画を確認することは、SQLを扱うシステムにとって重要なタスクになります。

SQLが実行されるときの仕組み

SQLが実行されると、Oracleでは大きく分けて2つの動作をします。
(1)実行されたSQLをチェックして書き換える
(2)実行計画を作成する
それぞれの動作について詳しく見ていきます。

(1)実行されたSQLをチェックして書き換える
実行されたSQLについて以下のようなチェックを行います。
・SQLの内容が文法的に正しいか
・指定したテーブルやカラムが存在するか
・実行ユーザーに必要な権限があるか
・結合などが含まれている場合はどのように処理されるか
また、チェックしたSQLはより高速に実行できる内容に書き換えます。
演算の種類を変更するなど、より早く実行できるようなSQLに書き換え、データベース内部に保持します。

(2)実行計画を作成する
SQLのチェックが終わると、実行計画の作成を行っていきます。
テーブルのデータを参照する方法をとっても、インデックスを使う方法と使わない方法があり、結合する方法も結合順序などに様々な方法が考えられます。
このように、実行する手順を何通りも作成し、その中から最も効率的なものを選択します。
そうして出来上がった手順が「実行計画」ということになります。

実行計画で確認できる情報

実行計画では主に以下の情報を確認することができます。
(1)SQLの識別子
(2)実行するSQL
(3)実行計画のハッシュ値
(4)手順
(5)各手順の統計値
(6)その他情報

実行計画を確認する方法は多岐にわたり、その方法によっては確認できない情報もあるので注意が必要です。
では、実際にSI Object Browser for Oracleを利用して確認してみます。

Oracle SQL実行計画を確認し、パフォーマンスを高めよう 1

図1.実行計画の確認(SI Object Browser for Oracle)

確認方法は、SQL実行画面で実行計画モードに変更し、確認したいSQLを入力して実行するだけです。
SI Object Browser for Oracleでは、図1のとおりユーザー・セッションについての統計情報も確認することも可能です。

統計情報とは

データの特性を表す情報(テーブル、インデックス、表領域、データ種別、データ分布など)のことを統計情報と言います。
統計情報は実行計画を作成する際に利用されるため、とても重要な情報になります。

統計情報に含まれる内容は以下の通りです。
(1)テーブルの情報(行数、ブロック数、平均行数)
(2)カラムの情報(個数、種類、NULL数、データ分布)
(3)インデックスの情報(リーフブロック数、階層数、クラスタ化係数)

なお、統計情報はディクショナリ・ビューから確認することが可能です。
各統計情報と確認できるビューは以下の通りです。
(1) 表の統計情報： DBA_TABLES
(2) 索引の統計情報： DBA_INDEXES
(3) 列の統計情報： DBA_TAB_COLUMNS

また、統計情報の取得方法は以下の通りです。
(1) 自動取得：Oracleが定期的に自動で統計情報を取得します。
更新が行われたテーブルの統計情報のみ再取得しています。
(2) 手動取得：ユーザーが任意のタイミングで統計情報を取得します。
取得例：ANALYZE TABLE (テーブル名) COMPUTE STATISTICS
(3) 動的統計：オプティマイザ統計が存在しない場合など、テーブルの統計情報が利用できない場合に、サンプルデータを取得、利用し適切な実行計画を作成するための方法です。

実行計画と統計情報とデータベースの整合性

前述しましたが、実行計画は統計情報をもとに作成されます。
テーブルやカラムの統計情報を利用することで、アクセス効率の良い実行計画を作成することが可能となるためです。

また、統計情報はデータベースの実際の情報が反映されています。
SQLをより早く実行するためには、実行計画と統計情報、データベースの整合性を取らなければなりません。
整合性が取れていないと、SQLの実行速度が遅くなってしまう場合があります。

Oracle SQL実行計画を確認し、パフォーマンスを高めよう 2
図2.SQL実行時における統計情報と実行計画の関係

実行計画を作成して表示する

実行計画の確認方法は様々なものがありますが、主に使われるものを2つ紹介します。

(1) EXPLAIN PLAN文→DBMS_XPLAN.DISPLAYを実行する
この方法は、実際にSQLを実行しないため簡単に確認することが可能です。
その代わり、実行計画の内容がOracleの見積もった値となり、実際に実行した時と差異が発生する可能性があるので注意が必要です。

(2) SQLを実行した後にDBMS_XPLAN.DISPLAY_CURSORを実行する
この方法は、実際にSQLを実行しなければいけないものの、実行計画の内容は実測値を確認することが可能です。
また、事前に初期化パラメータSTATISTICS_LEVEL=ALLを設定し、DBMS_XPLAN.DISPLAY_CURSORの引数formatに'ALLSTATS LAST'を指定することで、実行計画だけでなく統計値も実測値を確認できるようになります。

ステップ・オペレーション

実行計画では、「ステップ」と「オペレーション」を確認することができます。
(1) ステップ：実行手順における要素で親子関係がある(ツリーになる)
(2) オペレーション：ステップで実行されている操作の種別(TABLE ACCESS FULLなど)

Oracle SQL実行計画を確認し、パフォーマンスを高めよう 3

図3.ステップとオペレーション(SI Object Browser for Oracle)

オペレーションについて代表的なものを３つ紹介します。

(1) TABLE ACCESS FULL
TABLE ACCESS FULLは、単純にテーブル全体を読み出す方法です。
他の方法よりも余計な処理が発生しないため、テーブル全体のデータが少量の場合はこの方法が有効な場合があります。

(2) INDEX UNIQUE SCAN
INDEX UNIQUE SCANは、一意制約または主キー制約が設定されたカラムに対するインデックスを使う方法です。
この制約が設定されているカラムを、SQLの条件式にしていた場合に選択されることがあります。
ユニークな列のインデックスを利用するため、データの量によっては非常に有効な方法となります。

(3) INDEX RANGE SCAN
INDEX RANGE SCANは、一意制約または主キー制約が設定されていないカラムに対するインデックスを使う方法です。
SQLの条件式で複数件のデータを取得する場合など、任意のインデックスを設定することでこの方法を活用することができます。

PLAN_TABLE出力の表示について

PLAN_TABLEは、EXPLAIN PLAN文で確認した実行計画の保存に利用できる一時テーブルです。
このテーブルはユーザー毎に自動的に作成されていますが、標準で提供されているSQLスクリプトを使用して手動で作成することも可能です。

PLAN_TABLEの内容はスクリプトまたはパッケージを使用して表示することができます。
(前述したDBMS_XPLAN.DISPLAYを実行して確認する方法は、実際にはPLAN_TABLEを利用しています。)

実行計画の確認例

今度はSQL*PLUSを利用し、実際に実行計画を確認してみます。
まずはEXPLAIN PLAN文→DBMS_XPLAN.DISPLAYを実行する方法で見ていきます。

SQL> EXPLAIN PLAN FOR SELECT CUST_CODE FROM CUSTOMERS_MST WHERE CUST_CODE = '00000001'; (※1)
解析されました。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY()); (※2)

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 1789900194

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| PK_CUSTOMERS_MST | 1 | 10 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
-------------------------------------------------------------------------------------
1 - access("CUST_CODE"=U'00000001')

13行が選択されました。

SQL>

※1 EXPLAIN PLAN文を実行している個所になります。
FOR句の後に実行計画を取得したいSQLを指定します。
※2 DBMS_XPLAN.DISPLAYを実行している個所になります。
今回は引数なしで実行したので、直前に実行したEXPLAIN PLAN文が対象となっています。

では次に、SQLを実行した後にDBMS_XPLAN.DISPLAY_CURSORを実行する方法で見ていきます。

SQL> SELECT CUST_CODE FROM CUSTOMERS_MST WHERE CUST_CODE = '00000001'; (※1)

CUST_CODE
----------------
00000001

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR()); (※2)

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID cx0r1jcrh0s9f, child number 0
-------------------------------------
SELECT CUST_CODE FROM CUSTOMERS_MST WHERE CUST_CODE = '00000001'

Plan hash value: 1789900194

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX RANGE SCAN| PK_CUSTOMERS_MST | 1 | 10 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("CUST_CODE"=U'00000001')

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

22行が選択されました。

SQL>

※1 対象のSQLを実行している個所になります。
※2 DBMS_XPLAN.DISPLAY_CURSORを実行している個所になります。
今回は簡単なSQLなので大きな差異はありませんが、出力された内容は実測値となっています。

PLAN_TABLE出力のカスタマイズ

PLAN_TABLE出力の内容は、独自に成形することも可能です。

(1) START WITH ID = 0 AND STATEMENT_ID
確認したいSQLのSTATEMENT_IDを指定できます。
(デフォルトでは直近に確認したSQLの実行計画が表示されます。)

(2) CONNECT BY句
STATEMENT_ID = PRIOR STATMENT_ID、PARENT_ID = PRIOR IDを結合キーとして、
親から子へツリーを移動して参照できるようになります。

(3) 疑似列LEVEL(CONNECT BYに関連付けられている)
子をインデントして表示します。

出力のカスタマイズを行った例を見ていきます。

SQL> EXPLAIN PLAN SET STATEMENT_ID = 'test' FOR SELECT CUST_CODE FROM CUSTOMERS_MST WHERE CUST_CODE = '00000001'; (※1)

解析されました。

SQL> SELECT cardinality "Rows", lpad(' ',level-1) || operation
2 ||' '||options||' '||object_name "Plan"
3 FROM PLAN_TABLE
4 CONNECT BY prior id = parent_id
5 AND prior statement_id = statement_id
6 START WITH id = 0
7 AND statement_id = 'test'
8 ORDER BY id; (※2)

Rows Plan
------- ----------------------------------------
1 SELECT STATEMENT
1 INDEX RANGE SCAN PK_CUSTOMERS_MST

SQL>

※1 EXPLAIN PLAN文を実行している個所になります。
SET STATEMENT_ID を指定し、その後の出力時に利用する識別子を付けます。
※2 PLAN_TABLEを参照している個所になります。
　statement_idを指定し、さらに出力内容をカスタマイズしています。
　(出力結果の親子関係にある2行目がインデントして出力されています。)

実行計画の比較

Oracle Database 19cから提供している実行計画の比較ツール(DBMS_XPLAN.COMPARE_PLANS API)では、参照した実行計画と任意の実行計画のリストを受け取り、双方の相違点を確認することができます。

実行計画比較の目的

ツールから出力される比較レポートで、実行計画の相違の原因を特定することができます。
比較レポートの活用事例は以下の通りです。

(1) パフォーマンスが低下しているSQLに対し、現在の計画と統計情報リポジトリで取得した古い計画を比較する
(2) 当初意図していた実行計画の再現に失敗し、新しい実行計画と意図していた実行計画の相違点を判断する
(3) ヒント句の追加、パラメータの変更、索引の作成が実行計画に与える影響を判断する
(4) SQLプロファイルに基づいて生成された実行計画、SQLパフォーマンス・アナライザによって生成された実行計画が、当初の実行計画と異なる点を判断する

実行計画比較の出力形式

DBMS_XPLAN.COMPARE_PLANSを使用し、テキスト、XML、HTML形式でレポートを作成することができます。比較レポートの形式は以下の通りです。

(1) COMPARE PLANS REPORTセクション
COMPARE PLANS REPORTセクションには、レポートを実行したユーザーや比較した実行計画の数などが出力されます。

COMPARE PLANS REPORT
---------------------------------------------------------------------------------------------
Current user : TEST
Total number of plans : 2
Number of findings : 1
---------------------------------------------------------------------------------------------

(2) COMPARISON DETAILSセクション
COMPARISON DETAILSセクションには、実行計画の情報(実行計画の番号、実行計画のソース、実行計画の属性、解析スキーマ、SQLテキスト)や、比較結果などが出力されます。
比較結果では、結合順序、結合方法、アクセスパス、パラレル配分方法などの論理的な相違点が確認できます。

Comparison Results (1):
-----------------------------
1. 問合せブロックSEL$1, Alias "CUSTOMERS_MST"@"SEL$1": 操作INDEXが異なる方法で行われました - 参照プラン:
RANGE SCAN (line: 1)、現在のプラン: FULL SCAN (line: 1)。

実行計画比較の実行例

条件式の異なるSQLを2つ実行し、比較を行ってみます。
まずはSQLを2つ実行します。(条件式を変えています。)

SQL> SELECT CUST_CODE FROM CUSTOMERS_MST WHERE CUST_CODE = '00000001';

CUST_CODE
----------------
00000001

SQL> SELECT CUST_CODE FROM CUSTOMERS_MST WHERE CUST_SUB_NO = '1';

CUST_CODE
----------------
00000001
SQL>

次にV$SQLにてSQL_IDを取得します。

SQL> SELECT SQL_ID FROM V$SQL WHERE SQL_TEXT like '%SELECT CUST_CODE FROM CUSTOMERS_MST%';

SQL_ID
--------------------------
cx0r1jcrh0s9f
96prr5dvvpy0d

SQL>

取得したSQL_IDをもとにDBMS_XPLAN.COMPARE_PLANSを実行します。

SQL> BEGIN
2 :v_rep := DBMS_XPLAN.COMPARE_PLANS(
3 reference_plan => cursor_cache_object('cx0r1jcrh0s9f', NULL),
4 compare_plan_list => plan_object_list(cursor_cache_object('96prr5dvvpy0d', NULL)),
5 type => 'TEXT',
6 level => 'TYPICAL',
7 section => 'ALL');
8 END;
9 /

PL/SQLプロシージャが正常に完了しました。

SQL>

最後に比較レポートを出力します。

SQL> SET PAGESIZE 50000
SQL> SET LONG 100000
SQL> SET LINESIZE 210
SQL> COLUMN report FORMAT a200
SQL> SELECT :v_rep REPORT FROM DUAL;

REPORT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

COMPARE PLANS REPORT
---------------------------------------------------------------------------------------------
Current user : TEST
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 : cx0r1jcrh0s9f
Child Number : 0
Plan Database Version : 21.0.0.0
Parsing Schema : "TEST"
SQL Text : SELECT CUST_CODE FROM CUSTOMERS_MST WHERE CUST_CODE =
'00000001'

Plan
-----------------------------

Plan Hash Value : 1789900194

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 | |
| * 1 | INDEX RANGE SCAN | PK_CUSTOMERS_MST | 1 | 10 | 1 | 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("CUST_CODE"=U'00000001')

Notes
-----
- Dynamic sampling used for this statement ( level = 2 )

---------------------------------------------------------------------------------------------
Plan Number : 2
Plan Found : Yes
Plan Source : Cursor Cache
SQL ID : 96prr5dvvpy0d
Child Number : 0
Plan Database Version : 21.0.0.0
Parsing Schema : "TEST"
SQL Text : SELECT CUST_CODE FROM CUSTOMERS_MST WHERE CUST_SUB_NO
= '1'

Plan
-----------------------------

Plan Hash Value : 471938215

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 | |
| * 1 | INDEX FULL SCAN | PK_CUSTOMERS_MST | 1 | 23 | 1 | 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("CUST_SUB_NO"=1)
* 1 - filter("CUST_SUB_NO"=1)

Notes
-----
- Dynamic sampling used for this statement ( level = 2 )

Comparison Results (1):
-----------------------------
1. 問合せブロックSEL$1, Alias "CUSTOMERS_MST"@"SEL$1": 操作INDEXが異なる方法で行われました - 参照プラン:
RANGE SCAN (line: 1)、現在のプラン: FULL SCAN (line: 1)。

---------------------------------------------------------------------------------------------

SQL>

Comparison Resultsには、異なる方法で実行された旨がしっかりと出力されていました。
実行されたSQLに対して比較を行うことができるので、意図しないパフォーマンス低下を起こしたSQLを調査する場合にも、有効に活用できます。

SQL計画管理(SQL Plan Management)

Oracleは統計情報をもとに最適な実行計画を予測で生成するため、実行者にとっては最適とはならない可能性があります。そのため、予期していない実行計画の変更によるパフォーマンス低下を避けるため、実行計画を固定する方法として、SQL計画管理があります。

SQL計画管理を利用するメリット

SQL計画管理を利用することで以下のメリットを享受できます。

(1) システムの変更による影響を抑えることができる
システムの変更によりSQLの実行計画に影響が出ると、パフォーマンスが低下する可能性があります。SQL計画管理を利用することで、実行計画が固定され安定したパフォーマンスを維持できます。

(2) 特定のSQLが利用する実行計画のみを変更することができる
統計情報の更新により一部SQLのパフォーマンスが向上しても、その他SQLのパフォーマンスが低下する可能性があります。SQL計画管理を利用することで、他のSQLに影響を出さずにパフォーマンスチューニングを行うことが可能です。

(3) 統計情報の自動収集ができる
統計情報を自動収集すると、そのたびに実行計画が変更される可能性があります。SQL計画管理を利用することで、実行計画の変更が必要ないSQLと、最新の統計情報を必要とするSQLを併用することが可能です。

(4) オプティマイザをバージョンアップした際に発生する実行計画の変更に耐えられる
オプティマイザのバージョンアップにより実行計画も変更される可能性があります。SQL計画管理を利用することで、既存SQLの影響を意識することなく、オプティマイザを最新の状態に保つことができます。

計画の取得

計画の取得とは、内部に保存された実行計画をSQL計画管理に認識させることです。
DBMS_SPMパッケージを使用して計画を手動で取得できますが、初期化パラメータを設定することにより、自動的に初期計画の取得をするよう設定できます。

計画の選択

計画の選択とは、内部に保存された実行計画の履歴に基づいて、実行計画の変更を検出するオプティマイザの機能です。また、パフォーマンスが低下しないように、適切な計画を選択するSQL計画ベースラインを使用することです。

計画の展開

計画の展開とは、オプティマイザが新しい計画を検証し、SQL計画ベースラインに追加するための処理です。
計画の展開は次の個別のステップで構成されます。
(1) SQL計画ベースラインに含まれる承認済の計画と、同じパフォーマンスかどうかを検証します。
(2) 承認済の計画と同じパフォーマンスであることが証明された場合、承認されていない計画を承認済の計画として計画ベースラインに追加します。

まとめ

SQLを扱うシステムの開発では、切り離すことのできない実行計画について説明してきました。
実行計画の理解は、SQLのパフォーマンスチューニングをする際には必須となります。
また、適切なSQLを発行するためにも必要な知識になります。
データベースを利用するだけでは、なかなか詳細が見えず意識がしづらい部分ですが、とても重要な要素になるので、ぜひ理解を深めてシステム開発に役立ててください。