ORA-01555 快照过旧


前一阵在导出数据时遇见的问题,后来在网上找到这篇文章,写的不错,大家分享一下.

ORA-01555 "Snapshot too old" - Detailed Explanation

ORA-01555 快照过旧--详细解释
Overview

1概述   
2This article will discuss the circumstances under which a query can return the   
3Oracle error ORA-01555 "snapshot too old (rollback segment too small)". The   
4article will then proceed to discuss actions that can be taken to avoid the   
5error and finally will provide some simple PL/SQL scripts that illustrate the   
6issues discussed.   
7本文将讨论查询返回 ORA-01555”快照过旧”(回滚段太小).本文将进一步探讨防止此错误的措施,并最终提供一些简单的用于演示的PL/SQL脚本.   
8Terminology   

术语
It is assumed that the reader is familiar with standard Oracle terminology such
as 'rollback segment' and 'SCN'. If not, the reader should first read the Oracle
Server Concepts manual and related Oracle documentation.
读者需要熟悉标准Oracle术语,如”回滚段”与”SCN”.否则,应当首先阅读Oracle Server Concepts手册与相关文档.
In addition to this, two key concepts are briefly covered below which help in
the understanding of ORA-01555:
进一步的,有助于理解ORA-01555的两个关键概念概述如下 :
1. READ CONSISTENCY:

1. 读一致性
This is documented in the Oracle Server Concepts manual and so will not be
discussed further. However, for the purposes of this article this should be read
and understood if not understood already.
文档可见Oracle Server Concepts,此处不深入讨论.但,为理解本文,如尚未理解,请阅读并理解.

Oracle Server has the ability to have multi-version read consistency which is
invaluable to you because it guarantees that you are seeing a consistent view of
the data (no 'dirty reads').
Oracle Server支持多版本读一致性,它保证你看到一致的数据(无"脏"数据),这时不可估量的.

2. DELAYED BLOCK CLEANOUT:

2. 延迟的块清出

This is best illustrated with an example: Consider a transaction that updates a
million row table. This obviously visits a large number of database blocks to
make the change to the data. When the user commits the transaction Oracle does
NOT go back and revisit these blocks to make the change permanent. It is left
for the next transaction that visits any block affected by the update to 'tidy
up' the block (hence the term 'delayed block cleanout').
用一个例子可以很好的说明:考虑一个更新100万行的表的事务.这显然需要访问大量的数据库块以修改数据.当用户提交事务时,Oracle并不重新访问这些块并进行永久性修改.这将留给下一个需要访问此更新涉及块的事务来"清洁"这些块(即,延迟的块清出).

Whenever Oracle changes a database block (index, table, cluster) it stores a
pointer in the header of the data block which identifies the rollback segment
used to hold the rollback information for the changes made by the transaction.
(This is required if the user later elects to not commit the changes and wishes
to 'undo' the changes made.)
Oracle修改任一数据库块(索引,表,簇)时,均在数据块头存放一个指针,指向保存有事务修改的回滚信息的回滚段.(这用于防止用户决定不提交更改,并希望"重做"已做的修改).

Upon commit, the database simply marks the relevant rollback segment header
entry as committed. Now, when one of the changed blocks is revisited Oracle
examines the header of the data block which indicates that it has been changed
at some point. The database needs to confirm whether the change has been
committed or whether it is currently uncommitted. To do this, Oracle determines
the rollback segment used for the previous transaction (from the block's header)
and then determines whether the rollback header indicates whether it has been
committed or not.

If it is found that the block is committed then the header of the data block is
updated so that subsequent accesses to the block do not incur this processing.

This behaviour is illustrated in a very simplified way below. Here we walk
through the stages involved in updating a data block.

STAGE 1 - No changes made

Description: This is the starting point. At the top of the
data block we have an area used to link active
transactions to a rollback
segment (the 'tx' part), and the rollback segment
header has a table that stores information upon
all the latest transactions
that have used that rollback segment.

In our example, we have two active transaction
slots (01 and 02)
and the next free slot is slot 03. (Since we are
free to overwrite committed transactions.)

Data Block 500 Rollback Segment Header 5
+----+--------------+ +----------------------+---------+
| tx | None | | transaction entry 01 |ACTIVE |
+----+--------------+ | transaction entry 02 |ACTIVE |
| row 1 | | transaction entry 03 |COMMITTED|
| row 2 | | transaction entry 04 |COMMITTED|
| ... .. | | ... ... .. | ... |
| row n | | transaction entry nn |COMMITTED|
+-------------------+ +--------------------------------+

STAGE 2 - Row 2 is updated

Description: We have now updated row 2 of block 500. Note that
the data block header is updated to point to the
rollback segment 5, transaction
slot 3 (5.3) and that it is marked uncommitted
(Active).

Data Block 500 Rollback Segment Header 5
+----+--------------+ +----------------------+---------+
| tx |5.3uncommitted|-+ | transaction entry 01 |ACTIVE |
+----+--------------+ | | transaction entry 02 |ACTIVE |
| row 1 | +-->| transaction entry 03 |ACTIVE |
| row 2 changed | | transaction entry 04 |COMMITTED|
| ... .. | | ... ... .. | ... |
| row n | | transaction entry nn |COMMITTED|
+------------------+ +--------------------------------+

STAGE 3 - The user issues a commit

Description: Next the user hits commit. Note that all that
this does is it
updates the rollback segment header's
corresponding transaction
slot as committed. It does nothing to the data
block.

Data Block 500 Rollback Segment Header 5
+----+--------------+ +----------------------+---------+
| tx |5.3uncommitted|--+ | transaction entry 01 |ACTIVE |
+----+--------------+ | | transaction entry 02 |ACTIVE |
| row 1 | +--->| transaction entry 03 |COMMITTED|
| row 2 changed | | transaction entry 04 |COMMITTED|
| ... .. | | ... ... .. | ... |
| row n | | transaction entry nn |COMMITTED|
+------------------+ +--------------------------------+

STAGE 4 - Another user selects data block 500

Description: Some time later another user (or the same user)
revisits data block 500. We can see that there
is an uncommitted change in the
data block according to the data block's header.

Oracle then uses the data block header to look up
the corresponding rollback segment transaction
table slot, sees that it has been committed, and
changes data block 500 to reflect the
true state of the datablock. (i.e. it performs
delayed cleanout).

Data Block 500 Rollback Segment Header 5
+----+--------------+ +----------------------+---------+
| tx | None | | transaction entry 01 |ACTIVE |
+----+--------------+ | transaction entry 02 |ACTIVE |
| row 1 | | transaction entry 03 |COMMITTED|
| row 2 | | transaction entry 04 |COMMITTED|
| ... .. | | ... ... .. | ... |
| row n | | transaction entry nn |COMMITTED|
+------------------+ +--------------------------------+

ORA-01555 Explanation

  1
  2There are two fundamental causes of the error ORA-01555 that are a result of   
  3Oracle trying to attain a 'read consistent' image. These are : 
  4
  5o The rollback information itself is overwritten so that Oracle is unable to   
  6rollback the (committed) transaction entries to attain a sufficiently old enough   
  7version of the block. 
  8
  9o The transaction slot in the rollback segment's transaction table (stored in   
 10the rollback segment's header) is overwritten, and Oracle cannot rollback the   
 11transaction header sufficiently to derive the original rollback segment   
 12transaction slot. 
 13
 14Both of these situations are discussed below with the series of steps that cause   
 15the ORA-01555. In the steps, reference is made to 'QENV'. 'QENV' is short for   
 16'Query Environment', which can be thought of as the environment that existed   
 17when a query is first started and to which Oracle is trying to attain a read   
 18consistent image. Associated with this environment is the SCN   
 19(System Change Number) at that time and hence, QENV 50 is the query environment   
 20with SCN 50. 
 21
 22CASE 1 - ROLLBACK OVERWRITTEN 
 23
 24This breaks down into two cases: another session overwriting the rollback that   
 25the current session requires or the case where the current session overwrites   
 26the rollback information that it requires. The latter is discussed in this   
 27article because this is usually the harder one to understand. 
 28
 29Steps: 
 30
 311\. Session 1 starts query at time T1 and QENV 50 
 32
 332\. Session 1 selects block B1 during this query 
 34
 353\. Session 1 updates the block at SCN 51 
 36
 374\. Session 1 does some other work that generates rollback information. 
 38
 395\. Session 1 commits the changes made in steps '3' and '4'.   
 40(Now other transactions are free to overwrite this rollback information) 
 41
 426\. Session 1 revisits the same block B1 (perhaps for a different row). 
 43
 44Now, Oracle can see from the block's header that it has been changed and   
 45it is later than the required QENV (which was 50). Therefore we need to get an   
 46image of the block as of this QENV. 
 47
 48If an old enough version of the block can be found in the buffer cache   
 49then we will use this, otherwise we need to rollback the current block to   
 50generate another version of the block as at the required QENV. 
 51
 52It is under this condition that Oracle may not be able to get the   
 53required rollback information because Session 1's changes have generated   
 54rollback information that has overwritten it and returns the ORA-1555 error. 
 55
 56CASE 2 - ROLLBACK TRANSACTION SLOT OVERWRITTEN 
 57
 581\. Session 1 starts query at time T1 and QENV 50 
 59
 602\. Session 1 selects block B1 during this query 
 61
 623\. Session 1 updates the block at SCN 51 
 63
 644\. Session 1 commits the changes   
 65(Now other transactions are free to overwrite this rollback information) 
 66
 675\. A session (Session 1, another session or a number of other sessions) then   
 68use the same rollback segment for a series of committed transactions. 
 69
 70These transactions each consume a slot in the rollback segment   
 71transaction table such that it eventually wraps around (the slots are written to   
 72in a circular fashion) and overwrites all the slots. Note that Oracle is free to   
 73reuse these slots since all transactions are committed. 
 74
 756\. Session 1's query then visits a block that has been changed since the   
 76initial QENV was established. Oracle therefore needs to derive(获得) an image of the   
 77block as at that point in time. 
 78
 79Next Oracle attempts to lookup the rollback segment header's transaction   
 80slot pointed to by the top of the data block. It then realises that this has   
 81been overwritten and attempts to rollback the changes made to the rollback   
 82segment header to get the original transaction slot entry. 
 83
 84If it cannot rollback the rollback segment transaction table sufficiently   
 85it will return ORA-1555 since Oracle can no longer derive the required version   
 86of the data block. 
 87
 88  
 89It is also possible to encounter a variant of the transaction slot being   
 90overwritten when using block cleanout. This is briefly described below : 
 91
 92Session 1 starts a query at QENV 50. After this another process updates the   
 93blocks that Session 1 will require. When Session 1 encounters these blocks it   
 94determines that the blocks have changed and have not yet been cleaned out (via   
 95delayed block cleanout). Session 1 must determine whether the rows in the block   
 96existed at QENV 50, were subsequently changed, 
 97
 98In order to do this, Oracle must look at the relevant rollback segment   
 99transaction table slot to determine the committed SCN. If this SCN is after the   
100QENV then Oracle must try to construct an older version of the block and if it   
101is before then the block just needs clean out to be good enough for the QENV. 
102
103If the transaction(处理) slot has been overwritten and the transaction table cannot   
104be rolled back to a sufficiently old enough version then Oracle cannot derive   
105the block image and will return ORA-1555. 
106
107(Note: Normally Oracle can use an algorithm for determining a block's SCN   
108during block cleanout even when the rollback segment slot has been overwritten.   
109But in this case Oracle cannot guarantee that the version of the block has not   
110changed since the start of the query). 
111
112Solutions解决问题的办法   
113~~~~~~~~~ 
114
115This section lists some of the solutions that can be used to avoid(避免)the ORA-01555   
116problems discussed in this article. It addresses the cases where rollback   
117segment information is overwritten by the same session and when the rollback   
118segment transaction table entry is overwritten. 
119
120It is worth highlighting(最重要的) that if a single session experiences the ORA-01555 and   
121it is not one of the special cases listed at the end of this article, then the   
122session must be using an Oracle extension延伸 whereby由此 fetches across commits are   
123tolerated. This does not follow the ANSI model and in the rare cases where   
124ORA-01555 is returned one of the solutions below must be used. 
125
126CASE 1 - ROLLBACK OVERWRITTEN 
127
1281\. Increase size of rollback segment which will reduce the likelihood of   
129overwriting rollback information that is needed. 
130
1312\. Reduce the number of commits (same reason as 1). 
132
1333\. Run the processing against a range of data rather than the whole table.   
134(Same reason as 1). 
135
1364\. Add additional(附加) rollback segments. This will allow the updates etc. to be   
137spread across more rollback segments thereby reducing the chances of overwriting   
138required rollback information. 
139
1405\. If fetching across commits, the code can be changed so that this is not   
141done. 
142
1436\. Ensure that the outer select does not revisit the same block at different   
144times during the processing. This can be achieved by : 
145
146\- Using a full table scan rather than an index lookup   
147\- Introducing a dummy sort so that we retrieve all the data, sort it and   
148then sequentially visit these data blocks. 
149
150CASE 2 - ROLLBACK TRANSACTION SLOT OVERWRITTEN 
151
1521\. Use any of the methods outlined above except for '6'. This will allow   
153transactions to spread their work across multiple rollback segments therefore   
154reducing the likelihood or rollback segment transaction table slots being   
155consumed. 
156
1572\. If it is suspected that the block cleanout variant is the cause, then force   
158block cleanout to occur prior to the transaction that returns the ORA-1555. This   
159can be achieved by issuing the following in SQL*Plus, SQL*DBA or Server Manager   
160: 
161
162alter session set optimizer_goal = rule;   
163select count(*) from table_name; 
164
165If indexes are being accessed then the problem may be an index block and   
166clean out can be forced by ensuring that all the index is traversed. Eg, if the   
167index is on a numeric column with a minimum value of 25 then the following query   
168will force cleanout of the index : 
169
170select index_column from table_name where index_column > 24; 
171
172Examples   
173~~~~~~~~ 
174
175Listed below are some PL/SQL examples that can be used to illustrate the   
176ORA-1555 cases given above. Before these PL/SQL examples will return this error   
177the database must be configured as follows : 
178
179o Use a small buffer cache (db_block_buffers).   
180  
181REASON: You do not want the session executing the script to be able to find   
182old versions of the block in the buffer cache which can be used to satisfy a   
183block visit without requiring the rollback information. 
184
185o Use one rollback segment other than SYSTEM. 
186
187REASON: You need to ensure that the work being done is generating rollback   
188information that will overwrite the rollback information required. 
189
190o Ensure that the rollback segment is small. 
191
192REASON: See the reason for using one rollback segment. 
193
194ROLLBACK OVERWRITTEN 
195
196rem * 1555_a.sql -   
197rem * Example of getting ora-1555 "Snapshot too old" by   
198rem * session overwriting the rollback information required   
199rem * by the same session. 
200
201drop table bigemp;   
202create table bigemp (a number, b varchar2(30), done char(1)); 
203
204drop table dummy1;   
205create table dummy1 (a varchar2(200)); 
206
207rem * Populate the example tables.   
208begin   
209for i in 1..4000 loop   
210insert into bigemp values (mod(i,20), to_char(i), 'N');   
211if mod(i,100) = 0 then   
212insert into dummy1 values ('ssssssssssss');   
213commit;   
214end if;   
215end loop;   
216commit;   
217end;   
218/ 
219
220rem * Ensure that table is 'cleaned out'.   
221select count(*) from bigemp; 
222
223declare   
224\-- Must use a predicate so that we revisit a changed block at a different   
225\-- time. 
226
227\-- If another tx is updating the table then we may not need the predicate   
228cursor c1 is select rowid, bigemp.* from bigemp where a < 20; 
229
230begin   
231for c1rec in c1 loop 
232
233update dummy1 set a = 'aaaaaaaa';   
234update dummy1 set a = 'bbbbbbbb';   
235update dummy1 set a = 'cccccccc';   
236update bigemp set done='Y' where c1rec.rowid = rowid;   
237commit;   
238end loop;   
239end;   
240/ 
241
242ROLLBACK TRANSACTION SLOT OVERWRITTEN 
243
244rem * 1555_b.sql - Example of getting ora-1555 "Snapshot too old" by   
245rem * overwriting the transaction slot in the rollback   
246rem * segment header. This just uses one session. 
247
248drop table bigemp;   
249create table bigemp (a number, b varchar2(30), done char(1)); 
250
251rem * Populate demo table.   
252begin   
253for i in 1..200 loop   
254insert into bigemp values (mod(i,20), to_char(i), 'N');   
255if mod(i,100) = 0 then   
256commit;   
257end if;   
258end loop;   
259commit;   
260end;   
261/ 
262
263drop table mydual;   
264create table mydual (a number);   
265insert into mydual values (1);   
266commit; 
267
268rem * Cleanout demo table.   
269select count(*) from bigemp; 
270
271declare 
272
273cursor c1 is select * from bigemp; 
274
275begin 
276
277\-- The following update is required to illustrate the problem if block   
278\-- cleanout has been done on 'bigemp'. If the cleanout (above) is commented   
279\-- out then the update and commit statements can be commented and the   
280\-- script will fail with ORA-1555 for the block cleanout variant.   
281update bigemp set b = 'aaaaa';   
282commit; 
283
284for c1rec in c1 loop   
285for i in 1..20 loop   
286update mydual set a=a;   
287commit;   
288end loop;   
289end loop;   
290end;   
291/ 
292
293Special Cases   
294~~~~~~~~~~~~~ 
295
296There are other special cases that may result in an ORA-01555. These are given   
297below but are rare and so not discussed in this article : 
298
299o Trusted Oracle can return this if configured in OS MAC mode. Decreasing   
300LOG_CHECKPOINT_INTERVAL on the secondary database may overcome the problem. 
301
302o If a query visits a data block that has been changed by using the Oracle   
303discrete transaction facility then it will return ORA-01555. 
304
305o It is feasible that a rollback segment created with the OPTIMAL clause   
306maycause a query to return ORA-01555 if it has shrunk during the life of the   
307query causing rollback segment information required to generate consistent read   
308versions of blocks to be lost. 
309
310Summary   
311~~~~~~~ 
312
313This article has discussed the reasons behind the error ORA-01555 "Snapshot too   
314old", has provided a list of possible methods to avoid the error when it is   
315encountered, and has provided simple PL/SQL scripts that illustrate the cases   
316discussed.
Published At
Categories with 数据库类
Tagged with
comments powered by Disqus