oracleのinsert select性能

質問

みなさん。こんにちは。
下記のsqlは、約６６万行をテーブルに挿入します。
しかし、何時間経っても終わらない。
原因はそのsqlでプロシージャー「sp_getEOutSrcRate」を呼び出す
ことが分かりました。
しかし、Select分のみを実行すると、すぐに結果が出ます。
なぜinsert時性能は悪いかが分からない。
みなさんがぜひ原因と解決策を教えて頂きたいと思います。
よろしくお願いいたします。

Insert /*+ append */
Into tbl_temp_eoutsrcrate
(iengineeringid, ioutsrcassignmentid, ieinstallationtypeid, iprodcategoryid, seoutsourcetypelist, IOUTSRCRATE)
Select e.iengineeringid, o.ioutsrcassignmentid, e.ieinstallationtypeid, e.iprodcategoryid, o.seoutsourcetypelist,
round(sp_getEOutSrcRate(e.iengineeringid, e.ieinstallationtypeid, e.iprodcategoryid, o.seoutsourcetypelist), 2) rate
From tbl_epengineering e, tbl_epoutsrcassignment o
Where o.iengineeringid = e.iengineeringid
And e.icancelflag = 0

回答No.5

uresiiwa

【解決策】
１．まず、実行計画を確認しましょう。appendヒントがうまく効いているか、知りたいですね。
explain plan for SQL文;
select * from table(dbms_xplan.display());

→ "LOAD AS SELECT"ならダイレクトパスインサートになっている（appendヒントが効いている）。しかし、これだけ遅いと、なっていないのではないか。なっていない場合は、"INSERT STATEMENT"（通常ＩＮＳＥＲＴ）のはず。

２．ダイレクトパスインサートにならない理由としては、トリガーが設定されていることが考えられます。
select * from user_triggers;
→ 当該テーブルにトリガーはついていないでしょうか？

３．トリガーがついているのであれば、それが無効化しても良いものであれば、無効にしてＩＮＳＥＲＴすれば、ダイレクトパスインサートできると思います。
alter table tbl_temp_eoutsrcrate disable all triggers;
ＳＱＬ実行
-- 有効に戻す
alter table tbl_temp_eoutsrcrate enable all triggers;

しかし、意味があってトリガーがついているのであれば、単純に無効にするとダメかもですが、設計を確認してみてください。

【原因について】
トリガー以外だと、redoログやログバッファ、データファイルの配置や設定がまずくて更新処理が非常に遅いなんていう可能性もゼロではありません。はっきり特定するためには、トレースを取りましょう。
以下を実行するユーザにＡＬＴＥＲ ＳＥＳＳＩＯＮ権限が必要ですので無ければ権限付与してください。
alter session set sql_trace = true;
SQL実行
alter session set sql_trace = false;
→ v$parameterの"user_dump_dest"にtrcファイルが出力される。そのファイルをtkprofコマンドで整形した結果を確認すると、何に時間がかかったかが分かる。

回答No.4

Siegrune

#1です。
ひょっとして、単にinsertが遅いだけでは？
例えば、insert文（selectが付いていないもの）約６６万行を実行しても
質問のinsert selectとほとんど同じ時間かかっているとか。
## 試せとはいっていません。念のため。
もし、そうだとすると、以下のアプローチが有効と思います。

create table tbl_temp_eoutsrcrate　のinitialとnextはどうなっていましたか？
INSERTはテーブルが拡張されると極端に遅くなります。
nextのサイズを大きくしてみましょう。(Alter tableで変えてみてください。)

なお、appendヒント（/*+ append */のこと）使っているから、空きブロックを使わずに
ハイ・ウォーター・マーク以降にどんどん追加されるので、拡張がおきやすいです。
空きがたくさんあるのに拡張しているようなら、appendヒントをはずしてください。

appendヒントの注意点というかデメリットはここをみてもらうと分かりやすいかと。
http://www.atmarkit.co.jp/fdb/rensai/orasql12/orasql12_2.html

回答No.3

3rd_001

>しかし、Select分のみを実行すると、すぐに結果が出ます。
>なぜinsert時性能は悪いかが分からない。

【Selectのみの実行】と【Select/Insertの実行】は異なることを理解しましょう。

【Selectのみの実行】
対象：tbl_epengineering、tbl_epoutsrcassignment

【Select/Insertの実行】
対象：tbl_temp_eoutsrcrate、tbl_epengineering、tbl_epoutsrcassignment

上記のようにInsert時はSelectのみと異なりtbl_temp_eoutsrcrateへのINSERT処理が走ります。Selectが速くてもInsertが遅ければ、トータルの実行時間は遅くなります。

まず、sp_getEOutSrcRateを外した状態でInsertを実施した場合の速度を測定しなければ
本当にsp_getEOutSrcRateが原因かはわかりません。確認ずみでしょうか？

sp_getEOutSrcRateを外した状態でも遅い場合はtbl_temp_eoutsrcrate側に問題があると考えられます。tbl_temp_eoutsrcrateに索引やトリガーはついてますか？

TraceをとってSQLの実行計画を見ないことには第三者から指摘できるのはこれくらいです。

補足

sp_getEOutSrcRateを外した状態でInsertを実施した場合の速度は非常に遅いです。
また、tbl_temp_eoutsrcrateに索引やトリガーはついていないです。

回答No.2

SaKaKashi

Insert先のテーブルに主キーやインデックスがあったら、inser前に削除して、insert後に主キーやインデックスを再作成する

回答No.1

Siegrune

sp_getEOutSrcRate
は何をやっていますか？
ひょっとしてシステム標準のストアドプロシジャですか？
（知っているバージョンでは見たことないけど）
・・・それなら、oracleのバージョンを書いてもらわないと。
違うならその中身が分からないと答えようがないです。

例えば、
tbl_temp_eoutsrcrate
を
sp_getEOutSrcRate
内部で使用しているなら
selectで処理したとき早く、insert selectで処理したとき非常に遅い
となり得ますが、sp_getEOutSrcRateの中身をみないとなんともいえません。

selectとinsert selectの両方について
explain planを取得して、中を見比べてみるというのも調べる方法ではあります。
・・・これも結果をもらわないとこちらとしてはわからないことに変わりはないですが。

補足

回答ありがとうございます。
sp_getEOutSrcRateは自分で作ったファンクションです。