番外編(Q&A explain plan の time 列について)

メルマガファンの皆様こんにちは!

突然ですが、今回のメルマガは番外編と致しまして読者からお寄せ頂きました
質問に回答させて頂きたいと思います。

連載中の「行移行・行連鎖に関する検証」は、次回より再開させて頂きます。
楽しみにしていた皆様、申し訳ございません m(_ _)m

それでは、早速ですが質問を紹介させて頂きます。

【質問】

explain plan で表示される time 列についての質問です。

----------------------------------------------------------------------
|Id|Operation               |Name     |Rows|Bytes|Cost(%CPU)|Time    |
----------------------------------------------------------------------
| 0|INSERT STATEMENT        |         |108K|  25M| 287   (1)|00:00:05|
| 1| LOAD TABLE CONVENTIONAL|HOGE     |    |     |          |        |
| 2|  TABLE ACCESS FULL     |HOGE_TEMP|108K|  25M| 287   (1)|00:00:05|
----------------------------------------------------------------------

1. time 列の 00:00:05 の単位は、”分:秒:秒以下” でしょうか?
2. この値を実際の処理時間の参考値としていいのでしょうか?
3. 上記の例のように insert の時間は time 列に含まれないのでしょうか?
4. insert の時間が含まれないとして、insert の時間を予測することはでき
ないのでしょうか?

【回答】

この質問、ギャバンが回答させて頂きます。
困っている人(質問)を助けるのが正義のヒーローの役目ですからねっ!!

と、下らない前置きはこの辺にしておいて・・・

ご質問の explain plan の結果より、以下のような insert 文が実行されると
想定して解説させて頂きます。

  SQL> insert into hoge select * from hoge_tmp;

まず、弊社環境で適当なサイズの hoge_tmp 表を作成し explain plan の結果
を取得致しました。

  SQL> explain plan for
   2  insert into hoge select * from hoge_tmp;

解析されました。

  SQL> select * from table(dbms_xplan.display());
--------------------------------------------------------------------
|Operation               |Name     |Rows |Bytes|Cost(%CPU)|Time    |
--------------------------------------------------------------------
|INSERT STATEMENT        |         |1562K| 244M|2745   (5)|00:00:10|
| LOAD TABLE CONVENTIONAL|HOGE     |     |     |          |        |
|  TABLE ACCESS FULL     |HOGE_TEMP|1562K| 244M|2745   (5)|00:00:10|
--------------------------------------------------------------------

※表示の都合上、一部の表示を削っています(以下、同様)。

この結果を今回のベースデータとして解説していきたいと思います。
着目する値は、以下 Cost と Time です。

Cost -> 2745
Time -> 00:00:10

●質問1. time 列の 00:00:05 の単位は、”分:秒:秒以下” でしょうか?

⇒ これは “時:分:秒” です。

この時間は、plan_table の time 列から取得しており、この列の単位は “秒”
であるとマニュアルに記載されています。

Oracle Databaseパフォーマンス・チューニング・ガイド
11gリリース1(11.1)
→ 表12-1 PLAN_TABLE列
http://otndnld.oracle.co.jp/document/products/oracle11g/111/doc_dvd/server.111/E05743-02/ex_plan.htm#i18300

参考までに、plan_table を直接参照した結果を以下に掲載致します。

  SQL> select id ,OPERATION,TIME from plan_table;
          ID OPERATION                            TIME
  ---------- ------------------------------ ----------
           0 INSERT STATEMENT                       10
           1 LOAD TABLE CONVENTIONAL
           2 TABLE ACCESS                           10  ★ ← 10(秒)

●質問2. この値を参考値としていいのでしょうか?

⇒ ある程度は・・・ですね。

実際の結果とは異なる場合も多い為、参考程度の情報として捉えておくことを
お薦め致します。
先ほど explain plan で 10 秒と見積られた insert 文を実行してみます。

  SQL> set timing on
  SQL> insert into hoge select * from hoge_tmp;

  1500000行が作成されました。

  経過: 00:00:02.93 ★

約 3 秒で終了しました。
見積り時間とは、3 倍以上の差になりました。

見積り結果より速くなる分には良いかなと思いますが逆のケースもあります。
例えば、insert 対象表に索引が存在している場合は、処理性能が大きく劣化
することがありますが、索引の有無に関わらず explain plan の結果は同じ
結果になります。

insert 対象表に索引を作成し、同じ insert 文を実行してみます。

  SQL> create index hoge_i1 on hoge (col2);
  SQL> insert into hoge select * from hoge_tmp;

  1500000行が作成されました。

  経過: 00:00:14.17 ★

今度は、約 14 秒もかかりました。
他にも索引を追加していくと更に遅くなっていきます。

索引追加後の explain plan の結果は・・・

--------------------------------------------------------------------
|Operation               |Name     |Rows |Bytes|Cost(%CPU)|Time    |
--------------------------------------------------------------------
|INSERT STATEMENT        |         |1562K| 244M|2745   (5)|00:00:10|
| LOAD TABLE CONVENTIONAL|HOGE     |     |     |          |        |
|  TABLE ACCESS FULL     |HOGE_TEMP|1562K| 244M|2745   (5)|00:00:10|
--------------------------------------------------------------------

冒頭に記載した結果と全く同じです。

Cost -> 2745
Time -> 00:00:10

これは、索引の有無は判断していないということもありますが、そもそもの話
insert の時間は含んでいないんですね。

その為、
「(select 部分に関して)ある程度は参考になるかな・・・」
という感じですかね。

実際の現場では、Time 列の結果で時間を予測するというより、チューニング
調査の過程で最も Cost や Time が小さくなる実行計画を探し出す、という
用途で利用されていることが多くあり、これは有効な方法と考えています。

●質問3. insert は時間に含まれないのでしょうか?

⇒ 直前に答えが出てきてしまいましたが、含まれません。

以下は、insert 文を除いた select 文のみの explain plan の結果です。

 SQL> explain plan for
   2  select * from hoge_tmp;

  解析されました。
  SQL> select * from table(dbms_xplan.display());
--------------------------------------------------------------------
|Operation               |Name     |Rows |Bytes|Cost(%CPU)|Time    |
--------------------------------------------------------------------
|SELECT STATEMENT        |         |1562K| 244M|2745   (5)|00:00:10|
|  TABLE ACCESS FULL     |HOGE_TEMP|1562K| 244M|2745   (5)|00:00:10|
--------------------------------------------------------------------

insert 文が含まれる時と全く同じ結果となりました。

Cost -> 2745
Time -> 00:00:10

この結果から select 文のみの見積り結果を出力していることが分かります。

●質問4. insert の時間を予測することはできないのでしょうか?

残念ながら explain plan の time 列のような情報で insert 時間を予測する
ようなことはできません。
実測結果よりサンプルデータを取得し、そのデータを基準にしてデータ比率等
によって予測して頂くのが現実的な手段になるかと思います。

以上です。

疑問は解消されましたでしょうか?
追加質問や疑問点等ございましたら遠慮なくお問い合わせ下さい!

助けを求める声があるところにギャバンは現れます。きっと・・・

恵比寿が恋しい ・・・ 都内某所にて
もとい
宇宙が恋しい ・・・ 地球某所にて