C# と VB.NET の質問掲示板

ASP.NET、C++/CLI、Java 何でもどうぞ

C# と VB.NET の入門サイト

Re[16]: Oracle 更新処理パフォーマンスについて


(過去ログ 66 を表示中)

[トピック内 19 記事 (1 - 19 表示)]  << 0 >>

■38411 / inTopicNo.1)  Oracle 更新処理パフォーマンスについて
  
□投稿者/ poro (11回)-(2009/07/13(Mon) 19:02:54)

分類:[.NET 全般] 

データ更新時のパフォーマンスについて質問させてください。

大量データの更新処理を行っているのですが、
更新対象のテーブルによって更新時間が全く違っています。

パフォーマンスに差がでている考えられる理由を教えていただけないでしょうか?

以下が、実行内容です。

Aテーブルには、4万件のデータ
Bテーブルに、12万件のデータがあり、
AテーブルもBテーブルも、1レコードずつ更新していきます。

Aテーブル(4万件)の方は、3分ぐらいで更新されるのですが、
Bテーブル(12万件)の方は、15分経っても2〜3千件しか更新されません。

プログラム上、
更新前にトランザクションを張り、
終了時にコミット/ロールバックを行うといった一般的な考え方で更新処理を行っております。

各テーブルの設定内容に原因があるかとは思うのですが、
どのような設定がパフォーマンスに影響するか知識がないため、
よろしくお願いいたします。

引用返信 編集キー/
■38412 / inTopicNo.2)  Re[1]: Oracle 更新処理パフォーマンスについて
□投稿者/ Jitta on the way (353回)-(2009/07/13(Mon) 19:30:33)
No38411 (poro さん) に返信
> 各テーブルの設定内容に原因があるかとは思うのですが、
> どのような設定がパフォーマンスに影響するか知識がないため、
> よろしくお願いいたします。
>

それぞれのテーブルの要素と、インデックスの有無、update 文の構成を教えてください。


一般的に、インデックスがある行を使って操作すると、速度が向上する傾向があります。
引用返信 編集キー/
■38413 / inTopicNo.3)  Re[2]: Oracle 更新処理パフォーマンスについて
□投稿者/ poro (12回)-(2009/07/13(Mon) 19:45:06)
No38412 (Jitta on the way さん) に返信

 お返事ありがとうございます。

> インデックスの有無

Aテーブル、Bテーブル共にインデックスは使用しておりません。

> update 文の構成を教えてください。

UPDATE Aテーブル SET 項目A = 'VALUE' WHERE 項目A = 'CONDITION'
UPDATE Bテーブル SET 項目A = 'VALUE' WHERE 項目A = 'CONDITION'

引用返信 編集キー/
■38415 / inTopicNo.4)  Re[3]: Oracle 更新処理パフォーマンスについて
□投稿者/ 魔界の仮面弁士 (1149回)-(2009/07/13(Mon) 20:27:15)
2009/07/13(Mon) 20:38:01 編集(投稿者)

No38413 (poro さん) に返信
> UPDATE Aテーブル SET 項目A = 'VALUE' WHERE 項目A = 'CONDITION'
パラメータ化はしておらず、単一行更新の SQL を繰り返し発行しているという事でしょうか?


> UPDATE Bテーブル SET 項目A = 'VALUE' WHERE 項目A = 'CONDITION'
この更新処理は、
 UPDATE Bテーブル SET 項目A='ORACLE' WHERE 項目A='MSSQL'
 UPDATE Bテーブル SET 項目A='ORACLE' WHERE 項目A='SQLCE'
 UPDATE Bテーブル SET 項目A='Access' WHERE 項目A='ORACLE'
のように、後続の UPDATE 処理が、先行した UPDATE の結果を上書きする事もあるのでしょうか?


>>> Aテーブル(4万件)の方は、3分ぐらいで更新されるのですが、
>>> Bテーブル(12万件)の方は、15分経っても2〜3千件しか更新されません。
条件無しの
 SELECT COUNT(*) FROM 表名
の問い合わせに要する時間は、それぞれ何ミリ秒でしょうか?

あと、一時表領域は十分な大きさが用意されていますか?
(自動拡張によって、領域確保時間の増大や断片化を招いているとか)


>>インデックスの有無
> Aテーブル、Bテーブル共にインデックスは使用しておりません。
項目Aにインデックスを張るべきかどうかは、ケースバイケースですが、
提示された条件の場合、項目Aにインデックスが張られていないとすれば、
 WHERE 項目A='CONDITION'
の問い合わせに対して、テーブルが全行スキャンされる事になるので、
件数が増えれば、処理時間は加速度的に増えていくかと思います。
(今回の場合、検索対象列も更新対象列も同じフィールドなのが悩ましいところですが…)


もしもインデックス無しでの更新が必要だとしたら、'VALUE' と 'CONDITION' を
Oracle の TEMPORARY TABLE に入れておき、それを Bテーブル と繋げて更新するようにすれば、
スキャン回数が一回で済んで、多少は効率良く更新されるかもしれません。(試していませんけれども)


UPDATE Bテーブル SET 項目A=(SELECT WORKTBL.VALUE FROM WORKTBL WHERE WORKTBL.CONDITION=Bテーブル.項目A)
引用返信 編集キー/
■38422 / inTopicNo.5)  Re[4]: Oracle 更新処理パフォーマンスについて
□投稿者/ poro (14回)-(2009/07/14(Tue) 10:16:52)
ご返信ありがとうございます。

> UPDATE Aテーブル SET 項目A = 'VALUE' WHERE 項目A = 'CONDITION'
>パラメータ化はしておらず、単一行更新の SQL を繰り返し発行しているという事でしょうか?

説明不足でした、
申し訳ありません。

AテーブルとBテーブルは、
親(ヘッダ情報)と子(明細情報)の関係にあります。

Aテーブルの項目Aの値を元に、
再度プログラム上で採番し(4万件分採番)、同一項目である項目Aに再設定するという仕様です。

また、Bテーブルも同一の項目Aをもつため、
採番した値で更新するといった仕様です。



COUNT(*)の経過時間についてですが、
Aテーブルに関しては、00:00:00.28
Bテーブルに関しては、00:00:00.01
です。

一時表領域に関しては、
自動拡張になっています。

Aテーブル、Bテーブルともに同じ表領域ですが、
テーブルの更新時に影響が発生することもありますでしょうか?



引用返信 編集キー/
■38426 / inTopicNo.6)  Re[5]: Oracle 更新処理パフォーマンスについて
□投稿者/ 魔界の仮面弁士 (1150回)-(2009/07/14(Tue) 11:31:42)
No38422 (poro さん) に返信
> Aテーブルの項目Aの値を元に、
> 再度プログラム上で採番し(4万件分採番)、同一項目である項目Aに再設定するという仕様です。
採番の仕組みにもよりますが、例えば
 UPDATE TBL SET A = '456' WHERE A = '123'
 UPDATE TBL SET A = '123' WHERE A = '456'
のように、番号が入れ替わるような更新があった場合、
1レコードごとに更新する方法だと、矛盾が生じませんか?


先の回答のように、一時表に溜めておいて一括更新する事が
可能であるかどうかも検討してみてください。


> COUNT(*)の経過時間についてですが、
> Aテーブルに関しては、00:00:00.28
> Bテーブルに関しては、00:00:00.01
> です。
28倍の時間差――と思いきや、Bの方が早いのですか。

キャッシュに入る事による測定誤差もあるでしょうし、あまり意味は無いですが、
この「Aの全行スキャン(WHERE 句)に 28 ミリ秒かかる」という結果から、更新にかかる時間を概算すると:

とりあえず更新(SET 句)の時間をほぼゼロだと仮定しても、このペースで更新すると、
4 万回の更新作業なら、約186分という計算になりそうですね(15分あたり 3200件程度の処理速度)。



> 一時表領域に関しては、
> 自動拡張になっています。
サイズが不足していた場合、領域確保がパフォーマンスの低下を招く可能性がありますが、
充分なサイズが割り当ててあるならば、自動拡張で良いと思います。


> Aテーブル、Bテーブルともに同じ表領域ですが、
> テーブルの更新時に影響が発生することもありますでしょうか?
無関係とは言えませんが、今回の件では影響度は無視できる範囲だと思います。
引用返信 編集キー/
■38429 / inTopicNo.7)  Re[5]: Oracle 更新処理パフォーマンスについて
□投稿者/ やじゅ (1098回)-(2009/07/14(Tue) 11:58:02)
やじゅ さんの Web サイト
No38422 (poro さん) に返信
> AテーブルとBテーブルは、
> 親(ヘッダ情報)と子(明細情報)の関係にあります。
>
> Aテーブルの項目Aの値を元に、
> 再度プログラム上で採番し(4万件分採番)、同一項目である項目Aに再設定するという仕様です。
>
> また、Bテーブルも同一の項目Aをもつため、
> 採番した値で更新するといった仕様です。
>

この内容だけなら、私なら全てストアドにして、クライアントからはストアドを呼ぶ方式にします。
クライアントで1レコードずつ処理しているのかな。

実際の実装は、どのようにしているのでしょうか?
引用返信 編集キー/
■38430 / inTopicNo.8)  Re[5]: Oracle 更新処理パフォーマンスについて
□投稿者/ Jitta on the way (354回)-(2009/07/14(Tue) 12:02:30)
No38422 (poro さん) に返信
> ご返信ありがとうございます。
>
>>UPDATE Aテーブル SET 項目A = 'VALUE' WHERE 項目A = 'CONDITION'
> >パラメータ化はしておらず、単一行更新の SQL を繰り返し発行しているという事でしょうか?
>
> 説明不足でした、
> 申し訳ありません。
これは、.NET のプログラムでは、この通りの SQL 文を組み立てているのか、ということです。'Value','Condition'をパラメータ化すれば、若干速くなります。


> Aテーブルの項目Aの値を元に、
> 再度プログラム上で採番し(4万件分採番)、同一項目である項目Aに再設定するという仕様です。

ん?
これは、登録時に適当に付けられた番号を整形する、という意味でしょうか?それとも、なんらかのタイミングで、番号を全ての付け替えるという意味でしょうか?
後者なら、項目が持つ意味とか、もう少し考え直す必要があるような?

>
> また、Bテーブルも同一の項目Aをもつため、
> 採番した値で更新するといった仕様です。

カスケードをすれば良いのではないでしょうか?

引用返信 編集キー/
■38432 / inTopicNo.9)  Re[6]: Oracle 更新処理パフォーマンスについて
□投稿者/ poro (16回)-(2009/07/14(Tue) 14:35:19)
魔界の仮面弁士 さん
やじゅさん
Jitta on the way さん

ご返信ありがとうございます。

既存のプログラムでは、
クライアントで1レコードずつ処理していました。

会社の先輩にも、
駄目だしされました。。。

実は、今回、新人研修でデータを移行する(※SQLServer ⇒ Oracle)というプログラムを組むことになっており、
一度、全てのデータを移行した後、
各テーブルが持つNO項目などを新しい体系にして振りなおす(採番)など処理を行わなければなりません。

先ほどの例では、
ヘッダ情報と明細情報で説明しましたが、
他テーブルにもリンクするNo項目を持つため、
単純にカスケードできないような気がします。

とりあえず、
データなどは本番環境と同様のものを使用してよいとのことで、
私が作成したプログラムの出来次第では本番プロジェクトでも使ってもらえるとの事です。
私的な話になってしまい申し訳ありません。


移行時は、ダイレクトインサートパスなどを利用することにより高速に処理ができたのですが、
大量データの更新処理の仕組みについて迷っていました。

魔界の仮面弁士さんのご指摘の通り、
「採番後の値」と「採番前の値」を保持したテーブルを用意してみて、
教えていただいた更新SQLを投げたところうまくいきました。
(※他のテーブルで試して、10万件が10分ほど)
ありがとうございます。

しかし、
やじゅさんのご指摘をみると、
ストアドでのパフォーマンスも気になります。

別テーブルを参照し1回更新SQLを投げるやり方と
ストアドを利用した更新の方法は、どちらが実用的なのでしょうか?

やはりストアドを利用した方が劇的にパフォーマンスが良くなるのでしょうか?
初歩的な質問とは思いますが、よろしくお願いいたします。

引用返信 編集キー/
■38434 / inTopicNo.10)  Re[7]: Oracle 更新処理パフォーマンスについて
□投稿者/ はつね (1040回)-(2009/07/14(Tue) 16:13:35)
はつね さんの Web サイト
No38432 (poro さん) に返信
> 別テーブルを参照し1回更新SQLを投げるやり方と
> ストアドを利用した更新の方法は、どちらが実用的なのでしょうか?
>
> やはりストアドを利用した方が劇的にパフォーマンスが良くなるのでしょうか?

ケースバイケースだと思います。
データ移行(しかも1回きり)とかだとスタアド必要かなーという感じ。


なお、もしストアドを使うようなケースだったしてもストアドのようにサーバ側に
登録してつかうのではなく無名SQLブロックにした方がいいでしょう。

#つまり質問時に「データ移行を行っている」という情報も重要な情報ということですね。

引用返信 編集キー/
■38532 / inTopicNo.11)  Re[8]: Oracle 更新処理パフォーマンスについて
□投稿者/ poro (17回)-(2009/07/17(Fri) 19:01:26)
返信が遅くなってしまい、
申し訳ありません。

はつねさん

返信ありがとうございます。
とりあえず、ストアドではやらない方針になりました。


現状、魔界の仮面弁士さんの案で処理を行っています。

しかし、やはりパフォーマンスで問題が起こっています。

1万件程度ほどのデータであれば、数分で更新が完了するのですが、
30万件になると2〜3時間処理時間がかかってしまいました。

大量件数のUPDATE時に
メモリ(SGA or PGA)領域などを利用するとは思いますが、

実行対象SQLが遅くなる理由として、
大量のメモリを使用し、
SWAPしてしまい処理が遅くなったりすることがあるのでしょうか?

また、パフォーマンスを改善したいのですが、
何かいい方法はありますでしょうか?
(※DBパラメータを変更することはできないため、クライアント側で改善方法を探しています。
  SWAPすることがあるのであれば、1万件単位に処理を行う予定なのですが…
  その場合は、はつねさんの言うように無名SQLブロックも考えてみたいです。)


***************************************
(実行SQL)
UPDATE
TABLE_A
SET
ITEM_X = (SELECT
ITEM_Z
FROM
TABLE_B
WHERE
ITEM_Y = A.ITEM_X)
WHERE
EXISTS(SELECT
ITEM_Y
FROM
TABLE_B
WHERE
ITEM_Y = A.ITEM_X)
***************************************
引用返信 編集キー/
■38533 / inTopicNo.12)  Re[9]: Oracle 更新処理パフォーマンスについて
□投稿者/ poro (18回)-(2009/07/17(Fri) 19:04:18)
***************************************
(実行SQL)
UPDATE
  TABLE_A
SET 
  ITEM_X = (SELECT 
              ITEM_Z
            FROM 
              TABLE_B
            WHERE 
              ITEM_Y = A.ITEM_X)
WHERE
	EXISTS(SELECT 
	         ITEM_Y
	       FROM 
	         TABLE_B
	       WHERE 
 	         ITEM_Y = A.ITEM_X)
***************************************

引用返信 編集キー/
■38540 / inTopicNo.13)  Re[10]: Oracle 更新処理パフォーマンスについて
□投稿者/ やじゅ (1109回)-(2009/07/17(Fri) 21:34:54)
やじゅ さんの Web サイト
2009/07/18(Sat) 02:17:19 編集(投稿者)

No38533 (poro さん) に返信

質問事項を整理し直さないと、わかりにくくなってますね。
前の4万件から30万件に変わっているのと、採番したテーブルはどれにあたるとか
一時テーブルを作成している箇所が遅いとか、その後の更新処理が遅いのとか

採番用の一時テーブルを作成が、30万件となるなら、それは遅いだろう。
一時テーブルを作成後に更新する処理は30万件だとしてもそんなにかかる
ことはないと思うんだよね。

無名ブロックSQLかストアドにすれば、採番処理と更新処理を一括で出来るから
速度は向上するんでないかな。

引用返信 編集キー/
■38553 / inTopicNo.14)  Re[11]: Oracle 更新処理パフォーマンスについて
□投稿者/ poro (19回)-(2009/07/18(Sat) 12:40:38)
やじゅさん

ご返信ありがとうございます。


整理しますと、
データ移行(SQLServer→Oracle)が完了したあと、
移行後のテーブルから(Oracle側から)情報を抽出し採番を行います。

採番の情報は、採番前の値と採番した値を保持します。
データ登録時は、ダイレクトインサートパスを用いることで

データファイルに直接データを出力するため、
DB側での通常登録時の余計なコストを省略でき、
データ登録に置いてはストアドよりも効率がいいです。
(※時間はデータサイズにもよりますが、100万件登録で90秒ほどであるため、
  データ件数については全く心配していません。)

その後、採番した項目をもつ複数のテーブルの同項目に対して
更新処理を行わなければなりません。

パフォーマンスで問題となっているのは、
更新処理のときです。

以前投降したSQL(※魔界の仮面弁士さん案)実行時に
数万件なら運用に耐えれるスピードですが、
数十万件であると、使えるレベルではありませんでした。

また、報告している更新データ件数は、
具体性を出すものであったため、
コロコロ変更してしまいましたが、
少量(数万)と大量(数十万)と考えていただいて構いません。

問題は、やじゅさんが返信していただいている、以下の部分がとても遅いようです。
>一時テーブルを作成後に更新する処理は30万件だとしてもそんなにかかる
>ことはないと思うんだよね。

よって、
疑問に思っているところは、
データベース作成時に割り当てたメモリを
大量データの更新処理で使い切ってしまい
スワップすることなどがあるの…???と考えていますが、

実際に、
大量データの更新時にOracle側のメモリ使用の問題で遅くなることはあるのでしょうか
(※具体的な内容は、前投稿参照)

読みにくく、
まとまりのない文章になってしまい、
誠に申し訳ありません。
ご協力していただけると嬉しいです、
よろしくお願いいたします。
引用返信 編集キー/
■38554 / inTopicNo.15)  Re[12]: Oracle 更新処理パフォーマンスについて
□投稿者/ やじゅ (1114回)-(2009/07/18(Sat) 14:27:59)
やじゅ さんの Web サイト
No38553 (poro さん) に返信

整理ありがとうございます。
現在の問題点としては、一時表の作成後の更新部分が遅いということですね。

上記投稿のSQLであれば、TABLE_Bが一時表であると考えると、
一時表のインデックスをITEM_Yに付けるとか(既に付けてあるのかな?)

EXISTS(SELECT ITEM_Y FROM TABLE_B
↓
EXISTS(SELECT 1 FROM TABLE_B   '1 or *
http://www.geocities.jp/mickindex/database/db_optimize.html

カーソルを使うとか、サブクエリとカーソルループでどの程度の差があるかなー?
CURSORのとこでINNER JOIN TABLE_A があってもいいかも・・・

下記は未検証です。

DECLARE
    CURSOR c IS
        SELECT ITEM_Y,ITEM_Z FROM TABLE_B
BEGIN
    FOR r IN c LOOP
        UPDATE TABLE_A A 
        SET A.ITEM_X = r.ITEM_Z
        WHERE r.ITEM_Y = A.ITEM_X
    END LOOP;
END;

引用返信 編集キー/
■38623 / inTopicNo.16)  Re[13]: Oracle 更新処理パフォーマンスについて
□投稿者/ poro (20回)-(2009/07/21(Tue) 14:36:22)
やじゅ さん

ご返信ありがとうございます。

>
> 上記投稿のSQLであれば、TABLE_Bが一時表であると考えると、
> 一時表のインデックスをITEM_Yに付けるとか(既に付けてあるのかな?)
>

インデックスは付けていませんでしたので、
やじゅさんのご指摘通りの項目にインデックスを付けて
更新処理を行ったところ、劇的に更新速度が上がりました。

30万件で2時間かかっていたのが、
2〜3分で処理が終わりました。

EXISTS部分がボトルネックになっていたようです。
インデックスの付与でここまで劇的に変わるものなのかと正直驚いております。
結果も正常に更新できているようです。

皆さんのお陰で、
無事に研修プログラムも終わりに近づいてきました。

会社の先輩にも、
私が作成したプログラムの仕組みをそのまま活かして使用できそうだと、
かなり評価していただくことができました。

ありがとうございます。
引用返信 編集キー/
■38658 / inTopicNo.17)  Re[14]: Oracle 更新処理パフォーマンスについて
□投稿者/ Jitta (584回)-(2009/07/22(Wed) 22:24:53)
脱線失礼。

> インデックスの付与でここまで劇的に変わるものなのかと正直驚いております。
 インデックスを付けていない表を SELECT で取り出すと、たいていの場合、INSERT した順に表示されます。これを見て、「テーブルには、データが登録された順に格納されている」と、“勘違い”している人が多く見られます。
 データベースが扱うのは、データの集合です。この“集合”に、“順序”というものはありません。袋の中にあるボールを取り出しながら、何らかの条件に従っているかどうか見分けるようなものです。この説明だと、SELECT するごとに表示される順序は異なる事になります。実際、INSERT, DELETE を繰り返すと、取り出す順序が異なる現象に出くわします。
 さて、インデックスを付けると、その「順序のない集合」に順序を付けることになります。そして、付けた順序に従って分類がされている状態になります。そうすると、インデックスが付いていることで、インデックスを元にした取り出しが早くなることが理解できるでしょうか。

 ところが、何でもかんでもインデックスを付ければいいというものではありません。インデックスが付いている以上、どこかでインデックスを更新することが必要になります。この更新のタイミングは、たいてい、INSERT, DELETE, UPDATE が行われたときになります。その為、インデックスが付いた表を更新すると(インデックスに指定されている項目を更新すると)、インデックスの更新を行うために、若干時間がかかります。
 この、「更新処理で、インデックスの更新に若干時間がかかる」ことを理由に、「インデックス使用禁止」という人がいます。import するときにも、かなり時間をとられますし(設定で、一時的に無効に出来る)。しかし、実際には、表に対する取得と更新のどちらが多いのか、どの程度の時間がかかることが許されるのか、などを考慮して、インデックスを作成するべきです。参照することはほとんど無い、記録としての意味しかないような表であれば、インデックスを作成する必要はないでしょう。反対に、マスター表であれば、注意深くインデックスを作成しておく方がよいでしょう。

引用返信 編集キー/
■38665 / inTopicNo.18)  Re[15]: Oracle 更新処理パフォーマンスについて
□投稿者/ なちゃ (316回)-(2009/07/22(Wed) 22:51:49)
まあ簡単にイメージするには、
30万項目が順不同に載った辞書から特定の項目があるか調べるのと、
30万項目がアルファベット順に載った辞書から特定の項目があるか調べるのと、
どのくらい時間に差が出るか想像してみればいいと思います。

引用返信 編集キー/
■38757 / inTopicNo.19)  Re[16]: Oracle 更新処理パフォーマンスについて
□投稿者/ poro (21回)-(2009/07/24(Fri) 16:32:30)
Jittaさん
なちゃさん

ケースバイケースですね。
勉強になります、ありがとうございます。

ちなみに、
インデックスの付与だけでなく、
開発環境のOracle側のパラメータのメモリ使用項目値なども変更したところ、
さらに早くなりました。

本当に、
ありがとうございました。



引用返信 編集キー/


トピック内ページ移動 / << 0 >>

このトピックに書きこむ

過去ログには書き込み不可

管理者用

- Child Tree -