SYSAUX表領域が肥大化した際の対処方法

オラクルデータベースの基本
2025.02.11

1. SYSAUX表領域とは

SYSAUX表領域は、Oracleデータベースの補助的な表領域であり、主に以下のコンポーネントのデータを格納します。

  • AWR（自動ワークロードリポジトリ）
  • Statspack
  • Oracle Text
  • Oracle Streams
  • SQL Tuning Advisor
  • その他のデータベースコンポーネント

SYSAUX表領域が肥大化すると、ディスク領域を圧迫し、データベースのパフォーマンスにも影響を与える可能性があります。本記事では、その対処方法について解説します。

💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？

Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？

2. SYSAUX表領域の使用状況を確認する

SYSAUX表領域のサイズや使用率を確認するには、以下のSQLを実行します。

SELECT tablespace_name, file_name, bytes/1024/1024 AS size_mb, maxbytes/1024/1024 AS max_size_mb
FROM dba_data_files
WHERE tablespace_name = 'SYSAUX';

また、SYSAUX表領域内のコンポーネントごとの使用状況を確認するには、以下のSQLを使用します。

SELECT occupant_name, space_usage_kbytes/1024 AS space_usage_mb
FROM v$sysaux_occupants
ORDER BY space_usage_mb DESC;

この結果をもとに、どのコンポーネントが表領域を大量に使用しているかを特定します。

SQL> SELECT tablespace_name, file_name, bytes/1024/1024 AS size_mb, maxbytes/1024/1024 AS max_size_mb
  2  FROM dba_data_files
  3  WHERE tablespace_name = 'SYSAUX';

TABLESPACE_NAME      FILE_NAME                                      SIZE_MB MAX_SIZE_MB
-------------------- ------------------------------------------- ---------- -----------
SYSAUX               /u01/app/oracle/oradata/V19/sysaux01.dbf          760  32767.9844

SQL> SELECT occupant_name, space_usage_kbytes/1024 AS space_usage_mb
  2  FROM v$sysaux_occupants
  3  ORDER BY space_usage_mb DESC;

OCCUPANT_NAME                                      SPACE_USAGE_MB
-------------------------------------------------- --------------
SDO                                                      231.8125
XDB                                                        68.375
SM/AWR                                                    60.0625
SM/OTHER                                                   48.875
AO                                                        45.8125
AUDSYS                                                    44.4375
LOGMNR                                                         11
SM/OPTSTAT                                                 9.9375
SM/ADVISOR                                                 9.4375
WM                                                         6.5625
PL/SCOPE                                                   2.8125
SQL_MANAGEMENT_BASE                                        2.6875
TEXT                                                       2.6875
JOB_SCHEDULER                                              1.9375
STREAMS                                                    1.6875
LOGSTDBY                                                   1.5625
AUTO_TASK                                                   .5625
EM_MONITORING_USER                                           .375
SMON_SCN_TIME                                               .3125
ORDIM/ORDDATA                                                   0
ORDIM/ORDPLUGINS                                                0
ORDIM/SI_INFORMTN_SCHEMA                                        0
EM                                                              0
XSAMD                                                           0
ULTRASEARCH                                                     0
ULTRASEARCH_DEMO_USER                                           0
EXPRESSION_FILTER                                               0
TSM                                                             0
AUDIT_TABLES                                                    0
STATSPACK                                                       0
XSOQHIST                                                        0
ORDIM                                                           0

32行が選択されました。

2.1 v$sysaux_occupants で確認できる主なコンポーネント

コンポーネント名説明
AWR (Automatic Workload Repository)自動ワークロードリポジトリのスナップショットデータ
Statspackパフォーマンス監視のためのスナップショット
SQL Tuning AdvisorSQLチューニングアドバイザのデータ
SQL Access AdvisorSQLアクセスアドバイザのデータ
Oracle Textテキスト検索機能のメタデータ
Oracle Streamsストリームレプリケーションのメタデータ
XDBXMLデータベースのメタデータ
Workspace Managerバージョニングやワークスペース管理のデータ
Enterprise Manager RepositoryOracle Enterprise Manager のデータ
Unified Auditing統合監査のログデータ

この表を参考にして、不要なコンポーネントを削除または設定変更することで、SYSAUX表領域の使用量を管理できます。

AWR（Automatic Workload Repository）とは？Oracle性能分析の基礎から実践まで解説！
Oracle Databaseを扱ううえで、パフォーマンス問題に直面したことはありませんか？そんな時に頼りになるのが AWR（Automatic Workload Repository） です。AWRは、定期的にシステムの性能情報を自動で収...
oracle-master00000.com
2025.04.16

3. AWRスナップショットの確認と削除

3.1 保存されているAWRスナップショットの確認

SELECT snap_id, begin_interval_time, end_interval_time 
FROM dba_hist_snapshot 
ORDER BY begin_interval_time DESC;
SQL> SELECT snap_id, begin_interval_time, end_interval_time
  2  FROM dba_hist_snapshot
  3  ORDER BY begin_interval_time DESC;

   SNAP_ID BEGIN_INTERVAL_TIME                 END_INTERVAL_TIME
---------- ----------------------------------- -----------------------------------
        19 25-02-11 18:40:20.104               25-02-11 19:00:24.833
        18 25-02-11 18:20:15.495               25-02-11 18:40:20.104
        17 25-02-11 18:00:10.560               25-02-11 18:20:15.495
        16 25-02-11 17:40:05.824               25-02-11 18:00:10.560
        15 25-02-11 17:20:01.157               25-02-11 17:40:05.824
        14 25-02-11 17:00:56.584               25-02-11 17:20:01.157
        13 25-02-11 16:40:51.904               25-02-11 17:00:56.584
        12 25-02-11 16:20:47.237               25-02-11 16:40:51.904
        11 25-02-11 16:00:42.240               25-02-11 16:20:47.237
        10 25-02-11 15:40:37.504               25-02-11 16:00:42.240
         9 25-02-11 15:20:32.775               25-02-11 15:40:37.504
         8 25-02-11 15:00:27.847               25-02-11 15:20:32.775
         7 25-02-11 14:40:23.177               25-02-11 15:00:27.847
         6 25-02-11 14:20:18.185               25-02-11 14:40:23.177
         5 25-02-11 14:00:13.190               25-02-11 14:20:18.185
         4 25-02-11 13:40:08.265               25-02-11 14:00:13.190
         3 25-02-11 13:21:04.308               25-02-11 13:40:08.265
         2 25-02-11 13:10:03.000               25-02-11 13:21:04.308
         1 25-02-11 01:29:46.000               25-02-11 01:40:49.795

19行が選択されました。

💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？

Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？

3.2 AWRスナップショットの保持期間を確認

SELECT retention FROM dba_hist_wr_control;
SQL> SELECT retention FROM dba_hist_wr_control;

RETENTION
----------------------------------------
+00010 00:00:00.0

3.3 保持期間を短縮（例: 7日間に変更）

BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
    retention => 11520,  -- 8日（分単位）
    interval => 60       -- 1時間ごとにスナップショットを取得
  );
END;
/
SQL> BEGIN
  2    DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
  3      retention => 11520,  -- 8日（分単位）
  4      interval => 60       -- 1時間ごとにスナップショットを取得
  5    );
  6  END;
  7  /

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT retention FROM dba_hist_wr_control;

RETENTION
----------------------------------------
+00008 00:00:00.0

3.4 古いスナップショットの削除

exec dbms_workload_repository.drop_snapshot_range(1, 5);

例)SNAP_IDの1～5を削除する場合

この処理を定期的に実施することで、SYSAUX表領域の肥大化を防ぐことができます。

SQL> SELECT snap_id, begin_interval_time, end_interval_time
  2  FROM dba_hist_snapshot
  3  ORDER BY begin_interval_time DESC;

   SNAP_ID BEGIN_INTERVAL_TIME                 END_INTERVAL_TIME
---------- ----------------------------------- -----------------------------------
        19 25-02-11 18:40:20.104               25-02-11 19:00:24.833
        18 25-02-11 18:20:15.495               25-02-11 18:40:20.104
        17 25-02-11 18:00:10.560               25-02-11 18:20:15.495
        16 25-02-11 17:40:05.824               25-02-11 18:00:10.560
        15 25-02-11 17:20:01.157               25-02-11 17:40:05.824
        14 25-02-11 17:00:56.584               25-02-11 17:20:01.157
        13 25-02-11 16:40:51.904               25-02-11 17:00:56.584
        12 25-02-11 16:20:47.237               25-02-11 16:40:51.904
        11 25-02-11 16:00:42.240               25-02-11 16:20:47.237
        10 25-02-11 15:40:37.504               25-02-11 16:00:42.240
         9 25-02-11 15:20:32.775               25-02-11 15:40:37.504
         8 25-02-11 15:00:27.847               25-02-11 15:20:32.775
         7 25-02-11 14:40:23.177               25-02-11 15:00:27.847
         6 25-02-11 14:20:18.185               25-02-11 14:40:23.177
         5 25-02-11 14:00:13.190               25-02-11 14:20:18.185
         4 25-02-11 13:40:08.265               25-02-11 14:00:13.190
         3 25-02-11 13:21:04.308               25-02-11 13:40:08.265
         2 25-02-11 13:10:03.000               25-02-11 13:21:04.308
         1 25-02-11 01:29:46.000               25-02-11 01:40:49.795

19行が選択されました。

SQL> exec dbms_workload_repository.drop_snapshot_range(1, 5);

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT snap_id, begin_interval_time, end_interval_time
  2  FROM dba_hist_snapshot
  3  ORDER BY begin_interval_time DESC;

   SNAP_ID BEGIN_INTERVAL_TIME                 END_INTERVAL_TIME
---------- ----------------------------------- -----------------------------------
        19 25-02-11 18:40:20.104               25-02-11 19:00:24.833
        18 25-02-11 18:20:15.495               25-02-11 18:40:20.104
        17 25-02-11 18:00:10.560               25-02-11 18:20:15.495
        16 25-02-11 17:40:05.824               25-02-11 18:00:10.560
        15 25-02-11 17:20:01.157               25-02-11 17:40:05.824
        14 25-02-11 17:00:56.584               25-02-11 17:20:01.157
        13 25-02-11 16:40:51.904               25-02-11 17:00:56.584
        12 25-02-11 16:20:47.237               25-02-11 16:40:51.904
        11 25-02-11 16:00:42.240               25-02-11 16:20:47.237
        10 25-02-11 15:40:37.504               25-02-11 16:00:42.240
         9 25-02-11 15:20:32.775               25-02-11 15:40:37.504
         8 25-02-11 15:00:27.847               25-02-11 15:20:32.775
         7 25-02-11 14:40:23.177               25-02-11 15:00:27.847
         6 25-02-11 14:20:18.185               25-02-11 14:40:23.177

14行が選択されました。

4. Statspackのデータの確認と削除

4.1 保存されているStatspackのスナップショットを確認

SELECT snap_id, snap_time 
FROM stats$snapshot 
ORDER BY snap_time DESC;
SQL> alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';

セッションが変更されました。

SQL> SELECT snap_id, snap_time
  2  FROM stats$snapshot
  3  ORDER BY snap_time DESC;

   SNAP_ID SNAP_TIME
---------- -------------------
        31 2025/02/11 19:28:08
        26 2025/02/11 18:58:08
        25 2025/02/11 18:28:08
        24 2025/02/11 17:58:08
        23 2025/02/11 17:28:08
        22 2025/02/11 16:58:08
        21 2025/02/11 16:28:08
        14 2025/02/11 15:58:08
        13 2025/02/11 15:28:08
        12 2025/02/11 14:58:08
        11 2025/02/11 14:28:09
         1 2025/02/11 13:58:09

12行が選択されました。

4.2 Statspackのスナップショット数を確認

SELECT COUNT(*) FROM stats$snapshot;

4.3 古いスナップショットを削除

EXEC statspack.purge(i_num_days => 7);

Statspackのスナップショットを削除することで、SYSAUX表領域の使用量を減らすことができます。

5. 未使用のコンポーネントを削除

SYSAUX表領域に格納されている不要なコンポーネントを削除することで、空き領域を確保できます。

5.1 使用されていないコンポーネントの確認

SELECT occupant_name, space_usage_kbytes/1024 AS space_usage_mb
FROM v$sysaux_occupants
ORDER BY space_usage_mb DESC;

5.2 その他のコンポーネントの削除

使用していない機能がある場合は、適宜コンポーネントを無効化または削除してください。

6. まとめ

SYSAUX表領域が肥大化した場合、以下の対策を実施することで、使用量を削減できます。

  1. SYSAUX表領域の使用状況を確認する
  2. AWRスナップショットの確認・削除を行う
  3. Statspackの古いデータを削除する
  4. 不要なコンポーネントを削除する
  5. SYSAUX表領域を縮小する

これらの対応を定期的に実施することで、データベースのパフォーマンスを維持し、ディスク領域を適切に管理できます。

適宜、データベースの運用方針に合わせて設定を調整し、無駄な領域を削減することが重要です。

[参考]
Oracle Databaseデータベース管理者ガイド 19c

OracleデータベースのSYSTEMとSYSAUX表領域について解説
Oracleデータベースを正しく運用するためには、SYSTEM表領域とSYSAUX表領域の理解が不可欠です。これらはデータベースの「心臓」と「補助循環器」ともいえる存在で、どちらも欠かせません。本記事では、初心者向けの基礎解説から、トラブル...
oracle-master00000.com
2024.11.19

💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？

Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？

コメント

タイトルとURLをコピーしました