Redo and Undo - Expert Oracle Database Architecture - page 337

■ You may be wondering why there were 376 bytes of redo generated in the prior example's output. as processes
consume space within the database, oracle does some internal housekeeping. These changes are recorded in the data
dictionary, which in turn generates some redo and undo.

Note

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.

Investigating Undo
We've already discussed a lot of undo segment topics. We've seen how they are used during recovery, how they
interact with the redo logs, and how they are used for consistent, nonblocking reads of data. In this section, we'll look
at the most frequently raised issues with undo segments.
The bulk of our time will be spent on the infamous ORA-01555: snapshot too old error, as this single issue
causes more confusion than any other topic in the entire set of database topics. Before we do this, however, we'll
investigate one other undo-related issue: the question of what type of DML operation generates the most and least
undo (you might already be able to answer that yourself, given the preceding examples with temporary tables).

What Generates the Most and Least Undo?
This is a frequently asked but easily answered question. The presence of indexes (or the fact that a table is an index-
organized table) may affect the amount of undo generated dramatically, as indexes are complex data structures and
may generate copious amounts of undo information.
That said, an INSERT will, in general, generate the least amount of undo, since all Oracle needs to record for
this is a rowid to “delete.” An UPDATE is typically second in the race (in most cases). All that needs to be recorded
are the changed bytes. It is most common that you UPDATE some small fraction of the entire row's data. Therefore, a
small fraction of the row must be remembered in the undo. Many of the previous examples run counter to this rule
of thumb, but that's because they update large, fixed-sized rows and they update the entire row. It is much more
common to UPDATE a row and change a small percentage of the total row. A DELETE will, in general, generate the most
undo. For a DELETE , Oracle must record the entire row's before image into the undo segment. The previous temporary
table example, with regard to redo generation, demonstrated that fact: the DELETE generated the most redo, and since