とある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)
- 作者: 小田圭二
- 出版社/メーカー: 翔泳社
- 発売日: 2006/06/21
- メディア: 単行本
- 購入: 27人 クリック: 385回
- この商品を含むブログ (48件) を見る