Database Application Developer and DBA
On History, Basics, and
Network Performance
I recently delivered an online Web seminar on Oracle Database security. At the end of
the seminar, I took questions from the audience. There were many participants—a lot of
questions on Oracle Total Recall—and when
it was done, many unanswered questions.
Until now. The unanswered questions
were forwarded to me, and I’ll be addressing
them here in this column. But before I begin,
here’s a quick overview of what Oracle Total
Recall is and does.
HOW ORACLE TOTAL RECALL WORKS
The purpose of Oracle Total Recall is to
provide long-term flashback query capability
—the flashback query can go many days,
weeks, months, or even years into the past.
Syntactically, an Oracle Total Recall query
looks no different than a standard flashback
query. It uses the AS OF and VERSIONS
BET WEEN syntax in the FROM list. But under
the covers, it operates very differently.
When flashback query was first introduced
in Oracle9i Database, I heard from many
developers asking if this new database feature
could be used to replace their own custom
audit trails. The developers had developed
custom triggers that would save the : OLD
records in an audit trail, and this approach
enabled them to reconstruct the data their
tables contained at any prior point in time.
The problem with this implementation was
twofold. First, coding the query to retrieve the
point-in-time data was nontrivial; it required
a UNION ALL between the current table
and the history table and a messy, complex
WHERE clause to get the right version of a
row. Second, it necessarily made the original
UPDATE and DELETE transactions at least
twice as big datawise as they were before
the addition of these custom triggers, which
resulted in increased response time for the
The purpose of
Oracle Total Recall
is to provide long-
term flashback
query capability.
end users. So, the developers were motivated
to find another approach that was less intrusive and easier to implement.
When they asked, “Can we use flashback
query instead of our own custom audit trails,”
the answer was simply, “No.” There were
a few technical reasons why that was the
answer. The first was that flashback query is
based on UNDO, so to execute a flashback
query on the data as of five hours ago, you
would have to have all of the UNDO generated in the last five hours available online.
Likewise, to execute a flashback query on
the data as of two days ago, you’d need all of
the UNDO generated in the last two days to
be available online. I do not know of many
systems in which the DBA would configure
the UNDO tablespace to be able to contain
two days of UNDO—even five hours is somewhat rare—let alone months or years of
UNDO. The UNDO tablespace would be huge.
Another reason flashback query is not a
replacement for custom audit trails is that
the theoretical limit for a flashback query is
five days, so using UNDO-based flashback
query is strictly limited to the last five days
of uptime for the database. So, even if you
kept the UNDO for a really long time, you still
couldn’t execute a flashback query on data
as it stood more than five days ago.
The last reason is that UNDO-based
flashback query is somewhat nonscalable.
The further back in time you use flashback
query, the longer it takes, because more work
has to be performed. To execute a flashback
query on data as of one hour ago, the data-
base would have to roll back all the blocks it
hit during the query to put them back the way
they were an hour ago. If, for instance, a given
block was modified by 100 different transac-
tions in that last hour, the database would
have to perform 100 rollback operations.
Now, if you asked for the same data as of two
hours ago, the database would likely have to
roll back many more changes to that block—
it would take longer to execute a flashback
query as of two hours ago than it would to
execute a flashback query as of one hour ago.
The further back in time you execute a flash-
back query, the longer it is likely to take for
the query to execute, because there are many
more changes to roll back.
