Resolving ORA-01555 Error: Snapshot Too Old (KBA1827)
At a Glance
Note
: The earlier large DML transaction could have been performed on the database even before the dSource was created or as part of a large transaction to create the table.
| ORA Error: | ORA-01555: Snapshot Too Old | |
|---|---|---|
| Affects ME versions: | This issue affects all Masking Versions: 4.x, 5.0, 5.1, 5.2, and 5.3. | |
| Root Cause: | The root cause of ORA-015555 is that UNDO is not present to satisfy a read-consistent request. This can be because of either one of two reasons:
Related to masking, this can happen as a result of one of the following cases:
|
|
| Best Practices: | There are some Best Practices for an Oracle masking job. These points will also reduce the chance of encountering ORA-01555.
|
|
| Solution 1 or 2? |
This depends on what caused the ORA-01555:
|
|
| Solution 1: Delayed Block Cleanout |
If the table is configured according to Best Practice, and a large DML transaction had been performed on the database earlier then these steps are needed. Delayed Block Cleanout is the process where Oracle completes a block cleanup after the transaction commits if Oracle can't perform a fast commit at the end of the transaction. Some notes about the SQL below:
|
|
| Solution 2: Manage UNDO Tablespace |
If solution 1 fails then the UNDO Tablespace is too small. The UNDO Tablespace is managed by Oracle (to its best ability). The action here is to increase the size of the UNDO tablespace or create a new and bigger UNDO Tablespace. Contact your DBA for assistance. |
Issue
ORA-01555 is a complex error. This is not a complete overview of ORA-01555 and there can also be other factors that are not detailed in this document that can cause the error.
Two scenarios explained:
- Current DML transactions caused the issue:
This is unlikely in a masking job configured according to the Best Practice. - An earlier DML transaction caused the issue:
This happens in any job and is due to an earlier large DML and Delayed Block Cleanout.
Not enough UNDO
This is unlikely the cause when using masking. The root cause here is that data is being changed at the same time it’s being read, and there is insufficient UNDO to satisfy read-consistent requests.
However, on an optimal masking job, this should not happen, as the SELECT statement is fetching 'virgin' unmasked blocks.
Delayed Block Cleanout
For masking job, this is the likely cause. The ORA-01555 may occur because of Delayed Block Cleanout… which is caused by an earlier large transaction (for examples UPDATE, INSERT, and DELETE ) on the masked table(s).
This article goes through both these scenarios and what to do to resolve these issues.
Best Practice and Optimal Masking Job
The diagram below shows an Optimal Masking Job in progress. Each rectangle represents an Oracle data block. The white blocks are unmasked data. The grey blocks are data read by the masking engine and are being transformed. The orange blocks are data currently being UPDATED and yet to be COMMITTED. The green blocks are masked committed data.
The fetch in the SELECT will read the data. On an optimal masking job, this will be using a Full Table Scan (FTS) and block after block is read (the order of the block). After masking transformation, the rows are updated and Oracle stores the original values in the UNDO Segment.
This means we can mask as many rows as we want - the Masked Committed values (pictured green) will never be touched after the commit and the SELECT will always fetch 'virgin' unmasked data (white below).
On an Optimal Masking Job:
- Do use 'ROWID' as Logical Key - this will ensure Full Table Scan (FTS).
- No 'WHERE' or 'ORDER BY' which will otherwise change the row order - this will ensure Full Table Scan (FTS).
- No indexes on masked columns - having indexes will make changes in additional Oracle data blocks used for indexes.
- Ensure that there is no external transaction running which will change the data on the masked table.
- Ensure Delayed Block Cleanout has been performed on all tables
In the example, there are 18 grey blocks which are around 18,000 rows. These are loaded into the masking engine and buffered between the masking steps. The Transaction has Updated around 8,000 rows out of 10,000 (default Commit Size) but not yet committed (these records are highlighted in orange above).
Masking Execution with ORA-01555
What happens when the Oracle database throws ORA-01555?
There are a few key points here:
- SELECT
- It is the SELECT that causes ORA-01555.
- As discussed, this happens due to one of these two reasons:
- Current DML transactions caused the issue:
- This is unlikely in a masking job configured according to the Best Practice.
- An earlier DML transaction caused the issue:
- This happens in any job and is due to an earlier large DML and Delayed Block Cleanout.
- Current DML transactions caused the issue:
Solution
The solution is to always run masking jobs:
- in accordance with the Best Practice; and
- ensure sufficient UNDO tablespace; and
- Performed Delayed Block Cleanout on all tables prior to running the masking job.
The Following Block Cleanout SQL Script will force a Delayed Block Cleanout.
- The script can be executed before on the tables (recommended before dSource ingestion) or be executed as a Pre-Script in the masking job.
ALTER session SET events '10949 trace name context forever, level 1'; SELECT count(*) FROM [TABLE_with_ORA-01555] WHERE [COLUMN_without_Index] = 'foo';
Note: Testing has shown no or little impact on the performance when running the Block Cleanout SQL Script. In the tests performed using the Block Cleanout SQL Script, the performance was actually better by around 10% and that included the execution of the script.