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指定説明
IOSTATSI/Oに関する実行統計を表示
MEMSTATSメモリに関する実行統計を表示
ALLSTATSIOSTATSと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見積もり時間
OMemOptimalで実行するために必要と見積もったメモリ・サイズ
1Mem1-passで実行するために必要と見積もったメモリ・サイズ
E-Temp一時時領域の見積もり使用量
実行統計説明
Startsそのステップが実行された回数
A-Rows処理行数
A-Time実行時間
Buffersバッファ・アクセス数
Readsディスクから読み込まれたブロック数
Writesディスクに書き出されたブロック数
Used-Mem使用したメモリ・サイズと処理方法(0:Optimal 1:1-pass >1:Multi-pass)
O/1/MOptimal、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 callsSQL実行時に内部で発行されたリカーシブコール数
db block getsDML等を発行したときなどに発生するカレントモードで読み込まれたブロック数
consistent getsSELECTを発行したときなどに発生する読み取り一貫性モードで読み込まれたブロック数
physical readsディスクアクセスによって読み込まれたブロック数
redo sizeREDOログに書き込まれたサイズ(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 processedSQLが処理した件数


 

アクセスパス

アクセスパス説明
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 |

Oracle_NestedLoops.png

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 | ★ハッシュ値にマッチする結合キーがあるかチェック

Oracle_Hash.png

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 ROWIDSROWIDをビットマップに変換する。
BITMAP CONVERSION COUNTROWIDの数を戻す。
BITMAP INDEX SINGLE VALUEビットマップインデックス内の1つのキーのビットマップを参照する。
BITMAP INDEX RANGE SCANビットマップインデックス内の特定の範囲のキーのビットマップを参照する。
BITMAP INDEX FULL SCANビットマップインデックス内の全ての範囲のキーのビットマップを参照する。
BITMAP MERGEレンジスキャンによる複数のビットマップを1つのビットマップにマージする。
BITMAP MINUS一方のビットマップのビットを、もう一方のビットマップから引く。
BITMAP OR2つのビットマップのOR演算を行う。
BITMAP AND2つのビットマップのAND演算を行う。
BITMAP KEY ITERATION表から各行を抽出してビットマップ索引のビットマップと照合し、該当するもののビットマップをすべて抽出する。(この後BITMAP MERGE操作が行われる)
CONNECT BYCONNECT BY句を含んでいる問い合わせの行を取り出す。
CONCATENATION複数の行セットをUNION-ALLして戻す。
COUNT選択された行の行数をカウントする。
COUNT STOPKEY選択された行をROWNUM句で制限する。
DOMAIN INDEXドメイン索引からROWIDを取り出す。
FILTER行セットから不要なものを取り除いて戻す。
FIRST ROW行セットの最初の行のみ戻す。
FOR UPDATEFOR UPDATE句が含まれている問い合わせによって戻された行を取り出してロックする。
HASH GROUP BYGROUP BY句が含まれている問い合わせの行セットを複数のグループにハッシングする。
HASH JOIN2つの行セットを結合して戻す。
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 ITERATORINリスト内の各値に対して、実行計画内の操作を繰り返す。
INTERSECTION2つの行セットから重複をなくして、それらの共通部分を戻す。
MERGE JOIN2つの行セットをそれぞれ特定の値でソートし、一方の行セットをもう一方の行セットの行とつき合わせて結果を戻す。
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 RANGEROWIDの範囲による、マテリアライズドビューからの行の取り出し
MAT_VIEW REWRITE ACCESS SAMPLE BY ROWID RANGEROWIDの範囲による、マテリアライズドビューからのサンプル行の取り出し
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 ROWID1つ以上のローカル索引ROWIDとグローバル索引ROWIDによる、マテリアライズドビューからの行の取り出し(パーティション)
MINUS一方の行セットにあって、もう一方の行セットにないものをもどす。
NESTED LOOPS外側表の各行を内側表の各行と比較してもどす。
NESTED LOOPS OUTER外部結合を実行するネステッドループ結合
PARTITIONPARTITION_START〜PARTITION_STOPの範囲の各パーティションに対して、実行計画内の操作を繰り返す。
PARTITION SINGLE1つのパーティションへの問い合わせ
PARTITION ITERATROR複数のパーティションへの問い合わせ
PARTITION ALL全てののパーティションへの問い合わせ
PARTITION INLISTINリストを基準にした繰り返し処理
PARTITION INVALID操作パーティションが空である状態
PX ITERATOR BLOCK( OR CHUNK)パラレルスキャン・スレーブ間でのオブジェクトのブロックまたはチャンク単位での分割
PX COORDINATORパラレルクエリ・コーディネーターを使用する
PX PARTITIONPARTITIONと同義だが、パラレル実行計画に表示される
PX RECEIVEPX SENDノードから送信されるパーティション化データを読み取る
PX SEND QC(RANDOM) (OR HASH) (OR RANGE)スレーブの2つのセット間における配分方法を実装する
REMOTEリモートデータベースからのデータの取り出し
SEQUENCEシーケンスへの操作を伴う処理
SORT AGGREGATEグループ関数を用いた結果における単一行の取り出し
SORT UNIQUE行セットをソートして重複を取り除く
SORT GROUP BYGROUP BY句問い合わせによって複数グループにソートする処理
SORT JOINマージ結合前のソート処理
SORT ORDER BYORDER 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 ROWID1つ以上のローカル索引ROWIDとグローバル索引ROWIDによる、行の取り出し(パーティション)
UNION2つの行セットから重複をなくして連結結果を戻す
VIEWビューへの問い合わせ結果を別の処理に戻す


 

トップ   編集 凍結解除 差分 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2015-12-20 (日) 19:18:23 (494d)