UNDO_RETENTION初期化パラメータについて

質問

ORACLE9iを自動UNDO管理モードで使用しています。
マニュアルによるとUNDO_RETENTIONはコミット後のデータの保存期間（目標値）を設定するもので、用途はフラッシュバック問い合わせ、読み取り一貫性の保障であるということですが。

質問：クエリー（SELECT）を投げた時点の対象テーブルの状態（スナップショット）もUNDO表領域に置かれ、UNDO_RETENTIONの期間保存されるのでしょうか？
そうすると「UNDO_RETENTIONはコミット後のデータの保存期間」というのとは少し違ってくるのかと思うのですが。。。

経緯：１時間ほど掛かる問い合わせで発生するORA-01555（スナップショットが古すぎます）のエラー対策の検討をしていところです。

よろしくお願いします。

回答No.4

conAw2 · ベストアンサー

>クエリー（SELECT）を投げた時点の対象テーブルの状態（スナップショット）もUNDO表領域に置かれ、
>UNDO_RETENTIONの期間保存されるのでしょうか？

UNDO表領域の本来の目的はロールバックに備えたものです。
したがってユーザが更新しようとした対象行の変更前のイメージがUNDOには保存されます。
逆に言えば単純なSELECT文はUNDO領域を原則使いません。
そしてこのUNDO領域は更新ユーザがコミットするまでは絶対に上書きされません！！
（そうでないとロールバックが実現できなくなりますので・・・）

以上を踏まえた上で
このUNDO表領域を読み取り一貫性にも利用しています。（フラッシュバック機能にもですが）
読み取り一貫性は、ユーザの読み取り時点のデータの内容をSELECT文が終了するまで
保障するものです。例えば読み取りに１時間かかるSELECT文を発行して
その間に誰か別のユーザがさきほどのSELECT文の対象行の一部を更新してコミットを
きられた時に読み取りの途中でその更新内容をとってきたらまずいですよね。
（今説明したのは文レベルの読み取り一貫性の話です。トランザクションレベルもありますが
ここでは関係ないので割愛します）

途中で更新されたデータをSELECT文の途中で読み取るとまずいのですが
じゃあ読み取り時点のデータをどこからもってくるのかそれがUNDO表領域になります。

ただし、先ほど説明したようにUNDO表領域は、
コミットされたデータに関しては、ロールバック不要になってますので
対象のUNDO領域は上書き可能になります。

通常であればこれで問題ないのですが
質問にもありますように結果に長い時間を要するSQL文を発行すると
UNDO領域の上書き可能が問題になってきます。

1時間かかるSELECT文を発行し、その間にSELECT対象の行を
他の沢山のユーザが、更新処理を行ったときを考えてみてください。
当然更新処理なのでロールバック用にどんどんUNDO表領域を使っていきます。
1時間かかるSELECT文は読み取り一貫性を確保するために
他のユーザに更新されたデータはUNDO表領域の方を参照しにいきます。

ところが、1時間かかるSELECT文を発行中に更新ユーザがコミットをきると
UNDO表領域のデータは上書き可能になります。
このときに別のユーザが更新処理を行うと、
その上書き可能な領域を使ってしまうことがあります。
その後にSELECT文が、上書きされたUNDO表領域を
読み込むと、前の情報が他の更新ユーザのロールバック用に上書きされて
読み取り時点のデータを読み込めない！読み取り一貫性エラーになるわけです。

そこでこれを回避するためにUNDO_RETENTIONがあります。
時間のかかるSELECT文のためにUNDO表領域の上書き禁止期間をもう少し延長させてください
つまりコミット後のデータのUNDO表領域をできるだけ上書き禁止にするものになります
（まぁこのパラメータは目標なので、UNDO表領域が足りなければ上書きされます）

【結論】
クエリー（SELECT）を投げた時点の対象テーブルの状態（スナップショット）はロールバック不要なのでUNDO表領域に置かれません。

補足

長文ありがとうございます。

【結論】ですが、ANo.1さんのご意見とまったく逆なのでどちらを信用していいものやら。

結論以外の部分を読ませて頂く限り、conAw2さんの方が自分の経験、オフィシャルな情報と照らし合わせて信頼性は高いと思っていますが。

他の方のご意見、どうお思いになりますか？

回答No.5

conAw2

>SELECT文が実行開始後に発生したトランザクションでの変更について変更
>前のイメージをUNDO表領域に保持します。
>経緯を見る限りでは、UNDO表領域を拡大するするのが最も適当だと思いますが。
>後は長時間かかる問合せをチューニングするとか。
ANo.1さんの意見と同じだと思います。
"SELECT文が実行開始後に発生したトランザクションでの変更"
これを、
"(ユーザAが発行した)SELECT文の実行開始後に発生した(他ユーザの)トランザクションでの変更（はUNDOに保存されます）"
と捉えてもらえれば同じです。

回答No.3

nora1962

>(1)読み取り一貫性の保障は必要。
oracleはfor updateを指定しなくても読み取り一貫性を保障します。

(2)SELECT発行時点のコミット済データをUNDO領域に持つ。
SELECT発行時点「後」のコミット前のイメージデータですよね。

回答No.2

entree

> マニュアルによるとUNDO_RETENTIONはコミット後のデータの保存期間（目
> 標値）を設定するもので、

変更前のデータの保存期間です。コミット後ではありません。

> 質問：クエリー（SELECT）を投げた時点の対象テーブルの状態（スナップ
> ショット）もUNDO表領域に置かれ、UNDO_RETENTIONの期間保存されるの
> でしょうか？

FOR UPDATE 句などをつけない限りは保存されません。
なぜなら FOR UPDATE 句などがつけられていない限り、Oracle は読み取り一貫性を保証する必要がないからです。

> 経緯：１時間ほど掛かる問い合わせで発生するORA-01555（スナップ
> ショットが古すぎます）のエラー対策の検討をしていところです。

ORA-1555 の原因の大半は長い SELECT の実行途中に、別のセッションから接続した誰かがまた読み取っていないデータを更新してしまい、かつ大量更新によって UNDO 表領域に移された変更前イメージが上書きされてしまった場合です。

UNDO_RETENTION=3600 (１時間) 以上にすることで、UNDO 表領域に空きがある限り、最低１時間は変更前イメージが上書きされなくなるので、これによって問題が解決する可能性はあります。

UNDO 表領域に空きがない場合は、まず UNDO 表領域の空きを増やすか自動拡張を有効にする必要があります。

補足

http://ja.wikipedia.org/wiki/Oracle_Database
によると
「読み取り一貫性：SELECTを発行した時点のデータが読み取れることを保障する機能。更新前のデータが格納されているUNDOセグメント～略～を参照することで、排他ロックによるブロックを受けずにデータを読み取ることができる。 」
とあります。（公式文書ではありませんが、、、）
自分の理解では
(1)読み取り一貫性の保障は必要。
(2)SELECT発行時点のコミット済データをUNDO領域に持つ。
なのですが。

間違っていますでしょうか？

回答No.1

nora1962

> クエリー（SELECT）を投げた時点の対象テーブルの状態（スナップショッ
> ト）もUNDO表領域に置かれ、UNDO_RETENTIONの期間保存されるのでしょう
> か？

SELECT文が実行開始後に発生したトランザクションでの変更について変更
前のイメージをUNDO表領域に保持します。
経緯を見る限りでは、UNDO表領域を拡大するするのが最も適当だと思いますが。
後は長時間かかる問合せをチューニングするとか。