Hi folks,
Recently I was going though a very good article on undo management. I am sharing few ideas that I got which are very useful for day to day db administration.
This is a very common problem for all of us, though we work for years, still we overlooked many concepts that are very much essential. for example here are few questions I am presenting, please have a look
Q-1 What is Undo ?
Q-2 How many undo can we have for a database ?
Q-3 What is Automatic UNDO Retention ?
Q-4 What is Guaranteed UNDO Retention?
Q-5 What are the various statuses for Undo Extents?
Q-6 How to check undo growth ?
Q-7 What are the possible causes for excessive undo growth?
Q-7 What is SMU ?
These seems very simple questions yet quite effective if we have the ideas.
Answering questions
What is Undo?Oracle maintains information to nullify changes made to the database. Such information consists of records of the actions of transactions, collectively known as undo. Oracle uses the undo to do the following:
- Rollback an active transaction
- Recover a terminated transaction
- Provide read consistency
- Recovery from logical corruptions
How many undo can we have for a database ?
We can have many undo tablespaces in a database, but only one can be Active per instance.
In Oracle Real Application Clusters (RAC) enviornment, we need to have one Active undo tablespace per instance. The UNDO_TABLESPACE parameter will be used for assigning a particular undo tablespace to an instance.
What is UNDO Retention
Undo Retention refers to duration of retaining the undo data after a transaction.
After a transaction is committed, undo data is no longer needed for rollback or transaction recovery purposes. However, for consistent read purposes, long-running queries may require this old undo information for producing older images of data blocks. Furthermore, the success of several Oracle Flashback features can also depend upon the availability of older undo information. For these reasons, it is desirable to retain the old undo information for as long as possible.
Automatic undo management eliminates the complexities of managing rollback segment space and lets you exert control over how long undo is retained before being overwritten.
You can set the UNDO_RETENTION parameter to a low threshold value so that the system retains the undo for at least the time specified in the parameter.
What is Automatic UNDO Retention
There is no parameter for this, Automatic UNDO Retention is enabled by default in 10g.
Oracle Database 10g automatically tunes a parameter called the undo retention period. The undo retention period indicates the amount of time that must pass before old undo information. Which means the undo information for committed transactions can be overwritten. The database collects usage statistics and tunes the undo retention period based on these statistics and on undo tablespace size. Provided that automatic undo management is enabled, the database automatically tunes the undo retention period as follows:
The current value for tuned undo retention can be viewed by following query.
SELECT TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME,
TUNED_UNDORETENTION FROM V$UNDOSTAT;
For AUTOEXTEND undo tablespaces, the system retains undo for at least the time specified in this parameter, and automatically tunes the undo retention period to satisfy the undo requirements of the queries.
This could lead to excessive undo generation, to honor undo retention
What is Guaranteed UNDO Retention
Oracle Database 10g lets you guarantee undo retention. In Oracle 10g Release 2, you can enable and disable undo retention.
When you enable this option, the database never overwrites unexpired undo data. That is undo data whose age is less than the undo retention period.
This option is disabled by default, which means that the database can overwrite the unexpired undo data to avoid failure of DML operations if there is not enough free space left in the undo tablespace.
By enabling the guarantee option, you instruct the database not to overwrite unexpired undo data even if it means risking failure of currently active DML operations. Therefore, use caution when enabling this feature.
To enable do the following against the undo tablespace.
ALTER TABLESPACE UNDOTBS RETENTION GUARANTEE;
A typical use of the guarantee option is when you want to ensure deterministic and predictable behavior of Flashback Query by guaranteeing the availability of the required undo data.
What are the various statuses for Undo Extents
Transaction Status of the undo in the extent can be any of the following:
SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*)
FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
ACTIVE - Undo Extent is Active, Used by a transaction.
EXPIRED - Undo Extent is expired (Exceeded the Undo Retention). (Transaction Commited and passed the undo retention)
UNEXPIRED - Undo Extent will be required to honor UNDO_RETENTION.(Transactions committed but still honoring )
How to check undo growth
To understand the free space with undo tablespace.
SELECT SUM(BYTES) FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME='&UNDOTBS';
To understand state of the extents, space-used in the current undo tablespace.
SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*)
FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
To understand the no of active transactions and its undo usage.
SELECT XIDUSN, XIDSLOT, XIDSQN, USED_UBLK FROM V$TRANSACTION WHERE STATUS='ACTIVE' ;
What are the possible causes for excessive undo growth
There could be various causes for excessive undo growth. To start the diagnosis we need to understand the following.
Transactions with huge undo
It is obvious to see high undo usage when there are huge transactions.
If that is going to be the case this growth should be expected behavior.
UNDO RETENTION
Higher undo retention will cause higher undo growth. Because we wont mark the undo extents as EXPIRED till the duration of undo retention.
Disabling autoextend on datafiles of active undo tablespace will reuse the UNEXPIRED extents when it has space crunch. It is a trade-off between undo retention and undo space.
If you wish to satisfy Undo Retention, switch on autoextend in undo tablespace datafiles.
SELECT FILE_ID, AUTOEXTENSIBLE FROM DBA_DATA_FILES WHERE
TABLESPACE_NAME='&UNDOTBS';
To make those datafile auto extensible, run the following command.
ALTER DATABASE DATAFILE '&FILE_ID' AUTOEXTEND ON;
If you wish to switch off auto extend and to reuse the UNEXPIRED space, do the following
ALTER DATABASE DATAFILE '&FILE_ID' AUTOEXTEND OFF;
State of undo extents
The status of the undo extents needs to be closely monitored.
There are few bugs with different releases where EXPIRED extents are not being reused.
(a) If good number of extents in UNEXPIRED status, it could be due to high undo_retention.
SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*)
FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
What is AUM / SMU
Automatic Undo Management(AUM) is introduced in Oracle 9i, which replaces the rollback segments.
This is also called System Managed Undo(SMU) as the undo is managed by oracle.
Automatic undo management is undo-tablespace based. You allocate space in the form of an undo tablespace, instead of allocating many rollback segments in different sizes.
Oracle strongly recommends their customers to use Automatic Undo Management (AUM).
Hope this article will help you...
Recently I was going though a very good article on undo management. I am sharing few ideas that I got which are very useful for day to day db administration.
This is a very common problem for all of us, though we work for years, still we overlooked many concepts that are very much essential. for example here are few questions I am presenting, please have a look
Q-1 What is Undo ?
Q-2 How many undo can we have for a database ?
Q-3 What is Automatic UNDO Retention ?
Q-4 What is Guaranteed UNDO Retention?
Q-5 What are the various statuses for Undo Extents?
Q-6 How to check undo growth ?
Q-7 What are the possible causes for excessive undo growth?
Q-7 What is SMU ?
These seems very simple questions yet quite effective if we have the ideas.
Answering questions
What is Undo?Oracle maintains information to nullify changes made to the database. Such information consists of records of the actions of transactions, collectively known as undo. Oracle uses the undo to do the following:
- Rollback an active transaction
- Recover a terminated transaction
- Provide read consistency
- Recovery from logical corruptions
How many undo can we have for a database ?
We can have many undo tablespaces in a database, but only one can be Active per instance.
In Oracle Real Application Clusters (RAC) enviornment, we need to have one Active undo tablespace per instance. The UNDO_TABLESPACE parameter will be used for assigning a particular undo tablespace to an instance.
What is UNDO Retention
Undo Retention refers to duration of retaining the undo data after a transaction.
After a transaction is committed, undo data is no longer needed for rollback or transaction recovery purposes. However, for consistent read purposes, long-running queries may require this old undo information for producing older images of data blocks. Furthermore, the success of several Oracle Flashback features can also depend upon the availability of older undo information. For these reasons, it is desirable to retain the old undo information for as long as possible.
Automatic undo management eliminates the complexities of managing rollback segment space and lets you exert control over how long undo is retained before being overwritten.
You can set the UNDO_RETENTION parameter to a low threshold value so that the system retains the undo for at least the time specified in the parameter.
What is Automatic UNDO Retention
There is no parameter for this, Automatic UNDO Retention is enabled by default in 10g.
Oracle Database 10g automatically tunes a parameter called the undo retention period. The undo retention period indicates the amount of time that must pass before old undo information. Which means the undo information for committed transactions can be overwritten. The database collects usage statistics and tunes the undo retention period based on these statistics and on undo tablespace size. Provided that automatic undo management is enabled, the database automatically tunes the undo retention period as follows:
The current value for tuned undo retention can be viewed by following query.
SELECT TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME,
TUNED_UNDORETENTION FROM V$UNDOSTAT;
For AUTOEXTEND undo tablespaces, the system retains undo for at least the time specified in this parameter, and automatically tunes the undo retention period to satisfy the undo requirements of the queries.
This could lead to excessive undo generation, to honor undo retention
What is Guaranteed UNDO Retention
Oracle Database 10g lets you guarantee undo retention. In Oracle 10g Release 2, you can enable and disable undo retention.
When you enable this option, the database never overwrites unexpired undo data. That is undo data whose age is less than the undo retention period.
This option is disabled by default, which means that the database can overwrite the unexpired undo data to avoid failure of DML operations if there is not enough free space left in the undo tablespace.
By enabling the guarantee option, you instruct the database not to overwrite unexpired undo data even if it means risking failure of currently active DML operations. Therefore, use caution when enabling this feature.
To enable do the following against the undo tablespace.
ALTER TABLESPACE UNDOTBS RETENTION GUARANTEE;
A typical use of the guarantee option is when you want to ensure deterministic and predictable behavior of Flashback Query by guaranteeing the availability of the required undo data.
What are the various statuses for Undo Extents
Transaction Status of the undo in the extent can be any of the following:
SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*)
FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
ACTIVE - Undo Extent is Active, Used by a transaction.
EXPIRED - Undo Extent is expired (Exceeded the Undo Retention). (Transaction Commited and passed the undo retention)
UNEXPIRED - Undo Extent will be required to honor UNDO_RETENTION.(Transactions committed but still honoring )
How to check undo growth
To understand the free space with undo tablespace.
SELECT SUM(BYTES) FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME='&UNDOTBS';
To understand state of the extents, space-used in the current undo tablespace.
SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*)
FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
To understand the no of active transactions and its undo usage.
SELECT XIDUSN, XIDSLOT, XIDSQN, USED_UBLK FROM V$TRANSACTION WHERE STATUS='ACTIVE' ;
What are the possible causes for excessive undo growth
There could be various causes for excessive undo growth. To start the diagnosis we need to understand the following.
Transactions with huge undo
It is obvious to see high undo usage when there are huge transactions.
If that is going to be the case this growth should be expected behavior.
UNDO RETENTION
Higher undo retention will cause higher undo growth. Because we wont mark the undo extents as EXPIRED till the duration of undo retention.
Disabling autoextend on datafiles of active undo tablespace will reuse the UNEXPIRED extents when it has space crunch. It is a trade-off between undo retention and undo space.
If you wish to satisfy Undo Retention, switch on autoextend in undo tablespace datafiles.
SELECT FILE_ID, AUTOEXTENSIBLE FROM DBA_DATA_FILES WHERE
TABLESPACE_NAME='&UNDOTBS';
To make those datafile auto extensible, run the following command.
ALTER DATABASE DATAFILE '&FILE_ID' AUTOEXTEND ON;
If you wish to switch off auto extend and to reuse the UNEXPIRED space, do the following
ALTER DATABASE DATAFILE '&FILE_ID' AUTOEXTEND OFF;
State of undo extents
The status of the undo extents needs to be closely monitored.
There are few bugs with different releases where EXPIRED extents are not being reused.
(a) If good number of extents in UNEXPIRED status, it could be due to high undo_retention.
SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*)
FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
What is AUM / SMU
Automatic Undo Management(AUM) is introduced in Oracle 9i, which replaces the rollback segments.
This is also called System Managed Undo(SMU) as the undo is managed by oracle.
Automatic undo management is undo-tablespace based. You allocate space in the form of an undo tablespace, instead of allocating many rollback segments in different sizes.
Oracle strongly recommends their customers to use Automatic Undo Management (AUM).
Hope this article will help you...
No comments:
Post a Comment