Managing Undo Data: Transactions and Undo Segments

Undo data plays a key role in maintaining data consistency, providing read consistency, rolling back transactions, and recovering the database.

Broadly speaking, it paves the way to the following Oracle Database features:

1. Data Consistency: When a transaction modifies data, the original data is copied into the undo tablespace before any changes are made. This is known as undo data. If the transaction is rolled back, Oracle uses the undo data to restore the original data.
2. Read Consistency: When a query is executed, Oracle uses undo data to provide a consistent view of the data as of the start of the query. This means that even if the data is changed by other transactions while the query is running, the query will still return the data as it was at the start of the query.
3. Transaction Rollback: If a transaction needs to be rolled back (either because the user issued a ROLLBACK statement or because an error occurred), Oracle uses the undo data to undo the changes made by the transaction.
4. Database Recovery: In the event of a system failure, Oracle uses undo data during the recovery process to undo any uncommitted transactions that were in progress at the time of the failure.

The management of undo data is handled automatically by Oracle, but DBAs can influence its behavior through various initialization parameters and by properly sizing the undo tablespace. It’s important to monitor undo usage and tune accordingly to ensure optimal database performance.

In other words, managing undo data is an important aspect of database administration because it affects the performance of SQL queries and transactions, and the amount of disk space that is used. Inefficient use of undo space can lead to various errors and performance issues. Therefore, it’s crucial to understand and manage undo data effectively.

Undo Segments

Undo segments are a fundamental part of Oracle Database that handle data manipulation language (DML) operations. When a DML operation occurs, Oracle Database needs to keep a copy of the data before it was modified. The old data is stored in undo segments until the session is committed, rollbacked or killed.

Undo Tablespaces

An undo tablespace is a type of tablespace specifically dedicated to storing undo segments. Each Oracle Database instance has a single undo tablespace. The undo tablespace contains one or more undo segments that maintain transaction history necessary to roll back transactions and provide read consistency.

A Hands-On session.

As a proof of concept and to illustrate the monitoring of current transactions and the locks they hold, we are going to perform a massive update of our employees table and subsequently consult the status of the v$transaction and v$locked_object views, which will give us the number of retained and blocked records respectively.

column user format a10
column sid  format a10
column username format a10
column owner format a10
column object_name format a10
column object_type format a10
column osuser format a20
column machine format a20


SQL> select user, sys_context('USERENV', 'SID') sid from dual;

USER       SID       
---------- ----------
SYS        169       

SQL> update hr.employees
set salary = round(salary * 1.05)
;

107 rows updated.


-- Before the commit or rollback, let's see the number of records and used undo blocks stored in the undo data. 
SQL> select s.sid, s.serial#, s.username, t.used_urec, t.used_ublk
from   v$session s,
       v$transaction t
where  s.saddr = t.ses_addr;

       SID    SERIAL# USERNAME    USED_UREC  USED_UBLK
---------- ---------- ---------- ---------- ----------
       169      22701 SYS               107          2


-- Now, let's query the locked records and who is locking them.
SQL> SELECT    o.owner, o.object_name, o.object_type, s.sid, s.serial#, s.status, s.osuser, s.machine
FROM v$locked_object l, v$session s, dba_objects o
WHERE s.sid = l.session_id
  AND l.object_id = o.object_id;

OWNER      OBJECT_NAM OBJECT_TYP        SID    SERIAL# STATUS   OSUSER               MACHINE             
---------- ---------- ---------- ---------- ---------- -------- -------------------- --------------------
HR         EMPLOYEES  TABLE             169      22701 ACTIVE   carlos               MacBook.local   


SQL> rollback;
Rollback complete.

Only the session owner can commit or roll back the changes. The only way to unlock those records from a different session is to terminate the blocking session.

ALTER SYSTEM KILL SESSION 'sid,serial#';

--In our sample case:
ALTER SYSTEM KILL SESSION '169,22701';

Best practices.

When performing massive transactions is necessary to avoid saturation of the Undo tablespace and maintain database performance. Here are some best practices:

1. Proper Sizing of Undo Tablespace: Ensure that the Undo tablespace is adequately sized to handle the volume of data changes. Monitor the usage of the Undo tablespace regularly and adjust its size as needed.
2. Use Bulk Operations: Whenever possible, use bulk operations (BULK COLLECT, FORALL) instead of row-by-row processing. Bulk operations are more efficient and can significantly reduce the amount of undo generated.
3. Commit Frequently: For long-running transactions, consider committing the transaction in smaller chunks to reduce the amount of undo data. But be aware that this must be done carefully to maintain data consistency and integrity.
4. Optimize SQL Statements: Poorly written SQL can cause unnecessary undo. Make sure your SQL statements are optimized for performance.
5. Use Appropriate Isolation Level: Use the READ COMMITTED isolation level if possible. The SERIALIZABLE level can hold more locks and generate more undo.
6. Avoid Long-Running Transactions: Long-running transactions can cause the undo tablespace to fill up quickly. If possible, break down large transactions into smaller ones.
7. Monitor Undo Space: Regularly monitor the amount of undo space used by your transactions. Oracle provides several dynamic performance views (such as V$UNDOSTAT, V$TRANSACTION) that can help with this.

Remember, managing undo space effectively is crucial for maintaining good database performance. If you have any more questions or need further clarification, feel free to ask. I’m here to help!

Was this post helpful? Consider subscribing or leaving a comment.