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:
- 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.
- 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.
- 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.
- 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.
We recently explained how to resize the undo tablespace. Be sure to check out this post.
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:
- 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.
- 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.
- 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.
- Optimize SQL Statements: Poorly written SQL can cause unnecessary undo. Make sure your SQL statements are optimized for performance.
- Use Appropriate Isolation Level: Use the READ COMMITTED isolation level if possible. The SERIALIZABLE level can hold more locks and generate more undo.
- 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.
- 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.