実行計画の結果を見ても用語の意味からわかりません。。。

ORACLEで、動作の遅いSQLがあり、原因究明のため、A5で実行計画を表示させたのですが、用語の意味からわかりません。

UPDATE STATEMENT Cost = 142
 +-UPDATE      TEST001
    +-FILTER
    |  +-TABLE ACCESS   FULL  TEST001
    |  +-NESTED LOOPS
    |     +-NESTED LOOPS
    |     |  +-NESTED LOOPS
    |     |  |  +-TABLE ACCESS   FULL  TEST001
    |     |  |  +-TABLE ACCESS   BY INDEX ROWID  TEST001
    |     |  |     +-INDEX   RANGE SCAN  TEST001.SET_PK
    |     |  +-INDEX   RANGE SCAN  TEST_PK
    |     +-TABLE ACCESS   BY INDEX ROWID  TEST001
    +-VIEW
       +-SORT   GROUP BY
          +-VIEW
             +-SORT   GROUP BY
                +-NESTED LOOPS   OUTER
                   +-NESTED LOOPS   OUTER
                   |  +-NESTED LOOPS   OUTER
                   |  |  +-HASH JOIN
                   |  |  |  +-NESTED LOOPS
                   |  |  |  |  +-NESTED LOOPS
                   |  |  |  |  |  +-NESTED LOOPS   OUTER
                   |  |  |  |  |  |  +-TABLE ACCESS   BY INDEX ROWID  TEST002
                   |  |  |  |  |  |  |  +-INDEX   RANGE SCAN  TEST002_PK
                   |  |  |  |  |  |  +-TABLE ACCESS   BY INDEX ROWID  TEST003
                   |  |  |  |  |  |     +-INDEX   RANGE SCAN  TEST003_PK
                   |  |  |  |  |  +-INDEX   UNIQUE SCAN  TEST003_PK
                   |  |  |  |  +-TABLE ACCESS   BY INDEX ROWID  TEST003
                   |  |  |  +-TABLE ACCESS   FULL  TEST003
                   |  |  +-VIEW
                   |  |     +-TABLE ACCESS   BY INDEX ROWID  TEST003
                   |  |        +-INDEX   RANGE SCAN  TEST003
                   |  |           +-TABLE ACCESS   BY INDEX ROWID  TEST003
                   |  |           |  +-INDEX   UNIQUE SCAN  TEST003_PK
                   |  |           +-REMOTE      TEST004
                   |  +-REMOTE      TEST004
                   +-REMOTE      TEST005

このような実行計画、いったい何がどうなっているのか、どのように解析をしていったらいいのでしょう？

何か分かりやすく解説しているサイトや書籍はないものでしょうか？

いろいろ調べてみまして、ネストの深いところから実行されることと、「TABLE ACCESS FULL」は、テーブルフルスキャンをしている事はわかりました。

しかしながら、「FILTER」「NESTED LOOPS」「VIEW」「REMOTE」このあたりの用語の意味がわかりません。

この用語の意味だけでも教えて頂けますとありがたいです。

himakuma

2016/11/10 09:52

Oracleですか？？

GiveAHand

2016/11/10 11:46

すいません。はいOracleです。

退会済みユーザー

2016/11/10 11:52

ACCESS FULL どう考えてもこれで過ぎ

行動規範の内容に同意します

回答3件

ベストアンサー

このサイトで十分にわかると思います。

実行計画の操作一覧 - オラクル・Oracleをマスターするための基本と仕組み
http://www.shift-the-oracle.com/performance-tuning/explain-plan-operation.html

しかしながら、「FILTER」「NESTED LOOPS」「VIEW」「REMOTE」このあたりの用語の意味がわかりません。

一応一つ一つ。
「FILTER」レコードの絞り込みをしています。あまり気にしなくていいです。
「NESTED LOOPS」テーブル間の結合方法です。あまり気にしないでいいです。
「VIEW」検索結果の中間処理で内部でVIEW化しているだけです。気にしなくていいです。
「REMOTE」リモート接続でデータを取得しています。別DBなどからDBリンクで取得しているのでしょう。あまり気にしなくていいです。

つまりあなたが気にしているところは割とどうでもいいことです。
重要なのは、どう結合されているか、レコードをどう取得しているか、どのような性質のテーブルなのか、コストはどうなのかなどです。

「TABLE ACCESS BY INDEX ROWID」でアクセスしたほうがいいこともあれば「TABLE ACCESS FULL」でアクセスしたほうがいいこともあります。どちらがいいか判断し、逆の方法でアクセスしていたらヒント句で固定したりします。「TABLE ACCESS FULL」だから遅い、なんて思いこみだけは絶対にしてはいけません。

ぱっと見で気になるのは、TEST003に何度もアクセスしているところです。5回？。こんなにアクセスする必要ありますか？SQLの作りが悪いように感じます。TEST001も同様です。普通に素直にアクセスするSQLに書き換えられないか考えてみてください。

あとREMOTEのTEST004とTEST005がどちらもOUTERなのも気になります。そこもどうにかなりませんか？

さほど複雑なSQLでもないので、経験の少ない方でもがんばればなんとかなると思います。頑張ってください。

投稿2016/11/10 12:42

miu_ras

総合スコア902

GiveAHand

2016/11/11 15:44

miu_ras様大変わかすいご回答、ありがとうございます！なるほど、用語の意味よりも、実際がどうなのかということなのですね。教えて頂いたページを見てもまだちんぷんかんぷんなところありますが、動作確認で理解を深めていきたいと思います。ありがとうございました！

行動規範の内容に同意します

先ず始めにSQLを掲示できるレベルで掲示した方が、
回答者側も課題を共有しやすいので掲示できるならする方が良いでしょう。

ともあれ実行計画を見た感じだと、
無駄にサブクエリが使われている印象しかないので、
不要部分のカット、または別のアプローチを取れば速度改善は望めるでしょう。

後はSQLを段階的に分割して流すなどして、
時間がかかっている所を割り出すというやり方も有用です。

ちなみに同じようなサブクエリが何度も登場するなら、
最近のDBMSは軒並み**共通テーブル式(CTE)**をサポートしているので、
それへ置き換えることで速度改善を図るというのも一つの案でしょうね。

#実行計画の中身について
現在の質問者さんもレベルでは、
恐らく実行計画の単語の意味が分かったところで劇的に理解が深まることはない気がします。

Oracleに限らず、
いずれのDBMSも一般的な結合方法、インデックス項目の検索、非インデックス項目の検索には
大きな違いがないため、
この辺りの知識を深めるとどのDBMSを相手としてもある程度通用するスキルは身につくと思われます。

ただし一般論となりOracle特化の内容ではないので、興味がなければ読み飛ばしてください。

例えば結合方法だったら、

NESTED LOOP(入れ子ループ)
HASH JOIN(ハッシュ結合)
SORT MERGE JOIN(マージ結合)

などがあります。
(※それぞれ特徴があるので調べてみてね。)
ただ結合方法は基本的にはDBMS側が最適なアルゴリズム選択をしてくれるので、
あまり気にする機会は少ないかなと思われます。

次にインデックス項目の検索方法ですが、これは大きく２種類となります。

インデックス一意スキャン
インデックス範囲スキャン

上記はインデックスが利用された検索となるので、
大量にあるレコードから絞り込む際には威力を発揮します。

他にもインデックスはソートされた状態で管理されるという特性があるため、
GROUP BYやORDER BYなどソートが伴う処理ではインデックスを使わせて高速化という手法もあります。

ちなみにOracleの実行計画における、
TABLE ACCESS BY INDEX ROWIDというのは、
インデックス検索から物理レコードへのマッピングが行われている処理となります。
(要するにインデックスで検索済ませてから、物理レコードを取りにいっている)

最後に非インデックス項目の検索についてですが、
これは説明するまでもないですがインデックスを利用しないでは、
まず物理レコードを持ってきてからフィルタリングを行うことになります。
(OracleだとFilter(抽出条件)と表示されたはず)
この辺りはこちらが参考になります。

長くなりましたが、
この辺りの周辺知識を充実させているほど実行計画をより噛み砕けるので、
興味があったら勉強してみると良いかもしれませんね。

投稿2016/11/11 14:52

編集2016/11/11 15:19

Panzer_vor

総合スコア1636

GiveAHand

2016/11/12 00:08

Panzer_vor様お返事遅くなりました。すみません。大変分かりやすく、濃い内容をありがとうございます。結合方式の用語の意味であれこれ悩むよりも、インデックスやソートの方式を理解して、あれこれ試すのがよさそうですね。共通テーブル式(CTE)に関しても初めて知りました。試してみます。ありがとうございました！

行動規範の内容に同意します

miu_ras さんと同意見です。
差支えない範囲で実際のUPDATE文、各テーブルのレコード件数、CREATE TABLE, CREATE INDEX、
EXPLAIN PLAN を使って実行計画を取得する
で SQL*Plusから
SET LINESIZE 200
EXPLAIN PLAN SQL(上記で実行したUPDATE文) ;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY()) ;
を提示されては？

[Oracle]タグは追加しましょう。

投稿2016/11/10 12:55

編集2016/11/11 15:07