000038718 - Oracle 12c TEMP_UNDO_ENABLED parameter for managing GTT UNDO activity in RSA Identity Governance & Lifecycle

Document created by RSA Customer Support Employee on Apr 13, 2020
Version 1Show Document
  • View in full screen mode

Article Content

Article Number000038718
Applies ToRSA Product Set: RSA Identity Governance & Lifecycle
RSA Version/Condition: 7.0.x, 7.1.x, 7.2.x
IssueOracle 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:

CauseThere is a known Oracle defect associated with enabling this parameter.
ResolutionRSA Engineering continues to monitor the Oracle bug for resolution.
WorkaroundIf 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.

NotesWhile 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.