SYSAUX 表領域の AWR による断片化の解消方法
今号では、SYSAUX 表領域の AWR による断片化の解消方法をご紹介します。是非ご一読ください。
SYSAUX 表領域の AWR による断片化解消方法
弊社のサポートセンターでは、SYSAUX 表領域の肥大化に関するお問い合わせをよく頂いております。SYSAUX 表領域が肥大化する主な原因は、自動ワークロード・リポジトリ（略称 AWR )が占める領域の断片化が進むことです。今号では AWR による SYSAUX 領域の断片化の解消方法を説明します。
AWR とは Database 統計情報のスナップショットを自動的に収集/管理する機能です。通常、AWR のスナップショットは、一定保存期間を過ぎると SYSAUX 表領域から削除されます。「定期的にデータが削除されるにも関わらず、なぜ SYSAUX 表領域は肥大化するのか」と思われる方は多くいらっしゃると思います。エクステントの獲得と解放を繰り返していくと連続領域が減り、解放した領域を効率的に再利用できなくなり、断片化が進みます。
その結果として SYSAUX 表領域が肥大化します。
以下に、断片化を解消する方法を紹介します。
具体的には断片化が発生していることを確認し、次にスナップショットを削除し、オブジェクトを移動することで断片化が解消します。
断片化が発生していることの確認方法については、データディクショナリビューのdba_data_files と dba_free_space の空き領域と状態を確認します。
そこから、使用済みの状態である領域と既に削除された領域のバイト数を確認します。そのバイト数が小さいほど再利用されることが少なくなり、断片化に繋がります。
次に断片化を解消するための、スナップショットの削除手順についてご案内します。
まず、V$SYSAUX_OCCUPANTS ビューから SM/AWR (*1) コンポーネントの領域サイズを確認します。
そして、スナップショットを削除し、改めて SM/AWR の領域サイズを確認すると、サイズが小さくなったことがわかります。なお、お客様の運営上の規則により、スナップショットを削除することが難しい場合は、実施いただかなくても問題はありません。
最後に、オブジェクトを移動する作業を実施することで、セグメント単位でサイズが縮小し、断片化が解消されます。
*1 SM/AWR とは AWR のスナップショット（統計情報）を保存する場所（デフォルトの保存期間は8日）です。
以降で、実行コマンドと結果をもとに 4ステップに分けて詳細に説明します。
- 1.断片化が発生していることの確認
- 単独の空き領域数の確認：SYSAUX 表領域の Free|Frags の数を確認します。
SQL> set pages 999 SQL> col tsname format a30 justify c heading 'Tablespace' SQL> col nfrags format 999,990 justify c heading 'Free|Frags' SQL> SELECT total.TABLESPACE_NAME tsname, D nfrags FROM ( SELECT SUM(bytes) A, tablespace_name FROM dba_data_files GROUP BY tablespace_name ) TOTAL, ( SELECT SUM(bytes) B, MAX(bytes) C, COUNT(bytes) D, tablespace_name FROM dba_free_space GROUP BY tablespace_name ) FREE WHERE total.TABLESPACE_NAME = free.TABLESPACE_NAME(+) AND total.TABLESPACE_NAME = 'SYSAUX';
以下は SQL 文を実行した結果の表示：Free Tablespace Frags ---------------- -------- SYSAUX 3 --------------- --------
- Free Frags 列を確認し、2以上の値(3)が表示されたため、フラグメンテーション（断片化）が発生していることがわかります。
- 2.SYSAUX 表領域の領域サイズの確認
- SYSAUX 表領域に作成されているコンポーネントの種類とそのコンポーネントで使用している領域サイズは、V$SYSAUX_OCCUPANTS ビューから確認することが可能です。
- 領域サイズの確認 ( SPACE_USAGE_MBYTES の単位は MB )：SPACE_USAGE_MBYTES 列で出力される値が、各コンポーネントで使用している領域サイズを示します。
SQL> SELECT SCHEMA_NAME, OCCUPANT_NAME, TRUNC(SPACE_USAGE_KBYTES/1024,1) "SPACE_USAGE_MBYTES" FROM V$SYSAUX_OCCUPANTS WHERE OCCUPANT_NAME = 'SM/AWR';
以下は SQL 文を実行した結果の表示：SCHEMA_NAM OCCUPANT_NAME SPACE_USAGE_MBYTES ---------- ---------------------- ------------------ SYS SM/AWR 86.8 ★ ---------- ---------------------- ------------------
コンポーネント SM/AWR のサイズを確認します。 - 3.AWR スナップショットの削除
- AWR の情報を削除する場合、DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE プロシージャを使用することで、スナップショットの ID を指定して削除を行うことが可能です。
- スナップショットの ID の確認：SYSAUX 表領域に保存されてる、スナップショットを確認します。
SQL> connect / as sysdba SQL> SELECT snap_id, begin_interval_time, end_interval_time FROM dba_hist_snapshot ORDER BY snap_id;
以下は SQL 文を実行した結果の一部を抜粋：SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME -------- ------------------------- ------------------------- 1 24-MAY-14 02.44.18.000 AM 24-MAY-14 04.00.24.788 AM 2 24-MAY-14 04.00.24.788 AM 24-MAY-14 05.00.29.489 AM 3 24-MAY-14 05.00.29.489 AM 24-MAY-14 06.00.34.283 AM 4 24-MAY-14 06.00.34.283 AM 24-MAY-14 07.00.39.026 AM 5 24-MAY-14 07.00.39.026 AM 24-MAY-14 08.00.43.757 AM ....... ....... -------- ------------------------- -------------------------
- スナップショットの取得した日付や時間帯を確認した上で、削除するスナップショットの範囲を絞り、SNAP_ID を確認します。
- スナップショットの削除：確認したスナップショットの ID をベースにスナップショットを削除します。
SQL> exec dbms_workload_repository.drop_snapshot_range(1, 50);
以上のコマンドを実施していただくとスナップショットは削除されます。
注：スナップショットを取得中、テーブルに行ロックがかかる場合があるため、同じタイミングでスナップショットの削除を実施しないでください。
領域サイズの確認 ( SPACE_USAGE_MBYTES の単位は MB )：SM/AWR のサイズが減少したことを確認します。SQL> SELECT SCHEMA_NAME, OCCUPANT_NAME, TRUNC(SPACE_USAGE_KBYTES/1024,1) "SPACE_USAGE_MBYTES" FROM V$SYSAUX_OCCUPANTS WHERE OCCUPANT_NAME = 'SM/AWR'; 以下は SQL 文を実行した結果の表示： SCHEMA_NAM OCCUPANT_NAME SPACE_USAGE_MBYTES ---------- ------------------------- ------------------ SYS SM/AWR 66.3 ★ ---------- ------------------------- ------------------
- 上記、SQL 文の実施結果から、SM/AWR （ ★ と付いてるコンポーネントのサイズ）を見ていただくと、AWR の情報が削除されたことを確認いただけます。
- 4.セグメント単位でのサイズの縮小方法
- SYSAUX 表領域に作成されているテーブルは、基本的にデータの削除を DELETE 文で行っています。そのため、例えばある一時期に大量のデータが格納された場合に、その後DELETE が行われてデータ量が減少した場合でもセグメントのサイズがデータ量に応じて小さくなることはありません。これについては索引の場合にも同じことが起こり得ます。そのため、領域の断片化に対しては、MOVE コマンドを実行することでの対処が必要です。
- セグメント単位で確認 ( SIZE(MB) の単位は MB ) :AWR 関連のセグメントは、WRH$、WRM$、または WRI$_SCH で始まる名称のものです。
SQL> SELECT SEGMENT_TYPE, OWNER, SEGMENT_NAME, PARTITION_NAME, BYTES/1048576 "SIZE (MB)" FROM dba_segments WHERE tablespace_name='SYSAUX' AND (segment_name LIKE 'WRH$%' OR segment_name LIKE 'WRM$%' OR segment_name LIKE 'WRI$_SCH%'); 以下は SQL 文を実行した結果の一部を抜粋： SEGMENT_TYPE OWNER SEGMENT_NAME PARTITION_NAME SIZE(MB) ---------- ------------------------- ------------------ TABLE SYS WRH$_FILESTATXS_BL .0625 TABLE SYS WRH$_TEMPSTATXS .0625 TABLE SYS WRH$_DATAFILE 100 ★ INDEX SYS WRH$_DATAFILE_PK 30 ★ TABLE SYS WRH$_TEMPFILE .0625 TABLE SYS WRH$_COMP_IOSTAT .0625 TABLE SYS WRH$_IOSTAT_FUNCTION .0625 TABLE SYS WRH$_IOSTAT_FUNCTION_NAME .0625 TABLE SYS WRH$_IOSTAT_FILETYPE .0625 TABLE SYS WRH$_IOSTAT_FILETYPE_NAME .0625 TABLE SYS WRH$_IOSTAT_DETAIL .0625 TABLE SYS WRH$_SQLSTAT_BL .0625 ....... ....... ---------- ------------------------- ------------------
- 上記、SQL 文の実施結果から、セグメント WRH$_DATAFILE と WRH$_DATAFILE_PK のサイズを確認します。
- テーブルのセグメントに move コマンドの実行：
SQL> alter table <対象オブジェクト> move tablespace SYSAUX; (例) SQL> alter table SYS.WRH$_DATAFILE move tablespace SYSAUX;
- Table altered. が表示されたことを確認
- インデックスのセグメントに rebuild コマンドの実行：
SQL> alter index <対象オブジェクト> rebuild tablespace SYSAUX; (例) SQL> alter index SYS.WRH$_DATAFILE_PK rebuild tablespace SYSAUX;
- Index altered. が表示されたことを確認
- セグメントの確認：
SQL> SELECT SEGMENT_TYPE, OWNER, SEGMENT_NAME, PARTITION_NAME, BYTES/1048576 "SIZE (MB)" FROM dba_segments WHERE tablespace_name='SYSAUX' AND (segment_name LIKE 'WRH$%' OR segment_name LIKE 'WRM$%' OR segment_name LIKE 'WRI$_SCH%');
- 以下は SQL 文を実行した結果の一部を抜粋：
SEGMENT_TYPE OWNER SEGMENT_NAME PARTITION_NAME SIZE(MB) ------------- ----- ------------------------- --------------- TABLE SYS WRH$_FILESTATXS_BL .0625 TABLE SYS WRH$_TEMPSTATXS .0625 TABLE SYS WRH$_DATAFILE 50 ★ INDEX SYS WRH$_DATAFILE_PK 15 ★ TABLE SYS WRH$_TEMPFILE .0625 TABLE SYS WRH$_COMP_IOSTAT .0625 TABLE SYS WRH$_IOSTAT_FUNCTION .0625 TABLE SYS WRH$_IOSTAT_FUNCTION_NAME .0625 TABLE SYS WRH$_IOSTAT_FILETYPE .0625 TABLE SYS WRH$_IOSTAT_FILETYPE_NAME .0625 TABLE SYS WRH$_IOSTAT_DETAIL .0625 TABLE SYS WRH$_SQLSTAT_BL .0625 ....... ....... ------------- ----- ------------------------- ---------------
- 上記、SQL 文の実施結果から、セグメント WRH$_DATAFILE と WRH$_DATAFILE_PK のサイズが小さくなったことが確認できます。
まとめ
AWR を使って収集した情報は、保存期間に合わせて自動的に削除されるようになっています。ただし、エクステントの獲得と解放を繰り返していく内に、断片化が進むため、再利用できなくなる可能性があります。そのため、DB 構築時に SYSAUX 表領域の適切なサイズを見積もり、運用中は定期的に DB の診断を行うことをお勧めします。
(オラクル事業部 技術担当サポートセンターG 福富)