RE: Oracle Total Recall performance vs Materialized Views
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <cicciuxdba@xxxxxxxxx>, "'oracle-l-freelists'" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 29 Jul 2013 12:34:40 -0400
Two things seem likely to be useful:
1) Use recall to refresh any materialized view refreshes that fail or simply
use recall to materialize the views. This costs some space and maintenance,
but you don't pay for the recall over and over again as each query is made.
If few queries are made, your least operational cost solution might be
individual recall queries.
2) Move the whole shooting match to a frozen point in time clone of a
physical dataguard database.
Notice that you cancel recovery (for a while, in your case shortly before
the redo through 00:00:00 is applied seems best),
shut down the physical dataguard instance,
clone the dataguard database,
start recovery and roll THE CLONE (which has its own copy of the online redo
logs) forward to exactly the time (or event) you want,
shut down the clone,
startup rename resetlogs THE CLONE,
and then resume recovery on the untouched molested original physical
dataguard database.
This allows you to use the bulk of the horsepower of your dataguard
machinery to execute your point in time queries (which in fact will not need
the point in time specifications at all.)
Since the CLONE is frozen in respect to the production database, the
cautions against preparing aggregations in service of projecting results all
evaporate. Since updates are not being done on the CLONE, the usual
insert/update/delete overhead of excessive indexing goes away as well. Your
mileage may vary. This approach has proved beneficial and workable since at
least 1995. With modern cloning virtualization techniques such as Delphix,
it probably works better than ever because the time to copy would be
reduced. However it just simply works with or without fancy bells and
whistles and it predates oracle dataguard, strictly needing only physical
recovery to a point in time to work for it to work.
If someone thinks this requires re-instantiation of the dataguard database,
the instructions have not been understood.
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Guillermo Alan Bort
Sent: Monday, July 29, 2013 12:00 PM
To: oracle-l-freelists
Subject: Oracle Total Recall performance vs Materialized Views
Hi,
We are having a discussion about the following:
we have a set of tables, from these tables we need to have a snapshot of
the table at 00:00:00 exactly (all the tables at the same time). Right now
we are using materialized views and we refresh them nightly. Of course this
is a weak solution as if any refresh fails, we loose consistency.
as I don't like square wheels and the architect can't seem to build a
round one, I suggested using Oracle Total Recall, which based on the
documentation does everything we need (including guaranteeing consistency
and stuff like that).
The architect claims that the performance on the queries with flashback
(with FDA enabled) is unacceptable for the application. I am certain he
didn't perform any testing, and I will do so as soon as I finish building
the environment, but I'm sure there are some people out there who have
already implemented total recall in production so my question to you is what
is the performance overhead of FDA (I've read about the overhead during
commit, and the restriction regarding DDLs; I'm wondering about the
performance of queries).
Thank you very much,
Alan.-
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
