Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: commit or not commit inside a cursor loop
Andy,
this is a very good procedure to avoid the risk of this ORA-1555. I
once saw Tom Kyte give an example on how to do this, but I can't find
it again. I think this note from metalink is very useful in
understanding what really happens when we get an ORA-1555 error, and
ways to avoid it:
Overview
This article will discuss the circumstances under which a query can
return the
Oracle error ORA-01555 "snapshot too old (rollback segment too
small)". The
article will then proceed to discuss actions that can be taken to
avoid the
error and finally will provide some simple PL/SQL scripts that
illustrate the
issues discussed.
Terminology
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.
In addition to this, two key concepts are briefly covered below which
help in
the understanding of ORA-01555:
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 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').
2. DELAYED BLOCK CLEANOUT:
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').
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.)
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
There are two fundamental causes of the error ORA-01555 that are a
result of
Oracle trying to attain a 'read consistent' image. These are :
o The rollback information itself is overwritten so that Oracle is
unable to
rollback the (committed) transaction entries to attain a sufficiently
old enough
version of the block.
o The transaction slot in the rollback segment's transaction table
(stored in
the rollback segment's header) is overwritten, and Oracle cannot
rollback the
transaction header sufficiently to derive the original rollback
segment
transaction slot.
Both of these situations are discussed below with the series of steps
that cause
the ORA-01555. In the steps, reference is made to 'QENV'. 'QENV' is
short for
'Query Environment', which can be thought of as the environment that
existed
when a query is first started and to which Oracle is trying to attain
a read
consistent image. Associated with this environment is the SCN
(System Change Number) at that time and hence, QENV 50 is the query
environment
with SCN 50.
CASE 1 - ROLLBACK OVERWRITTEN
This breaks down into two cases: another session overwriting the
rollback that
the current session requires or the case where the current session
overwrites
the rollback information that it requires. The latter is discussed in
this
article because this is usually the harder one to understand.
Steps:
Now, Oracle can see from the block's header that it has been
changed and
it is later than the required QENV (which was 50). Therefore we need
to get an
image of the block as of this QENV.
If an old enough version of the block can be found in the
buffer cache
then we will use this, otherwise we need to rollback the current block
to
generate another version of the block as at the required QENV.
It is under this condition that Oracle may not be able to get
the
required rollback information because Session 1's changes have
generated
rollback information that has overwritten it and returns the ORA-1555
error.
CASE 2 - ROLLBACK TRANSACTION SLOT OVERWRITTEN
These transactions each consume a slot in the rollback segment
transaction table such that it eventually wraps around (the slots are
written to
in a circular fashion) and overwrites all the slots. Note that Oracle
is free to
reuse these slots since all transactions are committed.
6. Session 1's query then visits a block that has been changed
since the
initial QENV was established. Oracle therefore needs to derive an
image of the
block as at that point in time.
Next Oracle attempts to lookup the rollback segment header's
transaction
slot pointed to by the top of the data block. It then realises that
this has
been overwritten and attempts to rollback the changes made to the
rollback
segment header to get the original transaction slot entry.
If it cannot rollback the rollback segment transaction table
sufficiently
it will return ORA-1555 since Oracle can no longer derive the required
version
of the data block.
It is also possible to encounter a variant of the transaction slot
being
overwritten when using block cleanout. This is briefly described below
:
Session 1 starts a query at QENV 50. After this another process
updates the
blocks that Session 1 will require. When Session 1 encounters these
blocks it
determines that the blocks have changed and have not yet been cleaned
out (via
delayed block cleanout). Session 1 must determine whether the rows in
the block
existed at QENV 50, were subsequently changed,
In order to do this, Oracle must look at the relevant rollback
segment
transaction table slot to determine the committed SCN. If this SCN is
after the
QENV then Oracle must try to construct an older version of the block
and if it
is before then the block just needs clean out to be good enough for
the QENV.
If the transaction slot has been overwritten and the transaction
table cannot
be rolled back to a sufficiently old enough version then Oracle cannot
derive
the block image and will return ORA-1555.
(Note: Normally Oracle can use an algorithm for determining a block's
SCN
during block cleanout even when the rollback segment slot has been
overwritten.
But in this case Oracle cannot guarantee that the version of the block
has not
changed since the start of the query).
Solutions
This section lists some of the solutions that can be used to avoid the
ORA-01555
problems discussed in this article. It addresses the cases where
rollback
segment information is overwritten by the same session and when the
rollback
segment transaction table entry is overwritten.
It is worth highlighting that if a single session experiences the
ORA-01555 and
it is not one of the special cases listed at the end of this article,
then the
session must be using an Oracle extension whereby fetches across
commits are
tolerated. This does not follow the ANSI model and in the rare cases
where
ORA-01555 is returned one of the solutions below must be used.
CASE 1 - ROLLBACK OVERWRITTEN
CASE 2 - ROLLBACK TRANSACTION SLOT OVERWRITTEN
alter session set optimizer_goal = rule; select count(*) from table_name; If indexes are being accessed then the problem may be an indexblock and
select index_column from table_name where index_column > 24;
Examples
Listed below are some PL/SQL examples that can be used to illustrate
the
ORA-1555 cases given above. Before these PL/SQL examples will return
this error
the database must be configured as follows :
o Use a small buffer cache (db_block_buffers).
REASON: You do not want the session executing the script to be
able to find
old versions of the block in the buffer cache which can be used to
satisfy a
block visit without requiring the rollback information.
o Use one rollback segment other than SYSTEM.
REASON: You need to ensure that the work being done is generating
rollback
information that will overwrite the rollback information required.
o Ensure that the rollback segment is small.
REASON: See the reason for using one rollback segment.
ROLLBACK OVERWRITTEN
rem * 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));
drop table dummy1;
create table dummy1 (a varchar2(200));
rem * Populate the example tables.
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;
rem * Ensure that table is 'cleaned out'. select count(*) from bigemp;
declare
begin
for c1rec in c1 loop
update dummy1 set a = 'aaaaaaaa'; update dummy1 set a = 'bbbbbbbb'; update dummy1 set a = 'cccccccc'; update bigemp set done='Y' where c1rec.rowid = rowid; commit;
ROLLBACK TRANSACTION SLOT OVERWRITTEN
rem * 1555_b.sql - Example of getting ora-1555 "Snapshot too old" by rem * overwriting the transaction slot in the rollback rem * segment header. This just uses one session.
drop table bigemp;
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;
drop table mydual;
create table mydual (a number);
insert into mydual values (1);
commit;
rem * Cleanout demo table.
select count(*) from bigemp;
declare
cursor c1 is select * from bigemp;
begin
for c1rec in c1 loop
for i in 1..20 loop update mydual set a=a; commit; end loop;
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 this article :
o Trusted Oracle can return this if configured in OS MAC mode.
Decreasing
LOG_CHECKPOINT_INTERVAL on the secondary database may overcome the
problem.
o If a query visits a data block that has been changed by using the
Oracle
discrete transaction facility then it will return ORA-01555.
o It is feasible that a rollback segment created with the OPTIMAL
clause
maycause a query to return ORA-01555 if it has shrunk during the life
of the
query causing rollback segment information required to generate
consistent read
versions 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 to avoid the error when
it is
encountered, and has provided simple PL/SQL scripts that illustrate
the cases
discussed.
Amen
Daniel Received on Fri May 30 2003 - 09:59:18 CDT