Redo and Undo - Expert Oracle Database Architecture - page 336

In general, common sense prevails in estimating the amount of redo created. If the operation you perform causes
undo data to be created, then determine how easy or hard it will be to reverse (undo) the effect of your operation.
If you INSERT 2,000 bytes, the reverse of this is easy. You simply go back to no bytes. If you DELETE 2,000 bytes, the
reverse is INSERT ing 2,000 bytes. In this case, the redo is substantial.
Armed with this knowledge, you will avoid deleting from temporary tables. You can use TRUNCATE , bearing
in mind, of course, that TRUNCATE is DDL that will commit your transaction, and in Oracle9 i and before invalidate
your cursors. Or just let the temporary tables empty themselves automatically after a COMMIT or when your session
terminates. All of these methods generate no undo and, therefore, no redo. You should try to avoid updating a
temporary table unless you really have to for some reason. You should use temporary tables mostly as something
to be INSERT ed into and SELECT ed from. In this fashion, you'll make optimum use of their unique ability to not
generate redo.

Starting with 12 c
As you saw in the previous section, when issuing INSERT , UPDATE , and DELETE statements in a temporary table, the undo
for those changes is recorded in the undo tablespace, which in turn will generate redo. With the advent of Oracle 12 c ,
you can instruct Oracle to store the undo for a temporary table in a temporary tablespace via the TEMP_UNDO_ENABLED
parameter. When blocks are modified in a temporary tablespace, no redo is generated. Therefore, when
TEMP_UNDO_ENABLED is set to TRUE , any DML issued against a temporary table will generate little or no redo.

■ By default, TEMP_UNDO_ENABLED is set to FALSE . So unless otherwise configured, temporary tables will generate
the same amount of redo in 12 c as in prior releases.

Note

The TEMP_UNDO_ENABLED parameter can be set at the session or system level. Here's an example of setting it to
TRUE at the session level:

EODA@ORA12CR1> alter session set temp_undo_enabled=true;

Once enabled for a session, any modifications to data in a temporary table in that session will have a subsequent
undo logged to the temporary tablespace. Any modifications to permanent tables will still have undo logged to the
undo tablespace. To see the impact of this, I'll rerun the exact same code (from the “Prior to 12c” section) that displays
the amount of redo generated when issuing transactions against a permanent table and a temporary table—with the
only addition being that TEMP_UNDO_ENABLED is set to TRUE . Here is the output:

3,312,148 bytes of redo generated for "insert into perm "...
376 bytes of redo generated for "insert into temp "...

2,203,788 bytes of redo generated for "update perm set x = 2"...
376 bytes of redo generated for "update temp set x = 2"...

3,243,412 bytes of redo generated for "delete from perm"...
376 bytes of redo generated for "delete from temp"...

The results are dramatic: a trivial amount of redo is generated by the INSERT , UPDATE , and DELETE statements in a
temporary table. For environments where you perform large batch operations that transact against temporary tables,
you can expect to see a significant reduction in the amount of redo generated.