ORA-01555 Snapshot Too Old
ORA-01555 Snapshot Too
Old
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').
What
it means is that when you are executing a query, Oracle server
will provide you
a consistent view of the database as if someone
has taken a Snapshot of the database at the time
your query begins.
Hence the name “Snapshot
Too Old”.
How
Oracle Server Provide Consistent View
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Oracle server has the ability to determine whether a database
block has been modified or not with respect to your
query/transaction and if it was modified then how to retrieve
the unmodified version of that database block for
your transaction.
Whenever Oracle changes a database block (index, table, etc,) 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 decided not to commit the changes and wishes to
'undo/rollback' the changes made.)
Database block(s) also maintain transaction information in their
header using transaction table.
Upon commit, the database simply marks the relevant rollback
segment header entry as committed. Now, when one of the
changed blocks is revisited (Say during the execution of
your query) 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.
Hence to provide consistent view for your query/transaction
all it needs is to determine whether the db block your
query is using has been modified or not and
if it modified then go to the rollback segment related to
this block. Use the information there and present an
unmodified view of that db block to your query.
Reason
for ORA-01555 Snapshot Too Old
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For that it needs the relevant rollback information that it can apply to the db block and
provide your query the consistent view of that db block.When Oracle do not find
that relevant rollback information, this can be due to the fact that the ROLLBACK has
been overwritten OR the ROLLBACK TRANSACTION SLOT has been overwritten,
it produces “ORA-01555 Snapshot Too Old” Error.
Let’s have an Example:
We are taking an example of “ROLLBACK has been overwritten”.
Note: The names like M has been taken for demonstration purpose
only.
Oracle server may or may not store information like that.
Table under Consideration – EMP (Employee Table)
DB Block identifier (Arbitrary) - M
Row(s) identifier in DB Block (M) – A,C,G,J,B,L,H,N,T,S,Y etc.
When your query access data, it do that in a random fashion.
What it means is that you cannot guarantee that row C
will be read, and then row L will be read in the database
block M. Any row can be read at any time in a random fashion.
Now say one session X starts reading the rows from table EMP and
another session Y, which starts after session X, starts modifying
the rows of table EMP.
Session X reads row(s) A, G, L and S in random fashion and
meanwhile Session Y have modified row(s) N, T, B and Y.
Session Y move further and modified another row say C but due to
inappropriate size of rollback segment it overwrites the
rollback information that is required to undo the changes
to row T with its own.
Now Session X reads row T from db block M and will get to
know that the block has been modified.
Since X has started before Y, oracle has to provide the query
(related to session X) a consistent view of the database,
it will go to db block header to get the information about
the rollback segment that contain the relevant information
and hence to undo the modification of row T.
But in the end oracle will find that the required information
no longer exists(as it has been overwritten)
and hence it will throw
“ORA-01555 Snapshot Too Old” Error.
Solution
for ORA-01555 Snapshot Too Old
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1. You
should properly size your rollback segment.
2. You
should add more rollback segments to your database.
3. You
should appropriately set UNDO_RETENTION parameter depending upon the longest
running transaction/query in your database server.
4. You
should not commit too frequently, Commit only when required for transactional
purpose.
5. You
should try to tune your query and make it go faster.
6. You
should avoid fetching across commits.
You can also use Oracle
Enterprise Manager to gather the required statistics to aid your Undo
Management activity.
Comments
Post a Comment