Oracle Databaseのプロシージャ内で実行するUPDATE文が、特定の状況で処理に時間がかかりすぎる

Question

実現したいこと

・該当UPDATE文の処理時間短縮

前提・発生している問題

OracleDBを使用し、一時表へデータをINSERT・一時表内でUPDATE・一時表からSELECTを行うプロシージャを作成しました。
SQLDevelopperで実行した際、更新対象データ件数が8000件程度であれば下記のUPDATE文1つに20分以上かかります（1時間かかる場合もあり、その時々によって変わる）。
UPDATE文はプロシージャ内で複数記述していますが、処理時間を要するものは** DENSE_RANK() OVER(~~) **を利用している以下のようなUPDATE文の場合に限られるようで、それ以外のUPDATE文は1秒以内で完了します。

UPDATE文の大幅な処理速度の低下について考えられる原因・解決策・調査方法をご教示ください。

該当のソースコード

    UPDATE
        TMP_TABLE  T
    SET
        T.NUM = (
                   SELECT
                        WK_T.SORT_NO
                   FROM
                         (
                           SELECT DISTINCT
                                 COLUMN1
                                 , COLUMN2
                                 , COLUMN3
                                 , COLUMN4
                                 , SORT_COLUMN 
                                 , DENSE_RANK() OVER(ORDER BY SORT_COLUMN) AS SORT_NO
                           FROM
                                TMP_TABLE  
                          )WK_T
                     WHERE
                          T.COLUMN1 = WK_T.COLUMN1
                           AND T.COLUMN2 = WK_T.COLUMN2
                           AND T.COLUMN3 = WK_T.COLUMN3  
                           AND T.COLUMN4 = WK_T.COLUMN4  
                           AND T.SORT_COLUMN = WK_T.SORT_COLUMN
     　　　　　　　)
     ;

試したこと

更新対象データ件数を8000件から6000件に減らすと、同じUPDATE文が1秒以内で実行完了することを確認しました。

補足情報

・ACTIVEなSQLのコストを確認すると233059397でした。（そこからコストは変化しませんでした）

・また、以下のSQLでセッション情報を確認しました。

select
   a.INST_ID
  , a.SID
  , a.TYPE
  , a.ACTION
  , a.COMMAND
  , c.COMMAND_NAME
  , a.STATUS
  , a.LAST_CALL_ET                                -- 今の状態になってからの経過時間
  , trunc((sysdate - a.SQL_EXEC_START) * 24 * 60 * 60, 2) as "秒" --実行し続けている時間
  , a.WAIT_CLASS                                  -- 待機イベントクラス
  , a.EVENT                                       ----- 一時セグメントを利用しているか
  , u.TABLESPACE
  , u.SEGTYPE                                     -- 一時セグメントの種別
  , u.BLOCKS                                      -- 割り当てられたブロック数
  --PGA情報
  , p.PGA_USED_MEM                                --現在使用中のPGA
  , p.PGA_ALLOC_MEM                               --現在割り当てられているPGAメモリ
  , p.PGA_FREEABLE_MEM                            --解放できる割当済みPGAメモリ
  , p.PGA_MAX_MEM                                 --割り当てられた最大PGAメモリ
from
  gv$SESSION a 
  left outer join gv$process p 
    on a.INST_ID = p.INST_ID 
    and a.PADDR = p.ADDR 
  left outer join gv$transaction r 
    on a.SADDR = r.SES_ADDR 
    and a.INST_ID = r.INST_ID 
  left outer join gV$TEMPSEG_USAGE u 
    on a.SADDR = u.SESSION_ADDR 
    and a.SERIAL# = u.SESSION_NUM 
    and a.INST_ID = u.INST_ID 
  left outer join v$sqlcommand c 
    on a.COMMAND = c.COMMAND_TYPE 
where
  1 = 1 
  and a.TYPE = 'USER' 
  and a.status = 'ACTIVE' 
order by
  a.INST_ID
  , a.STATUS
  , a.SQL_EXEC_START

・使用している一時表の構造ですが、キーは無しでNUMBER型の項目が10個あるだけです。

・SORT_COLUMNでインデックスを作成しましたが効果は全くありませんでした。

当方、プログラミング初学者の為解決に必要な情報が分かりません。不足している情報があればご指摘いただきたいです。

Comments

[KOZ6.0]

SORT_AREA_SIZE 不足ですかね？
SORT_AREA_SIZE を広げるか、WK_T の部分も一時表にして事前に作っておくと改善するかもしれません。

[namoine_nepher]

回答ありがとうございます。
データベースに関して無知なので再度質問させていただきたいのですが、
DENSE_RANK() OVER(ORDER BY SORT_COLUMN)　の部分で使用するソートを行うためのメモリ（SORT_AREA_SIZE）が不足しているという認識でよろしいでしょうか。

https://www.reqtc.com/column/pga.html　こちらのサイトを参考にしたところ、
「インスタンスが共有サーバーのオプションで構成されていないかぎり、
SORT_AREA_SIZE パラメータを使用することはお薦めしません。
かわりに、PGA_AGGREGATE_TARGET を設定して、SQL 作業領域の自動サイズ指定
を使用可能にすることをお薦めします。」と書かれてありました。
使用しているデータベースの環境は専用サーバーであるためPGA_AGGREGATE_TARGETを変更してみようと思います。
何か間違って理解している点がありましたらご指摘いただきたいです（＞＜）！

[KOZ6.0]

DISTINCT でも重複を除くためにソートかけてるはずです。
主キーの情報が不足していますが、不要であるなら外したほうが良いです。

[KOZ6.0]

10G 以降、 DISTINCT は HASH を使ってますね。失礼しました。
こちらは HASH_AREA_SIZE になります。

[sazi]

チューニングに関しては、SQL文とキーおよびインデックスは正しい情報を提示して下さい。
質問のSQLはWK_T内にSORT_COLUMNはありませんが結合条件となっていて、このままだとエラーです。
また、関係なさそうという事でカラムの記述を省略しているのであれば、それも良くありません。

[namoine_nepher]

ありがとうございます！
SQLを修正しました。この一時表にキーとインデックスはありません。

Answer 1

一時表の利用は少量のデータを扱う場合に限った方が良いです。
一時表ではなく、一般のテーブルに変更して、where句で使う列にきちんとインデックスを設定してみては？

質問は修正できます。create table, create index, 実行計画も質問に提示できた方が適切なコメントが付きやすいです。

以前、select文にjoin句を使わずに複数のテーブルからの情報をワークテーブルにかき集めたSQLをjoinを使うように修正して、処理時間を半分以下に改善したことがあります。

Comments

[namoine_nepher]

ありがとうございます。結合条件にインデックスをつけて試したいと思います。（^^）
今のところ一時表を使うといった方向性なのですが、一時表の方が処理速度は速いのではないですか？

[Orlofsky]

わたしが質問に追加するように書いた内容の反映が不十分です。

一時表を使った場合と一般のテーブルを使った時でどちらが早いか実際に環境を用意して比較します。パフォーマンス・チューニングではその手間を惜しんではなりません。

Answer 2

更新対象データ件数を8000件から6000件に減らすと、同じUPDATE文が1秒以内で実行完了することを確認しました。

1/4のデータの増減で1秒⇒20分ということで考えると、
①SQLでの問い合わせが掛け合わせの件数分となっている。
※「(COLUMN1,COLUMN2, COLUMN3,・・・)のパターン数の2乗）件数分、問合せしているのでは無いかと推測
②結合条件に最適なインデックスが無い
上記の理由により遅くなっているのだと思います。

先ずは①を解消させることですが、質問の内容が端折られていて、実際のテーブル構造等も不明ですので、これ以上できるアドバイスはありません。

実行計画を取得してどこにネックがあるのか確認すると良いかと思います。

因みにですが、一時テーブルを作らなくても、元の表で以下のwindow関数で同じ結果になるのではないですか？

DENSE_RANK() OVER(PARTITION BY COLUMN1, COLUMN2, COLUMN3, COLUMN4 ORDER BY SORT_COLUMN) AS SORT_NO

Comments

[namoine_nepher]

ありがとうございます。結合条件にインデックスをつけて試したいと思います。
一時表の構造ですが、キーは無しでNUMBER型の項目が10個あるだけなのですが、処理件数が多いというだけでこんなにUPDATE文に時間がかかるのでしょうか。。。

[sazi]

サブクエリーを使用しているので、更新対象のテーブルを1件読むごとに、全表検索しその上でDISTINCTなどによるマージ処理が行われている事になります。
ある閾値で急激にパフォーマンスが落ちるのは、領域不足で実行計画が意図とは異なるものになっている可能性があります。
それには実行計画の内容を確認する事です。

その上で、先ずはインデックスの適用ですね。
一意なインデックスがあるようなら、サブクエリーではなくjoinを使用したupdate文に置き換えるとさらに改善されるかと思います。