ORA-01555 原因与解决ORA-01555的解释假设有张表，叫table1，里面有5000万行数据，假设预计全表扫描1次需要1
ORA-01555 原因与解决
ORA-01555的解释
假设有张表，叫table1，里面有5000万行数据，假设预计全表扫描1次需要1个小时，我们从过程来看：
1、在1点钟，有个用户A发出了select * fromtable1;此时不管将来table1怎么变化，正确的结果应该是用户A会看到在1点钟这个时刻table1的内容。这个是没有疑问的。
2、在1点30分，有个用户B执行了update命令，更新了table1表中的第4000万行的这条记录，这时，用户A的全表扫描还没有到达第4000万条。毫无疑问，这个时候，第4000万行的这条记录是被写到了回滚段里去了的，我假设是回滚段RBS1，如果用户A的全表扫描到达了第4000万行，是应该会正确的从回滚段RBS1中读取出1点钟时刻的内容的。(未更新前的数据）
3、这时，用户B将他刚才做的操作commit了，但是这时，系统仍然可以给用户A提供正确的数据，因为那第4000万行记录的内容仍然还在回滚段RBS1里，系统可以根据SCN来到回滚段里找到正确的数据，但是大家注意到，这时记录在RBS1里的第4000万行记录已经发生了一点重大的改变：就是这个第4000万行的在回滚段RBS1里的数据有可能随时被覆盖掉，因为这条记录已经被提交了！
4、由于用户A的查询时间漫长，而业务在一直不断的进行，RBS1回滚段在被多个不同的tracnsaction使用着，这个回滚段里的extent循环到了第4000万行数据所在的extent，由于这条记录已经被标记提交了，所以这个extent是可以被其他transaction覆盖掉的！
5、到了1点40分，用户A的查询终于到了第4000万行，而这时已经出现了第4条说的情况，需要到回滚段RBS1去找数据，但是已经被覆盖掉了，于是01555就出现了。
?
ORA-01555 Explanation
There are two fundamental causes of the error ORA-01555 that are aresult of Oracle trying to attain a ‘read consistent‘ image. Theseare :o The rollback information itself is overwritten so thatOracle is unable to rollback the (committed) transaction entries toattain a sufficiently old enough version of the block.o Thetransaction slot in the rollback segment‘s transaction table(stored in the rollback segment‘s header) is overwritten， andOracle cannot rollback the transaction header sufficiently toderive the original rollback segment transaction slot.Both of thesesituations are discussed below with the series of steps that causethe ORA-01555. In the steps， reference is made to ‘QENV‘. ‘QENV‘ isshort for ‘Query Environment‘， which can be thought of as theenvironment that existed when a query is first started and to whichOracle is trying to attain a read consistent image. Associated withthis environment is the SCN (System Change Number) at that time andhence， QENV 50 is the query environment with SCN 50.CASE 1 -ROLLBACK OVERWRITTENThis breaks down into two cases: anothersession overwriting the rollback that the current session requiresor the case where the current sessionoverwrites the rollbackinformation that it requires. The latter is discussed in thisarticle because this is usually the harder one tounderstand.Steps:1. Session 1 starts query at time T1 and QENV 502.Session 1 selects block B1 during this query3. Session 1 updatesthe block at SCN 514. Session 1 does some other work that generatesrollback information.5. Session 1 commits the changes made in steps‘3‘ and ‘4‘.
(Now other transactions are free to overwrite this rollbackinformation)6. Session 1 revisits the same block B1 (perhaps for adifferent row).Now， Oracle can see from the block‘s header that ithas been changed and it is later than the required QENV (which was50). Therefore we need to get an image of the block as of thisQENV.If an old enough version of the block can be found in thebuffer cache then we will use this， otherwise we need to rollbackthe current block to generate another version of the block as atthe required QENV.It is under this condition that Oracle may not beable to get the required rollback information because Session 1‘schanges have generated rollback information that has overwritten itand returns the ORA-1555 error.CASE 2 - ROLLBACK TRANSACTION SLOTOVERWRITTEN1. Session 1 starts query at time T1 and QENV 502.Session 1 selects block B1 during this query3. Session 1 updatesthe block at SCN 514. Session 1 commits the changes
(Now other transactions are free to overwrite this rollbackinformation)5. A session (Session 1， another session or a number ofother sessions) then use the same rollback segment for a series ofcommitted transactions.These transactions each consume a slot inthe rollback segment transaction table such that it eventuallywraps around (the slots are written to in a circular fashion) andoverwrites all the slots. Note that Oracle is free to reuse theseslots since all transactions are committed.6. Session 1‘s querythen visits a block that has been changed since the initial QENVwas established. Oracle therefore needs to derive(获得) an image ofthe block as at that point in time.Next Oracle attempts to lookupthe rollback segment header‘s transaction slot pointed to by thetop of the data block. It then realises that this has beenoverwritten and attempts to rollback the changes made to therollback segment header to get the original transaction slotentry.If it cannot rollback the rollback segment transaction tablesufficiently it will return ORA-1555 since Oracle can no longerderive the required version of the data block.It is also possibleto encounter a variant of the transaction slot being overwrittenwhen using block cleanout. This is briefly described below :Session1 starts a query at QENV 50. After this another process updates theblocks that Session 1 will require. When Session 1 encounters theseblocks it determines that the blocks have changed and have not yetbeen cleaned out (via delayed block cleanout). Session 1 mustdetermine whetherthe rows in the block existed at QENV 50， weresubsequently changed，In order to do this， Oracle must look at therelevant rollback segment transaction table slot to determine thecommitted SCN. If this SCN is after the QENV then Oracle must tryto construct an older version of the block and if it is before thenthe block just needs clean out to be good enough forthe QENV.If thetransaction（处理） slot has been overwritten and the transaction tablecannot be rolled back to a sufficiently old enough version thenOracle cannot derive the block image and will returnORA-1555.(Note: Normally Oracle can use an algorithm fordetermining a block‘s SCN during block cleanout even when therollback segment slot has been overwritten.But in this case Oraclecannot guarantee that the version of the block has not changedsince the start of the query).?解决问题的办法?This section lists some of the solutions that can be used toavoid（避免）the ORA-01555 problems discussed in this article. Itaddresses the cases where rollback segment information isoverwritten by the same session and when the rollback segmenttransaction table entry is overwritten.It is worthhighlighting（最重要的） that if a single session experiences theORA-01555 and it is not one of the special cases listed at the endof this article， then the session must be using an Oracleextension延伸 whereby由此 fetches across commits are tolerated. Thisdoes not follow the ANSI model and in the rare cases whereORA-01555 is returned one of the solutions below must be used.CASE1 - ROLLBACK OVERWRITTEN1.Increase size of rollback segment whichwill reduce the likelihood of overwriting rollback information thatis needed.2.Reduce the number of commits (same reason as 1).3.Runthe processing against a range of data rather than the wholetable.
(Same reason as 1).4.Add additional（附加） rollback segments. Thiswill allow the updates etc. to be spread across more rollbacksegments thereby reducing the chances of overwriting requiredrollback information.5.If fetching across commits， the code can bechanged so that this is not done.6.Ensure that the outer selectdoes not revisit the same block at different times during theprocessing. This can be achieved by :- Using a full table scanrather than an index lookup
- Introducing a dummy sort so that we retrieve all the data， sortit and then sequentially visit these data blocks.CASE 2 - ROLLBACKTRANSACTION SLOT OVERWRITTEN1. Use any of the methods outlinedabove except for ‘6‘. This will allow transactions to spread theirwork across multiple rollback segments therefore reducing thelikelihood or rollback segment transaction table slots beingconsumed.2. If it is suspected that the block cleanout variant isthe cause， then force block cleanout to occur prior to thetransaction that returns the ORA-1555. This can be achieved byissuing the following in SQL*Plus， SQL*DBA or Server Manager :altersession set optimizer_goal = rule;
select count(*) from table_name;If indexes are being accessed thenthe problem may be an index block and clean out can be forced byensuring that all the index is traversed. Eg， if the index is on anumeric column with a minimum value of 25 then the following querywill force cleanout of the index :select index_column fromtable_name where index_column > 24;Examples
~~~~~~~~
Listed below are some PL/SQL examples that can be used toillustrate the ORA-1555 cases given above. Before these PL/SQLexamples will return this error the database must be configured asfollows :o Use a small buffer cache (db_block_buffers).REASON: Youdo not want the session executing the script to be able to find oldversions of the block in the buffer cache which can be used tosatisfy a block visit without requiring the rollback information.oUse one rollback segment other than SYSTEM.REASON: You need toensure that the work being done is generating rollback informationthat will overwrite the rollback information required.o Ensure thatthe rollback segment is small.REASON: See the reason for using onerollback segment.ROLLBACK OVERWRITTENrem * 1555_a.sql -
rem * Example of getting ora-1555 "Snapshot too old" by
rem * session overwriting the rollback information required
rem * by the same session.drop table bigemp;
create table bigemp (a number， b varchar2(30)， done char(1));droptable dummy1;
create table dummy1 (a varchar2(200));rem * Populate the exampletables.
begin
for i in 1..4000 loop
insert into bigemp values (mod(i，20)， to_char(i)， ‘N‘);
if mod(i，100) = 0 then
insert into dummy1 values (‘ssssssssssss‘);
commit;
end if;
end loop;
commit;
end;
/rem * Ensure that table is ‘cleaned out‘.
select count(*) from bigemp;declare
-- Must use a predicate so that we revisit a changed block at adifferent
-- time.-- If another tx is updating the table then we may not needthe predicate
cursor c1 is select rowid， bigemp.* from bigemp where a< 20;begin
for c1rec in c1 loopupdate dummy1 set a = ‘aaaaaaaa‘;
update dummy1 set a = ‘bbbbbbbb‘;
update dummy1 set a = ‘cccccccc‘;
update bigemp set done=‘Y‘ where c1rec.rowid = rowid;
commit;
end loop;
end;
/ROLLBACK TRANSACTION SLOT OVERWRITTENrem * 1555_b.sql - Example ofgetting ora-1555 "Snapshot too old" by
rem *overwriting the transaction slot in the rollback
rem *segment header. This just uses one session.drop tablebigemp;
create table bigemp (a number， b varchar2(30)， done char(1));rem *Populate demo table.
begin
for i in 1..200 loop
insert into bigemp values (mod(i，20)， to_char(i)， ‘N‘);
if mod(i，100) = 0 then
commit;
end if;
end loop;
commit;
end;
/drop table mydual;
create table mydual (a number);
insert into mydual values (1);
commit;rem * Cleanout demo table.
select count(*) from bigemp;declarecursor c1 is select * frombigemp;begin-- The following update is required to illustrate theproblem if block
-- cleanout has been done on ‘bigemp‘. If the cleanout (above) iscommented
-- out then the update and commit statements can be commented andthe
-- script will fail with ORA-1555 for the block cleanoutvariant.
update bigemp set b = ‘aaaaa‘;
commit;for c1rec in c1 loop
for i in 1..20 loop
update mydual set a=a;
commit;
end loop;
end loop;
end;
/Special Cases
~~~~~~~~~~~~~
There are other special cases that may result in an ORA-01555.These are given below but are rare and so not discussed in thisarticle :o Trusted Oracle can return this if configured in OS MACmode. Decreasing LOG_CHECKPOINT_INTERVAL on the secondary databasemay overcome the problem.o If a query visits a data block that hasbeen changed by using the Oracle discrete transaction facility thenit will return ORA-01555.o It is feasible that a rollback segmentcreated with the OPTIMAL clause maycause a query to returnORA-01555 if it has shrunk during the life of the query causingrollback segment information required to generate consistent readversions of blocks to be lost.Summary
~~~~~~~
This article has discussed the reasons behind the error ORA-01555"Snapshot too old"， has provided a list of possible methods toavoid the error when it is encountered， and has provided simplePL/SQL scripts that illustrate the cases discussed.
