Oracle 12.2のSYSAUX急増をSM/OPTSTATまで切り分けた記録

先日、運用を担当している Oracle Database 12.2を使っている24時間稼働のシステムで、SYSAUX表領域の空き容量が急に減りました。

気付いたときにはもう余裕がほとんどなかったので、まず1,000MBから1,300MBへ増やし、それでも減り続けたため、最終的に2,000MBまで拡張しました。

正直、SYSAUXは気軽に触りたくない表領域です。 ただ、満杯に近いまま原因調査を続ける方が怖いので、先に空きを作ってから、何が増えているのかを追いました。

今回は備忘録も兼ねて、実際に確認したSQL、容量推移、判断したこと、見送った対応を整理します。

本番環境で実行する場合は、権限、ライセンス、メンテナンス時間、バックアップ方針を必ず確認してください。

Oracle SYSAUXとは

SYSAUXは、Oracle Databaseが内部機能で使用する補助的な表領域です。

Oracle Databaseでは、SYSTEM表領域とは別に、AWR、オプティマイザ統計履歴、各種Advisorなどの情報がSYSAUXに保存されます。

代表的なものには、次のような領域があります。

• SM/AWR：AWR（Automatic Workload Repository）の情報
• SM/ADVISOR：Segment Advisorなど各種Advisorの情報
• SM/OPTSTAT：オプティマイザ統計情報の履歴
• JOB_SCHEDULER：Oracle Scheduler関連の情報

通常、業務アプリケーションのテーブルをSYSAUXへ配置することはありません。

そのため、SYSAUXの使用量が急に増えた場合は、単純に「業務データが増えた」と考えるのではなく、Oracle内部のどの機能が領域を使用しているのかを切り分ける必要があります。

1,000MBから2,000MBまで拡張して調査時間を確保した

SYSAUXですが、最初に確認した時点で空きがほとんどありませんでした。 そこで、まずは次のように容量を増やしました。

ここで大事なのは、拡張を解決策として扱わなかったことです。 拡張は、原因調査を安全に進めるための時間稼ぎです。

表領域が枯渇しそうなときは、原因調査より先に業務影響を止める判断が必要になることがあります。

ORA-01653やORA-01654を避けるために先に空きを作った

SYSAUXが満杯に近いと、内部表や索引の拡張で次のようなエラーが出る可能性があります。

ORA-01653: unable to extend table ... in tablespace SYSAUX
ORA-01654: unable to extend index ... in tablespace SYSAUX

ORA-01653: unable to extend table ... in tablespace SYSAUX
ORA-01654: unable to extend index ... in tablespace SYSAUX

24時間稼働の環境だったため、調査中にこの状態へ入るのは避けたいところでした。 原因を見つける前に、まず落ちないだけの余白を作ります。

tomo

表領域のアラートを見ると、すぐ原因を探したくなります。でも空きがほぼない状態では、調査より先に延命が必要な場面があります。

拡張は恒久対応ではなく調査のための時間稼ぎだった

表領域を広げると、アラートはいったん静かになります。 でも、増え方が止まっていなければ同じことがまた起きます。

今回も、1,300MBへ拡張したあとに空き容量が減り続けました。 そのため、拡張後すぐに「どの内部機能が増えているのか」を確認しました。

V$SYSAUX_OCCUPANTSで最初に見えたのはSM/ADVISORだった

最初に見たのは、SYSAUX内でどの機能が容量を使っているかです。 Oracleでは V$SYSAUX_OCCUPANTS でoccupant別の使用量を確認できます。

occupant別の使用量を確認する

確認に使ったSQLです。

select occupant_name,
       space_usage_kbytes
from v$sysaux_occupants
order by space_usage_kbytes desc;

select occupant_name,
       space_usage_kbytes
from v$sysaux_occupants
order by space_usage_kbytes desc;

当初の主な使用量は次のような状態でした。

この時点では、いちばん大きいのはSM/ADVISORでした。 SYSAUX急増の記事ではSM/ADVISORや WRI$_ADV_OBJECTS が話題になることも多いので、最初にここを疑いました。

SM/ADVISORの内部表と索引が大きかった

SM/ADVISORの内訳を見ると、実際に大きいセグメントがありました。

select segment_name,
       segment_type,
       round(bytes / 1024 / 1024) mb
from dba_segments
where owner = 'SYS'
  and tablespace_name = 'SYSAUX'
  and segment_name like 'WRI$_ADV%'
order by bytes desc;

select segment_name,
       segment_type,
       round(bytes / 1024 / 1024) mb
from dba_segments
where owner = 'SYS'
  and tablespace_name = 'SYSAUX'
  and segment_name like 'WRI$_ADV%'
order by bytes desc;

確認時点では、次のあたりが目立っていました。

合計すると約412MBです。 現在のAdvisorオブジェクト件数やSegment Advisorタスク数に比べると、割り当て済み領域が大きく見えました。

現在サイズだけでは原因を決められない

ただ、ここでいったん止まりました。 SM/ADVISORが大きいことと、今回増え続けていることは別かもしれないため、です。

数日間見た結果、SM/ADVISOR自体はほとんど増えていませんでした。 大きいけれど、今回の増加速度の主因ではなさそうです。

SYSAUX調査では、現在サイズが最大のoccupantをそのまま犯人にしない方がいいです。大きいものと増えているものは別です。

差分で見ると増えていたのはSM/OPTSTATだった

次に、時間差分を取りました。 数日分の V$SYSAUX_OCCUPANTS の結果を残して、どのoccupantが実際に増えているかを見ます。

約2週間の推移でSM/OPTSTATが約300MB増えていた

SM/OPTSTATは、約2週間で次のように増えていました。

一度だけ大きく増えたのではありません。 平日は少しずつ、週末を挟むと大きく増えていました。

約2週間で見ると、SM/OPTSTATは約300MB増えています。 今回の調査では、現在の最大値ではなく、増加差分が原因を教えてくれました。

平日は少しずつ、週末は大きく増えていた

平日は10MB前後ずつ増え、週末を挟むと100MBを超える増加がありました。 ここで、自動統計収集の処理件数も確認しました。

平日: 約900件前後
週末: 約4,200～4,600件

平日: 約900件前後
週末: 約4,200～4,600件

Oracleの自動メンテナンス処理は、平日より週末のメンテナンスウィンドウが長く設定されることがあります。 そのため、週末に処理件数が増えること自体は不自然ではありません。

問題は、統計収集のたびに作られる統計履歴と内部索引が、既存領域に収まらず新しい領域を確保していたことです。

最大occupantと増加原因は別だった

今回の見え方を整理すると、こうなります。

ここを分けないと、SM/ADVISORの整理ばかりに意識が向きます。 それでは、いま表領域を押し上げている増加分には届きません。

AWRのセグメント差分で増加箇所を確認する

occupant単位でSM/OPTSTATが増えていると分かったら、次はどの内部表や索引に新しい領域が割り当てられたかを見ます。 ここではAWRのセグメント統計を使いました。

DBA_HIST_SEG_STATで直近14日の割り当て差分を見る

確認に使ったSQLです。

select o.object_name,
       round(sum(s.space_allocated_delta) / 1024 / 1024) mb
from dba_hist_seg_stat s
join dba_hist_seg_stat_obj o
  on s.obj# = o.obj#
 and s.dataobj# = o.dataobj#
 and s.ts# = o.ts#
where o.object_name like '%OPTSTAT%'
  and s.snap_id in (
        select snap_id
        from dba_hist_snapshot
        where begin_interval_time > sysdate - 14
      )
group by o.object_name
having sum(s.space_allocated_delta) > 0
order by mb desc;

select o.object_name,
       round(sum(s.space_allocated_delta) / 1024 / 1024) mb
from dba_hist_seg_stat s
join dba_hist_seg_stat_obj o
  on s.obj# = o.obj#
 and s.dataobj# = o.dataobj#
 and s.ts# = o.ts#
where o.object_name like '%OPTSTAT%'
  and s.snap_id in (
        select snap_id
        from dba_hist_snapshot
        where begin_interval_time > sysdate - 14
      )
group by o.object_name
having sum(s.space_allocated_delta) > 0
order by mb desc;

SPACE_ALLOCATED_DELTA は、スナップショット間で割り当てられた領域の差分を見るために使います。 ここでは、直近14日間でOPTSTAT系のどのセグメントが増えたかを確認しました。

WRI$_OPTSTAT系の内部表と索引が増えていた

結果として、増えていたのは統計履歴に関係する内部表や内部索引でした。 実名は環境やバージョンで見え方が変わるため、記事では役割で整理します。

上位だけで約150MBありました。 これで、SYSAUXの増加箇所はかなり絞れました。

AWR利用時はDiagnostic Packのライセンス確認が必要

DBA_HIST_% やAWRに関係する情報を使う場合は、ライセンス確認が必要です。 環境によっては、Diagnostic Packの利用可否を確認せずにAWR系ビューを参照してはいけません。

AWRを使えない環境では、V$SYSAUX_OCCUPANTS の定期記録、DBA_SEGMENTS の日次スナップショット、表領域使用率の履歴などで代替します。 精度は落ちますが、現在サイズと差分を分ける考え方は同じです。

統計履歴と業務テーブルの偏りを確認する

SM/OPTSTATが増えているなら、次に見るのは統計履歴です。 統計情報は、SQLの実行計画を決めるために使われます。

統計情報を更新すると、Oracleは以前の統計を履歴として保持します。 これは、必要に応じて過去の統計へ戻せるようにするためです。

統計履歴の保持期間は31日だった

統計履歴の保持期間は、次のSQLで確認しました。

select dbms_stats.get_stats_history_retention()
from dual;

select dbms_stats.get_stats_history_retention()
from dual;

結果は31日でした。 Oracleのオプティマイザ統計履歴は、デフォルトで31日保持されます。

最古の統計履歴も確認しました。

select dbms_stats.get_stats_history_availability()
from dual;

select dbms_stats.get_stats_history_availability()
from dual;

こちらも約31日前でした。 つまり、何年分もの古い統計履歴が残っていたわけではありません。

古い履歴の自動削除は完全停止していなかった

保持期間が31日で、最古の履歴も約31日前でした。 このため、古い履歴の削除が完全に止まっている状態ではなさそうです。

それでも増えていたので、今回は次のように推測をしました。

最近作られる統計履歴の量が多い
  ↓
古い履歴の削除や既存領域の再利用より増加が上回る
  ↓
SM/OPTSTATが段階的に拡張される

最近作られる統計履歴の量が多い
  ↓
古い履歴の削除や既存領域の再利用より増加が上回る
  ↓
SM/OPTSTATが段階的に拡張される

統計履歴のパージが動いているかどうかと、SYSAUXの割り当て領域が増えるかどうかは、完全には同じ話ではありません。

特定の一表に詳細統計が集中していないか確認した

特定の業務テーブルだけが大量の詳細統計を作っていないかも確認しました。

select owner,
       table_name,
       count(*) cnt
from dba_tab_col_statistics
where histogram <> 'NONE'
  and last_analyzed > sysdate - 14
group by owner, table_name
order by cnt desc;

select owner,
       table_name,
       count(*) cnt
from dba_tab_col_statistics
where histogram <> 'NONE'
  and last_analyzed > sysdate - 14
group by owner, table_name
order by cnt desc;

上位の表でも、詳細統計が設定されている列数は13～14列程度でした。 一つの表に数百列分の詳細統計が集中している状況ではありません。

そのため、特定の一表の異常というより、複数の表に分散した統計履歴が積み上がっていたと判断しました。

tomo

一つの表が悪い、と決められると対応は楽です。でも今回はそうではありませんでした。楽な結論に飛びつかず、分散して増えている前提で見ました。

自動統計収集エラーと容量増加を分けて見る

調査中、自動統計収集のエラーも出ていました。 これが容量増加の原因かどうかも切り分ける必要があります。

ORA-20001が約10分ごとに出ていた

確認したエラーは次のような内容です。

ORA-20001: Invalid task name for the current user
SYS.DBMS_STATS line 47207

ORA-20001: Invalid task name for the current user
SYS.DBMS_STATS line 47207

auto optimizer stats collection が約10分ごとに失敗していました。 統計アドバイザの内部タスクが存在しないことに関係するエラーと考えられます。

このエラー自体は、放置してよいものではありません。 自動統計収集まわりで繰り返し失敗しているため、別途対応が必要です。

エラー関連の内部表増加は数MB程度だった

ただし、直近14日のセグメント増加を確認すると、エラー管理に関係しそうな内部表や索引の増加は数MB程度でした。 今回見えていた約300MBのSM/OPTSTAT増加とは桁が違います。

つまり、エラーがあることと、SYSAUX急増の主因であることは分けて考える必要があります。

エラーは直すべきだが今回の主因ではなかった

今回の判断はこうです。

同じ時期に出ているエラーを、容量増加の原因と決めつけない方がいいです。増加量の差分で裏を取ります。

実施した対応と見送った対応

原因が見えてきたところで、対応方針を決めました。 本番は24時間稼働なので、すぐ実行できることと、実行しない方がよいことを分けました。

SYSAUXを2GBまで拡張した

実施したのは、SYSAUXを2GBまで拡張することです。 これは恒久対応ではありませんが、容量枯渇を避けるために必要でした。

表領域の拡張SQLは環境ごとに異なります。 データファイル名、自動拡張設定、ASMかファイルシステムか、監視しきい値を確認してから実行します。

-- 例です。実際のデータファイル名とサイズは環境に合わせて確認します。
alter database datafile '/path/to/sysaux01.dbf' resize 2000M;

-- 例です。実際のデータファイル名とサイズは環境に合わせて確認します。
alter database datafile '/path/to/sysaux01.dbf' resize 2000M;

容量を増やしたあとも、V$SYSAUX_OCCUPANTS の記録は続けます。 増加が止まっていないなら、次の枯渇までの時間を見積もる必要があります。

手動パージは24時間稼働のため見送った

古い統計履歴は、DBMS_STATS.PURGE_STATS で手動削除できます。 ただし、手動パージではUNDO、REDO、I/Oの負荷が出ます。

実行例としては、次のような形です。

begin
  dbms_stats.purge_stats(systimestamp - interval '14' day);
end;
/

begin
  dbms_stats.purge_stats(systimestamp - interval '14' day);
end;
/

今回の環境は24時間稼働でした。 そのため、即時パージは実施せず、自動パージに任せる方針にしました。

tomo

SYSAUXが減りそうなSQLを見ると、すぐ打ちたくなります。でも本番では、削除処理そのものの負荷も障害要因になります。

統計履歴保持期間を31日から14日へ短縮する方針にした

検討したのは、統計履歴の保持期間を31日から14日へ短縮することです。

begin
  dbms_stats.alter_stats_history_retention(14);
end;
/

begin
  dbms_stats.alter_stats_history_retention(14);
end;
/

この設定変更で変わるのは、過去の統計情報へ戻せる期間です。 今回の環境では、統計履歴を手動復元した実績がありませんでした。

影響しないものも整理しました。

保持期間短縮は、統計情報を消す操作ではなく、履歴として戻せる期間を短くする操作です。 この違いを説明できる状態にしてから変更します。

SYSAUX急増で見た方がいい確認順

今回の調査でいちばん効いたのは、順番を間違えないことでした。 現在サイズだけを見ると、SM/ADVISORへ寄り道していたと思います。

まず空き容量を確保する

空き容量がほぼない状態では、調査中に別のエラーが出る可能性があります。 本番であれば、原因調査より先に空きを作る判断も必要です。

ただし、拡張した時点で終わりにしません。 拡張後に使用量の推移を取り、次にどのくらいの速度で減るかを確認します。

現在サイズではなく増加差分を見る

V$SYSAUX_OCCUPANTS の現在値は入口として便利です。 ただし、現在値だけでは増加原因を決められません。

次のように、日ごとの差分を残します。

select occupant_name,
       space_usage_kbytes
from v$sysaux_occupants
order by space_usage_kbytes desc;

select occupant_name,
       space_usage_kbytes
from v$sysaux_occupants
order by space_usage_kbytes desc;

この結果を日次で保存し、SM/AWR、SM/ADVISOR、SM/OPTSTAT、JOB_SCHEDULERなどの差分を見ます。 単純ですが、ここでかなり見え方が変わります。

occupant、内部セグメント、統計履歴、エラーを分けて判断する

今回の確認順は、次の流れでした。

1. 容量枯渇を避けるために空きを確保する
2. V$SYSAUX_OCCUPANTSで機能別の使用量を見る
3. 数日間の差分で実際に増えているoccupantを特定する
4. DBA_SEGMENTSで内部表と索引の大きさを見る
5. AWRのSPACE_ALLOCATED_DELTAで増加セグメントを確認する
6. 統計履歴の保持期間と最古履歴を確認する
7. 自動統計収集エラーと容量増加を分けて判断する
8. 保持期間短縮やパージ可否を運用条件込みで決める

1. 容量枯渇を避けるために空きを確保する
2. V$SYSAUX_OCCUPANTSで機能別の使用量を見る
3. 数日間の差分で実際に増えているoccupantを特定する
4. DBA_SEGMENTSで内部表と索引の大きさを見る
5. AWRのSPACE_ALLOCATED_DELTAで増加セグメントを確認する
6. 統計履歴の保持期間と最古履歴を確認する
7. 自動統計収集エラーと容量増加を分けて判断する
8. 保持期間短縮やパージ可否を運用条件込みで決める

AWRが使えない場合は、5の部分を日次の DBA_SEGMENTS 記録に置き換えます。 精度は落ちますが、「いま増えているものを見る」という軸は残せます。

SYSAUXの調査は、派手なSQL一発で決まるより、地味な差分取りで進むことが多いです。今回もそこが一番効きました。

まとめ

今回のSYSAUX急増では、最初に見えた最大occupantはSM/ADVISORでした。 ただ、継続的に増えていたのはSM/OPTSTATでした。

確認した結論は次のとおりです。

• SM/ADVISORは最大の占有領域だったが、監視期間中はほぼ増えていなかった
• SM/OPTSTATは約2週間で約300MB増加していた
• 増えていたのは統計履歴を保存する内部表と管理用索引だった
• 特定の一つの業務テーブルに詳細統計が集中していたわけではなかった
• 週末は統計収集件数が平日の約4～5倍になり、増加量も大きかった
• 自動統計収集エラーは直すべきだが、今回の容量急増の主因ではなかった
• 手動パージは見送り、保持期間31日から14日への短縮を検討した　※未実施です

SYSAUXが増えたときは、現在サイズだけで原因を決めない方が安全です。 V$SYSAUX_OCCUPANTS の差分、内部セグメント、統計履歴、エラーを分けて見ると、対応の優先順位を決めやすくなります。

単純な表領域拡張で終わらせず、何が、いつ、どれだけ増えたかを残すことが、次の枯渇を防ぐ材料になります。