[Oracle] ローカル管理表領域のパフォーマンスと領域使用量

ローカル管理表領域を使うと、エクステントをビットマップを使って管理するため、エクステントが多くなってもパフォーマンスが悪化しないというメリットがあるといわれています。

実際、書籍などに示されているパフォーマンステストの結果を見ると、ディクショナリ管理表領域よりはかなりパフォーマンスがよいようです。

というわけで、これからはローカル管理表領域を使わない手はないでしょう．．．

ですが、エクステント数が増加したり、断片化が発生した場合の領域使用量やパフォーマンスはどうなんでしょう？

簡単なテストをして確かめてみました。

まずは、テスト環境作りですが．．．

その前に、ローカル管理表領域のエクステント管理方法には、自動割り当て（AUTOALLOCATE）、均一割り当て（UNIFORM）の２通りがあるので、この両者の違いについて調べてみました。

［自動割り当て］

エクステントの割り当てをOracleが管理します。最小エクステントサイズは64Kで、その後エクステントの増加に応じてエクステントサイズも1M、2Mと段々大きくなります。様々なサイズのオブジェクトが多数格納される場合や、データ量の予測が難しい場合や、厳密な領域制御が必要ない場合に選択することが推奨されています。ただし、エクステントサイズがバラバラになるため、エクステントの追加・削除が繰り返されると表領域内に使用できない領域が増加し、領域使用効率が低下するようです。

［均一割り当て］

エクステント数の増減に関わらず、均一サイズのエクステントを割り当てます。厳密な領域制御が必要な場合や、エクステントの数とサイズを正確に予測できるような場合に選択することが推奨されています。エクステントサイズが均一であるため、エクステントの追加・削除が繰り返されても表領域内に使用できない領域はできず、領域使用効率も低下しません。

と、こんな感じです。

領域使用効率やパフォーマンスに違いがありそうですので、一応両方のエクステント管理方法の表領域を作って試してみましょう。

では、早速自動割り当てと均一割り当てのローカル管理表領域を１つずつ作成してみます。

・エクステント自動割り当てのローカル管理表領域

CREATE TABLESPACE TEST_AUTO
    DATAFILE 'E:\ORACLE\ORADATA\ORCL\TEST_AUTO.DBF'
    SIZE 500M REUSE
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE
    SEGMENT SPACE MANAGEMENT AUTO;

・エクステント均一割り当てのローカル管理表領域

CREATE TABLESPACE TEST_UNIFORM
    DATAFILE 'E:\ORACLE\ORADATA\ORCL\TEST_UNIFORM.DBF'
    SIZE 500M REUSE
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
    SEGMENT SPACE MANAGEMENT AUTO;

でもって、それぞれの表領域に同一レイアウトのテーブルを作成してみます。

CREATE TABLE TEST_AUTO (
  ID    NUMBER(10),
  NAME  VARCHAR2(100),
  VALUE VARCHAR2(1000),
  CONSTRAINT PK_TEST_AUTO PRIMARY KEY ( ID )
    USING INDEX TABLESPACE TEST_AUTO
) TABLESPACE TEST_AUTO;

CREATE TABLE TEST_UNIFORM (
  ID    NUMBER(10),
  NAME  VARCHAR2(100),
  VALUE VARCHAR2(1000),
  CONSTRAINT PK_TEST_UNIFORM PRIMARY KEY ( ID )
    USING INDEX TABLESPACE TEST_UNIFORM
) TABLESPACE TEST_UNIFORM;

これであとはそれぞれのテーブルに対して、全く同じ操作を行うことで、パフォーマンスや表領域使用量の違いを見けばいいでしょう。

まず、作成したそれぞれのテーブルに適当な内容の30万件のレコードを登録してみます。

INSERT INTO [TEST_AUTO|TEST_UNIFORM] ( ID,NAME,VALUE ) SELECT ～;

それぞれの処理時間と登録後の表領域使用量はこんな感じです。

エクステント管理	処理時間（秒）	登録後の表領域使用量（MB）
自動割り当て	31.08	176
均一割り当て	33.00	176

ほぼ同じと考えていいでしょう。

ちなみに表領域使用量は以下のSQLで算出しました。

SELECT TABLESPACE_NAME,SEGMENT_NAME,BYTES/1024/1024 
  FROM DBA_SEGMENTS 
  WHERE TABLESPACE_NAME IN ('TEST_AUTO','TEST_UNIFORM')

次は両テーブルをアナライズした後に、インデックスを使うパターンと使わないパターンの検索を行ってみます。

・インデックスを使用する検索

  SELECT COUNT(*) FROM [TEST_AUTO|TEST_UNIFORM] WHERE ID BETWEEN 1001 AND 2000;

・インデックスを使用しない検索

  SELECT COUNT(*) FROM [TEST_AUTO|TEST_UNIFORM] WHERE NAME LIKE '%ABC%'

エクステント管理	インデックスを使用する検索（秒）	インデックスを使用しない検索（秒）
自動割り当て	0.00	4.05
均一割り当て	0.00	3.00

インデックスを使用する検索は瞬殺ですが、インデックスを使用しない検索では均一割当ての方が若干速いようです。

では次に登録した30万レコードのうち10万件のレコードを削除してみます。

DELETE FROM [TEST_AUTO|TEST_UNIFORM] WHERE MOD( ID, 100 ) <= 33;

エクステント管理	処理時間（秒）
自動割り当て	37.03
均一割り当て	41.00

今度は逆に自動割当の方が若干速いようです。

なんででしょう？

しかしまぁ、ともかくこれでかなり断片化が発生しているはずです。

それじゃあ、この状況で先程の検索を行うとどうなるでしょう？

エクステント管理	インデックスを使用する検索（秒）	インデックスを使用しない検索（秒）
自動割り当て	0.00	18.05
均一割り当て	0.00	10.02

断片化効果なのか、レコード件数が2/3になっているにもかかわらず、インデックスを使用しない検索が著しく遅いです。

10万件削除前の3～4倍遅くなっています。

自動割り当ての方は特に遅いです。

この調子じゃあ、登録も相当遅くなっているのではないでしょうか。

再度30万件を登録してみよう。

エクステント管理	処理時間（秒）	登録後の表領域使用量（MB）
自動割り当て	76.07	296
均一割り当て	72.02	292

やっぱり遅くなっています。

これも自動割当の方が若干遅いし、領域使用量にも差が出始めています。

では、ここで20万件を削除して最初に登録したときと同じ30万件に戻してみます。

DELETE FROM [TEST_AUTO|TEST_UNIFORM] WHERE MOD( ID, 100 ) > 66;

そして、もう一度検索してみるとどうなるでしょう？

エクステント管理	インデックスを使用する検索（秒）	インデックスを使用しない検索（秒）
自動割り当て	0.00	31.03
均一割り当て	0.00	28.07

ますます遅くなっています．．．

これは断片化と高水位標（ハイウォーターマーク）という2つの問題が重なったためでしょう。

高水位標は過去最大ここまでデータが入りましたよというマークで、どれだけレコードを削除してテーブルの中身がスカスカになっても高水位標の位置は変わりません。

で、全表走査を行うような検索を行うと、レコードのあるなしに関わらず、高水位標の位置までデータを読み込んでしまうために検索効率が悪くなってしまうのです。

では一度大量のレコードが登録されたテーブルは、ずっとパフォーマンスが悪いまま運用を続けなければならないのかというとそうでもありません。

高水位標はテーブルを再構築すると再設定されるます。

さらにテーブルを再構築すると断片かも解消されます。

では、ここでテーブルの再構築を実行してみましょう。

ALTER TABLE [TEST_AUTO|TEST_UNIFORM] MOVE TABLESPACE [TEST_AUTO|TEST_UNIFORM];

ALTER INDEX PK_TEST_AUTO REBUILD;

ちなみに、ここでインデックスの再構築を行っているのは、テーブルを再構築することによって、そのテーブルに設定されていたインデックスが無効になってしまうからです。

もし、インデックスを再構築しなくても、その時点ではエラーや警告は出ず、テーブル再構築後に始めてそのインデックスを使う時にエラーが出るため、実運用環境においてテーブルを再構築を行うときには注意しなければないません。

エクステント管理	処理時間（秒）	再構築後の表領域使用量（MB）
自動割り当て	26.01	184
均一割り当て	30.00	177

断片化が解消されたため、表領域使用量は最初に30万件登録した直後と変わらないレベルまで小さくなっています。

自動割り当ての方が若干領域使用量が多めになっているのは、両割り当て方法の性質の違いからくるものなのでしょう。

ともかくこれで断片化と高水位標の問題は解消されたはずですから、もう一度検索を行ってみましょう。

エクステント管理	インデックスを使用する検索（秒）	インデックスを使用しない検索（秒）
自動割り当て	0.00	4.06
均一割り当て	0.00	2.02

すばらしい！

最初に30万件登録した直後と同じレベルまで改善しているではないですか。

ってまぁ、表をきれいに作り直したんだから当たり前といえば当たり前なんですが．．．

以上の結果からもわかるように、いくらローカル管理標領域はパフォーマンスがよいといっても、大量データの登録・削除を繰り返せば、断片かも発生するし、高水位標も高くなるので次第にパフォーマンスは落ちていきます。

上記のテストではあえて断片化が起こりやすいような操作を行ったため、パフォーマンスの低下がよりはっきりと出ましたが、実際の運用環境においても同程度、もしくはそれ以上にパフォーマンスが低下するケースもあるでしょう。

このようなパフォーマンスの低下を防ぐには、断片化が発生しにくいような運用を心掛けるとともに、定期的にテーブルを再構築することも検討する必要があるでしょう。

（このあたりの話はまたの機会に．．．）


エクステント管理方法については上記のテスト結果を見る限りでは、パフォーマンス、領域使用量の両面において均一割り当ての方が自動割り当てよりも優れているようです。

ただ、では、どんなときでも均一割り当てを使えばいいのかというと、そこはなんとも言えないところです。

例えば、極端に見積もりサイズの異なるテーブルが同一表領域に混在している場合、均一割り当てのサイズを小さく設定すると大きなテーブルのエクステント数がやたら多くなりますし、逆に大きく設定すると、小さなテーブルに必要以上の大きさのエクステントが割り当てられてしまい、領域使用効率が低下してしまいます。

このように大きなテーブルと小さなテーブルが同一表領域に混在するような場合は、自動割り当ての方がむしろ適していると言えるのかもしれません。

あるいは、大きなテーブル用の表領域と小さなテーブル用の表領域を分けて、それぞれ均一割り当てのサイズを適切に設定するというのも一つの手ですが、管理は大変になるでしょう。

ということで、結局はエクステント管理方法についてはマニュアルにある通り、テーブルサイズの見積もりがどの程度正確にできるか、パフォーマンスや領域使用量の制御をどの程度厳密に行う必要があるかなどを考慮して臨機応変に選択していくという結論になってしまうようです。