UNDO概要
2016-06-27
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管理モードで動作しています。
|
1 2 3 4 5 |
SQL> show parameter UNDO_MANAGEMENT NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO |
エラーメッセージ
ORA-01555: スナップショットが古すぎます: ロールバック・セグメント番号string、名前"string"が小さすぎます
ORA-01555: snapshot too old: rollback segment number %s with name "%s" too small
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を想定）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
$ sqlplus / as sysdba SQL> -- 以下 SPFILE のパスが出力される場合は SPFILE を使用しています。 SQL> show parameter spfile SQL> set line 120 pages 1000 trim on SQL> col name for a15 SQL> col value for a20 SQL> col sid for a10 SQL> SQL> -- 現在値の確認 SQL> select INST_ID, NAME, VALUE from gv$parameter where name in ('undo_retention'); INST_ID NAME VALUE ---------- --------------- -------------------- 1 undo_retention 900 SQL> -- SPFILE の内容の確認 SQL> select SID, NAME, VALUE from v$spparameter where name in ('undo_retention'); SID NAME VALUE ---------- --------------- -------------------- * undo_retention SQL> -- 値の変更 SQL> ALTER SYSTEM SET UNDO_RETENTION = <変更後の値> SCOPE=BOTH; System altered. SQL> -- 現在値が変更されていることの確認 SQL> select INST_ID, NAME, VALUE from gv$parameter where name in ('undo_retention'); INST_ID NAME VALUE ---------- --------------- -------------------- 1 undo_retention <変更後の値> SQL> -- SPFILE の内容が更新されていることの確認（再起動時に同じ値が使用されることを確認） SQL> select SID, NAME, VALUE from v$spparameter where name in ('undo_retention'); INST_ID NAME VALUE ---------- --------------- -------------------- 1 undo_retention <変更後の値> |
PFILEを使用した変更については、以下を参照してください。
SPFILEとPFILEの概要と操作
表領域のサイズ増加にはSQL*Plusにログイン後、以下のコマンドを実行します。
|
1 2 3 4 5 6 |
SQL> col FILE_NAME for a70 SQL> -- ファイル名を確認 SQL> select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 as MBYTES,AUTOEXTENSIBLE 2 from DBA_DATA_FILES; SQL> ALTER DATABASE DATAFILE '<ファイル名>' RESIZE 300M; SQL> ALTER DATABASE DATAFILE '<ファイル名>' AUTOEXTEND ON NEXT 16M MAXSIZE 600M; |
値の検討（アドバイザの利用）
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の使用)
見積もりに使用するスナップショットの期間を取得します。
|
1 2 3 4 5 6 7 8 9 10 |
SQL> set pagesize 1000 linesize 120 trim on SQL> alter session set nls_date_format = 'yyyy/mm/dd hh24:mi:ss'; SQL> select SNAP_ID 2 , INSTANCE_NUMBER 3 , to_char(END_INTERVAL_TIME, 'yyyy/mm/dd hh24:mi:ss') as END_INTERVAL_TIME 4 , to_char(STARTUP_TIME, 'yyyy/mm/dd hh24:mi:ss') as STARTUP_TIME 5 from DBA_HIST_SNAPSHOT 6 -- １日前からのスナップショット番号を取得 7 where END_INTERVAL_TIME >= sysdate - 1 8 order by snap_id; |
分析したい期間のスナップショット番号を指定し、アドバイザによる分析を行います。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SQL> DECLARE 2 tid NUMBER; 3 tname VARCHAR2(30); 4 oid NUMBER; 5 BEGIN 6 -- タスクの作成を行います tname にはタスク名が入ります。 7 DBMS_ADVISOR.CREATE_TASK('Undo Advisor', tid, tname, 'Undo Advisor Task'); 8 -- タスクオブジェクト（アドバイザ入力データ）を作成します。 oid にはタスクオブジェクト ID が入ります。 9 DBMS_ADVISOR.CREATE_OBJECT(tname, 'UNDO_TBS', null, null, null, 'null', oid); 10 -- アドバイザの入力内容をセットします。 START_SNAPSHOT END_SNAPSHOT INSTANCE は適宜変更します。 11 DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'TARGET_OBJECTS', oid); 12 DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'START_SNAPSHOT', 183); 13 DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'END_SNAPSHOT', 187); 14 DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'INSTANCE', 1); 15 -- アドバイザの実行をします。 16 DBMS_ADVISOR.execute_task(tname); 17 END; 18 / PL/SQL procedure successfully completed. |
アドバイザの分析結果を確認します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
SQL> column OWNER format a10 SQL> -- TASK_NAME が select された際に、 tname 変数として定義します。 SQL> column TASK_NAME format a15 new_value tname SQL> select OWNER 2 , TASK_ID 3 , TASK_NAME 4 , STATUS 5 , CREATED 6 from DBA_ADVISOR_TASKS 7 where ADVISOR_NAME = 'Undo Advisor' 8 order by CREATED; SQL> SQL> -- 推奨事項の出力 SQL> column FINDING_NAME format a50 SQL> column MESSAGE format a50 SQL> select TASK_NAME 2 , FINDING_NAME 3 , MESSAGE 4 from DBA_ADVISOR_FINDINGS 5 where TASK_NAME = '&&tname'; TASK_NAME FINDING_NAME MESSAGE --------------- -------------------------------------------------- -------------------------------------------------- TASK_nnn normal, successful completion The undo tablespace is OK. 1 row selected. SQL> SQL> -- 推奨事項の対応状況 SQL> select TASK_NAME 2 , TYPE 3 , RANK 4 , PARENT_REC_IDS 5 , BENEFIT_TYPE 6 , ANNOTATION_STATUS 7 from DBA_ADVISOR_RECOMMENDATIONS 8 where TASK_NAME = '&&tname' 9 order by RANK; |
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で確認可能です。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
$ sqlplus / as sysdba SQL> alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss'; SQL> set line 120 pages 1000 trim on SQL> -- BEGIN_TIME から END_TIME の期間のチューニング結果 SQL> select BEGIN_TIME,END_TIME,INSTANCE_NUMBER,TUNED_UNDORETENTION 2 from DBA_HIST_UNDOSTAT 3 order by BEGIN_TIME,INSTANCE_NUMBER; BEGIN_TIME END_TIME INSTANCE_NUMBER TUNED_UNDORETENTION ------------------- ------------------- --------------- ------------------- 2016/06/10 13:22:00 2016/06/10 13:32:00 1 1162 2016/06/10 13:32:00 2016/06/10 13:42:00 1 1720 2016/06/10 13:42:00 2016/06/10 13:52:00 1 2321 2016/06/10 13:52:00 2016/06/10 14:02:00 1 2667 2016/06/10 14:02:00 2016/06/10 14:12:00 1 3268 2016/06/10 14:12:00 2016/06/10 14:22:00 1 2119 2016/06/10 14:22:00 2016/06/10 14:32:00 1 2397 2016/06/10 14:32:00 2016/06/10 14:42:00 1 2545 .... |
但し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文の実行時間の長さを確認できます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SQL> alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss'; SQL> set line 120 pages 1000 trim on SQL> -- BEGIN_TIME から END_TIME の期間の最大 SELECT 文実行時間と対象の SQLID SQL> select BEGIN_TIME,END_TIME,INSTANCE_NUMBER,MAXQUERYLEN,MAXQUERYSQLID 2 from DBA_HIST_UNDOSTAT 3 order by BEGIN_TIME,INSTANCE_NUMBER; BEGIN_TIME END_TIME INSTANCE_NUMBER MAXQUERYLEN MAXQUERYSQLID ------------------- ------------------- --------------- ----------- ------------- 2016/06/10 13:22:00 2016/06/10 13:32:00 1 322 0rc4km05kgzb9 2016/06/10 13:32:00 2016/06/10 13:42:00 1 880 cc58t2hkfpnaq 2016/06/10 13:42:00 2016/06/10 13:52:00 1 1480 cc58t2hkfpnaq 2016/06/10 13:52:00 2016/06/10 14:02:00 1 1827 cc58t2hkfpnaq 2016/06/10 14:02:00 2016/06/10 14:12:00 1 2428 cc58t2hkfpnaq 2016/06/10 14:12:00 2016/06/10 14:22:00 1 1279 8cddpr5wp7at1 2016/06/10 14:22:00 2016/06/10 14:32:00 1 1556 f3yfg50ga0r8n 2016/06/10 14:32:00 2016/06/10 14:42:00 1 1705 8cddpr5wp7at1 .... |
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の期間の中で生成されたブロック数を確認できます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SQL> alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss'; SQL> set line 120 pages 1000 trim on SQL> -- BEGIN_TIME から END_TIME の期間の UNDO ブロック生成数と最大 SELECT 文実行時間 SQL> select BEGIN_TIME,END_TIME,INSTANCE_NUMBER,UNDOBLKS,MAXQUERYLEN 2 from DBA_HIST_UNDOSTAT 3 order by BEGIN_TIME,INSTANCE_NUMBER; BEGIN_TIME END_TIME INSTANCE_NUMBER UNDOBLKS MAXQUERYLEN ------------------- ------------------- --------------- ---------- ----------- 2016/06/10 13:22:00 2016/06/10 13:32:00 1 1105 322 2016/06/10 13:32:00 2016/06/10 13:42:00 1 1003 880 2016/06/10 13:42:00 2016/06/10 13:52:00 1 5370 1480 2016/06/10 13:52:00 2016/06/10 14:02:00 1 2116 1827 2016/06/10 14:02:00 2016/06/10 14:12:00 1 847 2428 2016/06/10 14:12:00 2016/06/10 14:22:00 1 107 1279 2016/06/10 14:22:00 2016/06/10 14:32:00 1 134 1556 2016/06/10 14:32:00 2016/06/10 14:42:00 1 141 1705 .... |
トランザクションなどが多い場合は、UNDO領域用のメタデータなどにオーバーヘッドがかかるため、その分も容量確保する必要があります。ただし、必要な見積もり値はUNDOアドバイザを参照することが最適値に近くなると思われます。