Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 2 3 4 5 | SQL> show parameter undo_management NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string 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.
1 2 3 4 5 6 7 8 9 10 11 | SQL> show parameter undo_retention NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_retention integer 900 SQL> select min(tuned_undoretention), max(tuned_undoretention), round(avg(tuned_undoretention)) from v$undostat; MIN(TUNED_UNDORETENTION) MAX(TUNED_UNDORETENTION) ROUND(AVG(TUNED_UNDORETENTION)) ------------------------ ------------------------ ------------------------------- 1016 3941 2154 |
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,
1 2 3 4 5 | SQL> SELECT dbms_undo_adv.longest_query LONGEST_QUERY FROM dual; LONGEST_QUERY ------------- 3040 |
ii. After a certain date,
1 2 3 4 5 | SQL> SELECT dbms_undo_adv.longest_query(SYSDATE-1/24, SYSDATE) LONGEST_QUERY FROM dual; LONGEST_QUERY ------------- 1938 |
iii. Among certain AWR reports,
1 2 3 4 5 | SQL> SELECT dbms_undo_adv.longest_query(21146, 21147) LONGEST_QUERY FROM dual; LONGEST_QUERY ------------- 3040 |
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;
1 2 3 4 5 | SQL> select maxqueryid, maxquerylen from v$undostat where maxquerylen in (select max(maxquerylen) from v$undostat); MAXQUERYID MAXQUERYLEN ------------- ----------- 89w8y2pgn25yd 3040 |
ii. If it is very old, it can be found in the dictionary.
1 2 3 4 5 | SQL> select maxquerysqlid, maxquerylen from dba_hist_undostat where maxquerylen in (select max(maxquerylen) from dba_hist_undostat); MAXQUERYSQLID MAXQUERYLEN ------------- ----------- 1nrby9brx4vvt 3271 |
3. Learning the required RETENTION time according to the longest running query.
i. In the last 7 days,
1 2 3 4 5 | SQL> select dbms_undo_adv.required_retention from dual; REQUIRED_RETENTION ------------------ 3040 |
ii. Between a certain date,
1 2 3 4 5 | SQL> SELECT dbms_undo_adv.required_retention(SYSDATE-1/24, SYSDATE) required_retention FROM dual; REQUIRED_RETENTION ------------------ 1698 |
iii. Among certain AWRs,
1 2 3 4 5 | SQL> SELECT dbms_undo_adv.required_retention(21046, 21047) required_retention FROM dual; REQUIRED_RETENTION ------------------ 1671 |
4. Best possible retention period is learned.
i. In the last 7 days,
1 2 3 4 5 | SQL> SELECT dbms_undo_adv.best_possible_retention best_retention FROM dual; BEST_RETENTION -------------- 119478 |
ii. Between certain dates,
1 2 3 4 5 | SQL> SELECT dbms_undo_adv.best_possible_retention(SYSDATE-1/24, SYSDATE) best_retention FROM dual; BEST_RETENTION -------------- 150919 |
iii. Among certain AWRs,
1 2 3 4 5 | SQL> SELECT dbms_undo_adv.best_possible_retention(21046, 21047) best_retention FROM dual; BEST_RETENTION -------------- 177348 |
5. The required Undo Size is learned according to Best possible retention. (MB)
1 2 3 4 5 | SQL> SELECT dbms_undo_adv.required_undo_size(119478) required_undo_size FROM dual; REQUIRED_UNDO_SIZE ------------------ 32606 |
UNDO ADVISOR can also be viewed through OEM. It will give the same results as the values returned as a result of the query.
1 | Home -> Instances -> Server -> Database Configuration -> Automatic Undo Management |
If Show Graph is opened;
In the System Activity tab;
