When working with Oracle as a target or source, it is important to have a method in place on how to manage its undo records.
For general information about Undo, see: Managing the Undo Tablespace | docs.oracle.com
For a full guide on determining a good retention period, see Determining the Optimal Undo Retention Period | Doyensys.
Qlik’s Recommendations
Using SHOW parameter UNDO in an SQL Prompt will show all information related to undo:
SQL> Show parameter UNDO
NAME TYPE VALUE
-------- ------------ -------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL>
Oracle automatically tunes for the maximum possible undo retention (900 Seconds) period for fixed-size UNDO tablespaces based on the size and usage history of the UNDO tablespace. It ignores UNDO_RETENTION unless retention guarantee is enabled.
Use the below formula to determine the value for the UNDO_RETENTION parameter (see Determining the Optimal Undo Retention Period).
OPTIMAL UNDO_RETENTION= UNDO SIZE/(DB_BLOCK_SIZE*UNDO_BLOCK_PER_SEC)
Sometimes, the default values are sufficient to cater to the current workloads. However, if they are not, and if UNDO_RETENTION is not set and the UNDO_tablespace was not sized accordingly to the required value, the following error will be shown:
ORA-01555: snapshot too old: rollback segment number 9 with name "_SYSSMU1$" too small most of the time due to LONG running queries or during Full load
The below query will help you identify whether you need to increase the UNDO_RETENTION or add more space to UNDO_TABLESPACE.
SQL> SELECT d.undo_size / (1024 * 1024 ) "Current UNDO SIZE in MB ",
2 SUBSTR (e.VALUE, 1, 25) "UNDO RETENTION",
3 (TO_NUMBER (e.VALUE) * TO_NUMBER (f.VALUE) * g.undo_block_per_sec)
4 / (1024 * 1024 )
5 "Necessary UNDO SIZE IN MB"
6 FROM (SELECT SUM (a.BYTES) undo_size
7 FROM v$datafile A, v$tablespace b, dba_tablespaces C
8 WHERE c.CONTENTS = 'UNDO'
9 AND c.status = 'ONLINE'
10 AND b.NAME = c.tablespace_name
11 AND a.ts# = b.ts#) d,
12 v$parameter E,
13 v$parameter f,
14 (SELECT MAX (undoblks / ( (end_time - begin_time) * 3600 * 24))
15 undo_block_per_sec
16 FROM v$undostat) G
17 WHERE e.NAME = 'undo_retention' AND f.NAME = 'db_block_size';
Current UNDO SIZE in MB UNDO_RETENTION Necessary UNDO SIZE IN MB
------------------------ ----------------- -------------------------
21260 900 51.80859375
Current UNDO SIZE in MB: This shows the space allocated to UNDO_TABLESPACE.
UNDO_RETENTION: This shows how long it will be in the UNDO segments for read consistency in SECONDS.
Necessary UNDO_SIZE: Shows the required size to cater for the current UNDO_RETENTION of (in this example) 900 Seconds.
Please be mindful that you may have to increase the UNDO_TABLESPACE size to cater for a higher UNDO_RETENTION value or set a higher value of UNDO_RETENTION if space is not a constraint.
