Oracle/実行計画 - ナレッジ管理

Oracle/実行計画 †

Oracle/実行計画

実行計画の見方 †

------------------------------------------------------
| Id  | Operation                        | Name      |　実行順序
------------------------------------------------------　
|   0 | SELECT STATEMENT                 |           |　8　SELECT文の結果として出力
|   1 |   MERGE JOIN CARTESIAN           |           |　7　5 6で作成した表をCROSS結合
|   2 |     NESTED LOOPS                 |           |　5　2 3で作成した表を NESTED LOOPSで結合
|   3 |       TABLE ACCESS BY INDEX ROWID| DEPT      |　2　1 で絞り込んだ DEPT表 の行を取得する
|*  4 |         INDEX UNIQUE SCAN        | PK_DEPT   |　1　PK_DEPT索引 で絞り込む
|*  5 |       TABLE ACCESS FULL          | EMPLOYEES |　3　EMPLOYEES表 を FULLスキャン
|   6 |     BUFFER SORT                  |           |　6　4 で取得したテーブルをソート？
|*  7 |       TABLE ACCESS FULL          | EMP       |　4　EMP表 をFULLスキャン
------------------------------------------------------

インデントが深いものから実行される

同じ階層のものは上位にあるものから実行される

*が付与してるのはフィルタ

↑

実行計画の取得 †

直前に実行されたSQL文の実行計画取得

set serveroutput off
set line 10000 pages 0

-- 実際の行数や時間項目を出力するようにする（V$SQL_PLAN_STATISTICSビューに統計情報を出力）
alter session set statistics_level=all;

-- 実行計画を取得したいSQLを実施
select 1 from dual;

-- 直前に実施された実行計画の情報を出力
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>'ALL ALLSTATS LAST'));

format指定	説明
IOSTATS	I/Oに関する実行統計を表示
MEMSTATS	メモリに関する実行統計を表示
ALLSTATS	IOSTATSとMEMSTATSの両方を指定
LAST	最後に実行したSQLを表示。指定しない場合は同一SQL文の全実行を集計表示

カーソルキャッシュから実行計画を取得

-- v$sql などから sqlid と child_number を取得して実行（child_number省略すると 0 になる）
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('SQLID', CHILD_NUMBER, 'ALL ALLSTATS LAST'));

-- 上記SQLを一括で作成する場合
select distinct 'SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(''' || sql_id || ''',' || child_number || ',''' || 'ALL ALLSTATS LAST''));' from GV$SQL;

(実行例) 
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('520mkxqpf15q8', 0, 'ALL ALLSTATS LAST'));
SQL_ID  520mkxqpf15q8, child number 0
-------------------------------------
select 1 from dual

Plan hash value: 1388734953
-------------------------------------------------------------------------------------------------
| Id  | Operation        | Name | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |      1 |        |     2 (100)|          |      1 |00:00:00.01 |
|   1 |  FAST DUAL       |      |      1 |      1 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |
-------------------------------------------------------------------------------------------------

AWRからの実行計画取得

SELECT PLAN_TABLE_OUTPUT FROM table(DBMS_XPLAN.DISPLAY_AWR('SQL_ID',null,null,'ALL ALLSTATS LAST'));

AWRスナップショットに含まれるSQL文から実行計画を取得する。
AWRのSQLはtopnsqlの範囲内のみ取得する。MODIFY_SNAPSHOT_SETTINGプロシージャで、topnsqlの変更が可能。

EXPLAIN PLAN文

EXPLAIN PLAN FOR <任意のSQL文>
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

↑

出力結果 †

見積もり	説明
E-Rows	見積もり件数
E-Time	見積もり時間
OMem	Optimalで実行するために必要と見積もったメモリ・サイズ
1Mem	1-passで実行するために必要と見積もったメモリ・サイズ
E-Temp	一時時領域の見積もり使用量
実行統計	説明
Starts	そのステップが実行された回数
A-Rows	処理行数
A-Time	実行時間
Buffers	バッファ・アクセス数
Reads	ディスクから読み込まれたブロック数
Writes	ディスクに書き出されたブロック数
Used-Mem	使用したメモリ・サイズと処理方法(0:Optimal　1:1-pass　>1:Multi-pass)
O/1/M	Optimal、1-pass、Multi-passでそれぞれ実行された回数
Used-Tmp	使用した一時セグメントのサイズ
Max-Tmp	使用した一時セグメントの最大サイズ

※ サイズの単位はKbyte（Kの表示がなくても）
※ Optimalはメモリ内で全ての作業を終えること
　 1-passは中間データを一段階ディスクに退避してマージ
　 Multi-passは中間データを二段階以上ディスクに退避してマージ

↑

AUTOTRACE †

実行計画や、実行統計を取得する。ただしDDLは取得できない。DDLの実行計画を取得するには、EXPLAIN PLAN FOR を使う。
実行統計を出力するには、PLUSTRACEロールが必要（plustrce.sqlの実行）

SET AUTOTRACE { OFF | ON | TRACEONLY } [EXPLAIN] [STATISTICS]

オプション	説明
EXP[LAIN]	実行計画のみレポート出力する
STAT[ISTICS]	実行統計のみレポート出力する
TRACE[ONLY]	データをフェッチするが、結果を出力せずに実行計画、実行統計をレポート出力する

実行統計	説明
recursive calls	SQL実行時に内部で発行されたリカーシブコール数
db block gets	DML等を発行したときなどに発生するカレントモードで読み込まれたブロック数
consistent gets	SELECTを発行したときなどに発生する読み取り一貫性モードで読み込まれたブロック数
physical reads	ディスクアクセスによって読み込まれたブロック数
redo size	REDOログに書き込まれたサイズ（byte）
bytes sent via SQL*Net to client	クライアントへ送られた合計byte数
bytes received via SQL*Net from client	クライアントから受信した合計byte数
SQL*Net roundtrips to/from client	クライアントに送受信されたNetメッセージの合計数
sorts (memory)	メモリソート回数
sorts (disk)	ディスクソート回数
rows processed	SQLが処理した件数

↑

アクセスパス †

アクセスパス	①	②	③	④	⑤	説明
TABLE ACCESS FULL	○	×	×	×	○	表を構成するすべてのブロックにアクセス（表の大部分にアクセスする時に最適）
INDEX RANGE SCAN	×	○	○	○	×	索引に対して範囲検索を行う（表の10%以下のデータの場合に最適）
INDEX UNIQUE SCAN	×	○	○	○	×	一意索引に対して1行文のデータのROWIDを取得
TABLE ACCESS BY INDEX ROWID	×	○	○	○	×	INDEX RANGE SCANやINDEX UNIQUE SCANによって取得された対象行のROWIDを使用し、該当表にアクセスしデータ取得を行う必ずINDEX SCANとセットで出力される
INDEX FULL SCAN	×	○	○	○	×	索引を全て読み込んでデータ取得する。索引サクセスだけで必要なデータが取得できる場合に選択される（INDEX FULL SCAN後は表にアクセスすることはない）
INDEX FAST FULL SCAN	○	×	×	×	○	INDEX FULL SCANと同じだが、1回の読み込みで複数の索引ブロックを読み込む

① 一度の読み込みで、db_file_multiblock_read_count初期化パラメータで指定された数の連続したブロックをまとめて読み込む

② 一度の読み込みで、1つのブロックを読み込む

③ ディスクから読み込んだブロックをすべてバッファキャッシュに乗せる

④ ソートされた状態でデータを取得できる

⑤ パラレル実行ができる

↑

結合アルゴリズム †

結合	説明
NESTED LOOPS	結合対象となる表の抽出レコードが非常に少ない時に効果的（内部表へのアクセスをINDEXで絞り込めない場合は、クロス結合と同じになる）内部表のレコード絞り込みを如何に少なくするかが重要。またメモリ使用率が少ない否定条件（<>、!=）が利用できる唯一のアルゴリズム
HASH JOIN	結合対象となる表の抽出レコードが非常に多い場合に有効（フルスキャン同士の結合など）等価条件のみで使用可能。またパラレルクエリで結合対象表を読み込める小さい表の結合キーをハッシュ関数を使用しハッシュ値に変換。もう一方の大きいテーブルをスキャンし結合キーがそのハッシュに存在するか調べるハッシュ表はPGAに保存する為、小さい表をハッシュ表にした方がメモリ使用量が抑えられる（メモリ不足の場合、DISKに作成しパフォーマンス劣化する）
MERGE JOIN SORT JOIN	結合対象となる表が、結合対象列でソートし結合する。不等号を使った結合も利用可能（否定(!=)は不可）テーブルをソートする為、片方のテーブルを全てスキャンしたところで結合を終了できる。ソートのコストが高い為、ソートをスキップできるケースでは有効となり得る（大概NESTED LOOP か HASHを使う）
MERGE JOIN CARTESIAN	結合条件が不十分な時に選択される直積結合

↑

NESTED LOOPS †

select /*+ USE_NL (e d) */ e.fname, d.product from emp e, dept d
where e.lname = '川澄' and e.id = d.id

| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     2 |   114 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |           |       |       |            |          |
|   2 |   NESTED LOOPS               |           |     2 |   114 |     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | EMP       |     3 |    90 |     3   (0)| 00:00:01 |　★駆動表（FULLSCAN）
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPTID |     1 |       |     0   (0)| 00:00:01 |　★内部表へは結合キーのINDEXでアクセス
|*  5 |   TABLE ACCESS BY INDEX ROWID| DEPT      |     1 |    27 |     1   (0)| 00:00:01 |

↑

NESTED LOOPS SEMI †

EXISTS句または IN句を使用した場合のみ出現する。
結果には駆動表となるテーブルのデータしか含まれず、1行につき必ず1行しか結果が生成されない（通常の結合の場合、1対Nの結合の場合は行数が増えることがある）
内部表にマッチする行を1行でも発見した時点で残りの行の検索を打ち切れるため、通常の結合よりもパフォーマンスが良い。
下記は同じ結果になるが、後者のSQLは NESTED LOOPS SEMI を使用する。

select d.id, d.product from DEPT d inner join EMP e on d.id=e.id
select * from DEPT d where exists ( select * from EMP e where d.id=e.id)

↑

NESTED LOOPS ANTI †

NOT EXISTS句または NOT IN句を使用した場合のみ出現する。
内部表にマッチする行を1行でも発見した時点で残りの行の検索を打ち切るところまで同じ動作。
その行については駆動表の行を結果から除外する。

↑

HASH JOIN †

select /*+ USE_HASH (e d) */ e.fname, d.product from emp e, dept d
where e.lname = '川澄' and e.id = d.id

| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     2 |   114 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |       |     2 |   114 |     7  (15)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMP   |     2 |    54 |     3   (0)| 00:00:01 |　★EMPの結合キーでハッシュ表作成
|   3 |   TABLE ACCESS FULL| DEPT  |     3 |    90 |     3   (0)| 00:00:01 |　★ハッシュ値にマッチする結合キーがあるかチェック

↑

HINT句 †

/*+ HINT句 */

HINT句	説明
INDEX_FFS(表名 Pkey)	複数の索引ブロックをパラレル処理にて読み込む（順序が保証されない為、プライマリキー指定可能な表の count() を取得する場合などに利用する） select /+ INDEX_FFS(表名 PKey) / count() from 表名;
PARALLEL(表名, 並列度度)	並列処理を行う。RAC構成の複数ノードの場合、インターコネクトが発生するので注意。PARALLEL_FORCE_LOCAL初期化パラメータをTRUEにするとローカルのみに制限可能原稿セッションの直近のパラレル実行状況は、V$PQ_SESSTAT で確認可能。Allocation Widthが並列度、Allocation Heightが1インスタンス当たりの並列度

↑

統計情報 †

↑

統計情報の取得 †

DBMS_STATS.GATHER_TABLE_STATS(
    OWNNAME           => 'スキーマ'
    ,TABNAME          => 'テーブル'
    ,PARTNAME         => 'パーティション'
    ,ESTIMATE_PERCENT => 'サンプルレート(%)'
    ,BLOCK_SAMPLE     => 'サンプルレート(block)'
    ,METHOD_OPT       => 'ヒストグラム統計'
    ,DEGREE           => '並列度'
    ,GRANULARITY      => 'パーティション表オプション'
    ,CASCADE          => 'インデックス統計 有無'
);

-- 実行例
DBMS_STATS.GATHER_TABLE_STATS(
    OWNNAME           => 'SCOTT'
    ,TABNAME          => 'COSTS'
    ,ESTIMATE_PERCENT => 50
    ,METHOD_OPT       => 'FOR COLUMNS PROD_ID SIZE 254' -- ※指定できる値は後述
    ,DEGREE           => 2
    ,CASCADE          => TRUE
);

METHOD_OPTパラメータについて
```
FOR COLUMNS <カラム名> SIZE <バケット数(最大254種類)>
```
- バケット数はカラムに入ってる値の種類で、種類以上の値を設定することで頻度分布ヒストグラムが生成される。これは、それぞれの値が何行あるか（ENDPOINT_NUMBER）正確に記録する。
- 種類より少ないバケットを指定すると高さ調整ヒストグラムが生成される。ソートしたデータの件数をバケット数で割り、
  その件数分バケットに格納していき、格納された最後の値を記録（ENDPOINT_VALUEの値）する。
  ENDPOINT_VALUEが同じ値のバケットが複数存在した場合、その値が他の値より多く存在することを意味する。これをポピュラー値という。
  ポピュラー値が存在するとバケット番号（ENDPOINT_NUMBER）に抜けが発生する。バケット0は特殊な値で、最小値が入っている。
- ヒストグラムの分布は、USER_HISTOGRAMSで確認できる。
- ヒストグラムが作成されたカラムはUSER_TAB_COL_STATISTICSのHISTOGRAM列で確認できる

--ディクショナリ統計の取得
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS();

--固定表（X$表）の統計取得
EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS', 'X$KSWSASTAB', no_invalidate=>false);

↑

統計情報のリストア †

統計収集の履歴を確認

select * from user_tab_stats_history;

指定した時点の統計情報をリストアする

exec dbms_stats.restore_table_stats (ownname=>'スキーマ名', tabname=>'テーブル名', as_of_timestamp=>'時間');

統計情報をロック（収集されないようにする）及び、アンロック（収集されるようにする）

exec dbms_stats.lock_table_stat(ownname=>'スキーマ名', tabname=>'テーブル名');
exec dbms_stats.unlock_table_stat(ownname=>'スキーマ名', tabname=>'テーブル名');

↑

アクセスパス一覧 †

実行計画	説明
AND-EQUAL	複数のROWIDセットから重複をなくして、それらの共通部分を戻す。
BITMAP CONVERSION TO ROWIDS	ビットマップをROWID に変換する。
BITMAP CONVERSION FROM ROWIDS	ROWIDをビットマップに変換する。
BITMAP CONVERSION COUNT	ROWIDの数を戻す。
BITMAP INDEX SINGLE VALUE	ビットマップインデックス内の１つのキーのビットマップを参照する。
BITMAP INDEX RANGE SCAN	ビットマップインデックス内の特定の範囲のキーのビットマップを参照する。
BITMAP INDEX FULL SCAN	ビットマップインデックス内の全ての範囲のキーのビットマップを参照する。
BITMAP MERGE	レンジスキャンによる複数のビットマップを１つのビットマップにマージする。
BITMAP MINUS	一方のビットマップのビットを、もう一方のビットマップから引く。
BITMAP OR	２つのビットマップのOR演算を行う。
BITMAP AND	２つのビットマップのAND演算を行う。
BITMAP KEY ITERATION	表から各行を抽出してビットマップ索引のビットマップと照合し、該当するもののビットマップをすべて抽出する。（この後BITMAP MERGE操作が行われる）
CONNECT BY	CONNECT BY句を含んでいる問い合わせの行を取り出す。
CONCATENATION	複数の行セットをUNION-ALLして戻す。
COUNT	選択された行の行数をカウントする。
COUNT STOPKEY	選択された行をROWNUM句で制限する。
DOMAIN INDEX	ドメイン索引からROWIDを取り出す。
FILTER	行セットから不要なものを取り除いて戻す。
FIRST ROW	行セットの最初の行のみ戻す。
FOR UPDATE	FOR UPDATE句が含まれている問い合わせによって戻された行を取り出してロックする。
HASH GROUP BY	GROUP BY句が含まれている問い合わせの行セットを複数のグループにハッシングする。
HASH JOIN	２つの行セットを結合して戻す。
HASH JOIN ANTI	ハッシュ（左側）アンチ結合
HASH JOIN RIGHT ANTI	ハッシュ（右側）アンチ結合
HASH JOIN SEMI	ハッシュ（左側）セミ結合
HASH JOIN RIGHT SEMI	ハッシュ（右側）セミ結合
HASH JOIN OUTER	ハッシュ（左側）外部結合
HASH JOIN RIGHT OUTER	ハッシュ（右側）外部結合
INDEX UNIQUE SCAN	索引から単一ROWIDを取り出す。
INDEX RANGE SCAN	索引から特定の範囲のROWIDを取り出す。（昇順）
INDEX RANGE SCAN DESCENDING	索引から特定の範囲のROWIDを取り出す。（降順）
INDEX FULL SCAN	索引からすべてのROWIDを取得する。（昇順）
INDEX FULL SCAN DESCENDING	索引からすべてのROWIDを取得する。（降順）
INDEX FAST FULL SCAN	マルチブロック読み込みを使用して、索引のすべてのROWIDを取得する。
INDEX SKIP SCAN	連結索引の先頭列を使用せずに索引からROWIDを取得する。
INLIST ITERATOR	INリスト内の各値に対して、実行計画内の操作を繰り返す。
INTERSECTION	２つの行セットから重複をなくして、それらの共通部分を戻す。
MERGE JOIN	２つの行セットをそれぞれ特定の値でソートし、一方の行セットをもう一方の行セットの行とつき合わせて結果を戻す。
MERGE JOIN OUTER	外部結合を実行するマージ結合
MERGE JOIN ANTI	マージアンチ結合
MERGE JOIN SEMI	マージセミ結合
MERGE JOIN CARTESIAN	他の表への結合条件を持たないマージ結合（クロス結合）
MAT_VIEW REWRITE ACCESS FULL	マテリアライズドビューの全ての行の取り出し
MAT_VIEW REWRITE ACCESS SAMPLE	マテリアライズドビューのサンプル行の取り出し
MAT_VIEW REWRITE ACCESS CLUSTER	索引クラスタのキー値による、マテリアライズドビューからの行の取り出し
MAT_VIEW REWRITE ACCESS HASH	ハッシュクラスタのキー値による、マテリアライズドビューからの行の取り出し
MAT_VIEW REWRITE ACCESS BY ROWID RANGE	ROWIDの範囲による、マテリアライズドビューからの行の取り出し
MAT_VIEW REWRITE ACCESS SAMPLE BY ROWID RANGE	ROWIDの範囲による、マテリアライズドビューからのサンプル行の取り出し
MAT_VIEW REWRITE ACCESS BY USER ROWID	指定ROWIDによる、マテリアライズドビューからの行の取り出し
MAT_VIEW REWRITE ACCESS BY INDEX ROWID	索引ROWIDによる、マテリアライズドビューからの行の取り出し（非パーティション）
MAT_VIEW REWRITE ACCESS BY GLOBAL INDEX ROWID	グローバル索引ROWIDのみによる、マテリアライズドビューからの行の取り出し（パーティション）
MAT_VIEW REWRITE ACCESS BY LOCAL INDEX ROWID	１つ以上のローカル索引ROWIDとグローバル索引ROWIDによる、マテリアライズドビューからの行の取り出し（パーティション）
MINUS	一方の行セットにあって、もう一方の行セットにないものをもどす。
NESTED LOOPS	外側表の各行を内側表の各行と比較してもどす。
NESTED LOOPS OUTER	外部結合を実行するネステッドループ結合
PARTITION	PARTITION_START～PARTITION_STOPの範囲の各パーティションに対して、実行計画内の操作を繰り返す。
PARTITION SINGLE	１つのパーティションへの問い合わせ
PARTITION ITERATROR	複数のパーティションへの問い合わせ
PARTITION ALL	全てののパーティションへの問い合わせ
PARTITION INLIST	INリストを基準にした繰り返し処理
PARTITION INVALID	操作パーティションが空である状態
PX ITERATOR BLOCK( OR CHUNK)	パラレルスキャン・スレーブ間でのオブジェクトのブロックまたはチャンク単位での分割
PX COORDINATOR	パラレルクエリ・コーディネーターを使用する
PX PARTITION	PARTITIONと同義だが、パラレル実行計画に表示される
PX RECEIVE	PX SENDノードから送信されるパーティション化データを読み取る
PX SEND QC(RANDOM) (OR HASH) (OR RANGE)	スレーブの２つのセット間における配分方法を実装する
REMOTE	リモートデータベースからのデータの取り出し
SEQUENCE	シーケンスへの操作を伴う処理
SORT AGGREGATE	グループ関数を用いた結果における単一行の取り出し
SORT UNIQUE	行セットをソートして重複を取り除く
SORT GROUP BY	GROUP BY句問い合わせによって複数グループにソートする処理
SORT JOIN	マージ結合前のソート処理
SORT ORDER BY	ORDER BY句問い合わせによるソート処理
TABLE ACCESS FULL	表の全行の取り出し
TABLE ACCESS SAMPLE	表のサンプル行の取り出し
TABLE ACCESS CLUSTER	表の索引クラスタキー値による行の取り出し
TABLE ACCESS HASH	表のハッシュクラスタキー値による行の取り出し
TABLE ACCESS BY ROWID RANGE	表の特定のROWID範囲による行の取り出し
TABLE ACCESS BY USER ROWID	指定したROWIDによる行の取り出し
TABLE ACCESS BY INDEX ROWID	索引ROWIDによる行の取り出し（非パーティション）
TABLE ACCESS BY GLOBAL INDEX ROWID	グローバル索引ROWIDのみによる、行の取り出し（パーティション）
TABLE ACCESS BY LOCAL INDEX ROWID	１つ以上のローカル索引ROWIDとグローバル索引ROWIDによる、行の取り出し（パーティション）
UNION	２つの行セットから重複をなくして連結結果を戻す
VIEW	ビューへの問い合わせ結果を別の処理に戻す