Investigating Redo - Oracle Database Transactions and Locking Revealed - page 142

Starting with Oracle12 c , in an Oracle Active Data Guard configuration, you can issue DML statements directly on
a temporary table that exists in a standby database. We can view the amount of redo generated for a temporary table
in a standby database by running the same code (from the “Prior to 12c” section) against a standby database. The only
difference being the statements issuing transactions against permanent tables must be removed (because you cannot
issue DML on a permanent table in a standby database). Here is the output showing that 0 bytes of redo are generated:

0 bytes of redo generated for "insert into temp"...
0 bytes of redo generated for "update temp set x = 2"...
0 bytes of redo generated for "delete from temp"...

■ there's no need to set TEMP_UNDO_ENABLED in the standby database. this is because temporary undo is always
enabled in an Oracle active data guard standby database.

Note

Global temporary tables are often used for reporting purposes—like generating and storing intermediate query
results. Oracle Active Data Guard is often used to offload reporting applications to the standby database. Couple
global temporary tables with Oracle Active Data Guard, and you have a more powerful tool to address your reporting
requirements.

Summary
In this chapter we investigated how to measure redo. We also looked at the impact that NOLOGGING has on redo
generation. When used in combination with direct path operations (e.g., direct path insert), the generation of redo can
be reduced dramatically. However for regular DML statements, the NOLOGGING clause has no effect.
We explored the reasons why a log switch might be delayed. Oracle won't allow a redo log to be overwritten in the
event that DBWn has not yet finished checkpointing the data protected by the redo log or ARCn has not finished copying
the redo log file to the archive destination. This is mainly a problem for the DBA to detect (inspect the alert.log ) and
manage.
Lastly we discussed how redo is handled with transactions that occur in temporary tables. In 12c and above, the
amount of redo can be reduced to nearly nothing. For applications that use temporary tables this can have a positive
impact on performance.