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コマンドで整形した結果を確認すると、何に時間がかかったかが分かる。