8.1. Leveraging Undo Management
Whenever a process or a user session changes data in the database, Oracle saves the old value as it existed before it was modified as undo data. This provides a number of benefits to the database user.
It lets the user change their mind and roll back, or undo, the change to the database.
It supports read-consistent queries. Once a query starts, any changes to the query's underlying tables are not reflected in the query's results.
It supports flashback query, an Oracle feature introduced in Oracle9 i. Flashback query allows a user to see how a table looked at some point in the past. As long as the undo data still exists for the requested point of time, flashback queries are possible.
In the following sections, we present all aspects of undo management. First, we will show how transactions are related to undo management and how undo records are stored in an undo tablespace along with some of the features supported by undo records. Next, we will show you how to set up the initialization parameters to specify a target for how much undo is retained in the undo tablespace; in addition, we will show you the commands needed to guarantee that undo space is available for SELECT statements at the expense of DML commands.
Monitoring an undo tablespace is not unlike monitoring any other tablespace: you want to make sure that you have enough undo space in the tablespace to satisfy all types of user transactions, but not so much that you're wasting space that can be used for objects in other tablespaces. Therefore, we will present some methods to accurately calculate the optimal amount of undo space you will need. Finally, we will review the notification methods you can use to proactively alert you to problems with the undo tablespace.
8.1.1. Understanding Undo Segments
Undo segments, also known as rollback segments, are similar to other segments in the database, such as table or index segments, in that an undo segment is made up of extents, which in turn are made up of data blocks. Also, an undo segment contains data similar to that stored in a table. However, that is where the similarity ends. Undo segments must be stored in a special type of tablespace called an undo tablespace. Although a database can have more than one undo tablespace, only one undo tablespace can be active at any one time. Undo segments contain undo information about one or many tables involved in a transaction. Also, undo segments automatically grow and shrink as needed, acting as a circular buffer—transactions that fill up the extents in an undo segment can wrap around to the beginning of the segment if the first extent is not being used by an active transaction.
At the beginning of a transaction—in other words, when the first DML command is issued after a previous COMMIT or a user first connects to the database—the transaction is assigned to an undo segment in the undo tablespace. Any changes to any table in the transaction are recorded in the assigned undo segment. The names of the current active undo segments can be retrieved from the dynamic performance view V$ROLLNAME, as you can see in the following query:
SQL> select * from v$rollname;
USN NAME
---------- ----------------------------
0 SYSTEM
1 _SYSSMU1$
2 _SYSSMU2$
3 _SYSSMU3$
4 _SYSSMU4$
5 _SYSSMU5$
6 _SYSSMU6$
7 _SYSSMU7$8 _SYSSMU8$
9 _SYSSMU9$
10 _SYSSMU10$
11 rows selected.NOTE
The data dictionary view DBA_ROLLBACK_SEGS shows both active (online) and inactive (offline) undo segments in both the SYSTEM and undo tablespaces.
The undo segment with an undo segment number (USN) of 0 is an undo segment reserved for exclusive use by system users such as SYS or SYSTEM or if no other undo segments are online and the data being changed resides in the SYSTEM tablespace. In this example, nine other undo segments are available in the undo tablespace for user transactions.
The dynamic performance view V$TRANSACTION shows the relationship between a transaction and the undo segments. In the following query, you begin a transaction and then join V$TRANSACTION to V$ROLLNAME to find out the name of the undo segment assigned to the transaction:
SQL> set transaction name 'Update clerk salaries'; Transaction set. SQL> update hr.employees set salary = salary * 1.25 2 where job_id like '%CLERK'; 44 rows updated. SQL> select xid, status, start_time, xidusn seg_num, 2 r.name seg_name 3 from v$transaction t join v$rollname r 4 on t.xidusn = r.usn 5 where t.name = 'Update clerk salaries'; XID STATUS START_TIME SEG_NUM SEG_NAME -------- --------- ----------------- ------- ------------- 02002F00 ACTIVE 08/01/04 16:20:10 2 _SYSSMU2$ 9A140000 1 row selected.
The column XID is the internally assigned, unique transaction number assigned to this transaction, and it is assigned the undo segment _SYSSMU2$. The column XIDUSN (aliased as SEG_NUM in the query) is the undo segment number for _SYSSMU2$. A transaction can reside in only one undo segment; it cannot be moved to another undo segment. However, many different transactions can use the same undo segment.
If an extent in the assigned undo segment fills up and more space is required, the next available extent is used; if all extents in the segment are needed for current transactions, a new extent is allocated for the undo segment.
All undo segments are owned by SYS, regardless of who is making changes in a transaction. Each segment must have a minimum of two extents; the maximum number of extents in an undo segment is high: for an undo tablespace with a block size of 8KB, the default maximum number of extents per undo segment is 32,765.
During a media failure with an undo tablespace, the tablespace can be recovered using archived and online redo log files just as with any other tablespace; however, the instance must be in a MOUNT state to recover an undo tablespace.
8.1.2. Using Undo Data
Undo data is the old value of data when a process or a user changes data in a table or an index. Undo data serves four purposes in an Oracle database:
User rollback of a transaction
Read consistency of DML operations and queries
Database recovery operations
Flashback functionality
8.1.2.1. User Transaction Rollback
In Chapter 1, "Installing Oracle 10g," you learned about transactions and how they are managed within the database architecture. At the user level, you might have one or hundreds of DML commands (such as DELETE, INSERT, UPDATE, or MERGE) within a particular transaction that need to be undone by a user or a process that is making changes to one or more tables. Undoing the changes within a transaction is called rolling back part or all of the transaction. The undo information needed to roll back the changes is called, appropriately, the rollback information and is stored in a special type of tablespace called an undo tablespace.
NOTE
Configuring and sizing the undo tablespace is covered later in this chapter in the section "Configuring the Undo Tablespace."
When an entire transaction is rolled back, Oracle undoes all the changes since the beginning of the transactions, using the saved undo information in the undo tablespace, releases any locks on rows involved in the transaction, and ends the transaction.
NOTE
Locks on rows and tables are discussed later in this chapter in the section "Understanding Locks and Transactions."
If a failure occurs on the client or the network, abnormally terminating the user's connection to the database, undo information is used in much the same way as if the user explicitly rolled back the transaction, and Oracle undoes all the changes since the beginning of the transaction, using information saved in the undo tablespace.
8.1.2.2. Read Consistency
Undo also provides read consistency for users who are querying rows involved in a DML transaction by another user or session. When one user starts to make changes to a table after another user has already begun a query against the table, the user issuing the query will not see the changes to the table until after the query has completed and the user issues a new query against the table. Undo segments in an undo tablespace are used to reconstruct the data blocks belonging to the table to provide the previous values of the rows for any user issuing SELECT statements against the table before the DML statements' transaction commits.
For example, the user KELSIEJ begins a transaction at 3:00 P.M. that contains several longrunning DML statements against the EMPLOYEES table; the statements aren't expected to finish until 3:15 P.M. As each DML command is issued, the previous values of each row are saved in the transaction's undo segment. At 3:05 P.M., the user SARAHCR issues a SELECT against the EMPLOYEES table; none of the changes made so far by KELSIEJ are visible to SARAHCR. The undo tablespace provides the previous values of the EMPLOYEES table to SARAHCR and any other users querying the EMPLOYEES table between 3:00 P.M. and 3:15 P.M. Even if SARAHCR's query is still running at 3:20 P.M., the query still appears as it did at 3:00 P.M. before KELSIEJ started making changes.
NOTE
INSERT statements use little space in an undo segment; only the pointer to the new row is stored in the undo tablespace. To undo an INSERT statement, the pointer locates the new row and deletes it from the table if the transaction is rolled back.
In a few situations, either SARAHCR's query or KELSIEJ's DML statements might fail, either because the undo tablespace is not sized correctly or the undo retention period is too short.
NOTE
We will show you how to prevent these failures later in this chapter in the section "Monitoring, Configuring, and Administering Undo."
You can also apply read consistency to an entire transaction instead of just a single SELECT statement by using the SET TRANSACTION statement as follows:
SQL> set transaction read only; Transaction set.
Until the transaction is either rolled back or committed, all queries in the transaction see only changes to other tables that were committed before the transaction began. Only the following statements are permitted in a read-only transaction:
SELECT statements without the FOR UPDATE clause
LOCK TABLE
SET ROLE
ALTER SESSION
ALTER SYSTEM
In other words, a read-only transaction cannot contain any statement that changes data in a table, regardless of where the table resides. For example, although an ALTER USER command does not change data in the USERS or any other non-SYSTEM tablespace, it does change the data dictionary tables and therefore cannot be used in a read-only transaction.
8.1.2.3. Database Recovery
The undo tablespace is a key component for database recovery in the case of an instance failure. After the online redo log files bring both committed and uncommitted transactions forward to the point of the instance crash, the undo data in the tablespace is used to roll back the uncommitted transactions.
8.1.2.4. Flashback Operations
Several flashback options are supported by undo data. Flashback query and the package DBMS_ FLASHBACK, introduced in Oracle9i, allow you to query a table as of some point in the past. Flashback table, introduced in Oracle 10g, restores a table as of a point of time in the past using undo data.
8.1.3. Monitoring, Configuring, and Administering Undo
Compared with configuring rollback operations in releases previous to Oracle9i, managing undo in Oracle 10g requires little intervention. However, two particular situations will trigger intervention: either not enough undo space to handle all active transactions, or not enough undo space to satisfy long-running queries that need undo information for read consistency. Running out of undo space for transactions generates messages such as ORA-01650: Unable to extend rollback segment; long-running queries whose undo entries have been reused by current transactions typically receive the ORA-01555: Snapshot too old message.
In this section, we will show you how to configure the undo tablespace using two initialization parameters: UNDO_MANAGEMENT and UNDO_TABLESPACE. We will also present the methods available for monitoring the health of the undo tablespace, as well as using the EM Database Control's Undo Advisor to size or resize the undo tablespace. Using the dynamic performance view V$UNDOSTAT, you can calculate an optimal size for the undo tablespace if the Undo Advisor is not available. Finally, we will show you how to guarantee that long-running queries will have undo entries available, even if it means that a DML transaction fails, by using the RETENTION GUARANTEE option.
8.1.3.1. Configuring the Undo Tablespace
Manual undo management is not recommended, although it is still the default in Oracle 10g; use manual undo management only for compatibility with Oracle 8i or earlier. To configure automatic undo management, use the initialization parameters UNDO_MANAGEMENT, UNDO_TABLESPACE, and UNDO_RETENTION.
UNDO_MANAGEMENT
The parameter UNDO_MANAGEMENT specifies the way in which undo data is managed in the database: either manually using rollback segments or automatically using a single tablespace to hold undo information.
The allowed values for UNDO_MANAGEMENT are MANUAL and AUTO. To change the undo management mode, you must restart the instance. This parameter is not dynamic, as you can see in the following example:
SQL> alter system
2 set undo_management = manual;
set undo_management = manual
*
ERROR at line 2:
ORA-02095: specified initialization parameter
cannot be modifiedIf you are using an SPFILE, you can change the value of this parameter in the SPFILE only and then restart the instance for the parameter to take effect, as follows:
SQL> alter system 2 set undo_management = manual scope=spfile; System altered.
UNDO_TABLESPACE
The parameter UNDO_TABLESPACE specifies the name of the undo tablespace to use for read consistency and transaction rollback.
You can create an undo tablespace when the database is created; you can resize it later or create a new one later. In any case, only one undo tablespace can be active at any given time, unless the value of UNDO_TABLESPACE is changed while the old undo tablespace still contains active transactions. In this case, the old undo tablespace remains active until the last transaction using the old undo tablespace either commits or rolls back; all new transactions use the new undo tablespace.
If UNDO_TABLESPACE is not defined, but at least one undo tablespace exists in the database, the first undo tablespace discovered by the Oracle instance at startup is assigned to UNDO_TABLESPACE. You can find out the name of the current undo tablespace with the SHOW PARAMETER command, as in the following example:
SQL> show parameter undo_tablespace
NAME TYPE VALUE ----------------------- ----------- -------------------- undo_tablespace string UNDOTBS1
For most platforms, if an undo tablespace is not explicitly created in the CREATE DATABASE command, Oracle automatically creates one with the name SYS_UNDOTBS.
Here is an example of how you can switch the undo tablespace from UNDOTBS1 to UNDO_BATCH:
SQL> show parameter undo_tablespace NAME TYPE VALUE -------------------------- ----------- ------------------- undo_tablespace string UNDOTBS1 SQL> alter system set undo_tablespace=undo_batch; System altered. SQL> show parameter undo_tablespace NAME TYPE VALUE -------------------------- ----------- ------------------- undo_tablespace string UNDO_BATCH
UNDO_RETENTION
The parameter UNDO_RETENTION specifies, in seconds, how long undo information that has already been committed should be retained until it can be overwritten. This is not a guaranteed limit: if the number of seconds specified by UNDO_RETENTION has not been reached, and if a transaction needs undo space, already committed undo information can be overwritten.
NOTE
To guarantee undo retention, you can use the RETENTION GUARANTEE keywords for the undo tablespace, as you will see later in this chapter in the section "Guaranteeing Undo Retention."
Setting UNDO_RETENTION to zero turns on automatic undo retention tuning. This parameter is continually adjusted to retain just enough undo information to satisfy the longestrunning query to date. If the undo tablespace is not big enough for the longest-running query, automatic undo retention retains as much as possible without extending the undo tablespace. In any case, automatic undo retention attempts to maintain at least 900 seconds, or 15 minutes, of undo information.
Regardless of how long undo information is retained, it falls into one of three categories:
Uncommitted undo information Undo information that is still supporting an active transaction and is required in the event of a ROLLBACK or a transaction failure. This undo information is never overwritten.
Committed undo information Also known as unexpired undo, undo information that is no longer needed to support an active transaction but is still needed to satisfy the undo retention interval, as defined by UNDO_RETENTION. This undo can be overwritten, however, if an active transaction needs undo space.
Expired undo information Undo information that is no longer needed to support an active transaction and is overwritten when space is required by an active transaction.
Here is an example of how you can change undo retention from its current value to 12 hours:
SQL> show parameter undo_retention NAME TYPE VALUE ------------------ ----------- ----------------------- undo_retention integer 600 SQL> alter system set undo_retention = 43200; System altered. SQL> show parameter undo_retention NAME TYPE VALUE ------------------ ----------- ----------------------- undo_retention integer 43200
Unless you use the SCOPE parameter in the ALTER SYSTEM command, the change to UNDO_ RETENTION takes effect immediately and stays in effect the next time the instance is restarted. Note that changing the undo retention from 10 minutes to 12 hours will have a dramatic impact on the amount of space required in your undo tablespace.
8.1.3.2. Monitoring the Undo Tablespace
Undo tablespaces are monitored just like any other tablespace: if a specific set of space thresholds is not defined, the database default values are used; otherwise, a specific set of thresholds can be assigned. When an undo tablespace's datafiles do not have the AUTOEXTEND attribute set, transactions can fail because too many transactions are vying for too little undo space.
In Figure 8.1, you can see that the undo tablespace UNDOTBS1 is using the default thresholds for the database, issues a warning alert at 85 percent full, and issues a critical alert at 97 percent full. Ideally, you can adjust the undo tablespace when the warning alert is received and solve the space problem before the critical threshold is reached. If the undo tablespace usage tends to spike a lot, you can adjust the thresholds for UNDOTBS1 to send a warning alert at a lower threshold. In this case, the tablespace UNDOTBS1 is only at 50.27 percent capacity.
Figure 8.1. The Edit Tablespace UNDOTBS1 screen in EM Database Control
Although you can allow the datafiles in your undo tablespace to autoextend initially, turn off autoextend on its datafiles once you believe that the undo tablespace has been sized correctly. This prevents a single user from accidentally using up large amounts of disk space in the undo tablespace by neglecting to commit transactions as frequently as possible.
Figure 8.2 shows the Undo Management screen in EM Database Control. The current size of the undo tablespace is 160MB, and during the last 7 days the size of this undo tablespace has been sufficient to support the maximum undo generation rate of 977.0 KB/minute.
The EM Database Control uses the data dictionary view V$UNDOSTAT to calculate the undo usage rate and provide recommendations. V$UNDOSTAT collects 10-minute snapshots of the undo space consumption, and in conjunction with UNDO_RETENTION and the database block size, can provide an optimal undo tablespace size. In the following example, you can see the undo usage for the last several 10-minute periods:
SQL> select 2 to_char(begin_time,'yyyy-mm-dd hh24:mi:ss') 3 starttime, 4 to_char(end_time,'yyyy-mm-dd hh24:mi:ss') 5 endtime, 6 undoblks, 7 maxquerylen maxqrylen 8 from v$undostat;
STARTTIME ENDTIME UNDOBLKS MAXQRYLEN ------------------- ------------------- -------- --------- 2004-08-01 08:46:11 2004-08-01 08:48:47 13 0 2004-08-01 08:36:11 2004-08-01 08:46:11 61 0 2004-08-01 08:26:11 2004-08-01 08:36:11 31 0 . . . 2004-07-30 09:36:11 2004-07-30 09:46:11 68 0 2004-07-30 09:26:11 2004-07-30 09:36:11 30 0 2004-07-30 09:16:11 2004-07-30 09:26:11 52 0 2004-07-30 09:06:11 2004-07-30 09:16:11 626 190 2004-07-30 08:56:11 2004-07-30 09:06:11 203 0 2004-07-30 08:46:11 2004-07-30 08:56:11 66 0 2004-07-30 08:36:11 2004-07-30 08:46:11 31 0 2004-07-30 08:26:11 2004-07-30 08:36:11 73 0
Figure 8.2. The Undo Management screen in EM Database Control
The longest-running query occurred at 9:06 A.M. on July 30, 2004, which is also shown in Figure 8.2.
Running out of space in an undo tablespace can also trigger an ORA-01555: Snapshot too old error. Long-running queries that need a read-consistent view of one or more tables can be at odds with ongoing transactions that need undo space. Unless the undo tablespace is defined with the RETENTION GUARANTEE parameter (described later in this chapter in the section "Guaranteeing Undo Retention"), ongoing DML can use undo space that is needed for long-running queries. As a result, a Snapshot too old error is returned to the user executing the query, and an alert is generated. This alert is also known as a long query warning alert.
NOTE
This alert can be triggered independently of the space available in the undo tablespace if the UNDO_RETENTION initialization parameter is set too low.
Regardless of how often the Snapshot too old error occurs, the alert is generated at most once during a 24-hour period. Increasing the size of the undo tablespace or changing the value of UNDO_RETENTION does not reset the 24-hour timer. For example, an alert is generated at 10 A.M., and you add undo space at 11 A.M. The undo tablespace is still too small, and users are still receiving Snapshot too old errors at 2 P.M. You will not receive a long query warning alert until 10 A.M. the next day, but chances are you will get a phone call before then!
8.1.3.3. Sizing the Undo Tablespace Using the Undo Advisor
The EM Database Control Undo Advisor helps you determine how large an undo tablespace is necessary given adjustments to the undo retention setting.
In Figure 8.3, the Undo Advisor screen shows the automatically tuned undo retention of 753 minutes and an undo tablespace size of 94MB. If you don't expect your undo usage to increase or you don't expect to need to retain undo information longer than 753 minutes, you can drop the size of the undo tablespace if it is significantly more than 94MB.
On the other hand, if you expect to need undo information for more than 753 minutes, you can see the impact of this increase either by entering a new value for undo retention and refreshing the page or by clicking a point on the graph corresponding to the estimated undo retention. Figure 8.4 shows the results of increasing the undo retention to 103,204 minutes.
To support an undo retention setting of 103,204 minutes given the current undo usage, you must increase the undo tablespace size to 8625MB, or 8.625GB.
8.1.3.4. Sizing the Undo Tablespace Manually using V$UNDOSTAT
In the query you used in the section "Monitoring the Undo Tablespace" earlier in this chapter, you saw a spike in undo usage at 9:06 A.M. with a maximum undo usage of 626 undo blocks per second during that 10-minute interval. To size the undo tablespace using this information, you can use the following calculation:
undo_tablespace_size = UR * UPS * DB_BLOCK_SIZE
Figure 8.3. Auto-tuned undo retention settings
Figure 8.4. Specifying new undo retention settings
In this calculation, UR is the undo retention from the parameter UNDO_RETENTION, which was set to 43,200 in a previous example. UPS is the maximum undo blocks used per second from the UNDOBLKS column in V$UNDOSTAT. If our database has a block size of 8KB, the size of the undo tablespace should be:
undo_tablespace_size = 43200 * 626 * 8192 = 221537894400 = 206GB
Add about 10 to 20 percent to this calculation for unexpected spikes in undo usage.
8.1.3.5. Guaranteeing Undo Retention
By default, undo information from committed transactions (unexpired undo) is overwritten before a transaction fails because of a lack of expired undo. If your database requirements are such that you want long-running queries to succeed at the expense of DML in a transaction, such as in a data warehouse environment where a query can run for hours or even days, you can set the RETENTION GUARANTEE parameter for the undo tablespace.
This parameter is not available as an initialization parameter. Unlike most every other command or procedure in the database, it is not available via the EM Database Control; you must use ALTER TABLESPACE at the command line to set it, as in the following example:
SQL> alter tablespace undotbs1 retention guarantee; Tablespace altered.
Turning off the parameter is just as easy, as you can see in the next example:
SQL> alter tablespace undotbs1 retention noguarantee; Tablespace altered.
Different undo tablespaces can have different settings for RETENTION. As expected, you cannot set RETENTION for a tablespace that is not an undo tablespace. In the following example, you attempt to change the RETENTION setting for the USERS tablespace, and receive an error message:
SQL> select tablespace_name, contents, 2 retention from dba_tablespaces; TABLESPACE_NAME CONTENTS RETENTION ------------------------------ --------- ----------- SYSTEM PERMANENT NOT APPLY UNDOTBS1 UNDO NOGUARANTEE SYSAUX PERMANENT NOT APPLY TEMP TEMPORARY NOT APPLY USERS PERMANENT NOT APPLY EXAMPLE PERMANENT NOT APPLY OE_TRANS PERMANENT NOT APPLY
SQL> alter tablespace users retention guarantee;
alter tablespace users retention guarantee
*
ERROR at line 1:
ORA-30044: 'Retention' can only be specified
for undo tablespace