You might have heard the terms UNDO and REDO thrown around in conversations around space usage. In this tip, let us get a basic idea of what UNDO (formerly called ROLLBACK) is in Oracle. I promise you that this will be an interesting read and you will be happy you read this.
Unlike SQL Server (default behavior), all Oracle transactions are explicit. In other words, a COMMIT or ROLLBACK has to accompany one or more DML (Data Manipulation Language) statements. ROLLBACK is assumed if the session dies or fails to COMMIT DML explicitly.
Oracle keeps the “before image” of the changed data in UNDO. The data is stored like regular data but in an UNDO tablespace. A common misconception about UNDO is that it is associated with a SQL statement – it is not. UNDO is associated with a session. Specifically, the amount of “ACTIVE” UNDO used is proportional to the amount of uncommitted data in a transaction within the session.
Why is UNDO needed anyway?
Within a transaction, when data is changed, Oracle has to protect the changes physically in UNDO tablespaces for various reasons
- To be able to undo the changes if the user issues a ROLLBACK for the transaction
- To provide the ability to query the data “before changes” for sessions outside the transaction modifying the data
- Maintain information to provide database consistency upon recovery, in case the database was to crash or get corrupted
- Support Oracle’s flashback query feature – the ability to query data as of a point in time in the past
An interesting component of UNDO is a database parameter named UNDO_RETENTION. Its value is expressed in seconds. To understand UNDO_RETENTION, we have to understand how Oracle querying works
Here is what most people don’t know
- Say you issue a query at 10:09 AM.
- The query takes 1 hour to run
- The underlying data that the query criteria satisfies has been altered and committed by another user at 10:20 AM.
- Your query results come back at 11:09 AM
If someone were to ask you, would you see the changes made by the other user in your results, what would be your answer? Yes or No? A lot of people would have said “may be”. Some would have said “yes” and a few would have said “no”. “No” is actually the right answer.
Oracle always satisfies the queries with results as they existed as of the exact time the query was initiated irrespective of what happened to the data in the time it took to run the query. That does not mean it will succeed but that is what it will try to do and the term for it is “read consistency”. You would run into the infamous error ‘ORA-01555 snapshot too old‘ if Oracle was unable to give you a read consistent picture.
To be able to do this magic, Oracle has to keep the original data around to satisfy the query. Since, Oracle does not know ahead of time that your query takes an hour to run, it cannot also know how long to keep data as it existed precisely at 10:09 AM around. UNDO_RETENTION initialization parameter tells Oracle how long it should keep old data (that has now changed) around.
This query allows you to the overall UNDO picture at the system level
SELECT tablespace_name, status, ROUND(sum_bytes / (1024*1024), 0) AS used_MB, ROUND(undo_size / (1024*1024), 0) AS total_MB, ROUND((sum_bytes / undo_size) * 100, 0) AS used_percent, CASE WHEN status = 'ACTIVE' THEN 'Active Extents - Undo data that is part of the active transaction.' WHEN status = 'EXPIRED' THEN 'Expired Extents - Undo data whose age is greater than the undo retention period.' WHEN status = 'UNEXPIRED' THEN 'Unexpired Extents - Undo data whose age is less than the undo retention period.' END AS status_description FROM ( SELECT status, sum(bytes) sum_bytes FROM dba_undo_extents GROUP by status ), ( SELECT tablespace_name, SUM(a.bytes) undo_size FROM dba_tablespaces c INNER JOIN v$tablespace b ON b.name = c.tablespace_name INNER JOIN v$datafile a on a.ts# = b.ts# WHERE c.contents = 'UNDO' AND c.status = 'ONLINE' GROUP BY tablespace_name )
In a future post, we will see the different components of UNDO (based on the value of “status” above).