SSJ Tech Lab - Oracle Database テクニカルラボ

ORA-1555,ORA-01555の原因と対処

対応バージョン

Oracle Database 11.2 - 12.1

2016-06-16記事公開

ORA-1555は主に長時間のSELECTをした時に発生するエラーです。例えばINSERT-SELECTによってテーブルのコピーを行っている場合などの長時間SQLでよく発生します。このようなケースはよくあるため、頻繁に発生する障害の一つです。

現在11g以降のほとんどのデータベースは自動UNDO管理モード(Oracle推奨)となっているため、自動UNDO管理モードを前提として説明します。
パフォーマンスおよび管理性の観点からも自動UNDO管理モードを選択することが推奨されます。手動UNDO管理モードは下位互換性のために残っています。

参考情報(参考元マニュアル) https://docs.oracle.com/cd/E49329_01/server.121/b71276/pfgrf_build_db.htm#TGDBA94146
Oracle Databaseパフォーマンス・チューニング・ガイド
12c リリース1 (12.1)
B71276-01
4 パフォーマンスを考慮したデータベースの構成
4.1 初期インスタンス構成のパフォーマンスの考慮事項
4.1.2 UNDO領域の構成

自動UNDO管理モードの確認は以下のコマンドで行えます。AUTO(又は11g以上のデータベースではNULL)が設定されている場合は、自動UNDO管理モードで動作しています。

エラーメッセージ

ORA-01555: スナップショットが古すぎます: ロールバック・セグメント番号string、名前"string"が小さすぎます
ORA-01555: snapshot too old: rollback segment number %s with name "%s" too small
https://docs.oracle.com/cd/E49329_01/server.121/b71353/e1500.htm#ORA-01555
Oracle Databaseエラー・メッセージ
12c リリース1 (12.1)
B71353-01
4 ORA-01500からORA-02098

発生原因

SQLでSELECT文を実行している時もデータベースは常に更新されます。データに不整合が出ないように、SELECT文を開始した時点の、更新される前の過去のデータを取得することがあります。その時にUNDO表領域の過去データ(ロールバック・レコード)を読み込みます。
ロールバック・レコードを読み取ろうとした時に、既に新しいデータによって上書き済みとなっていた場合にORA-1555エラーが発生します。

発生要因例

最初に説明した部分とかぶりますが、例えばINSERT-SELECTによってテーブルのコピーを行っている場合などの長時間SQLでよく発生します。

通常運用時では以下のようなパターンが想像されます。

  • 例外的なメンテナンス処理を行った(テーブルのバックアップなど)
  • 処理の変更を加えた
  • 実行計画が変化した
  • データサイズが肥大化し、ロールバック・セグメントを多く使うようになった

対処方法

ORA-1555回避のアプローチ

ORA-1555回避のアプローチとしては、大まかに2種類あります。1つは出来るだけ過去のデータを見ないようにSQLの時間を短くします。もう1つはUNDO表領域の容量(過去データ保存量)を増やすことで、データの一貫性を保てる時間を延ばします。

データの肥大化などにより起こるべくして起きた内容であれば、UNDO表領域を大きくするような対処になるかと思われますが、SQLの高速化が可能であればそちらの対処でも可能です。費用対効果や実現性を見て対応をして下さい。

障害発生の時間と対象SQLの確認

エラーが発生した場合、アラートログ(alert_<インスタンス名>.log)および実行したクライアント側にORA-1555が出力されます。 アラートログにはORA-1555の記載された行の前後にトレースログの場所が記載されています。 最後に.trcが付与されたファイルです。(例:/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_8774.trc)
こちらの.trcファイルを上から見ていくと、エラーになったSQLが見つかると思います。そこで発生した時間なども確認できますので、解析用に使用することができます。

対処例

対処方法1:SQLの時間を短縮する

エラーの発生したSQLを特定し、SQLのチューニングなどによりSQLの実行時間を短くすることでエラーを回避します。統計情報を取得してみたら治ったなどもたまに聞く話です。

対処方法2:表領域を大きくする

UNDO表領域又はUNDO保存期間を大きくすることで、保存期間を増やして長時間SQLでエラーが出ないように対応します。

undo_retention(保存期間)の増加にはSQL*Plusにログイン後、以下のコマンドを実行します。(SPFILEを想定)

PFILEを使用した変更については、以下を参照してください。
SPFILEとPFILEの概要と操作

表領域のサイズ増加にはSQL*Plusにログイン後、以下のコマンドを実行します。

値の検討(アドバイザの利用)

UNDOアドバイザによる見積もり

サイズの見積もりについてどの程度大きくすればよいかは、該当時間帯のAWRやOEM(Oracle Enterprise Manager)からアドバイザで確認することもできます。OracleとしてはOEMのUNDOアドバイザを推奨しています。(Oracle Database 12c付属のOracle Enterprise Manager Database ExpressでもUNDOアドバイザは確認可能です。OEMにてデータベースログイン後、UNDO管理詳細を参照してください。また、Diagnostic Packライセンスは不要であり、Standard Editionでも確認可能です。)

また、使用実績をベースに見積もるため、一通りの処理が流れたデータベースでないと、正確な見積もりが行われないため注意してください

OEMを利用したアドバイザの参照
https://docs.oracle.com/cd/E57425_01/121/ADMQS/GUID-5801E369-B27D-4DF0-B291-68C37D641C43.htm
Oracle Database 2日でデータベース管理者
12cリリース1 (12.1)
B71307-07
UNDOアドバイザを使用したUNDO表領域の最小サイズの計算

UNDOアドバイザによる見積もり(PL/SQLの使用)

見積もりに使用するスナップショットの期間を取得します。

分析したい期間のスナップショット番号を指定し、アドバイザによる分析を行います。

アドバイザの分析結果を確認します。

DBA_ADVISORから始まる表のADDMにかかわるものはStandard Edition では表示させることが出来ませんので注意してください。

参考情報(参考元マニュアル)
https://docs.oracle.com/cd/E49329_01/license.121/b71334/options.htm#DBLIC167
Oracle Databaseライセンス情報
12cリリース1 (12.1)
B71334-02
2 オプションおよびパック
Oracle Management Pack
Oracle Diagnostics Pack
コマンドラインAPI

値の検討(SQLでの確認)

やはりSQL文で直接確認するほうが理解は深まるため、ここではSQL文での紹介を行います。
アドバイザで行う正確な値の出し方は一般公開されていない為、どんな内容から見積もるかというところまでの記載です。
UNDOの調整に必要な要素の理解として記載します。

UNDOの保存期間(UNDO_RETENTION)

保存期間の自動調整

UNDO表領域の調整にはV$UNDOSTAT又はDBA_HIST_UNDOSTATというビューを使用します。
V$UNDOSTATには10分間隔で過去4日間分のUNDO関連の統計情報が格納されています。ただし、インスタンスを再起動している場合は初期化されていますのでご注意ください。過去履歴はDBA_HIST_UNDOSTATに格納されています。

UNDOの保存期間は以下の3つを考慮します。

  • TUNED_UNDORETENTION(Oracleの内部調整)
  • UNDO_RETENTION初期化パラメータ(保証期間の指定(秒))
  • UNDO表領域へのRETENTION GUARANTEE付与(UNDO_RETENTION期間の保証)

自動拡張不可(AUTOEXTEND OFF)の設定をしている場合、UNDO表領域のサイズベースでUNDOの保存期間を調整します。
自動拡張可能(AUTOEXTEND ON)の設定をしている場合、SELECT文の実行時間の長さベースでUNDOの保存期間を調整します。これはUNDO表領域のMAXSIZEと現在のサイズが同じでも同一の動きとなります。
調整された結果はTUNED_UNDORETENTIONに反映されます。値はV$UNDOSTATで確認可能です。

但しLOBオブジェクトはUNDO_RETENTIONの値が使用され、自動チューニングが反映されません。

筆者のお勧めとしては自動拡張可能(AUTOEXTEND ON)とすることです。理由はMAXSIZEを大きめにして自動拡張としておくことで、UNDO表領域が不足した場合に、拡張結果でUNDOの不足容量が確認でき、ORA-01555が出にくくなるためです。

UNDO_RETENTION

自動拡張不可(AUTOEXTEND OFF)の場合、UNDO_RETENTION初期化パラメータはLOB以外無視されます。
自動拡張可能(AUTOEXTEND ON)の場合は、UNDO_RETENTIONは最低維持目標値となります。表領域のMAXSIZEを超えた場合はUNDO_RETENTIONを無視してロールバック・レコードが上書きされます。基本的に保証期間がSELECT文の実行時間の最大の長さに調整されるため、特に自動チューニング期間よりも前を指定するようなFLASHBACKクエリ、または想定外の長時間クエリが発生するような場合、UNDO_RETENTIONの値が役に立ちます。

保証期間の長さについては、DBA_HIST_UNDOSTATのMAXQUERYLENを参考にすることができます。MAXQUERYLENはBEGIN_TIMEからEND_TIMEの期間の中で最長のSELECT文の実行時間の長さを確認できます。

RETENTION GUARANTEE付与

UNDO表領域へのRETENTION GUARANTEE付与についてですが、こちらは自動拡張の状態にかかわらず、UNDO_RETENTIONの期間のデータが保証されるようになります。
もしUNDO表領域のサイズが足りなくなった場合、長時間SQLだけではなく、複数のDMLの失敗(保証によりロールバック・レコードの書き込みの失敗)が発生することとなるため、安易な設定は避けたほうが無難です。

UNDO表領域のサイズ

UNDO表領域のサイズについては、DBA_HIST_UNDOSTATのUNDOBLKSを参考にすることができます。例えばUNDOBLKSが(10分間のうち)最大となっているブロック生成数を基準に、SELECT文実行時間またはUNDO_RETENTIONの時間の分だけUNDOブロックが生成されたときのUNDOサイズにするなどとします。UNDOBLKSはBEGIN_TIMEからEND_TIMEの期間の中で生成されたブロック数を確認できます。

トランザクションなどが多い場合は、UNDO領域用のメタデータなどにオーバーヘッドがかかるため、その分も容量確保する必要があります。ただし、必要な見積もり値はUNDOアドバイザを参照することが最適値に近くなると思われます。