Oracleの一時表領域のお話

とあるOracle関連のトラブルに出くわし、一時表領域について学んだのでメモ。
Oracle Master Silver取っていようが、普段の仕事でDB周り触ってないから基礎知識が全然ありませんでしたという話。

そもそも一時表領域とは

Oracleの表領域には3つの種類がある。

  • 永続表領域
  • UNDO表領域
  • 一時表領域

一時表領域は主に、SORT・JOIN・UNION等で中間結果を保存するために使われる。
ただし、これはPGA(=メモリ)上ではソートエリアが足りなかった場合。
PGAにおけるソート領域は初期化パラメータのSORT_AREA_SIZEで指定されている。

一時表領域はスパースファイルである。

スパースファイルとは、「ファイルサイズと実際のディスク使用量が異なる」ファイルのこと。

たとえば以下のように表領域を作成したとする。

#SYSAUX
CREATE TABLESPACE SYSAUX DATAFILE /db/admin/dbf/sysaux.dbf SIZE 500M ....
#SYSTEM
CREATE TABLESPACE SYSTEM DATAFILE /db/admin/dbf/system.dbf SIZE 500M ....
#TEMPORARY
CREATE TABLESPACE TEMP1 DATAFILE /db/admin/dbf/temp01.dbf SIZE 5000M TEMPORARY
#UNDO
CREATE UNDO TABLESPACE UNDO1 DATAFILE /db/admin/dbf/undo01.dbf SIZE 5000M ....

さらに各々の使用量がこのような感じだったとして、

  • SYSAUX 450M(90%)
  • SYSTEM 450M(90%)
  • TEMP1 1G(20%)
  • UNDO1 500M(10%)

ターミナル上で確認してみると

ls -lak
-rw-rw-r--  1 user01 group01  5120 10月 19 21:17 2014 sysaux.dbf
-rw-rw-r--  1 user01 group01  5120 10月 19 21:17 2014 system.dbf
-rw-rw-r--  1 user01 group01  512000 10月 19 21:17 2014 temp01.dbf
-rw-rw-r--  1 user01 group01  512000 10月 19 21:17 2014 undo01.dbf

とまぁこんな感じ。

しかし、これをduで確認してみると、

du -sk /db/admin/dbf/*
512000   /db/admin/dbf/sysaux.dbf
512000   /db/admin/dbf/system.dbf
1024000  /db/admin/dbf/temp01.dbf
5120000	 /db/admin/dbf/undo01.dbf

永続表領域のSYSAUX、SYSTEM、UNDO表領域のUNDO1は定義時の最大サイズと一致するが、一時表領域であるTEMP1が一致しない。
だけど、TEMPのサイズは「現在の一時表領域の使用サイズ」と一致する!

つまり一時表領域は作成当初は実際のディスク使用量が0で、使われた分だけディスクの空き領域を埋めていく。

Linux環境設定/実際にはディスクを消費していないファイル(スパースファイル)を作る方法 - Linuxと過ごす


一時表領域の中身と使われ方。

一時表領域は次の3つから構成される。

  • 未使用領域
  • 使用中領域
  • 再利用可能領域

未使用領域はそのままで、定義してから一度も利用していない部分。
表領域ができてから初めてディスクソートが行われるとここを使うことになる。

ソートを実行中、そのブロックは使用中領域にとなる。
例えばソートで500Mのディスク領域が必要なら、500Mが使用中領域になる。
と同時に一時表領域のデータファイルサイズが500Mとなる。

ソートが終わると使用中領域は開放され、再利用可能領域となる。
再利用可能領域は、「ディスクスペースとして取ってはいるけど、まだ誰もソートしてない(=未使用)領域」となる。
先ほど例に挙げた500Mのソートが終わった後に、200Mのソート領域が必要になったとするとそれは500Mの再利用可能領域から200Mを使用中領域にしてソートを行う。
終われば当然開放し、元通り500Mの再利用可能領域に戻る。

そのためデータファイルサイズは変わらない。
次にデータファイルサイズが広がるタイミングは、500Mを超えるソート領域が必要になった時。
つまり、表領域の使用量=過去必要になったソート領域の最大値=再利用可能領域(+未使用から使用中となった領域)
という感じ。

一時表領域の使用状況を確認する

一時表領域の使用状況はV$SORT_SEGMENTテーブルで確認することができる。

  • 全体使用量(≒再利用可能領域):TOTAL_BLOCKS
  • 使用中領域:USED_BLOCKS
  • 再利用可能領域―使用中領域:FREE_BLOCKS

当然ながら使用中領域は実際にソートが走っている間しか増えないため、SQL実行中に定期的に監視する必要がある。

この他V$SORT_SEGMENTを見ることで、大量のディスクソートを行っているSQLを特定することができる。

http://www.insight-tec.com/mailmagazine/ora3/vol044.html
V$SORT_SEGMENT

絵で見てわかるOracleの仕組み (DB Magazine SELECTION)

絵で見てわかるOracleの仕組み (DB Magazine SELECTION)