ORA-01555錯誤類比詳解

下面是摘自聯機文檔Database Error Messages關於ORA-01555的描述

ORA-01555: snapshot too old: rollback segment number string with name "string"

too small

Cause: rollback records needed by a reader for consistent read are overwritten by

other writers

Action: If in Automatic Undo Management mode, increase undo_retention setting. Otherwise, use larger rollback segments

出現ORA-01555錯誤，通常有2種情況：          

1）SQL語句執行時間太長，或者UNDO資料表空間過小，或者事務量過大，或者過於頻繁的提交，導致執行SQL過程中進行一致性讀時，SQL執行後修改的前鏡像即UNDO資料）在UNDO資料表空間中已經被覆蓋，不能構造一致性讀塊CR blocks），這種情況最多。

2）SQL語句執行過程中，訪問到的塊，在進行延遲塊清除時，不能確定該塊的事務提交時間與SQL執行開始時間的先後次序。 這種情況很少。

 第1種情況解決的辦法：

1）增加UNDO資料表空間大小

2）增加undo_retention 時間，預設只有15分鐘

3）最佳化出錯的SQL，減少查詢的時間，首選方法

4）避免頻繁的提交

通過下面的例子，來看看ORA-01555是如何產生的

1.確定系統當前系統的undo的相關資訊

SQL> show parameter undo

NAME                                 TYPE        VALUE

------------------------------------ ----------- ---------------------

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1

SQL> col file_name for a32;

SQL> /

FILE_NAME                        BYTES/1024/1024 TABLESPACE_NAME

-------------------------------- --------------- -------------------

+DATA/orcl/datafile/undotbs1.dbf             140 UNDOTBS1

2. 重新建立一個大小為1m undo資料表空間，並且切換到這個undo資料表空間

SQL> create undo tablespace undotbs2 datafile '+DATA/orcl/datafile/undotbs2.dbf

size 1m;

Tablespace created.

SQL> alter system set undo_tablespace=undotbs2;

System altered.

SQL> show parameter undo;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS2

SQL>

3.建立一個測試表test,當我們往表裡插入資料的時候，就報了下面undotbs2無法擴充的錯誤，我們知道insert語句會寫復原段，由於這個insert的資料比較大，導致undo迅速增長，當達到1m的時候，oracle會首先檢查有沒有undo塊可以覆蓋，剛好都是沒有commit的資料區塊，所以這時候會試著擴充復原段，前面建立沒有指定自動擴充，因此就產生了ORA-30036的錯誤

SQL> create table test as select object_id,object_name from dba_objects;

Table created.

SQL> insert into test select * FROM TEST;

insert into test select * FROM TEST

           *

ERROR at line 1:

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS2'

4.清除這個表，重新建立一個表，插入兩行資料

SQL> create table test (id int,name varchar2(10));

Table created.

SQL> insert into test values(1,'a');

1 row created.

SQL> insert into test values(2,'b');

1 row created.

SQL> commit;

Commit complete.

5.這裡通過遊標變數來查詢這個表，這裡只是把這個表的查詢結果集賦給了類型為refcuersor的變數i，如果要輸出變數的值用print i。假設這裡時刻為t1

SQL> variable i refcursor;

SQL> exec open :i for select *from test;

PL/SQL procedure successfully completed.

6. 開啟另外一個session，修改這個表並做提交，這裡通過一個死迴圈，反覆修改並且提交

修改之前的資料，這樣做的目的主要是根據undo的特性覆寫undo塊

SQL> select *from test;

       ID NAME

---------- ----------

        1 a

        2 b

SQL>    declare

 2       i number := 1;

 3      begin

 4       loop

 5         update test set id=i where name = 'b';

 6        i := i+1;

 7         if mod(i,100)=0 then

 8           commit;

 9         end if;

10     end loop;

11    end;

12  /

7. 等待10秒左右輸出另外一個session的遊標變數i，這個時候比較著名的ORA-01555的錯誤就出現了。

SQL> print i

ERROR:

ORA-01555: snapshot too old: rollback segment number 13 with name

"_SYSSMU13_3721975596$" too small

no rows selected

 導致這個錯誤的原因就是sql查詢時間太長。我們通過遊標變數來製造一個長時間的查詢。這裡並沒有提示undo段無法擴充的錯誤，這是為什麼，根據前面的2個例子的結果分析，就可以推出undo段提交後的資料區塊是可以被覆蓋的，這裡undo雖然只有1m但是我們update產生的undo資料量並為達到1m，由於這個迴圈產生了大量的undo，所以在達到1m的時候發現無法擴充，就會覆寫commit後的undo塊，因此undo資料表空間過小也是導致這一錯誤的原因，根據Cause: rollback records needed by a reader for consistent read are overwritten by other writers這個原因，當使用者發出輸出i的請求時會在buffer cache中構造cr塊，然後返回給使用者，cr塊就是從修改前的undo塊構造的，這裡undo塊已經被覆蓋了無數遍了，所以就報了ORA-01555的錯誤。

8.前面的insert無法直觀的分析出commit前的資料是否能夠被覆蓋，把迴圈中的commit去掉。先猜想下，這裡會報錯什麼樣的錯誤，如果上面分析正確，毫無疑問會報出ORA-30036的錯誤，來驗證一下

SQL>    declare

 2       i number := 1;

 3      begin

 4       loop

 5         update test set id=i where name = 'b';

 6        if mod(i,100)=0 then

 7           i := i+1;

 8         end if;

 9     end loop;

10    end;

11  /

  declare

*

ERROR at line 1:

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS2'

ORA-06512: at line 5

 聯機文檔給出瞭解決方法Action: If in Automatic Undo Management mode, increase undo_retention setting. Otherwise, use larger rollback segments，所以前面的實驗類比只需要把undo資料表空間擴大或者自動成長開啟就行了，這真實環境中往往就可能是sql語句查詢時間過長，所以想解決問題還得從最佳化sql開始。

undo_retention的作用，這個值預設是900s，意思就是說commit後的在undo段中保留900s，但並不保證在900s時間內一定不被覆寫，從前面的實驗就可以看出在900s內已經被覆寫，如果要保證undo資料一定在900s不被覆寫的話，需要設定undo段retention的值為guarantee

SQL>  select tablespace_name,RETENTION from dba_tablespaces where tablespace_n

e='UNDOTBS2';

TABLESPACE_NAME                RETENTION

------------------------------ -----------

UNDOTBS2                       NOGUARANTEE

1. 通過下面的命令修改為guarantee

SQL> alter tablespace undotbs2 retention guarantee;

Tablespace altered.

SQL>  select tablespace_name,RETENTION from dba_tablespaces where tablespace_n

e='UNDOTBS2';

TABLESPACE_NAME                RETENTION

------------------------------ -----------

UNDOTBS2                       GUARANTEE

2. 如果保證900s，我們在執行前面的語句，可想而知就會報ORA-30036,還是驗證一下

SQL>    declare

 2       i number := 1;

 3      begin

 4       loop

 5         update test set id=i where name = 'b';

 6        i := i+1;

 7        if mod(i,100)=0 then

 8          commit;

 9         end if;

10     end loop;

11    end;

12  /

  declare

*

ERROR at line 1:

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS2'

ORA-06512: at line 5

本文出自 “一步一步” 部落格，請務必保留此出處http://5073392.blog.51cto.com/5063392/1294764