Oracle 12c TEMP_UNDO_ENABLED parameter for managing GTT UNDO activity in RSA Identity Governance & Lifecycle
3 years ago
Originally Published: 2020-04-13
Article Number
000049772
Applies To
RSA Product Set: RSA Identity Governance & Lifecycle
RSA Version/Condition: 7.0.x, 7.1.x, 7.2.x
 
Issue
Oracle 12c introduced a new parameter called TEMP_UNDO_ENABLED which allows the UNDO space needed for global temporary table transactions to be managed by TEMP tablespace instead of UNDO tablespace. The TEMP_UNDO_ENABLED parameter is defined as:
 
TEMP_UNDO_ENABLED determines whether transactions within a particular session can have a temporary undo log. The default choice for database transactions has been to have a single undo log per transaction. This parameter, at the session level / system level scope, lets a transaction split its undo log into temporary undo log (for changes on temporary objects) and permanent undo log (for changes on persistent objects).

By splitting the undo stream of a transaction into two streams (temporary and permanent), a database can provide separate storage and retention model for these. This results in overall reduction in the size of undo log and redo log in the database

If database applications make use of temporary objects (using global temporary tables or temporary table transformations), it is advisable to set this parameter's value to true.


RSA Identity Governance & Lifecycle makes significant use of global temporary tables. Setting this value to true can result in a performance benefit by reducing the amount of UNDO and REDO activity which in turn reduces I/O including I/O associated with archive redo logs when archiving log mode is enabled. However, RSA has seen issues associated with setting this parameter to true. Here is a list of known issues:
 
Cause
There is a known Oracle defect associated with enabling this parameter.
 
Resolution
RSA Engineering continues to monitor the Oracle bug for resolution.
 
Workaround
If you encounter any of the symptoms reported in this RSA Knowledge Base Article, disable the parameter as follows:
 
Login to SQL as SYS user
  
SQL> alter session set temp_undo_enabled=false scope=both;

By setting scope=both, this parameter will take effect immediately and will remain in effect after a system reboot.

 
Notes
While enabling this setting may provide overall better performance for the system, when the Oracle defect is encountered, this setting needs to be set to FALSE.
Don't see what you're looking for?