【效能優化】處理ORA-01555:Snapshot Too Old

海星星hktk發表於2014-09-10
優化
處理ORA-01555:snapshot too old

【實驗環境】
作業系統 RedHat 5.5
主機名 lxh
資料庫版本 Oracle 10.2.0
字符集 GHS16GBK
例項名 prod
監聽 LISTENER/1521

【實驗過程】
1、建立undo表空間

SYS@ prod>create undo tablespace undo_small
       datafile '/u01/app/oracle/oradata/prod/undo_small01.dbf' size 2m
       autoextend off;

2、讓系統使用該undo表空間

SYS@ prod>alter system set undo_tablespace = undo_small;

3、建立表

用普通使用者建立一張表，我們將建立表T來查詢和修改。注意我們在這個表中隨機地對資料排序。CREATE TABLE AS SELECT圖按查詢獲取的順序將行放在塊中。我們的目的只是把行弄亂，使它們不至於認為有某種順序，從而得到隨機的分佈：
SYS@ prod>conn scott/tiger
SYS@ prod>create table t as select * from all_objects order by dbms_random.random;

4、建立一個主鍵約束，目的是建立一個索引

SCOTT@ prod>alter table t add constraint t_pk primary key(object_id);

5、收集表的統計資訊，目的是讓優化器使用索引

SCOTT@ prod>exec dbms_stats.gather_table_stats( user, 'T', cascade=> true );

6、開始修改表

SCOTT@ prod>begin
 2    for x in ( select rowid rid from t )
 3    loop
 4    update t set object_name = lower(object_name) where rowid = x.rid;
 5    commit;
 6    end loop;
 7    end;
 8    /

7、執行查詢

在執行上面修改的同時，在另一個會話中執行一個查詢。這個查詢要讀表T，並處理每個記錄。獲取下一個記錄之前處理每個記錄所花的時間大約為1/100秒（使用DBMS_LOCK.SLEEP(0.01)來模擬，使用前先用SYS給SCOTT賦權）。在查詢中使用了FIRST_ROWS提示，使之使用前面建立的索引，從而通過索引（按OBJECT_ID排序）來讀出表中的行。由於資料是隨機地插入到表中的，我們可能會相當隨機地查詢表中的塊。這個查詢只執行幾秒就會失敗：
SYS@ prod>grant execute on dbms_lock to scott;

SCOTT@ prod>declare
 2   cursor c is
 3   select /*+ first_rows */ object_name
 4   from t
 5   order by object_id;
 6
 7   l_object_name t.object_name%type;
 8   l_rowcnt number := 0;
 9   begin
10   open c;
11   loop
12   fetch c into l_object_name;
13   exit when c%notfound;
14   dbms_lock.sleep( 0.1 );
15   l_rowcnt := l_rowcnt+1;
16   end loop;
17   close c;
18   exception
19   when others then
20   dbms_output.put_line( 'rows fetched = ' || l_rowcnt );
21   raise;
22   end;
23  /
declare
*
ERROR at line 1:

ORA-01555: snapshot too old: rollback segment number 19 with name "_SYSSMU19$" too small

ORA-06512: at line 21

可以看到，在遭遇ORA-01555:snapshot too old錯誤而失敗之前，它只處理了19個記錄。

8、解決方案

要修正這個錯誤，我們要保證做到兩點：
      1、資料庫中UNDO_RETENTION要設定得足夠長，以保證這個讀程式完成。這樣資料庫就能擴大undo表空間來保留足夠的undo，使我們能夠完成工作。
      2、undo表空間可以增長，或者為之手動分配更多的磁碟空間。
     對於這個例子，我認為這個長時間執行的程式需要大約600秒才能完成。我的UNDO_RETENTION設定為900（單位是秒，所以undo保持大約15分鐘）。我修改了undo表空間的資料檔案，使之一次擴大10MB，直到最大達到2GB。
SYS@ prod>show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDO_SMALL

SYS@ prod>select file#,name,bytes/1024/1024 M from v$datafile;

    FILE# NAME                                                        M
---------- -------------------------------------------------- ----------
        1 /u01/app/oracle/oradata/prod/system01.dbf                 500
        2 /u01/app/oracle/oradata/prod/undotbs01.dbf               1760
        3 /u01/app/oracle/oradata/prod/sysaux01.dbf                 290
        4 /u01/app/oracle/oradata/prod/users01.dbf                    5
        5 /u01/app/oracle/oradata/prod/example01.dbf                100
        6 /u01/app/oracle/oradata/prod/statspack01.dbf              300
        7 /u01/app/oracle/oradata/prod/tbs1.dbf                      10
        8 /u01/app/oracle/oradata/prod/test3.dbf                   1602
        9 /u01/app/oracle/oradata/prod/undo_small01.dbf               2

9 rows selected.


設定undo表空間自動擴充套件，每次擴大10m,最大2G。
SYS@ prod>alter database datafile 9 autoextend on next 10m maxsize 2000m;

9、再次開始修改表

SCOTT@ prod>begin
 2    for x in ( select rowid rid from t )
 3    loop
 4    update t set object_name = lower(object_name) where rowid = x.rid;
 5    commit;
 6    end loop;
 7    end;
 8    /

10、再次執行查詢

SCOTT@ prod> declare
 2   cursor c is
 3   select /*+ first_rows */ object_name
 4   from t
 5   order by object_id;
 6   l_object_name t.object_name%type;
 7   l_rowcnt number := 0;
 8   begin
 9   open c;
10   loop
11   fetch c into l_object_name;
12   exit when c%notfound;
13   dbms_lock.sleep( 0.1 );
14   l_rowcnt := l_rowcnt+1;
15   end loop;
16   close c;
17   exception
18   when others then
19   dbms_output.put_line( 'rows fetched = ' || l_rowcnt );
20   raise;
21   end;
22  /

此次更新過程中，查詢語句未報錯。


【實驗總結】

在設定了undo表空間自動擴充套件之後，我們成功地完成了工作，而且undo擴大得足夠大，可以滿足我們的需要。
在這個例子中，之所以會得到錯誤只是因為我們通過索引來讀表T，而且在全表上執行隨機讀。如果不是這樣，而是執行全表掃描，在這個特例中很可能不會遇到ORA-01555錯誤。原因是SELECT和UPDATE都要對T執行全表掃描，而SELECT掃描很可能在UPDATE之前進行（SELECT只需要讀，而UPDATE不僅要讀還有更新，因此可能更慢一些）。如果執行隨機讀，SELECT就更有可能要讀已修改的塊（即塊中的多行已經被UPDATE修改而且已經提交）。ORA-01555這個錯誤的出現取決於併發會話如何訪問和管理底層表。


呂星昊
2014.9.10

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29475508/viewspace-1267313/，如需轉載，請註明出處，否則將追究法律責任。

相關文章