Saturday , March 2 2024

Oracle Undo Management

In this article, I will try to explain how undo is managed, the purpose of undo tablespace, its place in daily use and its sizing.

Oracle recommends that Undo management be AUTO.

The parameter that determines whether Undo will be kept minimum is UNDO_RETENTION.

If AUTOEXTEND is set to ON, it will be auto-tune.

Therefore, it makes setting the parameter somewhat unnecessary.

Therefore, the most important criterion is to set Undo Tablespace to the correct size.

The UNDO ADVISOR package is used to set the size of Undo correctly. How to use the package is as follows.

1. Learning the duration of the longest running query.

i. In the last 7 days,

ii. After a certain date,

iii. Among certain AWR reports,

It can also be learned from v$session_longops. In this view, all transactions that take longer than 6 seconds can be seen.

2. Learning the longest running query.

i. If it is not too old;

ii. If it is very old, it can be found in the dictionary.

3. Learning the required RETENTION time according to the longest running query.

i. In the last 7 days,

ii. Between a certain date,

iii. Among certain AWRs,

4. Best possible retention period is learned.

i. In the last 7 days,

ii. Between certain dates,

iii. Among certain AWRs,

5. The required Undo Size is learned according to Best possible retention. (MB)

UNDO ADVISOR can also be viewed through OEM. It will give the same results as the values returned as a result of the query.

If Show Graph is opened;

In the System Activity tab;



Leave a Reply

Your email address will not be published. Required fields are marked *