小さい頃はエラ呼吸

いつのまにやら肺で呼吸をしています。


Oracleで大量データのinsertとdeleteを繰り返すと遅くなる理由

はじめに

Oracle DBのテーブルに対して、ストレステストや性能テスト目的で大量のデータのinsert、deleteを繰り返すと、データ量が少ないにもかかわらず、レスポンスが極端に悪くなることがあります。
この記事では、その理由と対策についてまとめてみました。

10日でおぼえる Oracle11g入門教室(DVD付)
井上 賢一郎
翔泳社
売り上げランキング: 159,156

insertで上昇するハイウォーターマーク


photo credit: V31S70 via photopin cc
テーブルに対して大量データをinsertすると、ハイウォーターマーク(以下、HWM)が上がります。
HWMとは、データがここの位置まであるという場所を指し示すポインタです。目盛りのあるコップに水が入っている状態をイメージするとわかりやすいかと思いますが、水の最高位がHWMです。
insert文を発行することによって少しずつHWMが上がっていきます。

deleteでハイウォーターマークは下がらない


photo credit: Dusty J via photopin cc
実はこのHWMですが、delete文を発行しただけでは下がりません。コップの水をすべて捨てたとしても、最高位のマーキングが元に戻らない状態です。
HWMは、常にデータの最高位を指し示すわけではなく、過去にここまでデータが格納されていた位置を指し示しているというわけです。

ハイウォーターマークの上昇で検索が遅くなる

Oracleはデータを検索(select)する際、テーブルの先頭からHWMまでを検索対象とします。
大量データのinsertによって、HWMがあがり、Oracleが検索対象とする範囲が広がっていきます。

しかし、delete文を発行してもHWMが下がらないため、データ量に関係なく、検索する範囲だけは広がったままという状態になります。

このため、性能テストなどで大量データのinsert、deleteを繰り返していると、データをないにもかかわらず大量データがあるような振る舞いになる場合があります。

ハイウォーターマークを下げる

HWMを下げるには、以下のような方法があります。

  • deleteではなく、truncateを使う
  • テーブルをdrop後、再度create tableする

データの切り捨てまたはテーブルの再作成によってHWMはリセットされます。

おわりに

Oracle DBで性能テストを行う場合には、以下のような点に注意すると良いです。

  • 大量データをdeleteしてもHWMは下がらない
  • HWMを下げるには、データの切り捨てまたはテーブルの再作成が必要
  • データ削除→テーブル削除→テーブル作成→データ挿入の順で実施する